Processor usage/tuning question - Mailing list pgsql-general

From Israel Brewster
Subject Processor usage/tuning question
Date
Msg-id BFC0E358-F0FA-418E-A240-53B9231AC1A1@ravnalaska.net
Whole thread Raw
Responses Re: Processor usage/tuning question
Re: Processor usage/tuning question
Re: Processor usage/tuning question
List 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
-----------------------------------------------



Attachment

pgsql-general by date:

Previous
From: Merlin Moncure
Date:
Subject: Re: How to find greatest record before known values fast
Next
From: Alan Hodgson
Date:
Subject: Re: Processor usage/tuning question