Skip site navigation (1) Skip section navigation (2)

Re: Configuration Advice

From: Arjen van der Meijden <acmmailing(at)tweakers(dot)net>
To: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>
Cc: Adam Rich <adam(dot)r(at)sbcglobal(dot)net>, 'Bricklen Anderson' <banderson(at)presinet(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Configuration Advice
Date: 2007-01-18 16:42:22
Message-ID: 45AFA36E.2040603@tweakers.net (view raw or flat)
Thread:
Lists: pgsql-performance
On 18-1-2007 17:20 Scott Marlowe wrote:
>> Besides that, mysql rewrites the entire table for most table-altering 
>> statements you do (including indexes). 
> 
> Note that this applies to the myisam table type.  innodb works quite
> differently.  It is more like pgsql in behaviour, and is an mvcc storage

Afaik this is not engine specific and also applies to InnoDB. Here is 
what the MySQL-manual sais about it:
"In most cases, ALTER TABLE works by making a temporary copy of the 
original table. The alteration is performed on the copy, and then the 
original table is deleted and the new one is renamed. While ALTER TABLE 
  is executing, the original table is readable by other clients. Updates 
and writes to the table are stalled until the new table is ready, and 
then are automatically redirected to the new table without any failed 
updates."

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

If it were myisam-only they sure would've mentioned that. Besides this 
is the behaviour we've seen on our site as well.

Since 'create index' is also an alter table statement for mysql, this 
also applies for adding indexes.

Best regards,

Arjen


In response to

pgsql-performance by date

Next:From: Jeremy HaileDate: 2007-01-18 17:28:27
Subject: Re: Configuration Advice
Previous:From: Scott MarloweDate: 2007-01-18 16:20:53
Subject: Re: Configuration Advice

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group