Skip site navigation (1) Skip section navigation (2)

Re: High Disk write and space taken by PostgreSQL

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: J Ramesh Kumar <rameshj1977(at)gmail(dot)com>
Cc: David Barton <dave(at)oneit(dot)com(dot)au>, pgsql-performance(at)postgresql(dot)org
Subject: Re: High Disk write and space taken by PostgreSQL
Date: 2012-08-16 21:16:59
Message-ID: CAHyXU0yAfS5sooaLUe-3d9N2S7sfPVpBWwO4HevrC9ycptNqOw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Aug 15, 2012 at 11:30 PM, J Ramesh Kumar <rameshj1977(at)gmail(dot)com> wrote:
>
> Hi David Barton,
>
> Please find the information below.
>
>> Are you able to provide a table schema?
>
>
> There are 109 different types of table. I am maintaining some tables are
> daily tables and some tables are ID based. So totally we have created around
> 350 tables and dropped around 350 tables. I will drop the old table and I
> don't delete any records. I am maintaing only last 30 days tables. I dropped
> tables which are older than 30 days. All the tables are only have basic data
> types like int, smallint, bigint, varchar.
>
>
>>
>> Were you using MyISAM or InnoDB on MySQL?
>
>
> I am using MyISAM tables in MySQL.

You can't compare a non-MVCC system such as MyISAM with a MVCC one.
MVCC systems have to store extra accounting information in order to
manage transactions and multiple versions of the same record for SQL
updates.  MVCC isn't all bad: for example you get much better
performance in the face of highly concurrent activity.  MyISAM does
full table locks which are not scalable at all.  The penalty for MVCC
storage may in some cases seem quite high if your tables have very
narrow records.

BTW, I am suspicious that your claim that you 'don't need'
transactions is correct, especially in the long term.

Anyways, there are several techniques to try and mitigate data growth
in postgres -- arrays for example.

merlin


In response to

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2012-08-16 21:39:19
Subject: Re: best practice to avoid table bloat?
Previous:From: Anibal David AcostaDate: 2012-08-16 21:10:31
Subject: Re: best practice to avoid table bloat?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group