Mysql – Issue with Storage Engine

myisamMySQLstorage-engine

Just got the below error in one of the slave. I just Googled it and changed the variable myisam_data_pointer_size from 4 to 6.

Somehow, my ALTER TABLE was successful.

  • Can you please let me know whether my approach was right. I am not sure on this variable?

  • Will this cause any performance issue. should I roll back it ?

    130208 9:40:19 [ERROR] Slave: Error 'The table '#sql-19fc_3f8acc6' is full' on query. Default database: 'registry'. Query: 'alter table enom_log_2012_12_03 add responsedatatype char(1) not null after requestdata', Error_code: 1114
    130208 9:40:19 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'pumpkin-bin.008662' position 411952695

Problem Description

Check for the Alerts in Errorlog for any errors and failures. Check if MySQL is up? if not verify if the process was Normal shutdown.

Advice

mysql> SHOW TABLE STATUS FROM registry LIKE 'enom_log_2012_12_03'\G
*************************** 1. row ***************************
           Name: enom_log_2012_12_03
         Engine: MyISAM
        Version: 9
     Row_format: Dynamic
           Rows: 1786679
 Avg_row_length: 2403
    Data_length: 4293600396
Max_data_length: 4294967295
   Index_length: 60999680
      Data_free: 0
 Auto_increment: 1786680
    Create_time: 2013-02-06 15:19:07
    Update_time: 2013-02-06 15:22:16
     Check_time: 2013-02-06 15:26:35
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)




mysql> show global variables like '%tmp%';
+----------------+-----------------------+
| Variable_name  | Value                 |
+----------------+-----------------------+
| bdb_tmpdir     | /usr/local/mysql/tmp/ |
| max_tmp_tables | 32                    |
| tmp_table_size | 8388608               |
| tmpdir         | /usr/local/mysql/tmp  |
+----------------+-----------------------+

mysql> show global variables like '%myisam%';
+---------------------------------+---------------+
| Variable_name                   | Value         |
+---------------------------------+---------------+
| myisam_data_pointer_size        | 4             |--before changing it was 4 .. i changed it to 6
| myisam_max_extra_sort_file_size | 2147483648    |
| myisam_max_sort_file_size       | 2147483647    |
| myisam_recover_options          | OFF           |
| myisam_repair_threads           | 1             |
| myisam_sort_buffer_size         | 4194304       |
| myisam_stats_method             | nulls_unequal |
+---------------------------------+---------------

Best Answer

I have not had much experience switching the myisam_data_pointer_size option from 4 to 6.

Simply put, I think you got it right based on the Documentation. Here is why:

According to the MySQL Documentation 5.5 on myisam_data_pointer_size:

The default pointer size in bytes, to be used by CREATE TABLE for MyISAM tables when no MAX_ROWS option is specified. This variable cannot be less than 2 or larger than 7. The default value is 6. See Section C.5.2.12, “The table is full”.

The fact that you changed it from 4 shows that the table was created originally in an earlier version of MySQL (default pointer size is 4 in MySQL 4.1).

Looking at the additional MySQL 4.1 reference for “The table is full”:

You are using a MyISAM table and the space required for the table exceeds what is permitted by the internal pointer size. MyISAM creates tables to permit up to 4GB by default, but this limit can be changed up to the maximum permissible size of 65,536TB (2567 – 1 bytes).

If you need a MyISAM table that is larger than the default limit and your operating system supports large files, the CREATE TABLE statement supports AVG_ROW_LENGTH and MAX_ROWS options. See Section 12.1.5, “CREATE TABLE Syntax”. The server uses these options to determine how large a table to permit.

If the pointer size is too small for an existing table, you can change the options with ALTER TABLE to increase a table's maximum permissible size. See Section 12.1.2, “ALTER TABLE Syntax”.

Since you ran the change and MySQL did not keel over in agony, you must have gotten it right.

CAVEAT #1

I noticed the table characteristics you posted

  • Rows: 1786679 (that's 1,786,679 rows)
  • Avg_row_length: 2403
  • Row_format: Dynamic

What disturbs me is the average length. Why? I have easily seen 1 Billion Row MyISAM tables and never had a client switch its pointer size. This may be a good time to reevaluate the table's layout.

Here are some possible ways to shrink the table without touching the pointer size ?

  • Does the table have a lot of TEXT/BLOB fields ?
  • Could those TEXT/BLOB field be removed an placed in another table ?
  • Could the table stand some normalization ?
  • Do you have INT fields that could be shrunk to TINY, SMALLINT, or MEDIUMINT ?

CAVEAT #2

The next time you get table is full, please do not automatically assume that the table reached its internal limit. Always check the datadir to make sure the disk itself is not full.

Related Question