El 03/10/14 a las 16:24, Israel Brewster escibió:
> 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?
>
First you need to find which the bottleneck is. Is possible that the
processor is being using such percentage
due to a disk high load.
For example, bad queries use to behave like this. They consume a lot of
CPU due to large seq scans on your tables.
Or, a bunch of not so small seqscans. Also user defined functions could
lead to a performance issue if the code isn't
really optimized.
> 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.
So, it looks like that writes aren't the issue. You'll need to check the
IO rate on your machine.
I'll recommend that you share the output of the sysstat commands. Also,
check the RSS of each
process with the command:
ps -u <yourDBuser> uf
--
--
Emanuel Calvo http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services