Re: [PERFORM] Slow query help
>I ask your help to solve a slow query which is taking more than 14 seconds to >be executed. >Maybe I am asking too much both from you and specially from postgresql, as it >is really huge, envolving 16 tables. > >Explain: >http://explain.depesz.com/s/XII9 > >Schema: >http://adj.com.br/erp/data_schema/ Hello, It seems that you don't pay much attention to column alignment. e.g. http://adj.com.br/erp/data_schema/tables/ERP_PUBLIC_sys_person.html This probably won't make any significant difference in your case, but this is something to be aware of when dealing with large tables. here is a good starting link for this topic: http://stackoverflow.com/questions/12604744/does-the-order-of-columns-in-a-postgres-table-impact-performance regards, Marc Mamin
[PERFORM] Slow query help
Hi, I ask your help to solve a slow query which is taking more than 14 seconds to be executed. Maybe I am asking too much both from you and specially from postgresql, as it is really huge, envolving 16 tables. Explain: http://explain.depesz.com/s/XII9 Schema: http://adj.com.br/erp/data_schema/ Version: PostgreSQL 9.2.14 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit OS: Centos 7.1 *Linux centos01.insoliti.com.br 3.10.0-327.3.1.el7.x86_64 #1 SMP Wed Dec 9 14:09:15 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux * * contains large objects: no * has a large proportion of NULLs in several columns: maybe * receives a large number of UPDATEs or DELETEs regularly: no * is growing rapidly: no * has many indexes on it: maybe (please see schema) * uses triggers that may be executing database functions, or is calling functions directly: in some cases * *History:*the system is still being developed. * *Hardware*: this is the development environment, a Dell T110-II server, with 8GB of ram and cpu as follows processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 58 model name : Intel(R) Pentium(R) CPU G2120 @ 3.10GHz stepping: 9 microcode : 0x1b cpu MHz : 1663.101 cache size : 3072 KB physical id : 0 siblings: 2 core id : 0 cpu cores : 2 apicid : 0 initial apicid : 0 fpu : yes fpu_exception : yes cpuid level : 13 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm pcid sse4_1 sse4_2 popcnt tsc_deadline_timer xsave lahf_lm arat epb pln pts dtherm tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms xsaveopt bogomips: 6185.92 clflush size: 64 cache_alignment : 64 address sizes : 36 bits physical, 48 bits virtual power management: processor : 1 vendor_id : GenuineIntel cpu family : 6 model : 58 model name : Intel(R) Pentium(R) CPU G2120 @ 3.10GHz stepping: 9 microcode : 0x1b cpu MHz : 1647.722 cache size : 3072 KB physical id : 0 siblings: 2 core id : 1 cpu cores : 2 apicid : 2 initial apicid : 2 fpu : yes fpu_exception : yes cpuid level : 13 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm pcid sse4_1 sse4_2 popcnt tsc_deadline_timer xsave lahf_lm arat epb pln pts dtherm tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms xsaveopt bogomips: 6185.92 clflush size: 64 cache_alignment : 64 address sizes : 36 bits physical, 48 bits virtual power management: Configuration: name | current_setting | source -+---+-- application_name| psql | client authentication_timeout | 1min | configuration file autovacuum | on| configuration file autovacuum_analyze_scale_factor | 0.05 | configuration file autovacuum_analyze_threshold| 10| configuration file autovacuum_freeze_max_age | 2 | configuration file autovacuum_max_workers | 6 | configuration file autovacuum_naptime | 15s | configuration file autovacuum_vacuum_cost_delay| 10ms | configuration file autovacuum_vacuum_cost_limit| 1000 | configuration file autovacuum_vacuum_scale_factor | 0.1 | configuration file autovacuum_vacuum_threshold | 25| configuration file bytea_output| hex | configuration file checkpoint_completion_target| 0.9 | configuration file checkpoint_segments | 32| configuration file checkpoint_timeout | 10min | configuration file client_encoding | UTF8 | client client_min_messages | log |
Re: [PERFORM] Slow Query Help
On Tue, Feb 5, 2013 at 9:15 AM, Will Platnick wplatn...@gmail.com wrote: We upgraded from PG 9.1 to 9.2. Since the upgrade, the # of active queries has raised significantly, especially during our peak time where lots of users are logging in. According to New Relic, this query is now taking up the most amount of time during peak activity and my pg_stat_activity and slow log sampling agrees. We have 3 DB servers referenced here, production running 9.2.2, semi-idle (idle except for replication when I ran the test) running 9.2.2, and 9.1.3 completely idle with an old dump restored. The only thing that stands out is: on your production server I see Total runtime: 7.515 ms, but the top node in EXPLAIN ANAYZE shows actual time as 0.179 ms. Not sure where that additional time is being spent though. It could be ExecutorStart/End, but have no idea why they should take so long. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- 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 Query Help
Good eye, I totally missed that! Any ideas on how to troubleshoot this delay? On Wednesday, February 6, 2013 at 3:51 AM, Pavan Deolasee wrote: On Tue, Feb 5, 2013 at 9:15 AM, Will Platnick wplatn...@gmail.com (mailto:wplatn...@gmail.com) wrote: We upgraded from PG 9.1 to 9.2. Since the upgrade, the # of active queries has raised significantly, especially during our peak time where lots of users are logging in. According to New Relic, this query is now taking up the most amount of time during peak activity and my pg_stat_activity and slow log sampling agrees. We have 3 DB servers referenced here, production running 9.2.2, semi-idle (idle except for replication when I ran the test) running 9.2.2, and 9.1.3 completely idle with an old dump restored. The only thing that stands out is: on your production server I see Total runtime: 7.515 ms, but the top node in EXPLAIN ANAYZE shows actual time as 0.179 ms. Not sure where that additional time is being spent though. It could be ExecutorStart/End, but have no idea why they should take so long. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee
Re: [PERFORM] Slow Query Help
Will Platnick wplatn...@gmail.com wrote: Will Platnick wplatn...@gmail.com wrote: The only thing that stands out is: on your production server I see Total runtime: 7.515 ms, but the top node in EXPLAIN ANAYZE shows actual time as 0.179 ms. Not sure where that additional time is being spent though. It could be ExecutorStart/End, but have no idea why they should take so long. Any ideas on how to troubleshoot this delay? Is the client which is running the query on the same machine as the server? If not, what's the ping time between them? -Kevin -- 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 Query Help
Clients are technically our pgbouncer which is on the same machine. The explain analyze was local through psql direct to postgresql. On Wednesday, February 6, 2013 at 11:22 AM, Kevin Grittner wrote: Will Platnick wplatn...@gmail.com (mailto:wplatn...@gmail.com) wrote: Will Platnick wplatn...@gmail.com (mailto:wplatn...@gmail.com) wrote: The only thing that stands out is: on your production server I see Total runtime: 7.515 ms, but the top node in EXPLAIN ANAYZE shows actual time as 0.179 ms. Not sure where that additional time is being spent though. It could be ExecutorStart/End, but have no idea why they should take so long. Any ideas on how to troubleshoot this delay? Is the client which is running the query on the same machine as the server? If not, what's the ping time between them? -Kevin
Re: [PERFORM] Slow Query Help
On Wed, Feb 6, 2013 at 9:52 PM, Kevin Grittner kgri...@ymail.com wrote: Will Platnick wplatn...@gmail.com wrote: Will Platnick wplatn...@gmail.com wrote: The only thing that stands out is: on your production server I see Total runtime: 7.515 ms, but the top node in EXPLAIN ANAYZE shows actual time as 0.179 ms. Not sure where that additional time is being spent though. It could be ExecutorStart/End, but have no idea why they should take so long. Any ideas on how to troubleshoot this delay? Is the client which is running the query on the same machine as the server? If not, what's the ping time between them? I don't think the network latency can cause that. The Total runtime is calculated on the server side itself - see ExplainOnePlan(). Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- 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 Query Help
On 05.02.2013 05:45, Will Platnick wrote: We upgraded from PG 9.1 to 9.2. Since the upgrade, the # of active queries has raised significantly, especially during our peak time where lots of users are logging in. According to New Relic, this query is now taking up the most amount of time during peak activity and my pg_stat_activity and slow log sampling agrees. We have 3 DB servers referenced here, production running 9.2.2, semi-idle (idle except for replication when I ran the test) running 9.2.2, and 9.1.3 completely idle with an old dump restored. The only thing that stands out is that it always checks both indexes for matches. Since you only want a single row as a result, it seems like it would be better to first check one index, and only check the other one if there's no match. Rewriting the query with UNION should do that: SELECT id, username, password, email, verified, timezone FROM users WHERE lower(username) = 'randomuser' UNION ALL SELECT id, username, password, email, verified, timezone FROM users WHERE lower(email) = 'randomuser' LIMIT 1; Also, if you can assume that email addresses always contain the @-character, you could take advantage of that and only do the lower(email) = 'randomuser' search if there is one. - Heikki -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Slow Query Help
We upgraded from PG 9.1 to 9.2. Since the upgrade, the # of active queries has
raised significantly, especially during our peak time where lots of users are
logging in. According to New Relic, this query is now taking up the most
amount of time during peak activity and my pg_stat_activity and slow log
sampling agrees. We have 3 DB servers referenced here, production running
9.2.2, semi-idle (idle except for replication when I ran the test) running
9.2.2, and 9.1.3 completely idle with an old dump restored.
HW: All servers have the same amount of RAM and CPU and all our indexes fit in
RAM. HD's differ, but they're all SSD and since the explain analyze's are all
indexes, I'm going to assume it doesn't mean that much in the context.
Configuration across all three servers is the same.
Table was auto vacuumed today.
Idle DB running 9.1.3
http://explain.depesz.com/s/7DZ
Semi-Idle DB running 9.2.2 (transactions ran previously and replication
happening, but no active traffic when query was ran)
http://explain.depesz.com/s/fEE
Production DB running 9.2.2 (Incredibly Active)
http://explain.depesz.com/s/qVW
Table public.users
Column | Type | Modifiers
---+--+
id| integer | not null default
nextval('users_id_seq'::regclass)
created_dt| timestamp with time zone | default now()
last_login_dt | timestamp with time zone | default now()
email | character varying| not null
verified | boolean | default false
first_name| character varying|
last_name | character varying|
username | character varying(50)| not null
location | character varying|
im_type | character varying|
im_username | character varying|
website | character varying|
phone_mobile | character varying(30)|
postal_code | character varying(10)|
language_tag | character varying(7) | default 'en'::character varying
settings | text | default ''::text
country | character(2) | default ''::bpchar
timezone | character varying(50)| not null default
'UTC'::character varying
verify_hash | character varying|
bio | character varying(160) |
twitter | character varying|
facebook | character varying|
search_updated_dt | timestamp with time zone | not null default now()
source| character varying|
auto_verified | boolean |
password | character(60)|
google| character varying|
gender| smallint | not null default 0
birthdate | date |
weibo | character varying|
Indexes:
users_pkey PRIMARY KEY, btree (id)
u_auth_email_index UNIQUE, btree (lower(email::text))
u_auth_uname_index UNIQUE, btree (lower(username::text))
u_auth_verify_hash_idx UNIQUE, btree (verify_hash)
users_search_updated_dt_idx btree (search_updated_dt DESC)
--
Will Platnick
Sent with Sparrow (http://www.sparrowmailapp.com/?sig)
