Please use plain text on the list, some folks don't have mail readers that can handle html easily. On Wed, Aug 15, 2012 at 10:30 PM, J Ramesh Kumar <rameshj1977@xxxxxxxxx> 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. Well that explains a lot. MyISAM is not transaction or crash safe. On a machine with decent hardware (i.e. it doesn't lie about fsync) you can pull the plugs out the back of your postgresql server and any committed transactions will still be there. Your myisam tables in mysql will be corrupted and data may or may not be there that you inserted. MyISAM is great if your data is easily reproduceable or not that important. If it's important etc then it's not such a great choice. Because of the overhead of being transactionally safe, postgresql actually writes everything twice, once to a write ahead log, and then flushed out to the actual tables. It is quite likely that at your very high write rate you have a LOT of transactional logs. >> 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. That's not the question. What David is wondering is if you have a lot of table bloat, for instance from a lot of updates or deletes. PostgreSQL uses an in-store MVCC system that can bloat your tables with a lot of deletes / updates happening at once or really fast. So it's more of a troubleshooting suggestion. I'm guessing that since you don't backup your data it's not that important, so mysql with myisam may be a better choice in some ways. OTOH if you need to run complex reporting queries, MySQL's query planner is dumb as a stump and will likely run very poorly or be missing features postgresql has like CTEs and what not. Trade off, neither db is perfect for everything, but know that complex queries in mysql can often take many orders of magnitude longer than in pgsql. >> 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. Ahhh but updates are the basically delete / inserts in disguise, so if there's enough, then yes, vacuum full would make a difference. Basically the difference you are seeing is the difference between a database (postgresql) and a data store (mysql + myisam). I wonder what you'd see if you tried mysql with innodb tables, which are transaction and crash safe like postgresql. I'm guessing there would be something a bit closer to parity there. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
