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
-----------------------------------------------
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 |