I use Aqua Data Studio in OS X to make queries in a PostgreSQL server, but the first query of the day runs too slow.
It not depends on the SQL, a simple SELECT current_timestamp will parse and execute in about 5 seconds, any subsequent query will take almost 30ms. If I close the app, reopen and try again this does not happen, only when restarting the machine.
If I use PgAdmin, it works fine from the first query. In Aqua it will be very clear that is not the execution time, but the parsing time that is causing this.
Now we have a continuous integration server, which runs a lot of tests at every commit, but fails at every first build of the day, and until we do a second commit (or do a forced build) it will be failed. This machine is a Linux one, and the build is a java app using JDBC, making me discard an OS issue and consider more a PostgreSQL + JDBC issue.
The primary server I am running on:
PostgreSQL 8.4.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Debian 4.3.2-1.1) 4.3.2, 64-bit
But it happens on several servers and versions:
PostgreSQL 8.4.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Debian 4.3.2-1.1) 4.3.2, 64-bit
PostgreSQL 9.1alpha3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Debian 4.3.2-1.1) 4.3.2, 64-bit
If I execute the first query on any server, any subsequent, on any server, will be ok.
Anyone has experienced this? How could I try to identify what is causing this?
Best Answer
This looks a lot like your database will be paged out over night or after system start when other processes need a lot of memory.
I think it would make sense to either add more memory to the db server. Another option is to issue that above select current_timestamp from a script before the CI server runs the tests (e.g. in some pre-built phase) to have the DB being paged in. The script would call the psql command line utility to issue the select.