Learn how to fine-tune PostgreSQL parameters, optimize SQL queries, and configure shared memory for optimal search engine performance.
Fine-tune your database parameters to extract the maximum performance out of it!
Hello,
These are a few insights from my experience while building a search engine and optimizing a PostgreSQL database toward high speeds.
Our configuration for the PostgreSQL server was:
Red Hat 7.2
PIV 2.00 GHz System
1024MB RAM
One of the first things I noticed after turning on the servlet program was that although queries were returned almost as fast as from the previous MySQL-based system, the load on the server was much higher. Then I started to dive into the details. I had optimized MySQL before by greatly increasing cache and buffer sizes and by throwing more RAM toward the problem. The single biggest thing to do before running PostgreSQL is to provide enough shared buffer space. But then, how much is enough?
There is a heated debate on this topic between people who argue that logically the entire RAM could be devoted to it, versus those who say that adding more RAM past a certain limit has no use. The more shared buffer cache you have, the bigger the percentage of your database that neither causes reads nor memory copying from the OS buffer cache. However, overall you will cache a smaller number of blocks because you will be buffering them twice. When you copy a block from the OS buffer to shared memory, the copy still exists in the OS buffer, meaning that block is now buffered twice. A single disk I/O is far more expensive than hundreds of copies between the OS buffer cache and PostgreSQL's shared memory. Also consider all the other background tasks running on the machine, such as cron. All of that takes memory; therefore, it is dangerous not to let the OS manage a good chunk of memory.
It turns out that these two opposing factors can be plotted on a graph, and the ideal point is where they cross.
Besides that, I also optimized SQL queries tailored specifically for my purpose. A major drawback in PostgreSQL lies in the evaluation of queries containing 'IN' and 'EXISTS'. Suppose:
Query 1. SELECT * FROM db1 WHERE ID IN ((SELECT id FROM db2 WHERE word = 'whatever')) LIMIT 20;
Query 2. SELECT * FROM db1 WHERE ID IN (1234, 2345, 1242, 1256, 1245, 1567, 2222, 22, 345, 234, 567, 456, 35, 56);
(where ID is the primary key)
The latter query is scanned using the index on ID, while the former results in a sequential scan. This occurs when the database runs the subquery for each row in the outer query. Instead, if we use explicit JOINS (as shown below), we can force the database to use an index scan:
Final Query:
select * from db1, db2 a, db2 b
where id = a.id and a.word = 'word1'
and id = b.id and b.word = 'word2'
etc
NOTE: You can also run into a sequential scan instead of an expected index scan if the number of tuples to be scanned exceeds 30–40% of the total tuples in the table. This threshold can be adjusted by changing the weights assigned to random_page_cost, cpu_tuple_cost, cpu_index_cost, and cpu_operator_cost used by the optimizer.
I also decided to allocate more RAM for the purpose, assigning 64MB of RAM toward the shared buffer space. The file /var/lib/pgsql/data/postgresql.conf contains settings for the database server. PostgreSQL uses system shared memory as a buffer. On a Linux system, you can check how much shared memory has been allocated by running the command:
cat /proc/sys/kernel/shmmax
And to view shared memory usage on the system:
ipcs
The result will be in bytes. By default, Red Hat 7.2 allocates 32MB of shared memory, which might not be enough for PostgreSQL. I increased this limit to 64MB by running:
echo 67108864 > /proc/sys/kernel/shmmax
You need to place this line in your PostgreSQL startup file or edit the /etc/rc.d/rc.local file for a permanent setting. Then, in postgresql.conf, I set shared_buffers to 8192. I also set sort_mem to 16384 (16MB for the sort memory area). Since connection pooling was in effect, I set max_connections to 50, and fsync was set to false.
shared_buffers = 8192
sort_mem = 16384
max_connections = 50
fsync = false
One initial hitch was that the system had to build up and tear down a PostgreSQL connection with each request. This was intolerable, so I started using the connection pooling features provided by Resin (http://caucho.com).
---
Varun
Acknowledgments: Curt, Bruce, Andrew, and others for clearing my doubts!
Database Optimisations and Fine-Tuning
Learn how to fine-tune PostgreSQL parameters, optimize SQL queries, and configure shared memory for optimal search engine performance.
Fine-tune your database parameters to extract the maximum performance out of it!
Hello,
These are a few insights from my experience while building a search engine and optimizing a PostgreSQL database toward high speeds.
Our configuration for the PostgreSQL server was:
Red Hat 7.2
PIV 2.00 GHz System
1024MB RAM
One of the first things I noticed after turning on the servlet program was that although queries were returned almost as fast as from the previous MySQL-based system, the load on the server was much higher. Then I started to dive into the details. I had optimized MySQL before by greatly increasing cache and buffer sizes and by throwing more RAM toward the problem. The single biggest thing to do before running PostgreSQL is to provide enough shared buffer space. But then, how much is enough?
There is a heated debate on this topic between people who argue that logically the entire RAM could be devoted to it, versus those who say that adding more RAM past a certain limit has no use. The more shared buffer cache you have, the bigger the percentage of your database that neither causes reads nor memory copying from the OS buffer cache. However, overall you will cache a smaller number of blocks because you will be buffering them twice. When you copy a block from the OS buffer to shared memory, the copy still exists in the OS buffer, meaning that block is now buffered twice. A single disk I/O is far more expensive than hundreds of copies between the OS buffer cache and PostgreSQL's shared memory. Also consider all the other background tasks running on the machine, such as cron. All of that takes memory; therefore, it is dangerous not to let the OS manage a good chunk of memory.
It turns out that these two opposing factors can be plotted on a graph, and the ideal point is where they cross.
Besides that, I also optimized SQL queries tailored specifically for my purpose. A major drawback in PostgreSQL lies in the evaluation of queries containing 'IN' and 'EXISTS'. Suppose:
Query 1. SELECT * FROM db1 WHERE ID IN ((SELECT id FROM db2 WHERE word = 'whatever')) LIMIT 20;
Query 2. SELECT * FROM db1 WHERE ID IN (1234, 2345, 1242, 1256, 1245, 1567, 2222, 22, 345, 234, 567, 456, 35, 56);
(where ID is the primary key)
The latter query is scanned using the index on ID, while the former results in a sequential scan. This occurs when the database runs the subquery for each row in the outer query. Instead, if we use explicit JOINS (as shown below), we can force the database to use an index scan:
Final Query:
select * from db1, db2 a, db2 b
where id = a.id and a.word = 'word1'
and id = b.id and b.word = 'word2'
etc
NOTE: You can also run into a sequential scan instead of an expected index scan if the number of tuples to be scanned exceeds 30–40% of the total tuples in the table. This threshold can be adjusted by changing the weights assigned to random_page_cost, cpu_tuple_cost, cpu_index_cost, and cpu_operator_cost used by the optimizer.
I also decided to allocate more RAM for the purpose, assigning 64MB of RAM toward the shared buffer space. The file /var/lib/pgsql/data/postgresql.conf contains settings for the database server. PostgreSQL uses system shared memory as a buffer. On a Linux system, you can check how much shared memory has been allocated by running the command:
cat /proc/sys/kernel/shmmax
And to view shared memory usage on the system:
ipcs
The result will be in bytes. By default, Red Hat 7.2 allocates 32MB of shared memory, which might not be enough for PostgreSQL. I increased this limit to 64MB by running:
echo 67108864 > /proc/sys/kernel/shmmax
You need to place this line in your PostgreSQL startup file or edit the /etc/rc.d/rc.local file for a permanent setting. Then, in postgresql.conf, I set shared_buffers to 8192. I also set sort_mem to 16384 (16MB for the sort memory area). Since connection pooling was in effect, I set max_connections to 50, and fsync was set to false.
shared_buffers = 8192
sort_mem = 16384
max_connections = 50
fsync = false
One initial hitch was that the system had to build up and tear down a PostgreSQL connection with each request. This was intolerable, so I started using the connection pooling features provided by Resin (http://caucho.com).
---
Varun
Acknowledgments: Curt, Bruce, Andrew, and others for clearing my doubts!
Database Optimisations and Fine-Tuning
By Howtodotips
Learn how to fine-tune PostgreSQL parameters, optimize SQL queries, and configure shared memory for optimal search engine performance.