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 <d...@oneit.com.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 > > >
