The following is was what I noticed when comparing particular postgresql configuration parameters for an application I currently use.
This is for a particular select query which is heavily used. It is being run against a 3 column 10 million row table with a non unique index on one of the int4 columns. The query returns (123910 rows). All times are averages of several attempts at running the query and no other activity except that to run the OS was taking place at the time. Where the best and worst times differ by a noticeable amount I have added a second line to the row.
shared_buffers effective_cache
1024 200 Time: 843.300 ms
8192 200 Time: 793.345 ms
16384 200 Time: 813.399 ms
32768 200 Time: 360.111 ms Time: 401.952 ms
65536 200 Time: 379.023 ms Time: 439.706 ms
131072 200 Time: 387.866 ms Time: 404.930 ms
262144 200 Time: 362.229 ms Time: 384.344 ms
327680 200 Time: 359.975 ms Time: 383.517 ms
shared_buffers effective_cache
1024 1024 Time: 840.544 ms
1024 8192 Time: 837.187 ms
1024 16384 Time: 829.379 ms
1024 32768 Time: 826.749 ms
1024 65536 Time: 824.526 ms
1024 131072 Time: 808.480 ms
1024 262144 Time: 825.258 ms
1024 327680 Time: 817.206 ms
We can see here that the shared_buffers has the largest single affect on performance for this select statement. Would increaseing the effective cache setting while using the optimum shared bufffers chage anything?
shared_buffers effective_cache
32768 1024 Time: 361.477 ms Time: 364.341 ms
32768 8192 Time: 383.347 ms Time: 388.539 ms
32768 16384 Time: 422.892 ms Time: 425.068 ms
32768 32768 Time: 356.700 ms Time: 387.811 ms
32768 65536 Time: 360.410 ms Time: 406.472 ms
32768 131072 Time: 423.960 ms Time: 425.333 ms
32768 262144 Time: 386.507 ms Time: 392.272 ms
32768 327680 Time: 383.629 ms Time: 385.097 ms
It is quite plain from above that the most important parameter between shared_buffers and effective_cache for my select query is definitely shared_buffers.
Adding “order by column” and testing
sort_mem
1024 Time: 640.896 ms
8192 Time: 582.495 ms
16384 Time: 505.777 ms
32768 Time: 505.784 ms
We can see here that increasing the sort_mem has a good effect on performance.
Now adding a simple limit clause to the sorted results gives me another boost to the procedure.
limit
50000 Time: 391.164 ms
25000 Time: 350.564 ms
10000 Time: 325.975 ms
1000 Time: 311.234 ms
1 Time: 309.303 ms
We can see here that preparing the results has a large impact in performance. Something else that a lot of people miss is the order the columns are in, in the query used.
For instance asking for the colums in the order they are in in the table we get
Time: 360.379 ms
If we reverse the order of three columns we get.
Time: 405.506 ms
This is a big difference if the query is called a lot.
perl -d:DProf
I have been running a simple search engine tool on UKlug and I have noticed that things are getting a bit sluggish due to the amount of jobs in the database (300K+). Its not an astronomical amount but the method I am using is starting to strain against the hardware. I am going to rewrite it (article for another day) but for now is there anything I could do to speed things up?
When something just isn’t running as fast as expected then its time to break out the Perl profiler. The search engine has a mod_perl front end which is the first pain in the ass. I am fully conversant with the mod_perl performance tuning guide but trying to profile mod_perl is not as straight forward as the guide suggests.
Luckily I always use modules for the bulk of the work on any cgi scripts so I created a mock script to call out to the modules and then ran the profiler against this as a stand alone program.
]$ perl -d:DProf mock_script.pl
This confirmed my suspicion that the main problem was database access. There are a couple of Perl functions that could be faster but tuning these when the database is such a bottle neck would be an exercise in futility. I know I have tuned the database to a point where it is not going to get any faster so everything is pointing at either a faster machine or a rewrite.
It just so happens that I have a faster machine to hand so running the offending SQL with timings on I got the following times.
Slow machine:
Time: 3003.434 ms
Fast Machine:
Time: 1683.190 ms
This is a marked improvement over the slower machine but it still a hellish time to wait for some results that have yet to be displayed. So how can I reduce the time taken to retrieve the results? More to follow.
Raja Fashions
I seen an advert for Raja fashions ( http://www.raja-fashions.com/ ) in the economist the other week. I could remember someone mentioning that there is a group of tailors from Hong Kong who sometimes com over here to ply their trade so I decided that I would go and have a look to see what sort of outfit they operated.
Alarm bell 1.
They had hired a small room in the Hilton and there was absolutely nothing on display except cuts of material.
Alarm bell 2.
The advertised prices in the Economist advert are complete rubbish and a lot lower than what they offered me.
Alarm bell 3.
Ladies tailoring for a suit adds £100 to the price.
Alarm bell 4.
There was another guy being seen to by the man himself and he seemed to be making some noise about the whole setup ie none of the information he was now being told was on the website etc.
Anyway at this point I decided I would rather not have anything to do with them so promptly left.
Alarm bell 5.
When I left the guy came out and asked me if I wanted a cheap suit. I explained that the way he was pressuring the guy in there put me off and no I was not particularly interested in a cheap suit which is why I was leaving. I sincerely doubt I would be getting anything but a cheap suit from him regardless of price paid hence my quick departure.
I would still love a tailor made suit and a full length trench coat but it looks as if I am going to have to head to Saville row rather than fly by night traveling salesmen.