Database tuning for OHDSI

Implementers
1

Most of the OHDSI tools work intensively with the database server, using SQL statements that are often more complicated than those used in other software, with multiple joins and large intermediary results. A nice example is this query in Achilles.

My question: do people have tips on how to optimize performance of database platforms for the OHDSI tools?

I heard @Christophe_Lambert and @msuchard complain about slow performance of PostgreSQL. @Rijnbeek, didn’t I hear you have managed to boost performance of PostgreSQL significantly with some minor tweaks?

2

Hi Martijn,

I have indeed been playing around with this. It seems that out of the box PostgreSQL is setup with a very basic configuration tuned for wide compatibility rather than performance. There are many tuning variables (for example memory settings) that have effect on the performance:

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Furthermore, I am interested in testing out the use of GPU for PostgreSQL queries:

https://community.hortonworks.com/articles/60416/pg-storm-let-postgresql-run-faster-on-the-gpu.html

and

http://strom.kaigai.gr.jp/manual.html

Furthermore, there are a lot of hints in the net to improve queries (for example select * is much slower un postgresql but often not needed).
See also: https://wiki.postgresql.org/wiki/Slow_Query_Questions

An interesting thing to look at is the EXPLAIN function that can help you figure out where you lose time:

https://www.postgresql.org/docs/9.2/static/using-explain.html

Now we have our new powerful server with 700+ Gb Ram and many core we will be playing around a bit more with these settings to see their effect on performance, I will let you know the results.

Peter

3

Thanks Peter!

Was there a tuning variable that you found to be especially important to change? Could you post the configuration file you used before you switched to your monster server?

4

Hi Martijn!
Here is a good way to find out what will make the biggest impact: http://pgtune.leopard.in.ua/