On Thu, Jul 2, 2015 at 9:57 PM Lukasz Wrobel < lukasz.wro...@motorolasolutions.com> wrote:
> Hello again. > > Thank you for all your responses. I will try to clarify more and attempt > to answer the questions you raised. > > I'm attaching the postgresql.conf this time. I cannot supply you guys with > a proper database schema, so I will try to supply you with some obfuscated > logs and queries. Sorry for the complication. > You postgresql.conf seems to have some issues. Can you explain about the choice of parameter values for below parameters? maintenance_work_mem = 32MB bgwriter_lru_maxpages = 0 synchronous_commit = off effective_cache_size is left to default random_page_cost is left to default I don't know anything about your hardware- memory, cpu and disk layout (and IOPS of disk) so can not really say what would be the right setting but this certainly does not seem right to me. > > First of all I seem to have misdirected you guys about the pg_stat* > tables. I have a virtual machine with the database from our test team, > which was running for a month. When I deploy it, our java application is > not running, so no queries are being executed. The pg_stat* tables contain > no data (which is surprising). When I launch the application and queries > start going, the stats are collected normally and autovacuums are being > performed. > It is still confusing to me. To help us understand can you specifically tell if you see anything in pg_stat_user_tables and pg_stat_user_indexes? > > I attached the output of vacuum verbose command. > > Seems like a lot of your tables have bloats > As for the pg_stat_activity, I have no "idle in transaction" records > there, but I do have some in "idle" state, that don't disappear. Perhaps > this means some sessions are not closed? I attached the query result as > activity.txt. > > I also have a few "sending cancel to blocking autovacuum" and "canceling > autovacuum task" messages in syslog. > > Can you share some of these log files? > Sample query explain analyze. This was ran after vacuum analyze of the > entire database. > > explain analyze SELECT col1, col2, col3, col4, col5 FROM ( table84 table84 > LEFT JOIN table57 table57 ON table84.col7 = table57.col7 ) LEFT JOIN > table19 table19 ON table84.col7 = table19.col7; > QUERY > PLAN > > ----------------------------------------------------------------------------------------------------------------------------------------------------- > Hash Right Join (cost=46435.43..108382.29 rows=189496 width=79) (actual > time=4461.686..13457.233 rows=5749 loops=1) > Hash Cond: (table57.col7 = table84.col7) > -> Seq Scan on table57 table57 (cost=0.00..49196.63 rows=337963 > width=57) (actual time=0.040..8981.438 rows=6789 loops=1) > -> Hash (cost=42585.73..42585.73 rows=189496 width=38) (actual > time=4447.731..4447.731 rows=5749 loops=1) > Buckets: 16384 Batches: 2 Memory Usage: 203kB > -> Hash Right Join (cost=18080.66..42585.73 rows=189496 > width=38) (actual time=1675.223..4442.046 rows=5749 loops=1) > Hash Cond: (table19.col7 = table84.col7) > -> Seq Scan on table19 table19 (cost=0.00..17788.17 > rows=187317 width=26) (actual time=0.007..2756.501 rows=5003 loops=1) > -> Hash (cost=14600.96..14600.96 rows=189496 width=20) > (actual time=1674.940..1674.940 rows=5749 loops=1) > Buckets: 32768 Batches: 2 Memory Usage: 159kB > -> Seq Scan on table84 table84 (cost=0.00..14600.96 > rows=189496 width=20) (actual time=0.059..1661.482 rows=5749 loops=1) > Total runtime: 13458.301 ms > (12 rows) > You have a lot of issues with this plan- - The statistics is not updated - There is a lot of hash join, sequential scan implying you don't have proper indexes or those are not useful (meaning your indexes are bloated too, consider reindexing them) > > Thank you again for your advice and I hope that with your help I'll be > able to solve this issue. > > Best regards. > Lukasz > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
