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?
What are your indexes? Is the size in the indexes or the database tables?
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
Ramesh
On Thu, Aug 16, 2012 at 9:06 AM, David Barton <dave@xxxxxxxxxxxx> 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
