System

Windows 7 Pro x64
i7 4770k
32GB of RAM
Asus R9 270
Samsung Pro 850 256GB
Samsung EV 840 500 GB

Background

I have two pokertracker databases. One containing my complete playing history is 125GB big is the one I use for complete accounting and comparative stats across time and another containing the most recent hand histories is the one I use for my HUD. The former is saved on my D Drive, a Samsung Evo 840 and the latter on my C Drive, a Samsung Pro 850.

The Problem

As the size of the databases has increased so did the time to load data. This became very cumbersome when I wanted to compare stats across different timeframes, especially on the larger database where it took minutes to switch from one dataset to another.

Steps I took before buying the new Drive

- I experimented a ton with the Postgresql config file. Nothing made an appreciable difference.

- I tried all the housekeeping options: reindexing, vacuuming and analyzing and finally clustering. Clustering ended up making a huge difference which is something I didn't expect and I hadn't tried before, because I had read that clustering wasn't needed if you had an SSD. Well, it does. It cut the loading times for my large database from 10 to around 2 minutes. Huge improvement.

Benchmarking

The Advanced Tab of my Stats tab has 38 columns and 6 rows.

It takes 1m 15s to load my small database.

It takes 1m 55s to load my big database.

Getting the Intel Optane 900p

Since the Hard Drive is supposedly the biggest bottleneck for pokertracker database speeds and the newly introduced Intel Optane 900p is the fastest SSD on the market being 3-5 times faster than the fastest NVME drive, I thought it would make an appreciable difference.

However, on the Intel 900p:

It takes 1m 15s to load my small database, i.e. no difference at all.

It takes 1m 47s to load my big database, i.e. the super duper fastest drive ever only shaves 8 seconds.

That doesn't justify the cost. I would expect that loading times would be bellow 1m for both databases.

Am I doing something wrong?

I downloaded and installed Intel's NVME and 900p drivers.

I double checked that the PCI express slot the drive is installed in is set to 4x.

I did benchmark the drive. Sequential speeds seem less fast than I would expect (around 1500MBs instead of 2200 MBs), but it's still 3 times faster than the other drives.

I suspect that me having an older 1150 motherboard and using Windows 7 instead of Windows 10 might be the problem, but I don't think the possibility is that high that either of those is the problem.

It needs to be said that all reviews of the Intel 900 state that most users won't be able to see a noticeable difference in day to day activities. But if you are only able to see an 8 second difference when loading a 125GB database, who can see enough of a difference to justify the cost?

I am afraid that this is as good of a performance as I can squeeze out of this drive. Question is, have I missed anything?

PS. Crossposting at the Pokertracker forums as well.

please wait for someone more knowledgeable than me to respond, but my understanding is that optane is mostly only significantly better specifically at low queue depths. see some results from tomshardware benchmarking:
https://img.purch.com/r/700x450/aHR0...dlMDA3LnBuZw==
http://www.tomshardware.com/reviews/...nt,5292-2.html

optane seems to mostly only crush the competition in sequential reads at very low queue depths. optane does hold onto the lead in random reads until higher queue depths.

i really don't know with any certainty, but my assumption is that you would see higher queue depths when crunching away at a big database. per tomshardware testing, the optane drive is only about ~10% faster than a samsung 960 evo/pro at high queue depths of random read. so an operation going from 115s to 107s would be approximately consistent with that.

Could be that your CPU is the bottleneck. Did you try overclocking? Did the loading-time improve?

Well, when i first built the pc, I first overclocked the memory because that's what the postgresql guys told me was important. Then, because I was using a new prime95 version which isn't suited for haswell cpus, but I didn't know it at the time, I was getting absurdly high temperatures and stability issues I couldn't resolve. So I didn't pursue it.

I am not sure that the cpu is going to be a bottleneck just for the optane, but not for the relatively slower mainstream ssds. I should give it a try though at some point.

Funny how some things work out. I tried googling whether postgresql reads produce higher queue depths, but what I ended up finding instead was a postgresql tuner web app written by the guy who made it. Basically, you feed the page your specs and it gives you numbers to use on your postgresql config file.

After doing so and tinkering a bit more, I was actually able to shave another 18-26 seconds from loading times.

So basically, the big database loads in 1m 28s on the 900p and in 1m 35 s on the Samsung SSD while the small database loads in 44s in both disks.

I highly highly highly recommend to anyone interested in improving their database performance to use this pgtuner.

http://pgtune.leopard.in.ua/

PS. Also lower max_connections. I am experimenting now to find out what's the max connections the database uses, but it's way less than the default 100. And that's important, because the less max connections you have, the higher you can set your work mem.

PS2. But yeah, I don't think the 900p is going to work out.

Underclock your CPU a little and/or set postgreSQL to use one core less and see how much performance you lose.

Ok, I also did a moderate overclock by just choosing an auto option on Bios. The overclock did shave another 6-10 seconds, but the improvement was consistent across the board, i.e. the CPU is not the bottleneck.

The last thing I am going to try is check performance Windows 10, but I am not holding my breath.

If a small overclock consistently shaved off 6-10 seconds then you are CPU limited.

It's obviously a bottleneck for performance, but it isn't a bottleneck for keeping the 900p SSD down. That is the question: is there something I have done or haven't done that doesn't let the new SSD deliver the spectacular performance I was expecting? So far, I haven't found anything.