Re: High Disk write and space taken by PostgreSQL

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.

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

The size I mentioned is the total folder size of the data directory. There
is no difference in the database schema / index between MySQL and
PostgreSQL.

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?

I don't take backup and restore.

Is there any period where you could try a full vacuum?

Since my app only doing inserts and drops(no delete), I believe the vacuum
will not give any advantage. So I have the below configuration in my
database. Event the updates only performed in a very small table which has
5 int + 1 small int + 1 real fields.

# To avoid freqent autovacuum
autovacuum_freeze_max_age = 2000000000
vacuum_freeze_min_age = 10000000
vacuum_freeze_table_age = 150000000

Thanks,
Ramesh

On Thu, Aug 16, 2012 at 9:06 AM, David Barton <dave(at)oneit(dot)com(dot)au> wrote:

> 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
>
>
>