Re: [PERFORM] Sudden Query slowdown on our Postgresql Server
On 2012-03-23 05:53, Sebastian Melchior wrote: Hi, we already used iostat and iotop during times of the slowdown, there is no sudden drop in I/O workload in the times of the slowdown. Also the iowait does not spike and stays as before. So i do not think that this is I/O related. As the disks are SSDs there also still is some "head room" left. I've seen a ssd completely lock up for a dozen seconds or so after giving it a smartctl command to trim a section of the disk. I'm not sure if that was the vertex 2 pro disk I was testing or the intel 710, but enough reason for us to not mount filesystems with -o discard. regards, Yeb -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Sudden Query slowdown on our Postgresql Server
Hi, unfortunately we cannot directly control the TRIM (i am not sure it even occurs) because the SSDs are behind an LSI MegaSAS 9260 Controller which does not allow access via smart. Even if some kind of TRIM command is the problem, shouldn't the iowait go up in this case? Sebastian On 23.03.2012, at 08:10, Yeb Havinga wrote: > On 2012-03-23 05:53, Sebastian Melchior wrote: >> Hi, >> >> we already used iostat and iotop during times of the slowdown, there is no >> sudden drop in I/O workload in the times of the slowdown. Also the iowait >> does not spike and stays as before. >> So i do not think that this is I/O related. As the disks are SSDs there also >> still is some "head room" left. > > I've seen a ssd completely lock up for a dozen seconds or so after giving it > a smartctl command to trim a section of the disk. I'm not sure if that was > the vertex 2 pro disk I was testing or the intel 710, but enough reason for > us to not mount filesystems with -o discard. > > regards, > Yeb > > > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Write workload is causing severe slowdown in Production
First off, thank you *so much* for that detailed explanation comparing with a real-time application performance benchmark, which was really enlightening for me. > How are you handling concurrency? (Are you using FOR SHARE on your > SELECT statements? Are you explicitly acquiring table locks before > modifying data? Etc.) You might be introducing blocking somehow. No, actually am not explicitly locking any tables -- all are *simple* select, update, insert statements only. > In particular, I recommend that you *never* leave transactions open > or hold locks while waiting for user response or input. Again, we're not leaving any transaction opens until for any user responses, etc. > When you hit that issue, there is not a continual slowdown -- > queries which normally run very fast (a small fraction of a second) > may periodically all take tens of seconds. Is that the pattern > you're seeing? Yes, you're correct. Queries those normally run fast are becoming slow at the time of this slowdown. > Besides the outdated PostgreSQL release and possible blocking, I > would be concerned if you are using any sort of ORM for the update > application. You want to watch that very closely because the > default behavior of many of them does not scale well. There's > usually a way to get better performance through configuration and/or > bypassing automatic query generation for complex data requests. Am not able to understand above statements (...any sort of ORM for the update application ...) clearly. Can you help me in understanding this? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Write workload is causing severe slowdown in Production
On 23 Březen 2012, 11:10, Gnanakumar wrote: > First off, thank you *so much* for that detailed explanation comparing > with > a real-time application performance benchmark, which was really > enlightening > for me. > >> How are you handling concurrency? (Are you using FOR SHARE on your >> SELECT statements? Are you explicitly acquiring table locks before >> modifying data? Etc.) You might be introducing blocking somehow. > > No, actually am not explicitly locking any tables -- all are *simple* > select, update, insert statements only. Are those wrapped in a transaction or not? Each transaction forces a fsync when committing, and if each of those INSERT/UPDATE statements stands on it's own it may cause of lot of I/O. >> Besides the outdated PostgreSQL release and possible blocking, I >> would be concerned if you are using any sort of ORM for the update >> application. You want to watch that very closely because the >> default behavior of many of them does not scale well. There's >> usually a way to get better performance through configuration and/or >> bypassing automatic query generation for complex data requests. > > Am not able to understand above statements (...any sort of ORM for the > update application ...) clearly. Can you help me in understanding this? There are tools that claim to remove the object vs. relational discrepancy when accessing the database. They often generate queries on the fly, and some of the queries are pretty awful (depends on how well the ORM model is defined). There are various reasons why this may suck - loading too much data, using lazy fetch everywhere etc. Are you using something like Hibernate, JPA, ... to handle persistence? Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Write workload is causing severe slowdown in Production
> Are those wrapped in a transaction or not? Each transaction forces a fsync > when committing, and if each of those INSERT/UPDATE statements stands on > it's own it may cause of lot of I/O. Yes, it's wrapped inside a transaction. May be this could be a reason for slowdown, as you've highlighted here. Atleast, we've got some guidance here to troubleshoot in this aspect also. > There are tools that claim to remove the object vs. relational discrepancy > when accessing the database. They often generate queries on the fly, and > some of the queries are pretty awful (depends on how well the ORM model is > defined). There are various reasons why this may suck - loading too much > data, using lazy fetch everywhere etc. Thanks for the clarification. > Are you using something like Hibernate, JPA, ... to handle persistence? No, we're not using any persistence frameworks/libraries as such. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Write workload is causing severe slowdown in Production
"Gnanakumar" wrote: >> When you hit that issue, there is not a continual slowdown -- >> queries which normally run very fast (a small fraction of a >> second) may periodically all take tens of seconds. Is that the >> pattern you're seeing? > > Yes, you're correct. Queries those normally run fast are becoming > slow at the time of this slowdown. But the question is -- while the update application is running is performance *usually* good with *brief periods* of high latency, or does it just get bad and stay bad? The *pattern* is the clue as to whether it is likely to be write saturation. Here's something I would recommend as a diagnostic step: run `vmstat 1` (or equivalent, based on your OS) to capture about a minute's worth of activity while things are running well, and also while things are slow. Pick a few lines that are "typical" of each and paste them into a post here. (If there is a lot of variation over the sample, it may be best to attach them to your post in their entirety. Don't just paste in more than a few lines of vmstat output, as the wrapping would make it hard to read.) Also, you should try running queries from this page when things are slow: http://wiki.postgresql.org/wiki/Lock_Monitoring If there is any blocking, that might be interesting. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
