kjkoster
09-08-2008, 21:04
Dear All,
Not many people know that MySQL has several database storage engines (http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html) to choose from. Even fewer people know that you choose these on a per-table basis. You can actually mix tables using different engines (http://dev.mysql.com/doc/refman/5.1/en/storage-engine-setting.html) in the same database.
For example, this forum keeps the login sessions in a memory table (http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html), while most other tables use the MyISAM engine. This means that restarting the server would clear the table and effectively logs all of you out. Luckily, your posts are retained (and I don't restart all that often).
Picking the right table is not as simple as a straightforward performance comparison. The InnoDB engine is considered to be one of the faster engines of MySQL when it comes to highly concurrent transaction processing. However, it never actually releases space it claims (http://bugs.mysql.com/bug.php?id=1287), causing active InnoDB tables to eat up all available disk space. This in turn forces database administrators to manually rebuild these tables to reclaim the wasted disk space.
This is just an example to show that it's not straightforward to pick the right table by looking at someone else's benchmarks.
Are there any issues that you are aware of when mixing table types?
What is your favourite MySQL table engine, and in what situation?
Kees Jan
PS. Perhaps I should introduce a MySQL blackhole table for the spammers. :p
Not many people know that MySQL has several database storage engines (http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html) to choose from. Even fewer people know that you choose these on a per-table basis. You can actually mix tables using different engines (http://dev.mysql.com/doc/refman/5.1/en/storage-engine-setting.html) in the same database.
For example, this forum keeps the login sessions in a memory table (http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html), while most other tables use the MyISAM engine. This means that restarting the server would clear the table and effectively logs all of you out. Luckily, your posts are retained (and I don't restart all that often).
Picking the right table is not as simple as a straightforward performance comparison. The InnoDB engine is considered to be one of the faster engines of MySQL when it comes to highly concurrent transaction processing. However, it never actually releases space it claims (http://bugs.mysql.com/bug.php?id=1287), causing active InnoDB tables to eat up all available disk space. This in turn forces database administrators to manually rebuild these tables to reclaim the wasted disk space.
This is just an example to show that it's not straightforward to pick the right table by looking at someone else's benchmarks.
Are there any issues that you are aware of when mixing table types?
What is your favourite MySQL table engine, and in what situation?
Kees Jan
PS. Perhaps I should introduce a MySQL blackhole table for the spammers. :p