Thread: Optimizing Database High CPU
Hi we are running a Postgresql Database 9.4.18 and we are noticing a high CPU usage. Nothing is critical at the moment but if we were to scale up more of what we are doing, I feel we are going to run into issues. It is a 2 x 6 core machine, 128GB ram, Raid 10 HDD The iostat metrics for the HDD look minimal < 10% util Available memory seems to be good. The CPU utilization is what bothering me user 5-7% sys 50-70% - seems high wa <0.5% So trying to troubleshoot possible high cpu: Number of concurrent connections averages 50 to 100 - seems high although we max at 200. No long running queries Streaming replication to backup server High update tables - we have about 4 tables that have a high volume of updates High update rate is what I am thinking is causing the issue and I found possibly setting fillfactor to a lower default which the internet says you need to do a vacuum full which I am trying to avoid but if it needs to be done we can schedule it. Just want to make sure if I am chasing the correct rabbit hole. Are there any statistics I could run to see if a setting change would help. Best, Scott -- T: @Thaumion IG: Thaumion Scottix@Gmail.com
On Wed, Feb 27, 2019 at 2:07 PM Scottix <scottix@gmail.com> wrote:
Hi we are running a Postgresql Database 9.4.18 and we are noticing a
high CPU usage. Nothing is critical at the moment but if we were to
scale up more of what we are doing, I feel we are going to run into
issues.
9.4 is old. A lot of improvements have been made sense then, including around contention issues. Such as replacing some use of spinlocks with use of atomic operations instead.
You would be better off putting time into upgrading, rather than putting time into worrying about performance issues on something that will soon be end-of-life anyway.
It is a 2 x 6 core machine, 128GB ram, Raid 10 HDD
The iostat metrics for the HDD look minimal < 10% util
Available memory seems to be good.
The CPU utilization is what bothering me
user 5-7%
sys 50-70% - seems high
wa <0.5%
So trying to troubleshoot possible high cpu:
Number of concurrent connections averages 50 to 100 - seems high
although we max at 200.
If those 50-100 connections are all active at once, yes, that is high. They can easily spend more time fighting each other over LWLocks, spinlocks, or cachelines rather than doing useful work. This can be exacerbated when you have multiple sockets rather than all cores in a single socket. And these problems are likely to present as high Sys times.
Perhaps you can put up a connection pooler which will allow 100 connections to all think they are connected at once, but forces only 12 or so to actually be active at one time, making the others transparently queue.
No long running queries
Streaming replication to backup server
High update tables - we have about 4 tables that have a high volume of updates
Is it a few transactions updating a lot of rows each, or many transactions updating a few rows each?
High update rate is what I am thinking is causing the issue and I
found possibly setting fillfactor to a lower default
I don't think that that is promising. I wouldn't expect high Sys time if this was the problem. And with a high rate of updates (unless each update is essentially to every row in the table), I would expect the table to reach a steady state of tuple density. Basically a too-high fillfactor will fix itself naturally over time, it might be just take a while to do it. If your system has been running for a while, it has probably already arrived at a steady state. You can use the extension pg_freespacemap to so how the freespace is spread around in your table blocks.
Are there any statistics I could run to see if a setting change would help.
I'd probably start with pg_stat_activity table's "state" column to see how many of your connections are active at once, and its columns "wait_event_type" and "wait_event" to see what they think they are waiting on (but those last columns aren't present until 9.6).
Cheers,
Jeff
If those 50-100 connections are all active at once, yes, that is high. They can easily spend more time fighting each other over LWLocks, spinlocks, or cachelines rather than doing useful work. This can be exacerbated when you have multiple sockets rather than all cores in a single socket. And these problems are likely to present as high Sys times.Perhaps you can put up a connection pooler which will allow 100 connections to all think they are connected at once, but forces only 12 or so to actually be active at one time, making the others transparently queue.
Can you expound on this or refer me to someplace to read up on this?
Context, I don't want to thread jack though: I think I am seeing similar behavior in our environment at times with queries that normally take seconds taking 5+ minutes at times of high load. I see many queries showing buffer_mapping as the LwLock type in snapshots but don't know if that may be expected. In our environment PgBouncer will accept several hundred connections and allow up to 100 at a time to be active on the database which are VMs with ~16 CPUs allocated (some more, some less, multi-tenant and manually sharded). It sounds like you are advocating for connection max very close to the number of cores. I'd like to better understand the pros/cons of that decision.
Context, I don't want to thread jack though: I think I am seeing similar behavior in our environment at times with queries that normally take seconds taking 5+ minutes at times of high load. I see many queries showing buffer_mapping as the LwLock type in snapshots but don't know if that may be expected. In our environment PgBouncer will accept several hundred connections and allow up to 100 at a time to be active on the database which are VMs with ~16 CPUs allocated (some more, some less, multi-tenant and manually sharded). It sounds like you are advocating for connection max very close to the number of cores. I'd like to better understand the pros/cons of that decision.
Alright will try the upgrade. > Is it a few transactions updating a lot of rows each, or many transactions updating a few rows each? It is a lot of transaction updating a few rows. Then will look into a connection pooler. Thanks for the response. On Wed, Feb 27, 2019 at 2:01 PM Michael Lewis <mlewis@entrata.com> wrote: >> >> If those 50-100 connections are all active at once, yes, that is high. They can easily spend more time fighting eachother over LWLocks, spinlocks, or cachelines rather than doing useful work. This can be exacerbated when you have multiplesockets rather than all cores in a single socket. And these problems are likely to present as high Sys times. >> >> Perhaps you can put up a connection pooler which will allow 100 connections to all think they are connected at once, butforces only 12 or so to actually be active at one time, making the others transparently queue. > > > Can you expound on this or refer me to someplace to read up on this? > > Context, I don't want to thread jack though: I think I am seeing similar behavior in our environment at times with queriesthat normally take seconds taking 5+ minutes at times of high load. I see many queries showing buffer_mapping as theLwLock type in snapshots but don't know if that may be expected. In our environment PgBouncer will accept several hundredconnections and allow up to 100 at a time to be active on the database which are VMs with ~16 CPUs allocated (somemore, some less, multi-tenant and manually sharded). It sounds like you are advocating for connection max very closeto the number of cores. I'd like to better understand the pros/cons of that decision. -- T: @Thaumion IG: Thaumion Scottix@Gmail.com
On Wed, Feb 27, 2019 at 5:01 PM Michael Lewis <mlewis@entrata.com> wrote:
If those 50-100 connections are all active at once, yes, that is high. They can easily spend more time fighting each other over LWLocks, spinlocks, or cachelines rather than doing useful work. This can be exacerbated when you have multiple sockets rather than all cores in a single socket. And these problems are likely to present as high Sys times.Perhaps you can put up a connection pooler which will allow 100 connections to all think they are connected at once, but forces only 12 or so to actually be active at one time, making the others transparently queue.Can you expound on this or refer me to someplace to read up on this?
Just based on my own experimentation. This is not a blanket recommendation, but specific to the situation that we already suspect there is contention, and the server is too old to have pg_stat_actvity.wait_event column.
Context, I don't want to thread jack though: I think I am seeing similar behavior in our environment at times with queries that normally take seconds taking 5+ minutes at times of high load. I see many queries showing buffer_mapping as the LwLock type in snapshots but don't know if that may be expected.
It sounds like your processes are fighting to reserve buffers in shared_buffers in which to read data pages. But those data pages are probably already in the OS page cache, otherwise reading it from disk would be slow enough that you would be seeing some type of IO wait, or buffer_io, rather than buffer_mapping as the dominant wait type. So I think that means you have most of your data in RAM, but not enough of it in shared_buffers. You might be in a rare situation where setting shared_buffers to a high fraction of RAM, rather than the usual low fraction, is called for. Increasing NUM_BUFFER_PARTITIONS might also be useful, but that requires a recompilation of the server. But do these spikes correlate with anything known at the application level? A change in the mix of queries, or a long report or maintenance operation? Maybe the query plans briefly toggle over to using seq scans rather than index scans or vice versa, which drastically changes the block access patterns?
In our environment PgBouncer will accept several hundred connections and allow up to 100 at a time to be active on the database which are VMs with ~16 CPUs allocated (some more, some less, multi-tenant and manually sharded). It sounds like you are advocating for connection max very close to the number of cores. I'd like to better understand the pros/cons of that decision.
There are good reasons to allow more than that. For example, your application holds some transactions open briefly while it does some cogitation on the application-side, rather than immediately committing and so returning the connection to the connection pool. Or your server has a very high IO capacity and benefits from lots of read requests in the queue at the same time, so it can keep every spindle busy and every rotation productive. But, if you have no reason to believe that any of those situations apply to you, but do have evidence that you have lock contention between processes, then I think that limiting the number active processes to the number of cores is a good starting point.
Cheers,
Jeff
Hey,
So I finally found the culprit. Turns out to be the THP fighting with itself.
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
It instantly went from a loadavg of 30 to 3
Also make sure you re-enable on reboot.
Anyway just wanted to give a followup on the issue incase anyone else is having the same problem.
On Mon, Mar 4, 2019 at 12:03 PM Jeff Janes <jeff.janes@gmail.com> wrote:
On Wed, Feb 27, 2019 at 5:01 PM Michael Lewis <mlewis@entrata.com> wrote:If those 50-100 connections are all active at once, yes, that is high. They can easily spend more time fighting each other over LWLocks, spinlocks, or cachelines rather than doing useful work. This can be exacerbated when you have multiple sockets rather than all cores in a single socket. And these problems are likely to present as high Sys times.Perhaps you can put up a connection pooler which will allow 100 connections to all think they are connected at once, but forces only 12 or so to actually be active at one time, making the others transparently queue.Can you expound on this or refer me to someplace to read up on this?Just based on my own experimentation. This is not a blanket recommendation, but specific to the situation that we already suspect there is contention, and the server is too old to have pg_stat_actvity.wait_event column.Context, I don't want to thread jack though: I think I am seeing similar behavior in our environment at times with queries that normally take seconds taking 5+ minutes at times of high load. I see many queries showing buffer_mapping as the LwLock type in snapshots but don't know if that may be expected.It sounds like your processes are fighting to reserve buffers in shared_buffers in which to read data pages. But those data pages are probably already in the OS page cache, otherwise reading it from disk would be slow enough that you would be seeing some type of IO wait, or buffer_io, rather than buffer_mapping as the dominant wait type. So I think that means you have most of your data in RAM, but not enough of it in shared_buffers. You might be in a rare situation where setting shared_buffers to a high fraction of RAM, rather than the usual low fraction, is called for. Increasing NUM_BUFFER_PARTITIONS might also be useful, but that requires a recompilation of the server. But do these spikes correlate with anything known at the application level? A change in the mix of queries, or a long report or maintenance operation? Maybe the query plans briefly toggle over to using seq scans rather than index scans or vice versa, which drastically changes the block access patterns?In our environment PgBouncer will accept several hundred connections and allow up to 100 at a time to be active on the database which are VMs with ~16 CPUs allocated (some more, some less, multi-tenant and manually sharded). It sounds like you are advocating for connection max very close to the number of cores. I'd like to better understand the pros/cons of that decision.There are good reasons to allow more than that. For example, your application holds some transactions open briefly while it does some cogitation on the application-side, rather than immediately committing and so returning the connection to the connection pool. Or your server has a very high IO capacity and benefits from lots of read requests in the queue at the same time, so it can keep every spindle busy and every rotation productive. But, if you have no reason to believe that any of those situations apply to you, but do have evidence that you have lock contention between processes, then I think that limiting the number active processes to the number of cores is a good starting point.Cheers,Jeff
--
T: @Thaumion
IG: Thaumion
Scottix@Gmail.com
IG: Thaumion
Scottix@Gmail.com