Performance in Reports still poor in comparison to HEM1

[rambo00007]

EDIT: Does running Postgres on a SSD have any impact on that matter?

I suspect that would have a positive impact overall but i'm not entirely sure it would add or takeaway performance when querying the database since it seems much of the results are held in memory then written to disk in chunks so I do think there is some disk read/write going on since i see a lot of drive liight activity too. I'd wait for an offical response from one of the MODS since i'd be interested to know the correct answeer.

[ElHive]

I'm asking because i'm running postgres on an SSD.

[rambo00007]

regardless if you have an SSD, EIDE etc would not make any difference with regards to how much memory is consumed. Thinking about it some more, although memory is used for faster retrieval it still has to read from disk to read from the DB files so having an SSD would mean it will read and write much faster than other types of drives resulting in better performance.

[kraada]

What happens is that PostgreSQL, if it can, fits your entire database into your RAM. When it can do that, performance is extremely good - as RAM is very, very fast.

Once your database is too large to fit in RAM, the database needs to be read from your hard drive when queries come in. SSDs are much faster at doing this.

If you only have something like 4G of RAM, by the time your programs are accounted for PostgreSQL might only have something like half a gig to fit in your database (especially if you're trying to 24 table - each table eats up ram!). A database of 50k hands is roughly half a gig - so you can see that as you import a good number of hands, the database is no longer going to fit in RAM somewhat quickly. Then disk access times matter since that is your bottleneck.

[rambo00007]

What happens is that PostgreSQL, if it can, fits your entire database into your RAM. When it can do that, performance is extremely good - as RAM is very, very fast.

IF you have say a 15 gig DB, assuming postgress will know that before hand, is it still going to try and push as much of that into RAM untill it's filled. I'm confused because if that was the case there would never be any available RAM for poker client software. I would of thought that the amount of RAM would be capped based on the work_mem variable providing it hasn't been hashed out in the .conf file to set that limit? I actually thought no data is read and stored in memory untill a query is executed and then freed up once completed then the results written to disk as a temporary cache?

[pt4pt4pt4]

i think i had seen a Paging stat that Postgres uses in swapping portions of memory with your database.


IDK

[tarix]

IF you have say a 15 gig DB, assuming postgress will know that before hand, is it still going to try and push as much of that into RAM untill it's filled. I'm confused because if that was the case there would never be any available RAM for poker client software.

This is not how file caching works in Windows. Memory used for caching files is not allocated and can be ejected for other uses immediately without the need for any disk activity. This is the reason that postgres lets Windows handle all of the database file cache because Windows is far more efficient at it.

I would of thought that the amount of RAM would be capped based on the work_mem variable providing it hasn't been hashed out in the .conf file to set that limit? I actually thought no data is read and stored in memory untill a query is executed and then freed up once completed then the results written to disk as a temporary cache?

Both work_mem and query memory is released by postgres when the query is completed. The only time is should be allocating large amounts of memory for itself is the shared_buffers which are allocated at start-up and during autovacuum.

[rambo00007]

thanks for the detailed response, much appreciated and informative

[ElHive]

PostgreSQL caches data itself so that subsequent runs will be faster than the first run when you're running the same report. That would explain the behavior you're seeing.

Uhm, i think PT4 isn't caching anything on my machine since subsequent runs take the same amount of time than the first run.

Maybe thats why it takes so long in the first place?

[kraada]

When you're on an SSD the bottleneck is your CPU, not disk access.