Michael,
Can you provide us with little more information about your server and the query that you are attempting to run.

- What version of LabKey are you running
- What is the database type (postgresql or MS SQL Server) and the version that you are using?
- Information about your LabKey Server and database hardware.
-- Are they run on separate servers
-- # CPU, Memory
-- Is the hardware a desktop, server, laptop, etc
- What is the query you are attempting to execute and how are you running it?
- Can you send us an export from the Queries Admin Console
-- Goto your Admin Console -> click on Queries in the Diagnostics section and hit the export button at the top of the page.
- Can you send us the labkey.log file for your server. You can find that in the Tomcat logs directory.

Thank you,

Brian

Hi Brian,
  We are currently running Labkey 11.1.
We are running Postgres 9.x - I'm not entirely certain right now which.

The server is running both Labkey and Postgres together. specs:

12 cpu cores (athlon)
32GB memory, java heap starts at 4GB, can grow to 20
It's a poweredge server

I've attached the query export and the labkey log gotten via the web interface. In the query log, find "pivot". The first hit is the query in question.

Thank you for the information and the files. I will look at them now.

I have one last question, what is the configuration of your postgresql database like. I am particularly interested in the the settings
- shared_buffers
- wal_buffers
- effective_cache_size
- work_mem

You can find these in the file postgresql.conf


-Brian

Unfortunately I don't have access to that info. I've requested it and I'll get back to you.

see postgres conf attached

Michael,

The problems you are experiencing are most likely being caused by the configuration of your PostgreSQL server. From the configuration file that you sent me, it looks like the database is using the default configuration.

The default configuration for PostgreSQL is meant to work on servers with 512MB or memory or less. If you change the configuration of your PostgreSQL server, the performance of you server will likely improve.

Documentation on tuning your postgresql server is available at

http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server and https://www.packtpub.com/article/server-configuration-tuning-postgresql

If you do not want to take the time to review all the documentation above, changing the following settings should provide a significant performance improvement

-shared_buffers = 12% of total memory size(but no larger than 2GB)
-work_mem = 10MB
- maintenance_work_mem = 1024MB
- wal_buffers = 4MB
- checkpoint_segments = 10   
- checkpoint_timeout = 15min
- enable_material = off
random_page_cost = 1.5
- effective_cache_size = 75% of total memory size

NOTE: I know very little information about your server configuration and other programs running on the server, so I recommend consulting with your DBA/System Administrator before making these changes. Or using these values as a starting point and evaluating the server performance after making the change.

Lastly, I would recommend reducing the Max Heap setting in Tomcat to either 12 or 16GB as the PostgreSQL database will now be using more of the servers memory.

-Brian

Thanks for the help Brian!