Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U2ZTv-0000Lm-9m for pgsql-performance@arkaria.postgresql.org; Tue, 05 Feb 2013 03:46:03 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1U2ZTu-00080Y-Ch for pgsql-performance@arkaria.postgresql.org; Tue, 05 Feb 2013 03:46:02 +0000 Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U2ZTt-00080T-CZ for pgsql-performance@postgresql.org; Tue, 05 Feb 2013 03:46:01 +0000 Received: from mail-vb0-f49.google.com ([209.85.212.49]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U2ZTp-0004wh-UZ for pgsql-performance@postgresql.org; Tue, 05 Feb 2013 03:46:00 +0000 Received: by mail-vb0-f49.google.com with SMTP id s24so4363231vbi.36 for ; Mon, 04 Feb 2013 19:45:56 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=x-received:date:from:to:message-id:subject:x-mailer:mime-version :content-type; bh=kCrSO5LQToQTsFDh0LAkiPM8g7QUTbfwqOo24vOTuiQ=; b=TyDiVVPWmE1OrXsusPm4AArWaDz+d5b99mqA4F4lFtg9ZNSA7rmBuRY/aXWcbvk3yl cmf1Yo0LpRRMPhllm1qKzzsqhJhAWs7oaSOStFBP51Thk+Rh3b6bW7zhYAYd0DQWgwLc eoTGUnb8Ppl3/RHvkPCNI77Jh+ugz8yZijiCozP7oWnpcnihWjP/6XSkDdiXVNneKWKS Z+UD6YHR9juj3iRo5YhOzp7x0B2+W130Lr2YsYbbaDpnguIosIz+xIJbTDopr1eVcxW2 S3KZwWA5EJEWBVASuTRKjZ0G2OAhcWxEiUfqqjySINt3HxqJPN5JKBMifgOYXa4VWj+2 LDqg== X-Received: by 10.58.23.169 with SMTP id n9mr21456404vef.58.1360035956365; Mon, 04 Feb 2013 19:45:56 -0800 (PST) Received: from [192.168.150.117] (cpe-142-105-213-24.stny.res.rr.com. [142.105.213.24]) by mx.google.com with ESMTPS id l18sm25497625vdh.10.2013.02.04.19.45.54 (version=TLSv1 cipher=RC4-SHA bits=128/128); Mon, 04 Feb 2013 19:45:55 -0800 (PST) Date: Mon, 4 Feb 2013 22:45:53 -0500 From: Will Platnick To: pgsql-performance@postgresql.org Message-ID: Subject: Slow Query Help X-Mailer: sparrow 1.6.4 (build 1178) MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="51108071_2ae8944a_6f60" X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --51108071_2ae8944a_6f60 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline 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) --51108071_2ae8944a_6f60 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

We upgraded from PG 9.1 to 9.2. Since the upgrade, the =23= of active queries has raised significantly, especially during our peak t= ime where lots of users are logging in.  According to New Relic, thi= s query is now taking up the most amount of time during peak activity and= my pg=5Fstat=5Factivity and slow log sampling agrees. We have 3 DB serve= rs 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: A= ll 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 ar= e all indexes, I'm going to assume it doesn't mean that much in the conte= xt. 


Confi= guration across all three servers is the same.


Table= was auto vacuumed today.


Idle = DB running 9.1.3


http://explain.depesz.com/s/7D= Z


Semi-= Idle DB running 9.2.2 (transactions ran previously and replication happen= ing, but no active traffic when query was ran)


http://explain.depesz.com/s/fE= E


Produ= ction DB running 9.2.2 (Incredibly Active)


http://explain.depesz.com/s/qV= W


 = ;                    &n= bsp;                 Table =22pub= lic.users=22

 = ;     Column       =7C       &nbs= p;   Type           =7C      = ;               Modifiers    = ;                  

-----= --------------+--------------------------+-------------------------------= ---------------------

 = ;id                =7C integer&nb= sp;                 =7C not null = default nextval('users=5Fid=5Fseq'::regclass)

 = ;created=5Fdt        =7C timestamp with time zone =7C= default now()

 = ;last=5Flogin=5Fdt     =7C timestamp with time zone =7C default= now()

 = ;email             =7C character varying&nb= sp;       =7C not null

 = ;verified          =7C boolean    &nbs= p;             =7C default false

 = ;first=5Fname        =7C character varying  &nbs= p;     =7C 

 = ;last=5Fname         =7C character varying  &nbs= p;     =7C 

 = ;username          =7C character varying(50)&nbs= p;   =7C not null

 = ;location          =7C character varying  &= nbsp;     =7C 

 = ;im=5Ftype           =7C character varying =       =7C 

 = ;im=5Fusername       =7C character varying    &n= bsp;   =7C 

 = ;website           =7C character varying  &= nbsp;     =7C 

 = ;phone=5Fmobile      =7C character varying(30)   = ; =7C 

 = ;postal=5Fcode       =7C character varying(10)   = ; =7C 

 = ;language=5Ftag      =7C character varying(7)    = ; =7C default 'en'::character varying

 = ;settings          =7C text      =               =7C default ''::text

 = ;country           =7C character(2)    = ;         =7C default ''::bpchar

 = ;timezone          =7C character varying(50)&nbs= p;   =7C not null default 'UTC'::character varying

 = ;verify=5Fhash       =7C character varying    &n= bsp;   =7C 

 = ;bio               =7C character varyi= ng(160)   =7C 

 = ;twitter           =7C character varying  &= nbsp;     =7C 

 = ;facebook          =7C character varying  &= nbsp;     =7C 

 = ;search=5Fupdated=5Fdt =7C timestamp with time zone =7C not null default = now()

 = ;source            =7C character varying&nb= sp;       =7C 

 = ;auto=5Fverified     =7C boolean        &nb= sp;         =7C 

 = ;password          =7C character(60)   = ;         =7C 

 = ;google            =7C character varying&nb= sp;       =7C 

 = ;gender            =7C smallint   &nbs= p;             =7C not null default 0

 = ;birthdate         =7C date       &nbs= p;             =7C 

 = ;weibo             =7C character varying&nb= sp;       =7C 

Index= es:

 = ;   =22users=5Fpkey=22 PRIMARY KEY, btree (id)

 = ;   =22u=5Fauth=5Femail=5Findex=22 UNIQUE, btree (lower(email::text)= )

 = ;   =22u=5Fauth=5Funame=5Findex=22 UNIQUE, btree (lower(username::te= xt))

 = ;   =22u=5Fauth=5Fverify=5Fhash=5Fidx=22 UNIQUE, btree (verify=5Fhas= h)

 = ;   =22users=5Fsearch=5Fupdated=5Fdt=5Fidx=22 btree (search=5Fupdate= d=5Fdt DESC)



-- 
Will Platnick=
Sent with Sparrow

--51108071_2ae8944a_6f60--