Yet another article comparing two database features. This time the different between the two storage engines MyISAM and InnoDB from MySQL will be compared, so you can choose which one you should choose for your project. Some fast facts: InnoDB supports foreign keys InnoDB implements row-level lock for inserting and updating while MyISAM implements table-level lock! InnoDB supports transactions MyISAM [...]
MySQL tries to be smart and to cram as much performance out of
available hardware, and one thing that most MySQLers knows is
that opening a table (ie. opening the frm file, and depending on
the storage engine, any additional files related to the table) is
expensive from a performance point of view. When we see the
opened_tables status variable increasing, we tend to
increase the table_open_cache setting and keep as many
tables as possible open and avoid reopening them.
When it comes to MyISAM though, this has a nasty side-effect. When the server crashes and there are MyISAM tables open, these might well need to be REPAIRed. And if you have big MyISAM tables, this is an issue. Let's say that your application use several MyISAM tables, with the same content, and that you create new tables after a certain time, to keep the size of each individual table down? There are other reasons why you have this effect also, say a large …
The other day at PSCE I worked on a customer case of what turned out to be a problem with poor data locality or a data fragmentation problem if you will. I tought that it would make a good article as it was a great demonstration of how badly it can affect MySQL performance. And while the post is mostly around MyISAM tables, the problem is not really specific to any particular storage engine, it can affect a database that runs on InnoDB in a very similar way.
MyISAM lacks support for clustering keys or even anything remotely similar. Its data file format allows new information to be written anywhere inside a table. Anywhere can be either at the end of a file where it can be simply appended or an empty space somewhere in the middle left after previously deleted row(s). This implies no particular order in which rows are stored unless there are absolutely no …[Read more]
This post continues Getting rid of huge ibdata file, no dump required, part I, where I describe way of converting your single-tablespace InnoDB database into a file-per-table one, without the pain of exporting and importing everything at once.
In previous part we put aside the issue of foreign keys. We address this issue now.
What if my InnoDB tables have foreign keys?
MyISAM does not support them, so you can't just ALTER an InnoDB table to MyISAM and back into InnoDB, and expect everything to work.
Alas, this calls for additional steps (i.e. additional ALTER commands). However, these still fall well under the concept of "do it one table at a time, then take time to recover your breath and replication lag".
Save , drop and restore your …[Read more]
You have been told (guilty as charged), that the only way to get rid of the huge InnoDB tablespace file (commonly named ibdata1), when moving to innodb_file_per_table, is to do a logical dump of your data, completely erase everything, then import the dump.
To quickly reiterate, you can only delete the ibdata1 file when no InnoDB tables exist. Delete this file with an existing InnoDB table, even a table in its own tablespace, and nothing ever works anymore.
The problem with the dump-based solution
The impact of doing a logical dump is often overwhelming. Well, the dump may be tolerable, but the restore is much longer. The real pain is that you can't do …[Read more]
A short while after I fixed the legacy bug that prevented
temporary MyISAM tables from using the dynamic record format, I
got an email from Davi Arnaut @ Twitter. It turned out that
Twitter needed to fix the very same problem, but for the case
when INFORMATION_SCHEMA temporary tables use MyISAM.
In short, INFORMATION_SCHEMA tables provide access to database metadata. Despite their name, they are more like views than tables: when you query them, relevant data is gathered from the dictionary and other server internals, not from tables. The gathered data is stored in a temporary table (memory or MyISAM depending on size) and then returned to the user.
The reason Davi emailed me was to let me know that he had further improved the fix for temporary MyISAM tables to also enable the use of dynamic record format for …
There are a few ways to implement counters. Even though it’s not a complex feature, often I see people having problems around it. This post describes how bad implementation can impact both application and MySQL performance and how to improve it.
A customer asked me for help with performance problem they were facing. I logged into their database and found many client connections waiting for table locks. Almost all threads were stuck on one, small table called hits. What was the reason?
The problem was related to the way they developed a very simple system for counting page views they later used in some reporting. The table structure was:
mysql> SHOW CREATE TABLE hits\G *************************** 1. row *************************** Table: hits Create Table: CREATE TABLE `hits` ( `cnt` int(11) NOT NULL ) ENGINE=MyISAM mysql> SELECT * FROM hits; +---------+ | cnt | +---------+ | 3823273 | +---------+
Last summer my colleague Marko Mäkelä committed this seemingly
innocent performance fix for InnoDB in MySQL 5.6:
3581 Marko Makela 2011-08-10
Bug#12835650 VARCHAR maximum length performance impact
row_sel_field_store_in_mysql_format(): Do not pad the unused part of
the buffer reserved for a True VARCHAR column (introduced in 5.0.3).
Add Valgrind instrumentation ensuring that the unused part will be
Before this, buffers which were used to send VARCHARs from InnoDB to the MySQL server were padded with 0s if the string was shorter than specified by the column. If, e.g., the string "foo" was stored in a VARCHAR(8), InnoDB used to write "3foo00000" to the buffer (the first character - 3 - determines the actual length of the string). However, even though these trailing bytes are not used anywhere, writing 0s to the buffer certainly has a cost. Hence …
Is there a way to create a caching table, some sort of a materialized view, such that upon selecting from that table, its data is validated/invalidated?
But to elaborate the point: say I have some table data_table. Can I rewrite all my queries which access data_table to read from some autocache_data_table, but have nothing changed in the query itself? No caveats, no additional WHEREs, and still have that autocache_data_table provide with the correct data, dynamically updated by some rule of our choice?
And: no crontab, no event scheduler, and no funny triggers on data_table? In such way that invalidation/revalidation occurs upon SELECT?
This post is long, but I suggest you read it through to understand the …[Read more]
- Support complex and efficient indexes at 100+ million rows.
- Predicable and consistent performance regardless of data size growth.
- Fast recovery.
Ensuring Predictable Performance at Scale
The Company: FictionPress operates both FictionPress.com and FanFiction.net and is home to over 6 million works of fiction, with millions of writers/readers participating from around the world in over 30 languages
The Challenge: FictionPress offers a number of interactive features to its large user base. These …[Read more]