PDA

View Full Version : Hand Import: Performance



dvvv
10-01-2009, 08:42 AM
Hi!

I have quite a fast system corei7 4Ghz and etc. running windows7 64bit. But the speed of the import when DB is more than 10GB is slower that i would like to have.

It checks whether that hand already exists in the DB and importing if not. That query is taking longer with each additional GB, but what is the main reason why speed decreases significantly after DB reached certain size, but then speed stabilyzes at about 20h\sec.

I exported all hands (9GB hand history) and importing them once again now, starting speed was about 150h\sec but now it is much slower.

Maybe you know what part of the import process is bottleneck here?

http://img25.imageshack.us/img25/3218/rese.png

http://img81.imageshack.us/img81/2593/98036595.png

From the attached screenshot you can see that CPU and hard drive is not used even close to 50%. For me it looks like my system is not used as much as it could. There is a lot of resources hanging around and nobody wants to use them. :confused:

Thank you for reading till the end. :)

netsrak
10-01-2009, 10:14 AM
Thats a very specific question, in general a relational database becomes slower with growing data tables. But i'll forward this to fabio for a better reply.

Rvg72
10-06-2009, 06:22 PM
It is hard to say what the issue is but 20h/sec is pretty slow for even a weaker PC. We are going to be making some changes to the importer module in an upcoming build so that it takes advantage of multiple CPU's - currently most of the work is done on just 1 CPU so if you have a quad core system then there is a lot more room for improvement

Roy

dvvv
10-09-2009, 02:26 PM
Hi,

I decided to play a different limit in parallel of NL200 FR on Full tilt - NL25 on Party. And while importing the hands speed was just awesome.

http://img515.imageshack.us/img515/323/nl25import.png

I didn't install any updates or run any processes against DB (cleanup, etc) or changed config of DB. It looks like speed is also dependent on the size of the DB for specific limit OR parser of the hands.

Maybe this is valuable info for you.

dvvv
10-19-2009, 01:06 AM
Hi!
Maybe you have info when upcoming module to increase performance on multiple CPU's will be ready.. e.g. which HM version?
Thanks

netsrak
10-19-2009, 03:39 AM
After releasing full version 1.09 (which was planned for last week til the virus problems hit us) we will start adding new features in the 1.10 betas. The import tuning will probably be one of it.

fifilein
11-23-2009, 06:36 PM
It is hard to say what the issue is but 20h/sec is pretty slow for even a weaker PC. We are going to be making some changes to the importer module in an upcoming build so that it takes advantage of multiple CPU's - currently most of the work is done on just 1 CPU so if you have a quad core system then there is a lot more room for improvement

Roy

i noticed something similar here. i am also trying to im port 5mio hands (~6gb) and its a painfull process.

Athlon X2 64 ~3Ghz; 4GB; WD 1,5TB (no super fast), Win 7

I seperated the files into several directories/batches and the import seem to start fast whenever i start a directory first (~300h/s); this is not a measurement error as it also shows "file imported" growing fast. then time by time it slows down to even 15-20hands/second.

i also moved the HH files to the network, so the HDD can keep its position at the database, should have helped something.

i took now on some tuning tips (not storing the handhistory for observed hands) and this is helping; disk i/o according to performance monitor is around 2* before, this correlates to currently 32h/s to before around 15-20h/s.

cpu is bored to death, but what i dont get is the following, according to performance monitor postgresql is writting several mbit/s - how can you make out of 27hands several mbit per second!!! is it reading the complete database into ram, then changing one hand, then writing back? either this is a postgresql tuning problem (file allocation?) or hem is using a very, very! bad algo.

i was thinking on buying 8gb/16gb of ram, make a ramdisk and let the database work from there (mirrored to harddisk).

Patvs
11-23-2009, 07:47 PM
according to performance monitor postgresql is writting several mbit/s


I've seen a HUGE degration if the 5 million hands you're importing are stored in hundreds of these big 10+ MB files. It's a lot faster if it just imports tens of thousands of <100 kb files.



Can you test the speeds of your harddisk with CrystalDiskMark (http://www.brothersoft.com/crystaldiskmark-download-144091.html)
It should look like this: (note: set test size to 500 MB, not to the default 100 MB as used in pic)

http://i234.photobucket.com/albums/ee183/Fire-Wizard/CrystalDiskMark-1.png


Pic above is the Intel X25-M G2 Postville SSD.
Pic below is a fast 7200 RPM normal harddisk.

http://crystalmark.info/software/CrystalDiskMark/images/CrystalDiskMark22.png


Original topic poster dvvv had 10MB/s. During these large imports PostgreSQL is relying on the 512K and 4K read/write speeds of your harddisk (I have 2x SSD in RAID0)

fifilein
11-24-2009, 04:49 AM
I've seen a HUGE degration if the 5 million hands you're importing are stored in hundreds of these big 10+ MB files. It's a lot faster if it just imports tens of thousands of <100 kb files.

files are several thousands of around 1,3mbyte, stopped yesterday at around 20h/s.




http://i234.photobucket.com/albums/ee183/Fire-Wizard/CrystalDiskMark-1.png

Pic above is the Intel X25-M G2 Postville SSD.
Pic below is a fast 7200 RPM normal harddisk.

http://crystalmark.info/software/CrystalDiskMark/images/CrystalDiskMark22.png


http://i48.tinypic.com/an2exy.jpg

Athlon X2 64 7750 2,7Ghz, 4GB Ram, WD15EADS; Win 7
(not the fastest HDD, but ok)



Original topic poster dvvv had 10MB/s. During these large imports PostgreSQL is relying on the 512K and 4K read/write speeds of your harddisk (I have 2x SSD in RAID0)

thats my whole point, what is the real data amount we are processing, somewhere in the KB/s area (btw. i am currently running at 10hands/second); and look how much pstgresql is writing; there is just a HUGE gap between the data processed and the amount of bits written. (this is even byte/second)

changing HDDs to SSD for a single user Database App looks more like working harder instead of smarter; not so long ago you would have been happy to have the machine i am currently having below my desk to serve quite a big amount of users...

http://i50.tinypic.com/2hqtf7d.jpg

btw. how many data fields does one hand have?

Patvs
11-24-2009, 05:14 AM
I have no idea how efficient the SQL importing is and if using another algorithm can improve this. I do know, during these large imports, my CPU is only strained 2%, and my harddisk is doing all the hard work.

"how many data fields does one hand have?"

1 million hands take up around 10 GB of space in the PostgreSQL database.
So your 6GB of handhistories (5 million hands) can end up to become a 50 GB database. (vacuum, etc. can decrease this size AFTERWARDS by 50%, purging hands makes it even smaller)

"several thousands of around 1,3mbyte"

So the data amount to be processed (both input and output) is significantly large. And most of it relies on the 4K read/write speed where you have 0.545MB/s (I have 26 MB/s)
Did you change some settings in the postgresql.conf file and rebooted, to improve performance? Start by increasing the size of your "shared_buffers = xMB" and set it to 25% of your RAM.


Edit:

shared_buffers = <num> — Editing this option is the simplest way to improve the performance of your database server. The default is pretty low for most modern hardware. General wisdom says that this should be set to roughly 25% of available RAM on the system. Like most of the options I will outline here you will simply need to try them at different levels (both up and down ) and see how well it works on your particular system. Most people find that setting it larger than a third starts to degrade performance.

effective_cache_size = <num> — This value tells PostgreSQL's optimizer how much memory PostgreSQL has available for caching data and helps in determing whether or not it use an index or not. The larger the value increases the likely hood of using an index. This should be set to the amount of memory allocated to shared_buffers plus the amount of OS cache available. Often this is more than 50% of the total system memory.

The default setting is 16 MB (or 32 MB) for shared_buffers...

fifilein
11-25-2009, 07:29 AM
I have no idea how efficient the SQL importing is and if using another algorithm can improve this. I do know, during these large imports, my CPU is only strained 2%, and my harddisk is doing all the hard work.

....

Edit:

shared_buffers = <num> — Editing this option is the simplest way to improve the performance of your database server. The default is pretty low for most modern hardware. General wisdom says that this should be set to roughly 25% of available RAM on the system. Like most of the options I will outline here you will simply need to try them at different levels (both up and down ) and see how well it works on your particular system. Most people find that setting it larger than a third starts to degrade performance.

i will try to work out this settings;

just couldnt let go to take a look at your algo :-) i was just tracing all sql commands during the import of one hand and try to understand it with the table names you are using.

seems you have a standard process for importing "1 hand history" and you apply that wheather you import 1 hand or 1000hands at a time, which means you have the overhead of summarising some stats 1000times instead of 1time at the end of the import (eg: you update "compiled results" after each hand which is importet; correct vpip, pfr, even all in stats, monthly stats).

i am not aware of the exact data structure so it would be guessing to give any hints, but a first look gave me around 40% of sql statements which could be save by optimising the import process (caching some tables in memory; or updating them after the import if data is available in the database)

Patvs
11-25-2009, 08:35 AM
I think PokerTracker 3 does this.
You can get 400 hands/s import speeds, but after a large import you're required to do "a cluster check, vacuum, analyse, and update cache" which takes HOURS. And in the end, it takes up the exact same amount of time.

But you bring up an interesting point.... maybe it can decrease all the agony, if you can do the cluster check manually afterwards. Psychologically you'll feel a lot better if you import for one hour with 100 hands/sec and a 2 hour maintenance cluster check afterwards, than importing for 3 hours with only 33 hands /s. :)

fifilein
11-25-2009, 10:27 AM
I think PokerTracker 3 does this.
You can get 400 hands/s import speeds, but after a large import you're required to do "a cluster check, vacuum, analyse, and update cache" which takes HOURS. And in the end, it takes up the exact same amount of time.

But you bring up an interesting point.... maybe it can decrease all the agony, if you can do the cluster check manually afterwards. Psychologically you'll feel a lot better if you import for one hour with 100 hands/sec and a 2 hour maintenance cluster check afterwards, than importing for 3 hours with only 33 hands /s. :)

this is interessting, alltough i am not convinced to be honest.

when doing mass data import you try to (if possible) get in as much data as possible just by writing and do the whole indexing afterswards. i am not talking abount vacuuimg now, program relevant data!

there are 2 reasons, i will give the example for vpip, ok?

2 assumptions
- information to calculate vpip is stored in other tables
- logic can be applied afterwards and is not dependend on former data

a) calculate vpip after each hand

repeat
1) read hand history (no not sql)
2) write detail table whatever
3) update vpip from summary table (+1)
which means 3a) postgres reads table
3b) postgres writes table
until finished

this means you have X * 2writes and X * 1 read

b) calculate vpip at the end

repeat
1) read hand history
2) write detail table whatever
until finished
3) update vpip set where... (i am sure you can get the whole stuff in one sql update statement) best try to get the whole statistics for all players in one line

so you have X * 1 write and 1* read (over table)/write

this reduces tremendeous overhead and has 2 main points
a) postgres just writes data, can therefore far better utilise caches, as they are not getting filled with read data
b) letting postgres do the update afterwards, is X* faster then reading each value at once (but higher cpu intense)

---

of course it depends how your algo looks exactly, i did a short runtime analyses; 20% of all transactions (assumption read=write time) are "update x+1" (taken from postgres statistics). so we should be talking about 20%+ here when optimising that (conservative assumption)

but this will be anyhow be analysed when you decide to go for faster import with 2.x :) so for the time beeing it will take december to import my hand histories :mad:

Patvs
11-25-2009, 12:36 PM
I've emailed your last two posts to the software developer.

dvvv
11-25-2009, 01:39 PM
I have related question... why is it dependent on 4kb read / write speed? Can it read the whole file(s) at once and then parse that?

Patvs
11-25-2009, 02:15 PM
I think it reads it hand for hand in a file.
And after every file only updates the displayed hands imported // hands/s info
If a file is 10 MB, it will import "part 1 of large file", "part 2 of large file" etc.

Every actual hand is only 1k of data.

fifilein
11-25-2009, 02:35 PM
I have related question... why is it dependent on 4kb read / write speed? Can it read the whole file(s) at once and then parse that?

reading of the HH isn't the problem; this is more or less linear read and thats no leak as we would say in poker language (i put it on the network and the transfer for hh->hem is marginal )

the whole filetransfer from postgresql/database file is the issue here

dvvv
11-25-2009, 02:37 PM
Maybe if it reads the whole file to memory that will be much quicker.

I think the issue is that same algorithm is used for "import from folder" and auto import.

For HUD to be working as it should it is "critical" to have updates after each hand that is why it is designed to read hand by hand.

But for Import from folder it is not critical, is it possible to separate these 2 features? I think all users can have a huge benefit from that.

dvvv
11-25-2009, 02:40 PM
reading of the HH isn't the problem; this is more or less linear read and thats no leak as we would say in poker language (i put it on the network and the transfer for hh->hem is marginal )

the whole filetransfer from postgresql/database file is the issue here

mm... it depends
If it reads 1 hand, than does INSERT and other stuff, then reads 2nd hand and repeats all the stuff - this could be the case

fifilein
11-25-2009, 02:50 PM
mm... it depends
If it reads 1 hand, than does INSERT and other stuff, then reads 2nd hand and repeats all the stuff - this could be the case

you didnt read the postings in this thread, did you?

dvvv
11-25-2009, 02:56 PM
you didnt read the postings in this thread, did you?
sorry, I missed the first post on 2nd page.

Rvg72
11-25-2009, 03:28 PM
The general process for the import is

1) read a hand history file, parse all the hands - this is normally extremely fast

2) go through each hand creating queries for all of the detail data. Once the "batch" gets to a certain size (which was determined using some tests on a typical system) it sends them all at once to the database. This process is repeated multiple times for a file if it is very large - most hand history files will probably be done in 1 or 2 batches if I recall correctly.

3) Save all the aggregated data for the hand history file

So we are batching just about everything. The problem right now is two fold.

1) we are not taking advantage of multiple core CPU's during the reading / parsing

2) the aggregated stats are aggregated per month for read performance however what this does do is it creates a very high % of Update statements rather than Inserts and Updates are much slower. This is what you might notice that if you import stats from a different limit or on the first couple of days of the month they import really quickly because the Insert % is close to 90% instead of 10%.

We are working on a bunch of things to make big performance improvements in a future update (focusing on the two problems I mentioned) but based on this info I'd be happy to hear if you have any other thoughts.

Roy

fifilein
11-25-2009, 04:04 PM
hey roy,

cool interesting answer; i found this issue interesting, so i looked today morning at it a little bit more in details.


The general process for the import is
2) go through each hand creating queries for all of the detail data. Once the "batch" gets to a certain size (which was determined using some tests on a typical system) it sends them all at once to the database. This process is repeated multiple times for a file if it is very large - most hand history files will probably be done in 1 or 2 batches if I recall correctly.


i think at 2) we have the problem, you are not treating it as "mass import" you are batching, yes - but you are batching 1000 times 1 hands; not 1 time 1000 hands (in terms of sql statements)



So we are batching just about everything. The problem right now is two fold.

1) we are not taking advantage of multiple core CPU's during the reading / parsing

2) the aggregated stats are aggregated per month for read performance however what this does do is it creates a very high % of Update statements rather than Inserts and Updates are much slower. This is what you might notice that if you import stats from a different limit or on the first couple of days of the month they import really quickly because the Insert % is close to 90% instead of 10%.

We are working on a bunch of things to make big performance improvements in a future update (focusing on the two problems I mentioned) but based on this info I'd be happy to hear if you have any other thoughts.

Roy

ad 1) should not really be a problem, the cpu utilisation even on one core is near zero; as you wrote reading/parsing is not the bottelneck

ad 2) i think here is the problem in your algorythm; i took quite a "detailled" look at your process (via sql tracer) and saw you make the following,

you have some columns where you store the consolidated stats; everytime you import a hand, you have several update statement (eg. update table.with.stats set vpip=vpip.old+vpip.hand), as you wrote - update statements are horrible

this is the result of database activites for import of x hands (don't know, but the relationship is important)
http://i50.tinypic.com/1z5iwsz.jpg

first) it seems that in some tables the same amount of operations is performed. do you store data twice, or is it a coincident?

so there are some possibilities to get red of all the update statements during importing (dont know if possible as of data size, memory, ...)
a) cache the complete "stat" table in memory and write back at end of importing process (outch); in my database those sume up to around 500mb and i have 1mio hands (alltought they dont scale liner)
aa) you could just store the "updates" in memory and writen them back at the end, tough, you would need to implement some kind of "page fault logic"
b) you could even think about creating flat files and importing them in a batch file, tough - not really the usual way
c) create a mass import algo; eg: if you can get vpip from data stored in any other tables; get rid of the vpip update statement during process and summarise the vpip with and update/select statement. this should remove a lot of update statements during import and improve speed drastically.

i think it would even make sense to go into details and processing time by table when importing, but i am not sure if its really possible, at least not with postgresql standard.


looking forward for +50%


btw. did i earn already my free licence for the table scanner? :D

fifilein
11-25-2009, 04:33 PM
http://planet.admon.org/howto/postgresql-database-performance-tips/

especially those three, i think they fit perfectly to the problem:

Never use many small selects when one big query could go the job.

Group many small UPDATES, INSERTS or DELETEs into large statements, or failing that, large transactions.

Consider bulk loading instead of serial INSERTS