Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 441466386CB for ; Fri, 20 Feb 2009 16:11:50 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 72490-01 for ; Fri, 20 Feb 2009 16:11:46 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from yw-out-2324.google.com (yw-out-2324.google.com [74.125.46.30]) by mail.postgresql.org (Postfix) with ESMTP id 283AB6380D6 for ; Fri, 20 Feb 2009 16:11:45 -0400 (AST) Received: by yw-out-2324.google.com with SMTP id 5so439556ywh.73 for ; Fri, 20 Feb 2009 12:11:44 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:content-type :content-transfer-encoding; bh=4PJutO7u4WsipwmOH4U2b+ebtwBllPZyi7R965KkqNY=; b=TTHaSbJxH+9PZ9NaqAkzF3qY2t0LGS9xVUKh0vBUx3Pf1d3mife1RoJeKF28BmjNku rrBaLgN/aFhZr51SATxIXUrVOWyiWJX3UJan7nY5FQ/6F/pt3ZUcae2ASpmDq3N5PnsI oNEq/ov/Q1Q+LKVQrKxNS1oD0gepjdD1d7QQs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type:content-transfer-encoding; b=G+dg0XCWZzbV/8h+vRh89dCLd6UJcKPL7bOF6MURK7oXrCXWgBoIeTowexAuHj6QUH 8474gm29tmGCy2wTRyugFkE+0hD3OTkMCxOsYUS/wJAS+SiUUjkHGi1z+Q+Xrie8PKXV 3srfKnh0HWhvpZmjMOikvZ89Tga3y3yRw/r5s= MIME-Version: 1.0 Received: by 10.150.133.18 with SMTP id g18mr2358390ybd.208.1235160704680; Fri, 20 Feb 2009 12:11:44 -0800 (PST) In-Reply-To: References: Date: Fri, 20 Feb 2009 18:11:44 -0200 Message-ID: Subject: Fwd: very, very slow performance From: Emanuel Calvo Franco To: pgsql-admin@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200902/158 X-Sequence-Number: 31379 2009/2/20 Tena Sakai : > Hi Emanuel, > >> Do you tried partitioned tables? diferent tablespaces? >> set the storage external for more important columns? >> what kind of indexes do you have? > > I think the table design is ok. Sorry, copy to the list. The table desing is ok. Partitioned tables is about cut tables in terms of number of regs. It improve your engine , specially if you are talking about that number of regs. > I don't know what "set the storage external" means. I't means the colmun will storage separated, sometimes improve performance. > Maybe you can explain it to me in lay-person's terms? > I think indices are ok, but I welcome your comments. > Indexes maybe ok. What I mean is th TYPE of that. Maybe HASH or GIST could be better, depends on the column format. > canon=# \d genotype > Table "public.genotype" > Column | Type | > Modifiers > > -----------------+-----------------------------+--------------------------------------------------------------- > genotypeid | integer | not null default > nextval('genotype_genotypeid_seq'::regclass) > subjectid | integer | > markerid | integer | > allele1id | integer | > allele2id | integer | > datecreated | timestamp without time zone | not null > datereplaced | timestamp without time zone | not null > ignore | character(1) | not null default > 'N'::bpchar > inconsistent | character(1) | not null default > 'N'::bpchar > sourcetablename | character varying | not null > sourceid | character varying | not null > Indexes: > "genotype_pkey" PRIMARY KEY, btree (genotypeid, datecreated) > "genotype_genotypeid_idx" btree (genotypeid) > "genotype_markerid_idx" btree (markerid) > "genotype_source_idx" btree (sourceid, sourcetablename) > "genotype_subjectid_idx" btree (subjectid) > > canon=# \d allele > Table "public.allele" > Column | Type | > Modifiers > > --------------+-----------------------------+----------------------------------------------------------- > alleleid | integer | not null default > nextval('allele_alleleid_seq'::regclass) > markerid | integer | > value | character varying | not null > datecreated | timestamp without time zone | not null > datereplaced | timestamp without time zone | not null > Indexes: > "allele_pkey" PRIMARY KEY, btree (alleleid, datecreated) > "allele_markerid_idx" btree (markerid) > >> try to run explain analyze for those querys, then >> post the results. > > Yes, it is running for last 13+ hours and I have no > idea how much longer it might take. > >> I recommend follow this thread: >> http://archives.postgresql.org/pgsql-hackers/2009-02/msg00718.php > > That's pretty interesting stuff. I need to experiment tweaking > parameters Joshua mentions. Alas, now is not a good time to do > so. > I'm glad if it's useful. > Regards, > > Tena Sakai > > -----Original Message----- > From: pgsql-admin-owner@postgresql.org on behalf of Emanuel Calvo Franco > Sent: Fri 2/20/2009 4:10 AM > To: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] very, very slow performance > > 2009/2/20 Tena Sakai : >> Hi Everybody, >> >> Here's the query (please read them in fixed-size font, if >> you can): >> >> select subjectid, genotype.markerid, a1.value as allele1, >> a2.value as allele2, genotype.dateCreated, >> genotype.dateReplaced, genotype.ignore, >> genotype.inconsistent >> from genotype, allele a1, allele a2 >> where >> allele1id = a1.alleleid >> and >> allele2id = a2.alleleid; >> >> Genotype table mentioned above has about 600,000,000+ rows. As >> I mentioned, there are 20 more of them running concurrently. >> 3 other jobs look like: >> > > Do you tried partitioned tables? diferent tablespaces? set the storage > external for > more important columns? what kind of indexes do you have? > >> SELECT a.markerid,a.type,a.localname,b.ncbibuild, >> a.chromosome,a.geneticposition,b.physicalposition, >> a.strand,a.stdflanks,a.maxflanks,a.datecreated, >> a.datereplaced,a.sourcetablename,a.sourceid, >> b.dbsnprsid,a.ignore,a.gene >> FROM public.marker a, public.snpposition b >> WHERE >> a.ignore= 'N' >> AND a.datecreated <= (timestamp'Wed Oct 29 09:35:54.266 2008') >> AND a.datereplaced > (timestamp'Wed Oct 29 09:35:54.266 2008') >> AND a.localname IN >> ('RS10757474','RS7859598','RS6148','RS9792663','RS1541125', >> >> 'RS10511446','RS10814410','RS12338622','RS875587', >> 'RS1590979', 'RS748786','RS958505','RS12352961', >> and on and on and on...); >> >> >> insert into summarystats >> select 'Marker by Chromosomes', chromosome, >> sourcetablename, >> count(*), null, to_timestamp('2009-02-18 >> 20:29:40.125', >> 'yyyy-mm-dd hh:mi:ss.ms') >> from marker >> where ignore = 'N' >> and datereplaced = '3000-01-01 12:00:00.000' >> and exists (select 1 >> from genotype >> where genotype.markerid = >> marker.markerid >> and genotype.ignore = 'N' >> and genotype.datereplaced = >> '3000-01-01 12:00:00.000') >> group by chromosome, sourcetablename; >> >> COPY public.genotype (genotypeid, subjectid, markerid, >> allele1id, allele2id, datecreated, datereplaced, >> ignore, inconsistent, sourcetablename, sourceid) >> TO stdout; >> > > Like Scott saids, try to run explain analyze for those querys, then > post the results. > > >> >> Can anybody suggest anything that I can do to gain speed? >> Any help is much appreciated. >> > > I recommend follow this thread: > http://archives.postgresql.org/pgsql-hackers/2009-02/msg00718.php > > >> Regards, >> >> Tena Sakai >> tsakai@gallo.ucsf.edu >> >> >> > > > > -- > Emanuel Calvo Franco > Sumate al ARPUG ! > (www.postgres-arg.org - > www.arpug.com.ar) > ArPUG / AOSUG Member > Postgresql Support & Admin > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > > -- Emanuel Calvo Franco Sumate al ARPUG ! (www.postgres-arg.org - www.arpug.com.ar) ArPUG / AOSUG Member Postgresql Support & Admin -- Emanuel Calvo Franco Sumate al ARPUG ! (www.postgres-arg.org - www.arpug.com.ar) ArPUG / AOSUG Member Postgresql Support & Admin