> So, does anyone have any idea about the origin of this increase in
PostgreSQL activity and performance decrease?
Since pg_stat_activity is not showing anything, my typical troubleshooting
is this:
1. Have your app log the exact queries, along with the time it takes to
run. You can do this yourself, or with the debugtoolbar. I wrote a few
debugtoolbar extensions that can help; they not only track these things,
but expose the data as .csv files via an API. They may not work with the
current version of pyramid. My production environment is a bit behind.
https://github.com/jvanasco/pyramid_debugtoolbar_api_sqlalchemy
https://github.com/jvanasco/pyramid_debugtoolbar_api_performance
2. Wait for things to get jammed again.
3. When things get jammed, run the slow queries as an EXPLAIN in a pg
console. ** make sure you do this while the app is jammed **. e.g.
"EXPLAIN {QUERY}"
4. Compare this EXPLAIN strategy to running the same query on a freshly
restarted postgresql, and again on a freshly started app.
5. Also look at the history of where things slowed down, and see if it is
happening after a "very different" query
In my experience, this stuff often happens because of memory and index
loading, and can be fixed by either creating partial indexes on the
database and hinting the queries to use those indexes OR increasing the
memory allocation.
The most typical causes I've seen is the app runs really fast while a
certain query/index is being used (query A), however running a "different"
query (query B) will unload all of the indexes/data for Query A from memory
as postgres needs to load the data for Query B. This can often be fixed by
trying to minimize the footprint of indexes used for each query, and making
sure both queries use indexes.
I've also had situations where many "test indexes" were still on a table,
and writing 1 row of data was really writing 30+ rows instead of 6 rows.
(The first row is the data, the additional ones are the indexes). There
have also been situations where the rows have triggers and check
constraints that involve other rows, however those triggers and check
constraints did not utilize indexes, so they start scanning the entire
table for that data. Doing this can also unload indexes used by the main
query. This sort of stuff gets buried from normal view, and people forget
to check for it.
--
You received this message because you are subscribed to the Google Groups
"pylons-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to pylons-discuss+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/pylons-discuss/5771cbaf-f437-4bb7-b1ce-20d0b763ef10n%40googlegroups.com.
