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 was going through compressing a bunch of archival myisam tables using myisampack

It was making decent progress until it got to the most recent tables. The overall database is a set of archives for a single production table. Each archive version represents 1 quarter of a year going back several years.

Nothing has drastically changed in the data being stored or the average row length. The individual archives have been growing with time as there simply are more on more rows each quarter over the previous one.

Up until the most recent 4 quarters the rate of compressing/rebuilding the index (which is only a single int PK) was around 14-16k rows/seconds.

The last ones were crawling along only doing about 1k rows/second. Even right from the start; it wasn't like it started of as fast as the others and then suddenly slowed down after getting so far into it.

There wasn't any spike in general system load or i/o contention from other processes when it started slowing down.

Has anyone experienced this? Is there something myisampack where it's overall rate of progress it should be expected to tank when compressing a table over a certain size?

Edit: Since these are myisam tables I should point out they are all in the same datadir and all stored on the same physical array.

share|improve this question

1 Answer 1

If you have any TEXT/BLOB fields in your MyISAM tables, you may just have bloated fields.

You verify this, go back to your older MyISAM tables and perform this:

SELECT LENGTH(TextOrBLOBField) FROM some_table;

Run this same query against more recent tables and see if the slowness is just O(n) (order n) running time based on the amount of recorded data.

share|improve this answer
    
The overall runtime had been increasing with about O(n) as things progressed. The rate seemed pretty constant though. There are definitely text field. My current working theory is that the more recent tables correspond to an increase of more international records resulting in more multi-byte utf8 encodings that happen to not compress as well. –  atxdba Feb 6 '12 at 23:31
    
You may be on to something in terms of charset usage –  RolandoMySQLDBA Feb 6 '12 at 23:36

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.