search

ODM2 / ODM2-performance-optimization

A repo to share and discuss methods for optimizing performance of ODM2 databases and software.
0 stars 0 forks source link

NVMe SSD improves database scan times exponentially #9

Open miguelcleon opened 4 years ago

miguelcleon commented 4 years ago

I recently got a new desktop with an NVMe SSD and I'm getting huge performance increases in query times. A query for a time series with about 140,000 values takes about 11.6 seconds on a server while it takes 0.334 seconds on the desktop with the NMVe SSD. This appears to be due to a data throughput of about 2.7 GB/s vs 95 to 100 MB/s on the server. This results in a massive decrease in the amount of time it takes to scan the database for the requested data.

emiliom commented 4 years ago

Thanks so much for sharing this, Miguel! I too have been musing lately about SSD's and relational databases, but I haven't done any actual, solid comparisons. I do struggle with some queries (and DELETE or TRUNCATE statements) that are taking forever on a PostgreSQL database (not ODM2) on a server with spinning disks vs on my laptop with an SSD. But it's not an apples-to-apples comparison, b/c the server is getting old and has older versions of everything, compared to my laptop.

miguelcleon commented 4 years ago

And the NVMe SSDs are much faster then SATA SSDs. It seems like a significant development. https://www.pcworld.com/article/2899351/everything-you-need-to-know-about-nvme.html

emiliom commented 4 years ago

Thanks for the info and the article; very helpful. I wasn't aware of NVMe. I think I've been using only SATA SSDs, but not completely sure. Update: Looks like I do have NVMe on my Linux laptop from Nov. 2016 ("256 GB PCle M.2 SSD"). No wonder it feels so fast compared to my server!

horsburgh commented 4 years ago

We've had issues with this in our Hydroinformatics class. I used to give students a SQL Dump file that loaded a bunch of observational data into an ODM 1.1.1 database in MySQL. On machines with an SSD, this data loading script would take about a minute to run, but students with spinning disks would take multiple hours to run the same script (and most would give up and send me an email to complain).

aufdenkampe commented 4 years ago

That's all great to know. My late 2016 MacBook Pro has an NVMe SSD, and it was noticeably faster than by previous MacBook Pro with a SATA III SSD.

Is there a way to configure AWS instances with SSDs? (that is affordable?)

emiliom commented 4 years ago

Is there a way to configure AWS instances with SSDs? (that is affordable?)

I assume there is, and that it's bound to be more expensive. Ask me again in a couple of months.

miguelcleon commented 4 years ago

Yes, you can get AWS instances with NVMe SSDs and prices are pretty reasonable, maybe only a little higher. The cost of these SSDs have come down a lot. Here is one link about some of them, this is from over a year ago. https://aws.amazon.com/blogs/aws/ec2-instance-update-c5-instances-with-local-nvme-storage-c5d/

miguelcleon commented 4 years ago

or better here https://aws.amazon.com/ec2/pricing/on-demand/

No SSD: m5.xlarge | 4 | 16 | 16 GiB | EBS Only | $0.192 per Hour

150 GB NVMe SSD:

m5ad.xlarge | 4 | N/A | 16 GiB | 1 x 150 NVMe SSD | $0.206 per Hour

aufdenkampe commented 4 years ago

Wow, those are reasonable prices. Is 150 GB of storage enough for your instances?

I also found this under "Storage Optimized": i3.xlarge | 4 | 13 | 30.5 GiB | 1 x 950 NVMe SSD | $0.312 per Hour

miguelcleon commented 4 years ago

Yes, it would be enough. Yeah, it's possible those storage optimized instances would work better but I'm not sure. One reddit post I saw said you only get a portion of the NVMe bandwidth with smaller general purpose instances.