Announcement

Announcement Module
Collapse
No announcement yet.

Optimize table fails!

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Optimize table fails!

    Hi everyone,

    I have a table with 3 indexes on a slave database. When I issue an optimize from a master server on that table (no indexes), the optimize goes through ok on a master, but fails on a slave:


    110127 6:33:12 [Warning] Warning: Optimize table got errno 2 on log_collector.logs, retrying110127 6:33:13 [ERROR] myisam_sort_buffer_size is too small


    My myisam_sort_buffer_size is set to 4Gb on a slave, and I don't think I can set it to be any larger.

    The table size when running optimize is approximately 80Gb with 1Billion of rows, partitioned with 12 partitions. Index takes approximately 30 Gb of that size.

    I understand there is a problem with rebuilding the index, so one option would be to remove indexes to reduce total index size, but i really need the indexes there.

    Running mysql 5.5.8 here.

    Any suggestions would be greatly appreciated.
    Thank you.

  • #2
    Hi,

    I would check if OPTIMIZE works on the slave if ran manually, if it still fails run CHECK TABLE, it might be some kind of corruption

    Comment


    • #3
      Thanks for reply Peter,

      Before posting this post I actually tried running optimize directly on a slave and the result was the same.
      I did not run check table, will try it today.

      Peter, do you think this may be related to MySQL limitations for index size or to some sort of data corruption within the table? Also what is supposed to happen if index being rebuilt is larger then myisam_sort_buffer_size?
      Can this also be related to the fact the table is partitioned?

      Thank you

      Comment


      • #4
        It should not be limitation for index size, especially as you state you do not have any indexes. If index is larger than myisam_sort_buffer_size when you should have temporary files created for sorting (hence you need enough space) Though if you run out of space the error message should have been different.

        Comment


        • #5
          Peter,

          Just want to clarify, I do not have any indexes on a master database, but the same table has 3 indexes on a slave. This was done to increase insert speed on a master database, as i needed burs insert speed of up to 100K inserts per second. On the slave database the index can reach 30-40 Gb alone, the total table size can reach 100Gb.

          The optimize runs no problem on a master as there is no index to rebuild, but crashes on slave when total index size approaches 30Gb.
          I am suspecting myisam_max_sort_file_size might be a problem as it was set to 10Gb only. I readjusted to myisam_max_sort_file_size = 30G, i will see tomorrow if that fixes the problem and will let you know.

          Spasibo

          Comment


          • #6
            myisam_max_sort_file_size can prevent optimize to use "sort" to build indexes though it should resort to "keycache" method. It is good though if it does not happen as it is very slow

            Comment


            • #7
              Ok, here are the latest updates after setting myisam_max_sort_file_size to 40 GB:

              I tried to run optimize today directly on a slave again. (the table was recreated from scratch 2 days ago after it crashed).
              This produced the following:


              110130 15:55:08 [Warning] Warning: Optimize table got errno 22 on sdv_log_collector.sdv_logs, retrying110130 16:03:10 [ERROR] Key 1 - Found too many records; Can't continue110130 16:03:10 [Warning] Warning: Enabling keys got errno 0 on sdv_log_collector.sdv_logs, retrying110130 16:04:46 [ERROR] Couldn't fix table with quick recovery: Found wrong number of deleted records110130 16:04:46 [ERROR] Run recovery again without -q


              It looks like recovery worked as the table was still usable.
              I performed "check table", this produced the following:


              sdv_log_collector.sdv_logs check warning Table is marked as crashed and last repair failedsdv_log_collector.sdv_logs check warning Found 0 deleted space in delete link chain. Should...sdv_log_collector.sdv_logs check error Found 0 deleted rows in delete link chain. Should ...sdv_log_collector.sdv_logs check error record delete-link-chain corruptedsdv_log_collector.sdv_logs check error Partition p5 returned errorsdv_log_collector.sdv_logs check error Corrupt


              The table crashed after this and is not usable anymore. It looks like partition5 crashed.

              The error log from now on produces this:


              110130 16:20:13 [ERROR] /usr/local/mysql/bin/mysqld: Table './sdv_log_collector/sdv_logs#P#p5' is marked as crashed and last (automatic?) repair failed110130 16:20:13 [ERROR] /usr/local/mysql/bin/mysqld: Table './sdv_log_collector/sdv_logs#P#p5' is marked as crashed and last (automatic?) repair failed110130 16:20:13 [ERROR] /usr/local/mysql/bin/mysqld: Table './sdv_log_collector/sdv_logs#P#p5' is marked as crashed and last (automatic?) repair failed110130 16:20:13 [ERROR] /usr/local/mysql/bin/mysqld: Table './sdv_log_collector/sdv_logs#P#p5' is marked as crashed and last (automatic?) repair failed


              After this I attempted to run ALTER TABLE sdv_logs REPAIR PARTITION p5;

              This fixed the partition:


              mysql> ALTER TABLE sdv_logs REPAIR PARTITION p5;+----------------------------+--------+----------+------------------------------------------+| Table | Op | Msg_type | Msg_text |+----------------------------+--------+----------+------------------------------------------+| sdv_log_collector.sdv_logs | repair | error | myisam_sort_buffer_size is too small || sdv_log_collector.sdv_logs | repair | warning | Number of rows changed from 0 to 9610615 || sdv_log_collector.sdv_logs | repair | status | OK |+----------------------------+--------+----------+------------------------------------------+3 rows in set (1 min 36.44 sec)


              Once the table was restored I tried to run optimize again and that worked fine.

              I am trying to understand what is happening? Can a bulk delete followed by an optimize issued from master corrupt a table on a slave? Can it be connected to binary log mode? (i am running mixed mode right now)

              I will try to run a delete followed by optimize on a master now and see what happens.

              Comment


              • #8
                And we are back to square one. Running delete followed by optimize on a master crashes on slave.


                110130 18:01:33 [ERROR] myisam_sort_buffer_size is too small110130 18:01:33 [Warning] Warning: Optimize table got errno 2 on sdv_log_collector.sdv_logs, retrying110130 18:01:33 [ERROR] myisam_sort_buffer_size is too small110130 18:01:33 [Warning] Warning: Optimize table got errno 2 on sdv_log_collector.sdv_logs, retrying110130 18:01:33 [ERROR] myisam_sort_buffer_size is too small110130 18:01:33 [Warning] Warning: Optimize table got errno 2 on sdv_log_collector.sdv_logs, retrying110130 18:01:33 [ERROR] myisam_sort_buffer_size is too small110130 18:01:33 [Warning] Warning: Optimize table got errno 2 on sdv_log_collector.sdv_logs, retrying110130 18:01:34 [ERROR] myisam_sort_buffer_size is too small110130 18:01:34 [Warning] Warning: Optimize table got errno 2 on sdv_log_collector.sdv_logs, retrying110130 18:01:36 [ERROR] myisam_sort_buffer_size is too small110130 18:01:36 [Warning] Warning: Optimize table got errno 2 on sdv_log_collector.sdv_logs, retrying110130 18:01:36 [ERROR] myisam_sort_buffer_size is too small110130 18:01:36 [Warning] Warning: Optimize table got errno 2 on sdv_log_collector.sdv_logs, retrying110130 18:01:36 [ERROR] myisam_sort_buffer_size is too small110130 18:01:36 [Warning] Warning: Optimize table got errno 2 on sdv_log_collector.sdv_logs, retrying110130 18:01:37 [ERROR] myisam_sort_buffer_size is too small110130 18:01:37 [Warning] Warning: Optimize table got errno 2 on sdv_log_collector.sdv_logs, retrying110130 18:01:37 [ERROR] myisam_sort_buffer_size is too small110130 18:01:37 [Warning] Warning: Optimize table got errno 2 on sdv_log_collector.sdv_logs, retrying110130 18:01:37 [ERROR] myisam_sort_buffer_size is too small110130 18:01:37 [Warning] Warning: Optimize table got errno 2 on sdv_log_collector.sdv_logs, retrying110130 18:01:38 [ERROR] myisam_sort_buffer_size is too small110130 18:01:38 [Warning] Warning: Optimize table got errno 2 on sdv_log_collector.sdv_logs, retrying110130 18:01:38 [ERROR] Key 1 - Found too many records; Can't continue110130 18:01:38 [Warning] Warning: Enabling keys got errno 0 on sdv_log_collector.sdv_logs, retrying110130 18:07:39 [ERROR] Couldn't fix table with quick recovery: Found wrong number of deleted records110130 18:07:39 [ERROR] Run recovery again without -q110130 18:07:44 [ERROR] Key 1 - Found too many records; Can't continue110130 18:07:44 [Warning] Warning: Enabling keys got errno 0 on sdv_log_collector.sdv_logs, retrying


                Is there anything else I can do? Any suggestions will be greatly appreciated.

                Comment


                • #9
                  Ok, have done some more tests and it looks like my index will corrupt even on non partitioned tables in the event when "keycache sorting" needs to be used due to myisam_sort_buffer_size being to small to rebuild the index. I understand that "keycache" is not a desirable way as it is extremely slow, but I still expect it to work! and it looks like it does not on my system.

                  I am starting to think this may be related to the mysql distribution I am using and the way it may be compiled (5.5.8 linux generic). I will try use another version tomorrow and see if it will help.

                  Comment


                  • #10
                    I've just downgraded the database to mysql-5.1.54-linux-x86_64-glibc23.
                    Running delete followed by optimize still crashes table index. Repair table will fix the table.

                    I get a different error code though this time:


                    Warning: Optimize table got errno 144


                    So it looks like mysql binaries are not really key here, can it be connected to the OS?

                    Any ideas please?

                    Comment


                    • #11
                      Ok, it looks like I figured it out!

                      The issue here was "myisam sort buffer size". When this value is set to 4Gb or higher (for 64 bit systems), table optimize will fail if executed after a bulk delete.
                      I did not pinpoint exactly what size the table has to be, but I got consistent results when I was performing tests on 40Gb table - 20Gb data, 20Gb index, removing 50% of the records.
                      It looks to me like a bug and i have confirmed this behavior on 5.5 and 5.1.

                      Hopefully this helps someone someday!

                      Comment


                      • #12
                        Hi,

                        Did you report a bug ?

                        I remember there is a whole series of bugs with going over 4G limit for various variables. I'm not surprised if there is one more.

                        Comment


                        • #13
                          Hi Peter,

                          Yes I did, http://bugs.mysql.com/bug.php?id=59925

                          Hopefully they can confirm it and it will get some traction.

                          The problem I had is that LOAD, DELETE, OPTIMIZE series takes a very ..very long time for the tables of this size, and that's on a HP DL580 64G ram, 8 disk hardware raid 5 with buffer! ( i know raid 5 but there are reasons why)

                          It took me so long that I started copying tables on the OS level, even then delete+optimize took 3 - 3.5 hours. I spent almost 4 days to more or less understand the behavior, but was not able to pinpoint exact index/data size ratio when it fails. I think this would have helped them a lot, but I just don't have anymore time

                          Comment

                          Working...
                          X