Re: [PERFORM] Sudden crazy high CPU usage
Sorry, but nothing unusual here either, I have compared the time just before with the same time the days before and the throughput pattern is exactly the same. No differences. Den 31/03/2014 kl. 22.01 skrev Will Platnick wplatn...@gmail.com: In New Relic, go back a half hour before the problem started so you can't see that this spike happened and send the same screenshot in. My guess is you have increased activity hitting the DB. Do you have pgbouncer or some kind of connection pooling sitting in front? 198 open server connections could account for an increase in load like you're seeing. Do you have postgresql addon in New Relic to show you how many queries are hitting the system to correlate data to? On Mon, Mar 31, 2014 at 1:36 PM, Sergey Konoplev gray...@gmail.com wrote: On Mon, Mar 31, 2014 at 3:25 AM, Niels Kristian Schjødt nielskrist...@autouncle.com wrote: I'm running postgresql 9.3 on a production server. An hour ago, out of the blue, I ran into an issue I have never encountered before: my server started to use CPU as crazy. The server is a standard ubuntu 12.04 LTE installation running only Postgres and Redis. The incident can be seen on the in numbers below: https://s3-eu-west-1.amazonaws.com/autouncle-public/other/cpu.png The increase doesn't look so sudden. My guess is that the server got some new activity. The advice is to setup the statistics collecting script by the link [1] and review the results for a period of hour or so. It shows charts of statements by CPU/IO/calls with aggregated stats, so you could probably find out more than with pure pg_stat_statements. [1] https://github.com/grayhemp/pgcookbook/blob/master/statement_statistics_collecting_and_reporting.md -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- 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 crazy high CPU usage
On 2014-03-31 19:16:58 +0200, Niels Kristian Schjødt wrote: Yes, I could install “perf”, though I’m not familiar with it. What would i do? :-) As root: perf record -a sleep 5 perf report my-nice-perf-report.txt And then send the my-nice-perf-report.txt file. Locally it's much nicer to see the output using perf report without redirect into a file, you'll get an interactive UI. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Slow Count-Distinct Query
On Sun, Mar 30, 2014 at 12:45 PM, Christopher Jackson crjac...@gmail.comwrote: Hi all, tl;dr - How can I speed up my count-distinct query? Depending on how often you need to run that query and how important it is to you, if you are willing to accept a performance hit on INSERT/UPDATE/DELETE of the participants table, you could create a summary table containing just the count of unique email addresses or the list of unique email addresses populated via trigger on INSERT/UPDATE/DELETE of the participants table. Another option is try out the new Materialized views ( http://www.postgresql.org/docs/current/static/sql-creatematerializedview.html) available in 9.3.
Re: [PERFORM] Slow Count-Distinct Query
Hi Bricklen, Thanks for the feedback. I'll play around with materialized views. My understanding is they have to be manually triggered for refresh and there's an exclusive lock on the view while the refresh is taking place. Is this your understanding as well? I'm using PG 9.3.3. If this is true, I'm curious what clever ways people have come up with to mitigate any issues with the lock. Thanks again, Chris On Tue, Apr 1, 2014 at 7:34 PM, bricklen brick...@gmail.com wrote: On Sun, Mar 30, 2014 at 12:45 PM, Christopher Jackson crjac...@gmail.comwrote: Hi all, tl;dr - How can I speed up my count-distinct query? Depending on how often you need to run that query and how important it is to you, if you are willing to accept a performance hit on INSERT/UPDATE/DELETE of the participants table, you could create a summary table containing just the count of unique email addresses or the list of unique email addresses populated via trigger on INSERT/UPDATE/DELETE of the participants table. Another option is try out the new Materialized views ( http://www.postgresql.org/docs/current/static/sql-creatematerializedview.html) available in 9.3.
Re: [PERFORM] Slow Count-Distinct Query
On Wed, Apr 2, 2014 at 1:22 PM, Christopher Jackson crjac...@gmail.com wrote: Hi Bricklen, Thanks for the feedback. I'll play around with materialized views. My understanding is they have to be manually triggered for refresh Yep. and there's an exclusive lock on the view while the refresh is taking place. Is this your understanding as well? Re-yep. I'm using PG 9.3.3. If this is true, I'm curious what clever ways people have come up with to mitigate any issues with the lock. Kevin Grittner has implemented REFRESH MATERIALIZED VIEW CONCURRENTLY in 9.4. A unique index is needed on the materialized view as well to authorize this concurrent operation. It has the merit to allow SELECT operations on the matview during the refresh. -- Michael -- 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] Slow Count-Distinct Query
Hi Bricklen, Thanks again for the feedback. The concurrent refresh sounds cool. I just saw the 9.4 release is tentatively scheduled for later this year. Do you know what people have been doing for view refreshes in the meantime? Thanks On Tue, Apr 1, 2014 at 11:48 PM, Michael Paquier michael.paqu...@gmail.comwrote: On Wed, Apr 2, 2014 at 1:22 PM, Christopher Jackson crjac...@gmail.com wrote: Hi Bricklen, Thanks for the feedback. I'll play around with materialized views. My understanding is they have to be manually triggered for refresh Yep. and there's an exclusive lock on the view while the refresh is taking place. Is this your understanding as well? Re-yep. I'm using PG 9.3.3. If this is true, I'm curious what clever ways people have come up with to mitigate any issues with the lock. Kevin Grittner has implemented REFRESH MATERIALIZED VIEW CONCURRENTLY in 9.4. A unique index is needed on the materialized view as well to authorize this concurrent operation. It has the merit to allow SELECT operations on the matview during the refresh. -- Michael
