Re: High Disk write and space taken by PostgreSQL

Dear Scott Marlowe,

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(dot)marlowe(at)gmail(dot)com>wrote:

> 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(at)gmail(dot)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.
>