[PERFORM] Query running slow for only one specific id. (Postgres 9.3) version
Postgresql 9.3 Version Guys Here is the issue that I'm facing for couple of weeks now. I have table (size 7GB) If I run this query with this specific registration id it is using the wrong execution plan and takes more than a minute to complete. Total number of rows for this registration_id is only 414 in this table explain analyze SELECT max(last_update_date) AS last_update_date FROM btdt_responses WHERE registration_id = 8718704208 AND response != 4; QUERY PLAN -- Result (cost=2902.98..2903.01 rows=1 width=0) (actual time=86910.730..86910.731 rows=1 loops=1) InitPlan 1 (returns $0) - Limit (cost=0.57..2902.98 rows=1 width=8) (actual time=86910.725..86910.725 rows=1 loops=1) - Index Scan Backward using btdt_responses_n5 on btdt_responses (cost=0.57..6425932.41 rows=2214 width=8) (actual time=86910.723..86910.723 rows=1 loops=1) Index Cond: (last_update_date IS NOT NULL) Filter: ((response 4) AND (registration_id = 8718704208::bigint)) Rows Removed by Filter: 52145434 Total runtime: 86910.766 ms Same query with any other registration id will come back in milli seconds explain analyze SELECT max(last_update_date) AS last_update_date FROM btdt_responses WHERE registration_id = 8688546267 AND response != 4; QUERY PLAN -- Aggregate (cost=529.75..529.78 rows=1 width=8) (actual time=19.723..19.723 rows=1 loops=1) - Index Scan using btdt_responses_u2 on btdt_responses (cost=0.57..529.45 rows=119 width=8) (actual time=0.097..19.689 rows=72 loops=1) Index Cond: (registration_id = 8688546267::bigint) Filter: (response 4) Rows Removed by Filter: 22 Total runtime: 19.769 ms Please let me know what I can do to fix this issue. Thanks
[PERFORM] Re: Query running slow for only one specific id. (Postgres 9.3) version
When I run vacuum analyze it fixes the problem but after 1 or 2 days the
problem comes back
Here is the table structure
Column |Type |
Modifiers | Storage | Stats target | Description
--+-+--+-+--+-
response_id | integer | not null default
nextval('btdt_responses_response_id_seq'::regclass) | plain | |
registration_id | bigint | not null
| plain | |
btdt_id | integer | not null
| plain | |
response | integer | not null
| plain | |
creation_date| timestamp without time zone | not null default now()
| plain | |
last_update_date | timestamp without time zone | not null default now()
| plain | |
Indexes:
btdt_responses_pkey PRIMARY KEY, btree (response_id)
btdt_responses_u2 UNIQUE, btree (registration_id, btdt_id)
btdt_responses_n1 btree (btdt_id)
btdt_responses_n2 btree (btdt_id, response)
btdt_responses_n4 btree (creation_date)
btdt_responses_n5 btree (last_update_date)
btdt_responses_n6 btree (btdt_id, last_update_date)
Foreign-key constraints:
btdt_responses_btdt_id_fkey FOREIGN KEY (btdt_id) REFERENCES
btdt_items(btdt_id)
btdt_responses_fk1 FOREIGN KEY (btdt_id) REFERENCES btdt_items(btdt_id)
Has OIDs: no
Options: autovacuum_enabled=true, autovacuum_vacuum_scale_factor=0.02,
autovacuum_analyze_scale_factor=0.02
Thanks
From: Igor Neyman [mailto:iney...@perceptron.com]
Sent: Friday, June 5, 2015 11:06 AM
To: Sheena, Prabhjot; pgsql-gene...@postgresql.org;
pgsql-performance@postgresql.org
Subject: RE: Query running slow for only one specific id. (Postgres 9.3) version
From:
pgsql-performance-ow...@postgresql.orgmailto:pgsql-performance-ow...@postgresql.org
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Sheena, Prabhjot
Sent: Friday, June 05, 2015 1:55 PM
To: pgsql-gene...@postgresql.orgmailto:pgsql-gene...@postgresql.org;
pgsql-performance@postgresql.orgmailto:pgsql-performance@postgresql.org
Subject: [PERFORM] Query running slow for only one specific id. (Postgres 9.3)
version
Postgresql 9.3 Version
Guys
Here is the issue that I'm facing for couple of weeks now. I have
table (size 7GB)
If I run this query with this specific registration id it is using the wrong
execution plan and takes more than a minute to complete. Total number of rows
for this registration_id is only 414 in this table
explain analyze SELECT max(last_update_date) AS last_update_date FROM
btdt_responses WHERE registration_id = 8718704208 AND response != 4;
QUERY PLAN
--
Result (cost=2902.98..2903.01 rows=1 width=0) (actual
time=86910.730..86910.731 rows=1 loops=1)
InitPlan 1 (returns $0)
- Limit (cost=0.57..2902.98 rows=1 width=8) (actual
time=86910.725..86910.725 rows=1 loops=1)
- Index Scan Backward using btdt_responses_n5 on btdt_responses
(cost=0.57..6425932.41 rows=2214 width=8) (actual time=86910.723..86910.723
rows=1 loops=1)
Index Cond: (last_update_date IS NOT NULL)
Filter: ((response 4) AND (registration_id =
8718704208::bigint))
Rows Removed by Filter: 52145434
Total runtime: 86910.766 ms
Same query with any other registration id will come back in milli seconds
explain analyze SELECT max(last_update_date) AS last_update_date FROM
btdt_responses WHERE registration_id = 8688546267 AND response != 4;
QUERY PLAN
--
Aggregate (cost=529.75..529.78 rows=1 width=8) (actual time=19.723..19.723
rows=1 loops=1)
- Index Scan using btdt_responses_u2 on btdt_responses (cost=0.57..529.45
rows=119 width=8) (actual time=0.097..19.689 rows=72 loops=1)
Index Cond: (registration_id = 8688546267::bigint)
Filter: (response 4)
Rows Removed by Filter: 22
Total runtime: 19.769 ms
Please let me know what I can do to fix this issue.
Thanks
Not enough info.
Table structure? Is registration_id - PK
[PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
Guys I have an issue going on with PGBOUNCER which is slowing down the site PGBOUNCER VERSION: pgbouncer-1.5.4-2.el6 (Hosted on separate machine) (16 cpu) 98GB RAM DATABASE VERION: postgresql 9.3 When the total client connections to pgbouncer are close to 1000, site application works fine but when the total client connections crosses 1150 site application starts showing slowness. Here is an example of output postgres@symds-pg:~ $ netstat -atnp | grep 5432 | wc (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) 9606720 104640 As you can see total connections are like 960 right now my site application is working fine. When connections crosses 1150 and even though I see lot of available connections coz my default_pool_size is set high to 250 but still the application gets slow. Database performance on the other end is great with no slow running queries or anything. So the only place I can think the issue is at PGBOUNCER end. pgbouncer=# show config; key| value | changeable ---+--+ job_name | pgbouncer| no conffile | /etc/pgbouncer/pgbouncer.ini | yes logfile | /var/log/pgbouncer.log| yes pidfile | /var/run/pgbouncer/pgbouncer.pid | no listen_addr | *| no listen_port | 5432 | no listen_backlog| 128 | no unix_socket_dir | /tmp | no unix_socket_mode | 511 | no unix_socket_group | | no auth_type | md5 | yes auth_file | /etc/pgbouncer/userlist.txt | yes pool_mode | transaction | yes max_client_conn | 3000 | yes default_pool_size | 250 | yes min_pool_size | 0| yes reserve_pool_size | 0| yes reserve_pool_timeout | 5| yes syslog| 0| yes syslog_facility | daemon | yes syslog_ident | pgbouncer| yes user | | no autodb_idle_timeout | 3600 | yes server_reset_query| | yes server_check_query| select 1 | yes server_check_delay| 30 | yes query_timeout | 0| yes query_wait_timeout| 0| yes client_idle_timeout | 0| yes client_login_timeout | 60 | yes idle_transaction_timeout | 0| yes server_lifetime | 3600 | yes server_idle_timeout | 600 | yes server_connect_timeout| 15 | yes server_login_retry| 15 | yes server_round_robin| 0| yes suspend_timeout | 10 | yes ignore_startup_parameters | extra_float_digits | yes disable_pqexec| 0| no dns_max_ttl | 15 | yes dns_zone_check_period | 0| yes max_packet_size | 2147483647 | yes pkt_buf | 2048 | no sbuf_loopcnt | 5| yes tcp_defer_accept | 1| yes tcp_socket_buffer | 0| yes tcp_keepalive | 1| yes tcp_keepcnt | 0| yes tcp_keepidle | 0| yes tcp_keepintvl | 0| yes verbose | 0| yes admin_users | postgres | yes stats_users | stats, postgres | yes stats_period | 60 | yes log_connections | 1| yes log_disconnections| 1| yes log_pooler_errors | 1
Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
Here is the output of OS limits postgres@symds-pg:~ $ ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 790527 max locked memory (kbytes, -l) 32 max memory size (kbytes, -m) unlimited open files (-n) 4096 pipe size(512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 16384 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited Thanks Prabhjot -Original Message- From: k...@rice.edu [mailto:k...@rice.edu] Sent: Thursday, June 18, 2015 10:16 AM To: Sheena, Prabhjot Cc: pgsql-performance@postgresql.org; pgsql-gene...@postgresql.org Subject: Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) On Thu, Jun 18, 2015 at 05:09:10PM +, Sheena, Prabhjot wrote: Guys I have an issue going on with PGBOUNCER which is slowing down the site PGBOUNCER VERSION: pgbouncer-1.5.4-2.el6 (Hosted on separate machine) (16 cpu) 98GB RAM DATABASE VERION: postgresql 9.3 When the total client connections to pgbouncer are close to 1000, site application works fine but when the total client connections crosses 1150 site application starts showing slowness. Here is an example of output postgres@symds-pg:~ $ netstat -atnp | grep 5432 | wc (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) 9606720 104640 As you can see total connections are like 960 right now my site application is working fine. When connections crosses 1150 and even though I see lot of available connections coz my default_pool_size is set high to 250 but still the application gets slow. Database performance on the other end is great with no slow running queries or anything. So the only place I can think the issue is at PGBOUNCER end. Hi Prabhjot, This is classic behavior when you have a 1024 file limit. When you are below that number, it work fine. Above that number, you must wait for a connection to close and exit before you can connect which will cause a delay. See what ulimit has to say? Regards, Ken -- 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] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
Hi Ken/ Will I have checked the ulimit value and we are nowhere hitting the max 4096 that we have currently set. Is there any other explanation why we should be thinking of bumping it to like ulimit -n 5 ( Add ulimit -n 5 to the start of whatever you use to start pgbouncer (init script, etc..)) even though we are not reaching 4096 max value Regards Prabhjot Singh -Original Message- From: k...@rice.edu [mailto:k...@rice.edu] Sent: Thursday, June 18, 2015 11:10 AM To: Sheena, Prabhjot Cc: pgsql-performance@postgresql.org; pgsql-gene...@postgresql.org Subject: Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) On Thu, Jun 18, 2015 at 05:41:01PM +, Sheena, Prabhjot wrote: Here is the output of OS limits postgres@symds-pg:~ $ ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 790527 max locked memory (kbytes, -l) 32 max memory size (kbytes, -m) unlimited open files (-n) 4096 pipe size(512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 16384 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited Thanks Prabhjot I would bump your open files as was suggested in your pgbouncer start script. Regards, Ken -- 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] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
Here is the output of pid postgres@symds-pg:~ $ cat /proc/15610/limits Limit Soft Limit Hard Limit Units Max cpu time unlimitedunlimitedseconds Max file size unlimitedunlimitedbytes Max data size unlimitedunlimitedbytes Max stack size10485760 unlimitedbytes Max core file size00bytes Max resident set unlimitedunlimitedbytes Max processes 1638416384processes Max open files4096 4096 files Max locked memory 3276832768bytes Max address space unlimitedunlimitedbytes Max file locksunlimitedunlimitedlocks Max pending signals 790527 790527 signals Max msgqueue size 819200 819200 bytes Max nice priority 00 Max realtime priority 00 Thanks Prabhjot Singh -Original Message- From: Jerry Sievers [mailto:gsiever...@comcast.net] Sent: Thursday, June 18, 2015 12:47 PM To: Sheena, Prabhjot Cc: k...@rice.edu; Will Platnick; pgsql-performance@postgresql.org; pgsql-gene...@postgresql.org Subject: Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) Sheena, Prabhjot prabhjot.si...@classmates.com writes: Hi Ken/ Will I have checked the ulimit value and we are nowhere hitting the max 4096 that we have currently set. Is there any other explanation why we should be thinking of bumping it to like ulimit -n 5 ( Add ulimit -n 5 to the start of whatever you use to start pgbouncer (init script, etc..)) even though we are not reaching 4096 max value If I can assume you're running on linux, best you get limits readout from... /proc/$PID-of-bouncer-process/limits Best not to trust that run time env of interactive shell is same as where bouncer launched from. FWIW Regards Prabhjot Singh -Original Message- From: k...@rice.edu [mailto:k...@rice.edu] Sent: Thursday, June 18, 2015 11:10 AM To: Sheena, Prabhjot Cc: pgsql-performance@postgresql.org; pgsql-gene...@postgresql.org Subject: Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) On Thu, Jun 18, 2015 at 05:41:01PM +, Sheena, Prabhjot wrote: Here is the output of OS limits postgres@symds-pg:~ $ ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 790527 max locked memory (kbytes, -l) 32 max memory size (kbytes, -m) unlimited open files (-n) 4096 pipe size(512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 16384 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited Thanks Prabhjot I would bump your open files as was suggested in your pgbouncer start script. Regards, Ken -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] pgbouncer issue
Guys can anyone please explain or point me to a link where i can understand this output for pgbouncer. What does each column of this table mean? pgbouncer=# show mem; name | size | used | free | memtotal --+--+--+--+-- user_cache | 184 | 12 | 77 |16376 db_cache | 160 |2 | 100 |16320 pool_cache | 408 |4 | 46 |20400 server_cache | 360 | 121 | 279 | 144000 client_cache | 360 | 1309 | 291 | 576000 iobuf_cache | 2064 |3 | 797 | 1651200 Thanks Prabhjot
Re: [PERFORM] pg bouncer issue what does sv_used column means
Here is some more information pool_mode | transaction We have transactional pooling and our application is set up in such a way that we have one query per transaction. We have set default pool size to 100. This is output . As you guys can see active connection are 100 and 224 are waiting. We are planning to move default pool size to 250. Please suggest if you guys think otherwise pgbouncer=# show pools; database | user| cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait ---+---+---++---+-+-+---+--+- pgbouncer | pgbouncer | 2 | 0 | 0 | 0 | 0 | 0 |0 | 0 site | feature | 418 | 0 |20 | 17 | 0 | 0 |0 | 0 site | service | 621 |224 | 100 | 0 | 0 | 0 |0 | 0 site | zabbix| 0 | 0 | 0 | 0 | 0 | 0 |0 | 0 Prabhjot Singh Database Administrator CLASSMATES 1501 4th Ave., Suite 400 Seattle, WA 98101 206.301.4937 o 206.301.5701 f From: Sheena, Prabhjot Sent: Friday, June 12, 2015 10:57 AM To: 'pgsql-gene...@postgresql.org'; 'pgsql-performance@postgresql.org' Subject: pg bouncer issue what does sv_used column means Guys we see spike in pg bouncer during the peak hours and that was slowing down the application. We did bump up the connection limit and it is helpful but now we again notice little spike in connection. And one thing that I notice that is different is jump in sv_used value when I run command show pools during problem times Can anyone please explain what value of sv_used means when i run show pools; Regards Prabhjot
[PERFORM] pg bouncer issue what does sv_used column means
Guys we see spike in pg bouncer during the peak hours and that was slowing down the application. We did bump up the connection limit and it is helpful but now we again notice little spike in connection. And one thing that I notice that is different is jump in sv_used value when I run command show pools during problem times Can anyone please explain what value of sv_used means when i run show pools; Regards Prabhjot
[PERFORM] postgresql upgrade from 9.3 to 9.4 error
Guys I m trying to upgrade postgresql 9.3 -> 9.4
OS VERSION : CentOS release 6.3 (Final) (2.6.32-279.el6.x86_64.x86_64)
When I run the upgrade script /usr/pgsql-9.4/bin/pg_upgrade
It gives me this error in the log file.
pg_restore: creating VIEW tables
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1206; 1259 10190710 VIEW
tables postgres
pg_restore: [archiver (db)] could not execute query: ERROR: column
pg_class.reltoastidxid does not exist
LINE 19: ELSE ( SELECT "pg_class"."reltoastidxid"
^
Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT binary_upgrade.set_next_pg_type_oid('10190712'::pg_catalog.oid);
Please let me know what is going on here and what I can do to get this fixed
Thanks
Prabhjot
