Thanks for the details.
As you said, MySQL with MyISAM is better choice for my app. Because I don't need transaction/backup. May be I'll try with InnoDB and find the disk write/space difference. Is there any similar methods available in postgresql like MyISAM engine ?
>>> Ahhh but updates are the basically delete / inserts in disguise, so if there's enough, then yes, vacuum full would make a difference.
The table which get update has very less data ie, only has 900 rows. Out of 10500 tables, only one table is getting update frequently. Is there any way to vacuum a specific table instead of whole database ?
Thanks,
Ramesh
On Thu, Aug 16, 2012 at 10:09 AM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:
Please use plain text on the list, some folks don't have mail readers
that can handle html easily.
Well that explains a lot. MyISAM is not transaction or crash safe.
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.
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.
That's not the question. What David is wondering is if you have a lot
>> 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.
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.
Ahhh but updates are the basically delete / inserts in disguise, so if
>> 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.
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.
