Raid installed and no performace improvement at housekeeping

[WhiteRider]

Try the ReIndex option in housekeeping after updating postgres.
I don't really know anything about Raid performance, I'm afraid.

[orbit09]

Hi,

i´m always try some test to find out the correct configuration. I hope i´ll finish it until friday. At the moment it looks like that it is not a postgres or PT Problem, but i´, not sure. If i have finished my test and have a detail result i let you know.

[kraada]

Please do keep us posted.

[orbit09]

Of course.
I finished my test and found some intresting thinks. (Sorry for my bad english).

My enviroment.

Mainboard (Asus M2NPV-VM) with AMD 64 X2 Dualcore 3800
Operation System Windows 7
Disk Samsug HD103UI (Datatransferate up to 100 MB/sec).

Some transaction with PT took a long time, that was the reason why i bought 2 additional disks (Samsung HD502HJ) with datatransferrate up to 130 MB/sec. Both disk were via internal raid controller bundled to raid 0. Yes raid 0. I was not afraid about data lost, because i regular backup my system.
The raidsystem was detected by windows 7 as disk D: and my first test with HD tune show very high datatransferrate, up to 260 MB/sec.

With this information, i expected, that PT and Postgres will work must faster and i can reduce my waiting time to abount the half.

I stopped all postgres processes and PT. Then i moved the base directory to D: and i created a link (with mklink) in the original directory.

Then i started all processes and upps the transactiontime was the same.

Now the analyse process was started.

First of all, PT logfile shows that the query-time and the Populate-time were nearly the same.
At this point i was surprised. There are two times, query-time and populate time. i should find out the differences and the effect to my expectations.
To do this i started the resourcenmanager of windows 7 and monitor the disk io operations and retry my test.

Now the real values of disk io with postgres shows surprising things:

One disk system: database activity for about 80 sec: datatransferrate as expected 100 MB/sec, then no diskactivity but PT was not ready. After 600 sec PT was ready and showed the results.
In the PT logfile the query-time was about 84 sec and the populate time about 603 sec. O-k. now i knew query-time and populate time and that diskspeed improvement can only reduce query time.
But one question still exist, why is the query-time the same at one disk system and two disk system, wuith the second as raid.

Raid-System: database activity for about 80 sec: datatransferrate, upps only 100 MB/sec, not the expected higher transferrate. It looks like, that my raid-system together with postgres only use one disk speed. Then i tried some test with other postgres parameter and other raid0 striping parameter but without success. Only about 100MB/sec.

Sub-Result:
If you like to improve your PT speed, first analyse were the bottleneck is. Is it the database, then raid-system can improve your speed, Is it PT then a raid system can not improve the speed.

After all these test, i delete my raid configuration and use the new two disk as separate disks. On one of them i have moved the base directory of postgres, because i saw, that some temporary file and logging information are written when the database was read.
With this configuration and monitoring via resourcenmanager i saw the high usage of disk at D: and the query time was reduced to 70 sec, which meanse about 12,5 %.
The populate time is always the same about 600 sec.

Endresult:
It is not enough to expect speed improvement when you will use an raid system. It is the question, at which point do you need improvement. The second is, sometimes a mainboard raid controller will not work as expected. A normal sequentiell benchmark is not enough to ensure the speed with a database.
From my point of view i need the improvement at the populate time and this can only be done if i buy a new processor or better a complete new computer, with both a raid controller which is designed for databases and a high speed processor which dramaticly reduced the populate time.

@WhiteRider and kraada: Many thanks for all your help and if you need further information, don´t histate and contact me via direct mail.

[kraada]

Thank you for this, I'll make sure the development team sees the thread and your results.

[zubs1aa]

Of course.

After all these test, i delete my raid configuration and use the new two disk as separate disks. On one of them i have moved the base directory of postgres, because i saw, that some temporary file and logging information are written when the database was read.
With this configuration and monitoring via resourcenmanager i saw the high usage of disk at D: and the query time was reduced to 70 sec, which meanse about 12,5 %.
The populate time is always the same about 600 sec.

Endresult:
It is not enough to expect speed improvement when you will use an raid system. It is the question, at which point do you need improvement. The second is, sometimes a mainboard raid controller will not work as expected. A normal sequentiell benchmark is not enough to ensure the speed with a database.
From my point of view i need the improvement at the populate time and this can only be done if i buy a new processor or better a complete new computer, with both a raid controller which is designed for databases and a high speed processor which dramaticly reduced the populate time.

@WhiteRider and kraada: Many thanks for all your help and if you need further information, don´t histate and contact me via direct mail.

What gates/bottlenecks hte speed of the population? is it solely CPU? or is it the the speed of the principal drive where PT3 (not the DB, but PT3 itself) is running?

Here is why I ask- you missed an option: replace OS / PT3 drive with a Solid State drive. I did this in the last week or so since I didn't want to shell out big $$ for a new machine and I can't upgrade processors. I don't have an actual benchmark apart from the speed of the SSD vs the speed of my prior old HDD, but subjectively, my OS runs 2x as fast (at minimum) as before, and PT3 runs significantly faster most of the time. (Though initial population of a tab takes forever and a day still which I don't understand, but that may be unrelated.)

Anyway, OP, I'd look at the spec on your primary HDD, that holds your OS and PT3. What speed is it running at? Even the cheapest SSD are running at nearly 180-200 MB/sec.. but also since they are headless and never fragmented in essence are probably effectively gathering/writing info at 10x the speed of an HDD.

I spent ~$300 for a 128GB SSD, which is a bit more than I needed - you can certainly get smaller or spend less, but it has made miraculous differences.

In addition to speeding PT3, since PT3 and all the poker clients are all installed on my C: drive (with the DB on another HDD) it has massively improved the performance of my poker clients as well as well as PT3 itself. For instance, I now have zero lag ever with the Rush HUD.

Anyway, thought I would through that idea at you OP ...

[zubs1aa]

Late Edit. I might have quoted the specs wrong. WHile these drives are specd at "continuous sequential R/W speeds of nearing 200mb/sec, they are also running at or near the full sata II spec of 3 GB/sec transfer. I'm not sure I know the difference between the 2 different specs, but you might.

[orbit09]

From my point of view, zubs1aa, I think there is a misunderstanding.
Speed improvement via SSD can only be done, if diskactivity is needed. In the phase of population of the result i could not see any diskactivities, so SSD can not help.
In the enviroment, which do you describe, i can believe, that SSD can improve the speed, but in my test scenarios, analysing the plays of opponents via reports SSD do not help.
In my normal pokersituation with HUD i don´t have any performance problems, but to get the play information about the active players at the table, i have created some reports to see their playing style and hand selection. This reports take a long time and that was the reason, why i looked for a quicker solution.

[zubs1aa]

From my point of view, zubs1aa, I think there is a misunderstanding.
Speed improvement via SSD can only be done, if diskactivity is needed. In the phase of population of the result i could not see any diskactivities, so SSD can not help.
In the enviroment, which do you describe, i can believe, that SSD can improve the speed, but in my test scenarios, analysing the plays of opponents via reports SSD do not help.
In my normal pokersituation with HUD i don´t have any performance problems, but to get the play information about the active players at the table, i have created some reports to see their playing style and hand selection. This reports take a long time and that was the reason, why i looked for a quicker solution.

Interesting. I didn't (and don't have an understanding) of what all the pieces are that take the time to populate a report. (Though i admit by population I thought you meant waiting for one of the PT tabs to fill.) While CPU is an issue, I also assumed that there was by nature a significant amount of disk activity in populating anything as it had to go out and slice through the DB.

GL with a solution for that. the population times you mentioned were clearly far too long....

[kraada]

When it says "Populating" it means that PT3 is actually formatting the report; the SQL query is run during the time it says "Loading".