SSD for database

[steel108]

First post, long time lurker.

What SSD is recommended for a PostgreSQL database? I will be using it for online poker; Pokertracker 3 and Holdemmanager specifically.

I had a Vertex 2 and was quite happy with it. I upgraded to the Vertex 3 240GB and it's more like a downgrade imo.

I have no clue if anybody is familiar with the software I am referring to so I'll give it quick synopsis:

Poker sites save the hands you play on your hard drive. What Pokertracker 3 does is read the information contained in the file and store it in a database. You can use this for you HUD (displays stats on people you are playing with in real time), review your play, look at results for tax purposes, etc.....

Each site structures the hand history differently (Pokertracker and read them all) but this is pokerstars:

PokerStars Game #58523843399: Omaha Pot Limit ($5/$10 USD) - 2011/03/02 6:12:39 ET
Table 'Hammonia XI' 6-max Seat #5 is the button
Seat 1: spocker2007 ($1283.60 in chips)
Seat 2: kes1981 ($2141 in chips)
Seat 3: JaGgE_87 ($1161 in chips)
Seat 4: jessica88 ($1400 in chips)
Seat 5: baik2 ($643.90 in chips)
Seat 6: Bagrovui ($2447.50 in chips)
Bagrovui: posts small blind $5
spocker2007: posts big blind $10
*** HOLE CARDS ***
Dealt to kes1981 [4h 7h 3c Ts]
kes1981: folds
JaGgE_87: folds
jessica88: folds
baik2: folds
Bagrovui: raises $20 to $30
spocker2007: calls $20
*** FLOP *** [Ac 8h Kd]
Bagrovui: bets $50
spocker2007: calls $50
*** TURN *** [Ac 8h Kd] [Th]
Bagrovui: checks
spocker2007: bets $50
Bagrovui: calls $50
*** RIVER *** [Ac 8h Kd Th] [Td]
Bagrovui: checks
spocker2007: bets $257
Bagrovui: folds
Uncalled bet ($257) returned to spocker2007
spocker2007 collected $257 from pot
*** SUMMARY ***
Total pot $260 | Rake $3
Board [Ac 8h Kd Th Td]
Seat 1: spocker2007 (big blind) collected ($257)
Seat 2: kes1981 folded before Flop (didn't bet)
Seat 3: JaGgE_87 folded before Flop (didn't bet)
Seat 4: jessica88 folded before Flop (didn't bet)
Seat 5: baik2 (button) folded before Flop (didn't bet)
Seat 6: Bagrovui (small blind) folded on the River

While you are playing each table you are on will have a file that is constantly being updated with hands you have played. So if I'm on 6 tables, I will have 6 files; after a few hours each file will have 100-200 hands. Pokertracker is importing the hands in real time.

Another import is a bulk import. If you are starting a new database, you can import all the hands at once.

TBH, I have no clue what benchmark is most important. One thing I can say is that whatever improvements were made in the Vertex 3 didn't translate over for my purposes. I have only seen a slight increase in import speed and database functionality. Any advice would be greatly appreciated. If more info is needed, just let me know and I'll do my best to answer.

 

[Stuka87]

How big is the database? Unless you have a database that is huge, anything thats accessed will be stored in memory (assuming you have enough memory). In my experience, memory is a better thing to invest in than storage until the database grows over a given size.

 

[steel108]

How big is the database? Unless you have a database that is huge, anything thats accessed will be stored in memory (assuming you have enough memory). In my experience, memory is a better thing to invest in than storage until the database grows over a given size.

My database is currently 5.92GB, but will continue to grow. I have 16GB of RAM installed right now.

Might as well give my PC specs:

2600k @ 4.6GHz

16GB DDR3 1600MHz RAM

ASUS P8P67 Pro mobo

Corsair H100

240 GB OCZ Vertex 3

I have Windows 7 64 bit installed on the Vertex 3 along with all the software (postgreSQL, Pokertracker, pokersites, etc) I use for poker. The rest is on a 2nd HD.

 

[beginner99]

How big is the database? Unless you have a database that is huge, anything thats accessed will be stored in memory (assuming you have enough memory). In my experience, memory is a better thing to invest in than storage until the database grows over a given size.

Poker databases can be huge if the according user plays a lot. several million hands played is not uncommon and considering it tracks not only your own stats but all the players you have played with... the schema of PT 3 is also rather complex and I assume there are lots of joins and other rather slow SQL's in the application.

@ OP
Vertex 3 "sucks" in i think 4k random reads and I would guess that's what is used a lot on such a scenario. The old crucial c300 was king there. maybe have a look at that one but that is just a pure guess.

Other suggestion are to clean the database and probably more important check Postgres manual for optimizing. Maybe there is a settign that limits Memory usage to 200 Mb or something? I know MySQL has a ton such settings which can have a HUGE impact on comparable small datasets.

Other common suggestion is to split your database for each level / play-type. Eg one for NL 100 one for NL 200 one for PLO 200 and so forth.

 

[velis]

Just look at a recent review and take the SSD that has the highest random read and random write numbers.
How you weigh these two numbers depends on access scenario of your database. What percentage of read vs write does your DB have? That is a figure only you can tell.

 

[steel108]

I tweaked the postreSQL configuration file already based on my specific rig.

I have heard that 4k read and write speeds were the main concern for databases. It seems that the new SSDs aren't really going to help databases. After a ton of searching, the best result is 83 (compared to 22 for the vertex 3). The price tag doesn't justify a purchase (8.5k) considering the numbers aren't mind blowing.

Poker databases can be huge if the according user plays a lot. several million hands played is not uncommon and considering it tracks not only your own stats but all the players you have played with... the schema of PT 3 is also rather complex and I assume there are lots of joins and other rather slow SQL's in the application.

@ OP
Vertex 3 "sucks" in i think 4k random reads and I would guess that's what is used a lot on such a scenario. The old crucial c300 was king there. maybe have a look at that one but that is just a pure guess.

Other suggestion are to clean the database and probably more important check Postgres manual for optimizing. Maybe there is a settign that limits Memory usage to 200 Mb or something? I know MySQL has a ton such settings which can have a HUGE impact on comparable small datasets.

Other common suggestion is to split your database for each level / play-type. Eg one for NL 100 one for NL 200 one for PLO 200 and so forth.

 

[razel]

You are relying too much on hardware to solve what sounds like a program that's not optimized. Please be absolutely sure that it's random disk I/O that's holding you back and not Pokertracker 3's affinity to constantly 'read a file.' If the file fits, then I'd test putting that file in RAM to see if there's any benefit. Otherwise, disk I/O is clearly not the issue.

 

[steel108]

You are relying too much on hardware to solve what sounds like a program that's not optimized. Please be absolutely sure that it's random disk I/O that's holding you back and not Pokertracker 3's affinity to constantly 'read a file.' If the file fits, then I'd test putting that file in RAM to see if there's any benefit. Otherwise, disk I/O is clearly not the issue.

I have been running import tests of 1 million hands with Pokertracker 3, Pokertracker 4, Holdemmanager, and Holdemanager 2. There isn't much improvement in performance on the Vertex 3 across both platforms and both generations.

I'm willing to try anything, how do I test it out on RAM?

 

[Emulex]

ramdisk

 

[jdstern]

As a DBA (yup they do exist), one of the biggest misconception is that a poorly architect database can be fixed with more hardware (there are exceptions). As pointed out by some posters above, the problem is more than likely with the database design or the application. You might contact the software vendor about how to tune their software, by adding indexes, partitioning tables or even optimizer hinting. The first thing you need to do before you buy any ssd, is determine where you bottleneck is. Is it the drive, the controller, the database? Is the query doing a cartesian join and generating a crap load of undo/temp data? And there is probably another dozen potential things to check.