Re: [GENERAL] very slow queries and ineffective vacuum
Well, right off the bat, it looks like you do not have indexes on table84.col7 table57.col7 table19.col7 At least a quick review of the query plan shows they are not being used if they do exist. So perhaps that is one of the chief causes for slow performance. On Thu, Jul 2, 2015 at 6:58 AM, 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. 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. I attached the output of vacuum verbose command. 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. 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) 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 -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] very slow queries and ineffective vacuum
2015-07-03 7:18 GMT+02:00 Sameer Kumar sameer.ku...@ashnik.com: 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. I checked a VACUUM log, and it looks well - so maybe you run VACUUM with too small frequency and now some tables needs VACUUM FULL, and some indexes needs REINDEX. When your read 5000 rows 2sec, then some some is strange - probably too less data density in data file. If you do some massive cleaning, more than 30%, it is good idea to run VACUUM FULL, if it is possible manually. Or if you can - use partitioning - then you drop a partition without negative effect on other data. Regards Pavel 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
Re: [GENERAL] very slow queries and ineffective vacuum
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
Re: [GENERAL] very slow queries and ineffective vacuum
On Thu, 2 Jul 2015 12:58:18 +0200 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. 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. I attached the output of vacuum verbose command. 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. Sample query explain analyze. This was ran after vacuum analyze of the entire database. The analyze doesn't seem to be working terribly well. Looking at the explain, it expects 337963 rows in table57, but there are only 6789. There are similar discrepencies with table19 and table84. I don't know if indexes are your problem. Those three tables are pretty small, so the sequential scans should be pretty quick (probably faster than index scans, since it looks like most of the rows are returned from all the tables. I'm somewhat confused by your description of the situation. Is the performance problem happening on the virtual machine? Because VMs are notorious for being on oversubscribed hosts and exhibiting performance far below what is expected. It would be worthwhile to do some disk speed and CPU speed tests on the VM to see what kind of performance it's actually capable of ... if the VM is performing poorly, there's not much you can do with PostgreSQL to improve things. 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) Thank you again for your advice and I hope that with your help I'll be able to solve this issue. Best regards. Lukasz -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] very slow queries and ineffective vacuum
On Wed, Jul 1, 2015 at 3:37 AM Lukasz Wrobel lukasz.wro...@motorolasolutions.com wrote: Hello. I have multiple problems with my database, the biggest of which is how to find out what is actually wrong. First of all I have a 9.3 postgres database that is running for about a month. Right now the queries on that database are running very slowly (select with a simple where on a non-indexed column on a table with about 5000 records takes 1,5s, a complicated hibernate select with 7 joins on tables of about 5000 records takes about 15s, insert or update on a table with 35000 records takes up to 20 mins). The tables and indexes on those tables are bloated to the point where this query: https://wiki.postgresql.org/wiki/Show_database_bloat shows wasted bytes in hundreds of MB. For whatever reason there is also no data in pg_stat* tables. Make sure that your tracking parameters are on- track_counts and track_activities So due to the long query times, there are multiple errors in my application logs like No free connection available or Could not synchronize database state with session, or Failed to rollback transaction and the application fails to start in the required time. One of things you can do is to set statement timeout in PostgreSQL configuration (but that may actually increase your problems by cancelling long running queries which seems to be too many in your case). The only thing that helps fix the situation seems to be vacuum full of the entire database. Regular vacuum doesn't even lower the dead tuples count (which appear by the thousands during application launching). Though I am not very sure but to me it seems this could be because your track_counts and track_activities is not set to on. Since your are not tracking them they are not being updated at all. try this- vacuum analyze a table vacuum analyze schema_name.table_name; reindex one of that table reindex table schema_name.table_name; Reindex of all the indexes in the database didn't help as well. All autovacuum parameters are default. Did you analyze the database tables? Since your track_count is off (I have assumed based on your above statements) your database tables might never have been analyzed which could be leading to wrong/sub-optimal plans. There doesn't seem to be any issues with disk space, memory or CPU, as neither of those is even 50% used (as per df and top). Is there any good tool that will monitor the queries and generate a report with useful information on what might be the problem? I tried pg_badger, but all I got were specific queries and their times, but the long query times are just one of the symptoms of what's wrong with the database, not the cause. Perhaps I'm missing some indexes on the tables (creating them on the columns on which the where clause was used in the long queries seemed to halve their times). Yes, if you create indexes then certainly those will be helpful depending on the volume of data in that table. Also how can I monitor my transactions and if they are closed properly? Check pg_stat_activity view. There is a column for state of the connection check there are too many connections in IDLE in transaction state. This means a connection has initiated a transaction but has not committed it yet. You can combine the state with status change time (state_change) column- select * from pg_stat_activity where now()-state_change'1 min'::interval and state='idle in transaction'; This will list all those sessions which have not committed for last one minute. You can look at using pgBouncer to effectively manage your sessions and connections. I will be grateful for any help and if you need more details I can provide them if possible. Best regards. Lukasz
Re: [GENERAL] very slow queries and ineffective vacuum
On Wed, Jul 1, 2015 at 4:51 AM Pavel Stehule pavel.steh...@gmail.com wrote: Hi What is an output of VACUUM VERBOSE statement? VACUUM can be blocked by some forgotten transaction. Check your pg_stat_activity table for some old process in idle in transaction state. Then connection should not be reused, and you can see a error messages about missing connections. I found this issue more time in Java application - when it doesn't handle transactions correctly. Same effect can have forgotten 2PC transaction. When VACUUM long time was not executed - the most fast repair process is a export via pg_dump and load. Another way is dropping all indexes, VACUUM FULL and creating fresh indexes. Autovacuum is based on tracking statistics - you have to see your tables in table pg_stat_user_tables, and you can check there autovacuum timestamp. Sometimes autovacuum has too low priority and it is often cancelled. As he has mentioned that he can not see anything in pg_stat* table which means that probably track_count and track_activities is set to off. In that case won't autovacuum be *unable* to do anything (since count of row changes etc is not being captured)? Regards Pavel Stehule 2015-06-30 14:57 GMT+02:00 Lukasz Wrobel lukasz.wro...@motorolasolutions.com: Hello. I have multiple problems with my database, the biggest of which is how to find out what is actually wrong. First of all I have a 9.3 postgres database that is running for about a month. Right now the queries on that database are running very slowly (select with a simple where on a non-indexed column on a table with about 5000 records takes 1,5s, a complicated hibernate select with 7 joins on tables of about 5000 records takes about 15s, insert or update on a table with 35000 records takes up to 20 mins). The tables and indexes on those tables are bloated to the point where this query: https://wiki.postgresql.org/wiki/Show_database_bloat shows wasted bytes in hundreds of MB. For whatever reason there is also no data in pg_stat* tables. So due to the long query times, there are multiple errors in my application logs like No free connection available or Could not synchronize database state with session, or Failed to rollback transaction and the application fails to start in the required time. The only thing that helps fix the situation seems to be vacuum full of the entire database. Regular vacuum doesn't even lower the dead tuples count (which appear by the thousands during application launching). Reindex of all the indexes in the database didn't help as well. All autovacuum parameters are default. There doesn't seem to be any issues with disk space, memory or CPU, as neither of those is even 50% used (as per df and top). Is there any good tool that will monitor the queries and generate a report with useful information on what might be the problem? I tried pg_badger, but all I got were specific queries and their times, but the long query times are just one of the symptoms of what's wrong with the database, not the cause. Perhaps I'm missing some indexes on the tables (creating them on the columns on which the where clause was used in the long queries seemed to halve their times). Also how can I monitor my transactions and if they are closed properly? I will be grateful for any help and if you need more details I can provide them if possible. Best regards. Lukasz
[GENERAL] very slow queries and ineffective vacuum
Hello. I have multiple problems with my database, the biggest of which is how to find out what is actually wrong. First of all I have a 9.3 postgres database that is running for about a month. Right now the queries on that database are running very slowly (select with a simple where on a non-indexed column on a table with about 5000 records takes 1,5s, a complicated hibernate select with 7 joins on tables of about 5000 records takes about 15s, insert or update on a table with 35000 records takes up to 20 mins). The tables and indexes on those tables are bloated to the point where this query: https://wiki.postgresql.org/wiki/Show_database_bloat shows wasted bytes in hundreds of MB. For whatever reason there is also no data in pg_stat* tables. So due to the long query times, there are multiple errors in my application logs like No free connection available or Could not synchronize database state with session, or Failed to rollback transaction and the application fails to start in the required time. The only thing that helps fix the situation seems to be vacuum full of the entire database. Regular vacuum doesn't even lower the dead tuples count (which appear by the thousands during application launching). Reindex of all the indexes in the database didn't help as well. All autovacuum parameters are default. There doesn't seem to be any issues with disk space, memory or CPU, as neither of those is even 50% used (as per df and top). Is there any good tool that will monitor the queries and generate a report with useful information on what might be the problem? I tried pg_badger, but all I got were specific queries and their times, but the long query times are just one of the symptoms of what's wrong with the database, not the cause. Perhaps I'm missing some indexes on the tables (creating them on the columns on which the where clause was used in the long queries seemed to halve their times). Also how can I monitor my transactions and if they are closed properly? I will be grateful for any help and if you need more details I can provide them if possible. Best regards. Lukasz
Re: [GENERAL] very slow queries and ineffective vacuum
Hi What is an output of VACUUM VERBOSE statement? VACUUM can be blocked by some forgotten transaction. Check your pg_stat_activity table for some old process in idle in transaction state. Then connection should not be reused, and you can see a error messages about missing connections. I found this issue more time in Java application - when it doesn't handle transactions correctly. Same effect can have forgotten 2PC transaction. When VACUUM long time was not executed - the most fast repair process is a export via pg_dump and load. Another way is dropping all indexes, VACUUM FULL and creating fresh indexes. Autovacuum is based on tracking statistics - you have to see your tables in table pg_stat_user_tables, and you can check there autovacuum timestamp. Sometimes autovacuum has too low priority and it is often cancelled. Regards Pavel Stehule 2015-06-30 14:57 GMT+02:00 Lukasz Wrobel lukasz.wro...@motorolasolutions.com: Hello. I have multiple problems with my database, the biggest of which is how to find out what is actually wrong. First of all I have a 9.3 postgres database that is running for about a month. Right now the queries on that database are running very slowly (select with a simple where on a non-indexed column on a table with about 5000 records takes 1,5s, a complicated hibernate select with 7 joins on tables of about 5000 records takes about 15s, insert or update on a table with 35000 records takes up to 20 mins). The tables and indexes on those tables are bloated to the point where this query: https://wiki.postgresql.org/wiki/Show_database_bloat shows wasted bytes in hundreds of MB. For whatever reason there is also no data in pg_stat* tables. So due to the long query times, there are multiple errors in my application logs like No free connection available or Could not synchronize database state with session, or Failed to rollback transaction and the application fails to start in the required time. The only thing that helps fix the situation seems to be vacuum full of the entire database. Regular vacuum doesn't even lower the dead tuples count (which appear by the thousands during application launching). Reindex of all the indexes in the database didn't help as well. All autovacuum parameters are default. There doesn't seem to be any issues with disk space, memory or CPU, as neither of those is even 50% used (as per df and top). Is there any good tool that will monitor the queries and generate a report with useful information on what might be the problem? I tried pg_badger, but all I got were specific queries and their times, but the long query times are just one of the symptoms of what's wrong with the database, not the cause. Perhaps I'm missing some indexes on the tables (creating them on the columns on which the where clause was used in the long queries seemed to halve their times). Also how can I monitor my transactions and if they are closed properly? I will be grateful for any help and if you need more details I can provide them if possible. Best regards. Lukasz
Re: [GENERAL] very slow queries and ineffective vacuum
Hello Lukasz, On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel lukasz.wro...@motorolasolutions.com wrote: There doesn't seem to be any issues with disk space, memory or CPU, as neither of those is even 50% used (as per df and top). Are you using the default PostgreSQL configuration settings, or have you custom tuned them? The default settings are targeted for wide compatibility and are not optimized for performance. If PostgreSQL is performing badly and using a small amount of system resources it is likely some tuning is needed. See docs: http://www.postgresql.org/docs/current/static/runtime-config.html On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel lukasz.wro...@motorolasolutions.com wrote: For whatever reason there is also no data in pg_stat* tables. You can also turn on tracking (for statistics views) by enabling statistics collection in the config http://www.postgresql.org/docs/current/static/runtime-config-statistics.html *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel lukasz.wro...@motorolasolutions.com wrote: Hello. I have multiple problems with my database, the biggest of which is how to find out what is actually wrong. First of all I have a 9.3 postgres database that is running for about a month. Right now the queries on that database are running very slowly (select with a simple where on a non-indexed column on a table with about 5000 records takes 1,5s, a complicated hibernate select with 7 joins on tables of about 5000 records takes about 15s, insert or update on a table with 35000 records takes up to 20 mins). The tables and indexes on those tables are bloated to the point where this query: https://wiki.postgresql.org/wiki/Show_database_bloat shows wasted bytes in hundreds of MB. For whatever reason there is also no data in pg_stat* tables. So due to the long query times, there are multiple errors in my application logs like No free connection available or Could not synchronize database state with session, or Failed to rollback transaction and the application fails to start in the required time. The only thing that helps fix the situation seems to be vacuum full of the entire database. Regular vacuum doesn't even lower the dead tuples count (which appear by the thousands during application launching). Reindex of all the indexes in the database didn't help as well. All autovacuum parameters are default. There doesn't seem to be any issues with disk space, memory or CPU, as neither of those is even 50% used (as per df and top). Is there any good tool that will monitor the queries and generate a report with useful information on what might be the problem? I tried pg_badger, but all I got were specific queries and their times, but the long query times are just one of the symptoms of what's wrong with the database, not the cause. Perhaps I'm missing some indexes on the tables (creating them on the columns on which the where clause was used in the long queries seemed to halve their times). Also how can I monitor my transactions and if they are closed properly? I will be grateful for any help and if you need more details I can provide them if possible. Best regards. Lukasz
Re: [GENERAL] very slow queries and ineffective vacuum
On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel lukasz.wro...@motorolasolutions.com wrote: Perhaps I'm missing some indexes on the tables (creating them on the columns on which the where clause was used in the long queries seemed to halve their times). Also how can I monitor my transactions and if they are closed properly? To track transactions that have not been left idle but not committed or rolled back you would: 1) Set track_activities true in the config (doc: http://www.postgresql.org/docs/current/static/runtime-config-statistics.html#GUC-TRACK-ACTIVITIES ) 2) Query the pg_stat_activity view for connections where state = 'idle in transaction' (doc: http://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW ) As you would suspect, transactions that have been left idle in transaction prevent vacuum from removing old tuples (because they are still in scope for that transaction) *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Tue, Jun 30, 2015 at 4:27 PM, William Dunn dunn...@gmail.com wrote: Hello Lukasz, On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel lukasz.wro...@motorolasolutions.com wrote: There doesn't seem to be any issues with disk space, memory or CPU, as neither of those is even 50% used (as per df and top). Are you using the default PostgreSQL configuration settings, or have you custom tuned them? The default settings are targeted for wide compatibility and are not optimized for performance. If PostgreSQL is performing badly and using a small amount of system resources it is likely some tuning is needed. See docs: http://www.postgresql.org/docs/current/static/runtime-config.html On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel lukasz.wro...@motorolasolutions.com wrote: For whatever reason there is also no data in pg_stat* tables. You can also turn on tracking (for statistics views) by enabling statistics collection in the config http://www.postgresql.org/docs/current/static/runtime-config-statistics.html *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel lukasz.wro...@motorolasolutions.com wrote: Hello. I have multiple problems with my database, the biggest of which is how to find out what is actually wrong. First of all I have a 9.3 postgres database that is running for about a month. Right now the queries on that database are running very slowly (select with a simple where on a non-indexed column on a table with about 5000 records takes 1,5s, a complicated hibernate select with 7 joins on tables of about 5000 records takes about 15s, insert or update on a table with 35000 records takes up to 20 mins). The tables and indexes on those tables are bloated to the point where this query: https://wiki.postgresql.org/wiki/Show_database_bloat shows wasted bytes in hundreds of MB. For whatever reason there is also no data in pg_stat* tables. So due to the long query times, there are multiple errors in my application logs like No free connection available or Could not synchronize database state with session, or Failed to rollback transaction and the application fails to start in the required time. The only thing that helps fix the situation seems to be vacuum full of the entire database. Regular vacuum doesn't even lower the dead tuples count (which appear by the thousands during application launching). Reindex of all the indexes in the database didn't help as well. All autovacuum parameters are default. There doesn't seem to be any issues with disk space, memory or CPU, as neither of those is even 50% used (as per df and top). Is there any good tool that will monitor the queries and generate a report with useful information on what might be the problem? I tried pg_badger, but all I got were specific queries and their times, but the long query times are just one of the symptoms of what's wrong with the database, not the cause. Perhaps I'm missing some indexes on the tables (creating them on the columns on which the where clause was used in the long queries seemed to halve their times). Also how can I monitor my transactions and if they are closed properly? I will be grateful for any help and if you need more details I can provide them if possible. Best regards. Lukasz
Re: [GENERAL] very slow queries and ineffective vacuum
Sorry I meant to say, To track transactions that *have been* left idle but not committed or rolled back you would... Typo *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Tue, Jun 30, 2015 at 4:33 PM, William Dunn dunn...@gmail.com wrote: On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel lukasz.wro...@motorolasolutions.com wrote: Perhaps I'm missing some indexes on the tables (creating them on the columns on which the where clause was used in the long queries seemed to halve their times). Also how can I monitor my transactions and if they are closed properly? To track transactions that have not been left idle but not committed or rolled back you would: 1) Set track_activities true in the config (doc: http://www.postgresql.org/docs/current/static/runtime-config-statistics.html#GUC-TRACK-ACTIVITIES ) 2) Query the pg_stat_activity view for connections where state = 'idle in transaction' (doc: http://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW ) As you would suspect, transactions that have been left idle in transaction prevent vacuum from removing old tuples (because they are still in scope for that transaction) *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Tue, Jun 30, 2015 at 4:27 PM, William Dunn dunn...@gmail.com wrote: Hello Lukasz, On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel lukasz.wro...@motorolasolutions.com wrote: There doesn't seem to be any issues with disk space, memory or CPU, as neither of those is even 50% used (as per df and top). Are you using the default PostgreSQL configuration settings, or have you custom tuned them? The default settings are targeted for wide compatibility and are not optimized for performance. If PostgreSQL is performing badly and using a small amount of system resources it is likely some tuning is needed. See docs: http://www.postgresql.org/docs/current/static/runtime-config.html On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel lukasz.wro...@motorolasolutions.com wrote: For whatever reason there is also no data in pg_stat* tables. You can also turn on tracking (for statistics views) by enabling statistics collection in the config http://www.postgresql.org/docs/current/static/runtime-config-statistics.html *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel lukasz.wro...@motorolasolutions.com wrote: Hello. I have multiple problems with my database, the biggest of which is how to find out what is actually wrong. First of all I have a 9.3 postgres database that is running for about a month. Right now the queries on that database are running very slowly (select with a simple where on a non-indexed column on a table with about 5000 records takes 1,5s, a complicated hibernate select with 7 joins on tables of about 5000 records takes about 15s, insert or update on a table with 35000 records takes up to 20 mins). The tables and indexes on those tables are bloated to the point where this query: https://wiki.postgresql.org/wiki/Show_database_bloat shows wasted bytes in hundreds of MB. For whatever reason there is also no data in pg_stat* tables. So due to the long query times, there are multiple errors in my application logs like No free connection available or Could not synchronize database state with session, or Failed to rollback transaction and the application fails to start in the required time. The only thing that helps fix the situation seems to be vacuum full of the entire database. Regular vacuum doesn't even lower the dead tuples count (which appear by the thousands during application launching). Reindex of all the indexes in the database didn't help as well. All autovacuum parameters are default. There doesn't seem to be any issues with disk space, memory or CPU, as neither of those is even 50% used (as per df and top). Is there any good tool that will monitor the queries and generate a report with useful information on what might be the problem? I tried pg_badger, but all I got were specific queries and their times, but the long query times are just one of the symptoms of what's wrong with the database, not the cause. Perhaps I'm missing some indexes on the tables (creating them on the columns on which the where clause was used in the long queries seemed to halve their times). Also how can I monitor my transactions and if they are closed properly? I will be grateful for any help and if you need more details I can provide them if possible. Best regards. Lukasz
Re: [GENERAL] very slow queries and ineffective vacuum
Lukasz Wrobel wrote: Hello. I have multiple problems with my database, the biggest of which is how to find out what is actually wrong. First of all I have a 9.3 postgres database that is running for about a month. Right now the queries on that database are running very slowly (select with a simple where on a non-indexed column on a table with about 5000 records takes 1,5s, a complicated hibernate select with 7 joins on tables of about 5000 records takes about 15s, insert or update on a table with 35000 records takes up to 20 mins). What's your operating system? What does pg_stat_user_tables tell you about the vacuum times for the bloated tables? Mainly, is autovacuum processing them at all? If not, are there log entries about autovacuum trouble (those would show up as ERROR mentioning automatic vacuuming)? If not, is autovacuum running at all, and is the stats collector working properly? I'd recommend setting log_autovacuum_min_duration to a value other than the default -1 and see whether it is doing anything. Also useful for debugging would be the VACUUM VERBOSE output for problematic tables. Maybe your tuple death rate is higher than what autovacuum can cope with, with default settings. In that case maybe you need a larger autovacuum_max_workers setting and/or a decrease of autovacuum_vacuum_cost_delay and/or a change of autovacuum_naptime. Sometimes, manual vacuuming of individual problematic tables also helps. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] very slow queries and ineffective vacuum
Jerry, On Tue, Jun 30, 2015 at 5:31 PM, Jerry Sievers gsiever...@comcast.net wrote: foodb/postgres =# \d pg_stat_activity|pg_prepared_xacts View pg_catalog.pg_prepared_xacts Column| Type | Modifiers -+--+--- transaction | xid | gid | text | prepared| timestamp with time zone | owner | name | database| name | View pg_catalog.pg_stat_activity Column | Type | Modifiers --+--+--- datid| oid | datname | name | pid | integer | usesysid | oid | usename | name | application_name | text | client_addr | inet | client_hostname | text | client_port | integer | backend_start| timestamp with time zone | xact_start | timestamp with time zone | query_start | timestamp with time zone | state_change | timestamp with time zone | waiting | boolean | state| text | query| text | foodb/postgres =# What exactly are you trying to tell us? If you want to provide someone details about one of the system views it is probably better to link them to the official documentation which lists not only the view's fields and their datatype but also their meaning,what they will be in their specific Postgres version, and any additional notes the community deemed useful *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Tue, Jun 30, 2015 at 5:31 PM, Jerry Sievers gsiever...@comcast.net wrote: William Dunn dunn...@gmail.com writes: Sorry I meant to say, To track transactions that have been left idle but not committed or rolled back you would... Typo foodb/postgres =# \d pg_stat_activity|pg_prepared_xacts View pg_catalog.pg_prepared_xacts Column| Type | Modifiers -+--+--- transaction | xid | gid | text | prepared| timestamp with time zone | owner | name | database| name | View pg_catalog.pg_stat_activity Column | Type | Modifiers --+--+--- datid| oid | datname | name | pid | integer | usesysid | oid | usename | name | application_name | text | client_addr | inet | client_hostname | text | client_port | integer | backend_start| timestamp with time zone | xact_start | timestamp with time zone | query_start | timestamp with time zone | state_change | timestamp with time zone | waiting | boolean | state| text | query| text | foodb/postgres =# Will J. Dunn willjdunn.com On Tue, Jun 30, 2015 at 4:33 PM, William Dunn dunn...@gmail.com wrote: On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel lukasz.wro...@motorolasolutions.com wrote: Perhaps I'm missing some indexes on the tables (creating them on the columns on which the where clause was used in the long queries seemed to halve their times). Also how can I monitor my transactions and if they are closed properly? To track transactions that have not been left idle but not committed or rolled back you would: 1) Set track_activities true in the config (doc: http://www.postgresql.org/docs/current/static/runtime-config-statistics.html#GUC-TRACK-ACTIVITIES ) 2) Query the pg_stat_activity view for connections where state = 'idle in transaction' (doc: http://www.postgresql.org/docs/current/static/monitoring-stats.html# PG-STAT-ACTIVITY-VIEW) As you would suspect, transactions that have been left idle in transaction prevent vacuum from removing old tuples (because they are still in scope for that transaction) Will J. Dunn willjdunn.com On Tue, Jun 30, 2015 at 4:27 PM, William Dunn dunn...@gmail.com wrote: Hello Lukasz, On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel lukasz.wro...@motorolasolutions.com wrote: There doesn't seem to be any issues with disk space, memory or CPU, as neither of those is even 50% used (as
Re: [GENERAL] very slow queries and ineffective vacuum
William Dunn dunn...@gmail.com writes: Sorry I meant to say, To track transactions that have been left idle but not committed or rolled back you would... Typo foodb/postgres =# \d pg_stat_activity|pg_prepared_xacts View pg_catalog.pg_prepared_xacts Column| Type | Modifiers -+--+--- transaction | xid | gid | text | prepared| timestamp with time zone | owner | name | database| name | View pg_catalog.pg_stat_activity Column | Type | Modifiers --+--+--- datid| oid | datname | name | pid | integer | usesysid | oid | usename | name | application_name | text | client_addr | inet | client_hostname | text | client_port | integer | backend_start| timestamp with time zone | xact_start | timestamp with time zone | query_start | timestamp with time zone | state_change | timestamp with time zone | waiting | boolean | state| text | query| text | foodb/postgres =# Will J. Dunn willjdunn.com On Tue, Jun 30, 2015 at 4:33 PM, William Dunn dunn...@gmail.com wrote: On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel lukasz.wro...@motorolasolutions.com wrote: Perhaps I'm missing some indexes on the tables (creating them on the columns on which the where clause was used in the long queries seemed to halve their times). Also how can I monitor my transactions and if they are closed properly? To track transactions that have not been left idle but not committed or rolled back you would: 1) Set track_activities true in the config (doc: http://www.postgresql.org/docs/current/static/runtime-config-statistics.html#GUC-TRACK-ACTIVITIES) 2) Query the pg_stat_activity view for connections where state = 'idle in transaction' (doc: http://www.postgresql.org/docs/current/static/monitoring-stats.html# PG-STAT-ACTIVITY-VIEW) As you would suspect, transactions that have been left idle in transaction prevent vacuum from removing old tuples (because they are still in scope for that transaction) Will J. Dunn willjdunn.com On Tue, Jun 30, 2015 at 4:27 PM, William Dunn dunn...@gmail.com wrote: Hello Lukasz, On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel lukasz.wro...@motorolasolutions.com wrote: There doesn't seem to be any issues with disk space, memory or CPU, as neither of those is even 50% used (as per df and top). Are you using the default PostgreSQL configuration settings, or have you custom tuned them? The default settings are targeted for wide compatibility and are not optimized for performance. If PostgreSQL is performing badly and using a small amount of system resources it is likely some tuning is needed. See docs: http:// www.postgresql.org/docs/current/static/runtime-config.html On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel lukasz.wro...@motorolasolutions.com wrote: For whatever reason there is also no data in pg_stat* tables. You can also turn on tracking (for statistics views) by enabling statistics collection in the config http://www.postgresql.org/docs/current/static/ runtime-config-statistics.html Will J. Dunn willjdunn.com On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel lukasz.wro...@motorolasolutions.com wrote: Hello. I have multiple problems with my database, the biggest of which is how to find out what is actually wrong. First of all I have a 9.3 postgres database that is running for about a month. Right now the queries on that database are running very slowly (select with a simple where on a non-indexed column on a table with about 5000 records takes 1,5s, a complicated hibernate select with 7 joins on tables of about 5000 records takes about 15s, insert or update on a table with 35000 records takes up to 20 mins). The tables and indexes on those tables are bloated to the point where this query: https://wiki.postgresql.org/wiki/Show_database_bloat shows wasted bytes in hundreds of MB. For whatever reason there is also no data in pg_stat* tables. So due to the
Re: [GENERAL] very slow queries and ineffective vacuum
How about your start by giving us a little useful information? Show us your_longest_query and the output from EXPLAIN your_longest_query; Although you say you have indexes, they may not be the correct indexes that you really need. Also, how many physical disks do you have? Do you have multiple tablespaces, if so, are your tables and indexes assigned separate tablespaces? On Tue, Jun 30, 2015 at 6:16 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Lukasz Wrobel wrote: Hello. I have multiple problems with my database, the biggest of which is how to find out what is actually wrong. First of all I have a 9.3 postgres database that is running for about a month. Right now the queries on that database are running very slowly (select with a simple where on a non-indexed column on a table with about 5000 records takes 1,5s, a complicated hibernate select with 7 joins on tables of about 5000 records takes about 15s, insert or update on a table with 35000 records takes up to 20 mins). What's your operating system? What does pg_stat_user_tables tell you about the vacuum times for the bloated tables? Mainly, is autovacuum processing them at all? If not, are there log entries about autovacuum trouble (those would show up as ERROR mentioning automatic vacuuming)? If not, is autovacuum running at all, and is the stats collector working properly? I'd recommend setting log_autovacuum_min_duration to a value other than the default -1 and see whether it is doing anything. Also useful for debugging would be the VACUUM VERBOSE output for problematic tables. Maybe your tuple death rate is higher than what autovacuum can cope with, with default settings. In that case maybe you need a larger autovacuum_max_workers setting and/or a decrease of autovacuum_vacuum_cost_delay and/or a change of autovacuum_naptime. Sometimes, manual vacuuming of individual problematic tables also helps. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
