Re: High Disk write and space taken by PostgreSQL - Mailing list pgsql-performance

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@gmail.com> 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.