Processor usage/tuning question

From: Israel Brewster <israel(at)ravnalaska(dot)net>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Processor usage/tuning question
Date: 2014-10-03 19:24:31
Message-ID: BFC0E358-F0FA-418E-A240-53B9231AC1A1@ravnalaska.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a Postgresql 9.3.5 server running on CentOS 6.5. In looking at some stats today, I saw that it was handling about 4-5 transactions/second (according to the SELECT sum(xact_commit+xact_rollback) FROM pg_stat_database; query), and an instance of the postmaster process was consistently showing 40%-80% utilization to handle this. I didn't think anything of that (the machine has plenty of capacity) until I mentioned it to a friend of mine, who said that utilization level seemed high for that many transactions. So if that level of utilization IS high, what might I need to tune to bring it down to a more reasonable level?

Some details:
Processors: 2x4core 2.5 GHz Xeon
Total Memory: 16GB
Hard Disk: SSD raid 10
wa value from top is typically 0.0%, sometimes up to 0.1%

The database consists (primary) of a single table with 5 indexes and 11 columns. The majority of transactions are probably single-row inserts (live location data from numerous aircraft). Current record count is 1,282,706, and kept fairly static on a day-to-day basis by a cleanup routine that runs each night and deletes old records (if that makes a difference). This database is streamed to a secondary hot read-only spare using streaming replication. The replica is using less than 1% processor on average.

I have followed the directions here: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server however I'm sure there are nuances I missed, or values that could be better selected for my usage case.

Some current postgres config values:
shared_buffers: 4GB
effective_cache_size: 12GB

Not sure what other information I should provide, so let me know what useful data I missed!

-----------------------------------------------
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
-----------------------------------------------

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrus 2014-10-03 20:28:27 Re: How to find greatest record before known values fast
Previous Message Vick Khera 2014-10-03 18:38:25 Re: Really strange foreign key constraint problem blocking delete