On Wed, Dec 23, 2009 at 7:45 AM, Renato Oliveira <renato.oliveira@xxxxxxxxxxx> wrote: > There are 100 connections open to the database server at any given time, > apparently using hibernate. > > We are using Postgres 8.2.4 compiled Update to 8.2.latest at your earliest chance. > The database size is 155GB > We have 8GB of RAM > 300GB Hard disk Raid1 > Everything is within a single Volume > Today the load average was as high as 15 > > Top result: > Tasks: 169 total, 30 running, 139 sleeping, 0 stopped, 0 zombie > Cpu(s): 69.6% us, 5.9% sy, 0.0% ni, 0.0% id, 24.3% wa, 0.2% hi, 0.0% si > Mem: 8251404k total, 8228428k used, 22976k free, 16296k buffers > Swap: 1052248k total, 47176k used, 1005072k free, 6664308k cached So, 6.6G of kernel cache, so you're not starving your machine of memory. 24.3% wait means that you've got 1 out of 4 cores waiting on IO all the time (assuming you've got a quad core machine here) > I am quite sure: > 1 – we do not have enough memory Given how much is being used for kernel cache you're probably ok. You could likely increase shared_buffers and work_mem and maintenance_work_mem a bit each and use some more for the db instead of letting the kernel have it all. Generally 1/4 mem to shared_buffers is reasonable on smaller memory machines. > 2 – our disk and RAID array setup is not good enough Hard to be sure. What do iostat -x 60 AND vmstat 60 say after running for a few minutes? > 3 – Postgres is not setup correctly and uses an older version Not real old, but it is not up to date on security / bug fixes. > 4 – It is using Slony and replication which does not work. Slony is a rather complex piece of software. If you don't need it's extra features and such, londiste from skype may be a better choice. > I would like if anyone could help with the following: > > 1 – Ways of proving my thoughts > 2 – Gather data evidence to prove vmstat, iostat, explain analyze <yourqueryhere> -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
