From: Arjen van der Meijden <acmmailing@tweakers.net>
To: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: Adam Rich <adam.r@sbcglobal.net>
Cc: Bricklen Anderson <banderson@presinet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Configuration Advice
Date: Thu, 18 Jan 2007 17:42:22 +0100
Message-ID: <45AFA36E.2040603@tweakers.net> (raw)
In-Reply-To: <1169137253.9586.79.camel@state.g2switchworks.com>
References: <090501c73a90$7f154330$6400a8c0@dualcore>
<45AF4ACA.5060805@tweakers.net>
<1169137253.9586.79.camel@state.g2switchworks.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: acmmailing@tweakers.net, smarlowe@g2switchworks.com, adam.r@sbcglobal.net, banderson@presinet.com
Subject: Re: Configuration Advice
In-Reply-To: <45AFA36E.2040603@tweakers.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox