SSD performance improvements set LIVE
22 mei 2009, door developmentIn a previous blog entry (One DVD per second) we described how we build a new fast SSD based Database server and how we benchmarked it.
This week we put the new SSD DB server to the ultimate test, namely “Going Live”.
It turned out that our investments definitely payed off. Our main online application M4N has seen a massive speed improvement. We found an average performance increase of ~6 times for all queries. As a result, the initial average performance increase for the whole application is approximately a factor 2. Keep in mind that the whole application depends on more than just the main DB. Of course, the performance of the Java Application Server and the network bandwidth play important roles as well.
The performance increase is most apparent when simply browsing through the different pages of the application. The whole experience now feels very snappy and even the more complex pages load nearly instantly. We have statistics on our average page speeds so I included this in the pictures.
Picture one the improvements with testing:
Speed improvements new database server SSD Postgresql
Picture two the improvements with speed on execution of queries:
Statistics speed improvement Postgresql database
Finally, during the night we execute a slew of maintenance queries. Normally, we see a number like this after the script that triggers these queries has finished executing:
real 348m6.451s
user 0m4.912s
sys 0m0.808s
The other morning however we saw this:
real 30m34.747s
user 0m2.752s
sys 0m1.008s
This amounts to a performance increase of over 11 times.
For now we thus carefully conclude that using SSDs indeed improves performance by a large margin.
27 mei, 2009 at 10:07
Nice impovements! Did you also made some changes in postgresql.conf? Your new server has more RAM, let the database use this extra RAM as well. These fast SSD’s have a complete different write-behaviour compared to the former SAS-drives, the database should handle writes different as well. Changes in the configurationfile could speed things up even further.
Good luck!
1 juni, 2009 at 14:45
We did indeed make some changes in the postgresql.conf file too, but as always its hard to say whether we have hit the absolute sweet spot with respect to tuning.
Off the top of my head we made the following changes:
maintenance_work_mem from 256MB to 2GB
temp_buffers from 8MB to 32MB
work_mem from 32MB to 256MB
vacuum_cost_delay from 50 to 0
max_stack_depth from 4M to 6MB
random_page_cost from 2 to 1.5 (sequential_page_cost = 1)
effective_cache_size from 4GB to 16GB
autovacuum_vacuum_cost_delay from 20 to 40
I think these numbers are pretty accurate, but to be absolute sure I would have to verify the change logs.
As for the total performance improvement, the new server is quite different from the old server. 64 bits instead of 32 bits, newer kernel, more memory, higher clocked CPUs with more cores and then of course the SSD storage system. With so many changes at once, it’s hard to attribute a specific percentage of the performance improvements to the SSD storage system.
As techies we would have loved to equip the old server with the new SSD storage system, in order to have a more apples to apples comparison. Unfortunately this wasn’t an option. Nevertheless experience with previous upgrades where we also doubled the memory, doubled the number of cores, increased the clock frequency of the CPUs, etc never gave us such a large performance boost as we saw with this new server.