Using atop to track your optimization results
Recently, I rediscovered atop. It is an excellent command-line tool (just like top) that shows some advanced statistics including disk usage and swap page faults per process. For the disk usage to work per process a kernel patch is currently required. However, even without the kernel patch if you know which processes running on your server you’ll be able to guess which is utilizing the disk more.
What made me search for a tool that shows disk usage is that I felt there is a disk bottle neck on the box. Of course my guess was the database since I’m now storing all the media inside the database. By using atop and watching the heavy update/insert/delete times I was able to correlate that the database is actually responsible for the disk overload. The problem was never felt from the website users, since the difference in response was very small for the human visitor to feel since those update processes run with idle priority. However, during massive updates I will see the disk saturated almost all the time.
The solution was playing the parameters in the /etc/postgres/postgres.conf file especially the buffers, as well as the kernel shared memory limit shmmax and shmall in /etc/sysctl.conf and also using the sysctl tool. The other side is of course optimizing the process doing the update by making more use of the timestamps provided by the RETS query especially the media. The larger effect was due to the update process optimization.
Some nice features in atop include showing the RGROW field, which shows how many pages the process added in the resident memory. A positive number will show that the process is doing page faults and adding pages in RAM. A -ve number shows that a process is losing pages from RAM due to insufficient RAM. To make sure postgres can find enough memory, the RGROW field should never be showing -ve numbers. Also, there is a global field call PAG which shows how many page faults are happening in the system. If that field goes red and no -ve numbers in postgres RGROW it means that postgres is taking the RAM out of other processes, and those processes are stressing the system. If those processes run frequently then this for sure will cause every thing to slowdown including the postgres server. So make sure that if you optimize postgres you keep enough memory for other processes to run as well.
Technorati Tags: postgres, postgresql, optimization, atop, memory, ram, linuxTags: atop, Linux, memory, optimization, postgres, postgresql, ram
