Quote:
Originally Posted by ekdikeo
I have some extremely slowass old EIDE hard disks, and I get about 9 to 10 hands per second, 3.33Ghz. From the anecdotes I'm seeing here, it seems to be that mysql is spending a huge amount of time CPU bound, which just doesn't seem to make any sense to me, especially considering that I ran a pretty sizeable database getting hits from 30-40 concurrent users regularly on a 33 MHz 486 a few years back. But, I wasn't using anything more complex than "select x from y where z", because I don't know any better.
I have made some small gains on the python side of things, which might translate into larger gains on slower CPUs, but it seems like mysql is using a lot of cpu..
I spent most of yesterday messing with import speed, mostly bulk import. Here's what I found out:
First I did this testing on a pc of similar speed to those of Eleatic Stranger and FileNotFound. Specifically, 1.6 GHz athlon (32 bit, one core), 512 MB, Fedora 9 fully patched. I had not done any tuning when I started this. For this testing I ran the database on my local pc.
Second, for a single file, import speed can vary by more than a factor of 2 based on the game played, the amount of action, etc. So YMMV.
I have about 120k hands in my mysql db and was getting import speeds of about 5/sec. With the test files I selected, I was also getting 5/sec into an empty db.
Importing those same test files into an empty postgres DB got me 12 hands/sec. Playing around with importing a bit moved that 12/sec up to 15/sec. I have made the "playing around" options in the config file, but I have not pushed those changes (see below).
I noticed that the index dropping/recreating is broken on postgres. I will try to fix this right away.
I tried to play with the postgres tuning in the link from fozzy a few posts above. Needless to say, I completely hosed my postgres server and that is when I went to bed. So I cannot test index dropping/recreating on postgres or any of the other postgres stuff until I fix that server.
A note on those tuning instructions. I think those are written for dedicated postgres SQL servers. Unless you have a lot of ram (like the OP of that advice), you will probably get into trouble with those parameters.
So my interim report on this is that postgres might be significantly faster, but don't try to drop the indexes on the bulk import.