]$ ./configure CFLAGS=’-O2′ –with-openssl –enable-thread-safety
]$ time make -j2
real 1m19.751s
user 2m4.832s
sys 0m11.613s
Tuning Postgres
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.
Tombstone
I was browsing around the web and I came across some quotes from Tombstone the movie. If you haven’t seen it then I suggest you go and watch it post haste. Great story and the dialogue is brilliant.
My favourite character was the Doc (Val Kilmer). While watching his character being played out its a good thing to keep in the back of your mind that he knows he’s dying. Some of the lines the Doc gets to say are nothing short of brilliant.
Rss Jobs Search Engine
I have now got over 50 job feeds in the database and I am still looking for more. If you know of an RSS Job feed that is not currently in the database please let me know and I will add it.
Software Raid
If you ever get this:
mdadm: error opening /dev/md0: No such file or directory
do:
ls -la /dev/md0
If the file does not exist create it as follows: (I am assuming your kernel already supports software raid)
mknod /dev/md0 b 9 0
Random Numbers And Shuffling Cards
This was an entertaining read.
Cheating at Cards
I found this as well:
For those wishing to test PRNG’s csrc.nist.gov
Distributed nightmare
This is an interesting read.
Reflections on Brilliant Digital Single Points of Internet 0wnership
Xen Virtaul Machine
I have been using UML for quite a while now. Unlike most people my main reason for using it is security. You can run a UML instance inside a chrooted env as an unpriviledged user which is a bonus because it’s still a grey area if people can break out of a UML instance over the wire and remain connected (correct me if I am wrong). Breaking out into a chroot (/) as a user with no privs would definitely add another barrier to entry (I am not saying it can’t be broke, just that the guy cracking it is on a differnt level from the normal script kiddie at least until it becomes common knowledge how to do it).
Of course using UML has a price. Its slow. The main problem with UML Is IO. It’s really crap with high IO applications like databases. I tried it with PostgreSQL and it was awfull. Too much context switching.
I had heard of Xen on the grapevine and decided to have a look at it to see if it would be any faster and I was pleasently suprised to find that it is as easy to install as a new kernel. It was at this point that I really kicked myself, Xen as it stands today (version 2.0.7) does not support SMP. So I would be losing a processor using it. Unfortunately this is not acceptable on the machine I am on so I decided to postpone my foray into Xen and stick with UML for the time being on this box or at least until Xen domain 0 can support an SMP machine.
I am however tempted to invest in a single proc machine and run Xen on it. I am confident that Xen does what it says on the tin and that performance gains are substantial over UML but I would rather run a vanilla machine on a dual proc than lose one of them.