Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1T1qtS-0008Ku-8U for pgsql-performance@postgresql.org; Thu, 16 Aug 2012 03:37:10 +0000 Received: from smtp4.wa.amnet.net.au ([203.161.124.53]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1T1qtN-0007oS-N5 for pgsql-performance@postgresql.org; Thu, 16 Aug 2012 03:37:09 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp4.wa.amnet.net.au (Postfix) with ESMTP id 4CFE37DAE7 for ; Thu, 16 Aug 2012 11:37:00 +0800 (WST) X-Virus-Scanned: amavisd-new at smtp4.wa.amnet.net.au Received: from smtp4.wa.amnet.net.au ([127.0.0.1]) by localhost (smtp4.wa.amnet.net.au [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id fMNkqX92WXmW for ; Thu, 16 Aug 2012 11:36:59 +0800 (WST) Received: from [192.168.167.135] (203.161.71.159.static.amnet.net.au [203.161.71.159]) by smtp4.wa.amnet.net.au (Postfix) with ESMTP id 117847DD20 for ; Thu, 16 Aug 2012 11:36:58 +0800 (WST) Message-ID: <502C6AD5.4070407@oneit.com.au> Date: Thu, 16 Aug 2012 11:36:53 +0800 From: David Barton User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:14.0) Gecko/20120714 Thunderbird/14.0 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: High Disk write and space taken by PostgreSQL References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------010705050108040306090103" X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201208/93 X-Sequence-Number: 47631 This is a multi-part message in MIME format. --------------010705050108040306090103 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi Ramesh, Are you able to provide a table schema? Were you using MyISAM or InnoDB on MySQL? If you back up the database & restore clean, what is the size comparison of the database filed on the restored copy to the existing one? It may be full of empty tuples. Is there any period where you could try a full vacuum? What are your indexes? Is the size in the indexes or the database tables? At the current rate of insertion, that table is going to get very large very quickly. Do you have anything deleting the rows afterwards? I have no experience with databases past 50M rows, so my questions are just so you can line up the right info for when the real experts get online :-) Regards, David On 16/08/12 11:23, J Ramesh Kumar wrote: > > Hi, > > My application has high data intensive operations (high number of > inserts 1500 per sec.). I switched my application from MySQL to > PostgreSQL. When I take performance comparison report between mysql > and pgsql, I found that, there are huge difference in disk writes and > disk space taken. Below stats shows the difference between MySQL and > PostgreSQL. > > > *MySQL* *PostgreSQL* > Inserts Per Second* 1500 1500 > Updates Per Second* 6.5 6.5 > Disk Write Per Second* 0.9 MB 6.2 MB > Database Size Increased Per day* 13 GB 36 GB > > > * approx values > > Why this huge difference in disk writes and disk space utilization? > How can I reduce the disk write and space ? Kindly help me. Please let > me know, if you require any other information(such as postgres.conf). > > Thanks, > Ramesh --------------010705050108040306090103 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hi Ramesh,

Are you able to provide a table schema?  Were you using MyISAM or InnoDB on MySQL?

If you back up the database & restore clean, what is the size comparison of the database filed on the restored copy to the existing one?  It may be full of empty tuples.  Is there any period where you could try a full vacuum?

What are your indexes?  Is the size in the indexes or the database tables?

At the current rate of insertion, that table is going to get very large very quickly.  Do you have anything deleting the rows afterwards?  I have no experience with databases past 50M rows, so my questions are just so you can line up the right info for when the real experts get online :-)

Regards, David

On 16/08/12 11:23, J Ramesh Kumar wrote:

Hi,

My application has high data intensive operations (high number of inserts 1500 per sec.). I switched my application from MySQL to PostgreSQL. When I take performance comparison report between mysql and pgsql, I found that, there are huge difference in disk writes and disk space taken. Below stats shows the difference between MySQL and PostgreSQL.


 MySQL PostgreSQL
Inserts Per Second* 1500 1500
Updates Per Second* 6.5 6.5
Disk Write Per Second* 0.9 MB 6.2 MB
Database Size Increased Per day* 13 GB 36 GB

* approx values

Why this huge difference in disk writes and disk space utilization? How can I reduce the disk write and space ? Kindly help me. Please let me know, if you require any other information(such as postgres.conf).

Thanks,
Ramesh

--------------010705050108040306090103--