Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been archiving some MySQL (version 5.0.96) MyISAM tables locally and deleting from my server after checking the data. Every time the process has been pretty similar and comes off mostly without a hitch. In the case of using mysqldump for a single table here is my process...

  1. Create a gzipped dump of the table in question. (mysqldump -u root -p db_name table_name | gzip > table_name.sql.gz)
  2. Download to my local computer, reverse gzip.
  3. Import into the local MySQL (same version) using "source PATH". (note: online server is linux, local computer is Windows... hasn't made a difference so far though)
  4. Compare CHECKSUM TABLE database.table; of online version and local version.
  5. Compare row count, data size, and index size. (just to be sure checksum didn't fail me somehow)
  6. If everything checks out, drop online table.

Like I said, mostly, this has been smooth sailing and after I'm satisfied I drop the online version of the table. However, for the most recent table I've gotten hung up on step 5 and I'm at a loss as to why exactly. On my latest import everything in step 4 and 5 matches... except the index size. It's a mismatch to the tune of 2.5 GB!

I know what you're thinking...

  1. The base data is most likely fine then, proceed with drop.
  2. Your online indexes are fragmented.

I would agree, however, my testing doesn't fully illuminate this and I want to be sure I understand what is going on before I drop the online table. (and for future reference as well)

After every previous test the index size matched upon importing the dump. Granted, it doesn't matter if point 2 applies. However, to address point 2 I ran OPTIMIZE TABLE against the online version of the table and the index size did not change.

Regarding disk space in general... I had 75 GB free locally and about 59 GB free online at the time of testing. Either should be enough for importing or optimizing and I received no messages indicating a failure in either case.

I've hit an impasse on trying to explain this discrepancy to myself. Also, I should note I ran through this entire process twice to ensure I wasn't missing something obvious. So...

Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?

  1. Fragmentation (Ran OPTIMIZE TABLE, no luck syncing size)
  2. Corruption (Ran through process twice and row count, data size, checksum all match)
  3. ...?

I can usually solve my own problems... consider me humbled. Any ideas are welcome.

PS I hope this makes for a good first question ;P

Local table after import:

+----------------+-------------+----------------+---------------+
| database.table | table_rows  | data_length    | index_length  |
+----------------+-------------+----------------+---------------+
| database.table | 145,250,083 | 23,385,263,363 | 5,853,283,328 |
+----------------+-------------+----------------+---------------+

Online source table:

+----------------+-------------+----------------+---------------+
| database.table | table_rows  | data_length    | index_length  |
+----------------+-------------+----------------+---------------+
| database.table | 145,250,083 | 23,385,263,363 | 8,199,018,496 |
+----------------+-------------+----------------+---------------+


mysql> OPTIMIZE TABLE database.table;

+-------------------------------------+----------+----------+----------+
| Table                               | Op       | Msg_type | Msg_text |
+-------------------------------------+----------+----------+----------+
| database.table                      | optimize | status   | OK       |
+-------------------------------------+----------+----------+----------+
1 row in set (17 min 41.92 sec)

mysql> SELECT CONCAT(table_schema, '.', table_name) AS 'database.table',
    -> FORMAT(table_rows, 0) AS table_rows,
    -> FORMAT(data_length, 0) AS data_length,
    -> FORMAT(index_length, 0) AS index_length
    -> FROM information_schema.TABLES
    -> WHERE table_name = 'table';

+----------------+-------------+----------------+---------------+
| database.table | table_rows  | data_length    | index_length  |
+----------------+-------------+----------------+---------------+
| database.table | 145,250,083 | 23,385,263,363 | 8,199,018,496 |
+----------------+-------------+----------------+---------------+


The table schema is below. I ran SHOW CREATE TABLE both online and locally and then put the results in two Notepad++ panes and ran "Compare". It returned as an exact match. I've obfuscated some column names, but the rest of the column (and other) definitions have remained the same.

CREATE TABLE `generic_dummy_table_name` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user` char(22) NOT NULL,
  `num_column_1` mediumint(8) unsigned default '0',
  `num_column_2` int(10) unsigned default '0',
  `num_column_3` int(10) unsigned default '0',
  `num_column_4` mediumint(8) unsigned default '0',
  `num_column_5` mediumint(8) unsigned default '0',
  `num_column_6` mediumint(8) unsigned default '0',
  `num_column_7` mediumint(8) unsigned default '0',
  `num_column_8` bigint(11) unsigned default '0',
  `datetime_1` datetime NOT NULL default '0000-00-00 00:00:00',
  `datetime_1_actual` char(29) default NULL,
  `datetime_2` datetime NOT NULL default '0000-00-00 00:00:00',
  `datetime_2_actual` char(29) default NULL,
  `datetime_3` datetime NOT NULL default '0000-00-00 00:00:00',
  `datetime_4` datetime NOT NULL default '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `created_at` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY (`user`,`datetime_4`),
  KEY (`datetime_4`)
) ENGINE=MyISAM AUTO_INCREMENT=1257737641 DEFAULT CHARSET=latin1

Note: The auto increment value might seem odd versus row count, but worry not... that's because this is not the first time I've archived this table.

share|improve this question

migrated from serverfault.com Apr 30 at 15:37

This question came from our site for system and network administrators.

    
Please run CHECKSUM TABLE database.table; on both tables and tell us if the checksum values are the same. –  RolandoMySQLDBA Apr 30 at 19:40
    
@RolandoMySQLDBA Sorry, I may have glossed over this originally. Step 4 above is where I ran it. (before the issue with the index size mismatch) I've updated the question to clarify checksum method used and highlight that bit of text. You did get me curious though because I left off the "database" originally when running the checksums. Unfortunately, being explicit with the database didn't reveal an error in the previous process. (such as the wrong database accidentally used) Correct databases were used and checksums still match as before. –  Michael X Apr 30 at 21:00

1 Answer 1

I think I have a plausible explanation you find very intriguing.

When you loaded the MyISAM table, only one index got loaded into the .MYI file in a lopsided manner.

Which index would that be ? THE PRIMARY KEY. Why ?

Indexes usually use BTREEs. They are designed to collect keys into a single BTREE node and balance the BTREE internally until the BTREE is full. On the insert into the full node, the full tree node is split and the keys inside are divided.

Most often a BTREE node will split is when you load the keys in order. For proof, look at the worst case BTREE --- the balanced binary tree.

I have several posts in the DBA STackExchange where I mention how a balanced binary only has node with one key. I also mention how a leaf pages must rebalance about 45% of the time when inserting a new key. Here are those posts:

My working theory is that random inserts into the table may actually make the .MYI smaller by prevent tree node splits as much as possible.

In order to make this happen, you will have to something quite unusual. Ready for this ?

ALTER TABLE generic_dummy_table_name ORDER BY datetime;

or

ALTER TABLE generic_dummy_table_name ORDER BY user,datetime;

That right, you can reorder the physical rows of the table. This may make the indexes a different size by inducing BTREE splits on another index.

Try reordering the table. Then, mysqldump it and reload it. You may find that the .MYI may get smaller or bigger. You cannot really predict it.

Next time, try reloading the MyISAM table with a very large bulk insert buffer.

Just run

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 1024;

then connect to mysql and reload the mysqldump.

GIVE IT A TRY !!!

share|improve this answer
    
I am intrigued and will tinker around with the ordering thing tomorrow to see what happens to the source table. The table is 30GB, so it will take some time to get done. One point of clarification... OPTIMIZE TABLE on the source table doesn't compensate for this? (this is above my knowledge level, clearly, lol) As far as the bulk_insert_buffer_size setting... you lost me. Can you clarify your thinking there? I had it set to 256M... of course, I'm definitely willing to try a higher setting to see what happens. –  Michael X May 1 at 4:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.