Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id BEF1D475B8F for ; Mon, 7 Oct 2002 06:00:41 -0400 (EDT) Received: from bachata.cybertec.at (unknown [62.116.21.146]) by postgresql.org (Postfix) with SMTP id 35A79475921 for ; Mon, 7 Oct 2002 06:00:40 -0400 (EDT) Received: (qmail 4501 invoked from network); 7 Oct 2002 10:00:44 -0000 Received: from unknown (HELO cybertec.at) (62.116.21.147) by 62.116.21.146 with SMTP; 7 Oct 2002 10:00:44 -0000 Message-ID: <3DA15B7C.8010005@cybertec.at> Date: Mon, 07 Oct 2002 12:01:32 +0200 From: =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20020830 X-Accept-Language: en-us, en MIME-Version: 1.0 To: shridhar_daithankar@persistent.co.in, pgsql-general@postgresql.org Subject: Re: [pgsql-performance] Large databases, performance References: <3DA1A331.21316.F7E742B@localhost> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/355 X-Sequence-Number: 31353 I wonder if the following changes make a difference: - compile PostgreSQL with CFLAGS=' -O3 ' - redefine commit delays also: keep in mind that you might gain a lot of performance by using the SPI if you are running many similar queries try 7.3 - as far as I remeber there is a mechanism which caches recent execution plans. also: some overhead was reduced (tuples, backend startup). Hans >Ok. I am back from my cave after some more tests are done. Here are the >results. I am not repeating large part of it but answering your questions.. > >Don't ask me how these numbers changed. I am not the person who conducts the >test neither I have access to the system. Rest(or most ) of the things remains >same.. > >MySQL 3.23.52 with innodb transaction support: > >4 concurrent queries :- 257.36 ms >40 concurrent queries :- 35.12 ms > >Postgresql 7.2.2 > >4 concurrent queries :- 257.43 ms >40 concurrent queries :- 41.16 ms > >Though I can not report oracle numbers, suffice to say that they fall in >between these two numbers. > >Oracle seems to be hell lot faster than mysql/postgresql to load raw data even >when it's installed on reiserfs. We plan to run XFS tests later in hope that >that would improve mysql/postgresql load times. > >In this run postgresql has better load time than mysql/innodb ( 18270 sec v/s >17031 sec.) Index creation times are faster as well (100 sec v/s 130 sec). >Don't know what parameters are changed. > >Only worry is database size. Postgresql is 111GB v/s 87 GB for mysql. All >numbers include indexes. This is really going to be a problem when things are >deployed. Any idea how can it be taken down? > >WAL is out, it's not counted. > >Schema optimisation is later issue. Right now all three databases are using >same schema.. > >Will it help in this situation if I recompile posgresql with block size say 32K >rather than 8K default? Will it saev some overhead and offer better performance >in data load etc? > >Will keep you guys updated.. > >Regards, > Shridhar > >----------------------------------------------------------- >Shridhar Daithankar >LIMS CPE Team Member, PSPL. >mailto:shridhar_daithankar@persistent.co.in >Phone:- +91-20-5678900 Extn.270 >Fax :- +91-20-5678901 >----------------------------------------------------------- > > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > -- *Cybertec Geschwinde u Schoenig* Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/1/913 68 09; +43/664/233 90 75 www.postgresql.at , cluster.postgresql.at , www.cybertec.at , kernel.cybertec.at