Learn about Centmin Mod LEMP Stack today
Register Now

Beta Branch update MariaDB 10.1 & 10.2 default /etc/my.cnf default for innodb_default_row_format

Discussion in 'Centmin Mod Github Commits' started by eva2000, Apr 30, 2018.

  1. eva2000

    eva2000 Administrator Staff Member

    36,323
    7,979
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,287
    Local Time:
    3:17 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    update MariaDB 10.1 & 10.2 default /etc/my.cnf default for innodb_default_row_format

    - MariaDB 10.1.32 and MariaDB 10.2.2 and higher introduced a new variable for InnoDB which Oracle MySQL already supported for innodb_default_row_format XtraDB/InnoDB Server System Variables. Prior to this MariaDB defaulted to innodb row format = COMPACT but MySQL 5.7.9 and MySQL 8.0 changed the defaults to DYNAMIC Oracle MySQL 8.0 GA Stable Release.
    - This update in Centmin Mod 123.09beta01 and newer branches sets in MariaDB 10.1 and 10.2 /etc/my.cnf the new variable which now changes the default row format for InnoDB tables from COMPACT to DYNAMIC, innodb_default_row_format = dynamic. This only applies to new Centmin Mod 123.09beta01 fresh installs so as to avoid any unforeseen changes to existing Centmin Mod user's MariaDB database tables. You can manually convert your InnoDB tables from ROW_FORMAT = COMPACT to ROW_FORMAT = DYNAMIC via ALTER TABLES command if needed. Basic example posted in gist at ROW_FORMAT COMPACT TO DYNAMIC for centmin mod mariadb 10 mysql
    - This update doesn't change the base my.cnf templates but instead at initial MariaDB install and MariaDB 10.1 and 10.2 upgrade times does an on the fly addition to existing /etc/my.cnf adding the value to [mariadb-10.1] or [mariadb-10.2] sections of /etc/my.cnf for variable set as, innodb_default_row_format = dynamic. This allows for the variable to only apply to MariaDB 10.1 or 10.2 servers and not MariaDB 10.0 as the variable doesn't exist in version prior to MariaDB 10.1.32 or 10.2.2.

    Example in MariaDB 10.1
    Code (Text):
    [mariadb-10.1]
    innodb_file_format = Barracuda
    innodb_default_row_format = dynamic
    innodb_file_per_table = 1
    

    Example in MariaDB 10.2 - innodb_file_format is commented out as it's deprecated in MariaDB 10.2 as native default in MariaDB 10.2 is Barracuda file format for InnoDB tables
    Code (Text):
    [mariadb-10.2]
    #innodb_file_format = Barracuda
    innodb_default_row_format = dynamic
    innodb_file_per_table = 1
    


    Continue reading...

    123.09beta01 branch
     
    • Useful Useful x 1
  2. eva2000

    eva2000 Administrator Staff Member

    36,323
    7,979
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,287
    Local Time:
    3:17 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    On fresh Centmin Mod 123.09beta01 installs after this update, you can verify if the new innodb_default_row_format MariaDB MySQL server variable is set to dynamic using SSH command below
    Code (Text):
    mysqladmin var | grep row_format | tr -s ' '
    

    should return dynamic instead of compact. Older Centmin Mod 123.09beta01 installs would return compact instead of dynamic.
    Code (Text):
    mysqladmin var | grep row_format | tr -s ' '
    | innodb_default_row_format | dynamic |
    

    So no more 767 byte limit errors for index length as Centmin Mod MariaDB 10.1 default to Barracuda file format with innodb_large_prefix enabled already. So max InnoDB max index length increases from 767 bytes to 3072 bytes :)
     
    • Like Like x 1
  3. pamamolf

    pamamolf Well-Known Member

    3,117
    295
    83
    May 31, 2014
    Ratings:
    +530
    Local Time:
    8:17 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    So on my old existing installation that i use 10.1.32 can i add this line manually?

    and then restart MariaDB?
     
  4. eva2000

    eva2000 Administrator Staff Member

    36,323
    7,979
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,287
    Local Time:
    3:17 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    Yes but only applies to database InnoDB tables newly created since. For existing InnoDB tables, you need to do ALTER TABLE command to change it as outlined in gist example link above.
     
    • Informative Informative x 1
  5. pamamolf

    pamamolf Well-Known Member

    3,117
    295
    83
    May 31, 2014
    Ratings:
    +530
    Local Time:
    8:17 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Doing this for every table needs a lot of time :(

    Any faster way?

    Maybe something like this for all tables in a database?

     
  6. eva2000

    eva2000 Administrator Staff Member

    36,323
    7,979
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,287
    Local Time:
    3:17 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    Example in gist shows how to generate all alter table commands by querying the
    INFORMATION_SCHEMA data and which you can script to run ROW_FORMAT COMPACT TO DYNAMIC for centmin mod mariadb 10 mysql. Google search on how to query MySQL INFORMATION_SCHEMA data on your server ;)

    i.e. INFORMATION_SCHEMA databases TABLES table has following columns you can query
    Code (Text):
    mysql -e "SHOW CREATE TABLE INFORMATION_SCHEMA.TABLES \G;"
    *************************** 1. row ***************************
           Table: TABLES
    Create Table: CREATE TEMPORARY TABLE `TABLES` (
      `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
      `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
      `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
      `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
      `ENGINE` varchar(64) DEFAULT NULL,
      `VERSION` bigint(21) unsigned DEFAULT NULL,
      `ROW_FORMAT` varchar(10) DEFAULT NULL,
      `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
      `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
      `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
      `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
      `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
      `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
      `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
      `CREATE_TIME` datetime DEFAULT NULL,
      `UPDATE_TIME` datetime DEFAULT NULL,
      `CHECK_TIME` datetime DEFAULT NULL,
      `TABLE_COLLATION` varchar(32) DEFAULT NULL,
      `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
      `CREATE_OPTIONS` varchar(2048) DEFAULT NULL,
      `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
    ) ENGINE=MEMORY DEFAULT CHARSET=utf8
    


    i.e. to list all InnoDB tables with ROW_FORMAT = Compact and generate ALTER TABLE commands which you can script/run in batches
    Code (Text):
    mysql -N -e "SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'InnoDB' AND ROW_FORMAT = 'Compact' AND TABLE_SCHEMA != 'mysql';" | while read d t e r; do echo "ALTER TABLE $d.$t ROW_FORMAT=DYNAMIC;"; done
    

    will return all InnoDB tables with ROW_FORMAT = Compact excluding mysql system database and generate ALTER TABLE commands. Below server only had one database named test1 with table name t1 with ROW_FORMAT = Compact
    Code (Text):
    ALTER TABLE test1.t1 ROW_FORMAT=DYNAMIC;
    


    on another server with wordpress install it would return the following
    Code (Text):
    mysql -N -e "SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'InnoDB' AND ROW_FORMAT = 'Compact' AND TABLE_SCHEMA != 'mysql';" | while read d t e r; do echo "ALTER TABLE $d.$t ROW_FORMAT=DYNAMIC;"; done
    ALTER TABLE wp2872328615dbx_16230.6619_commentmeta ROW_FORMAT=DYNAMIC;
    ALTER TABLE wp2872328615dbx_16230.6619_comments ROW_FORMAT=DYNAMIC;
    ALTER TABLE wp2872328615dbx_16230.6619_links ROW_FORMAT=DYNAMIC;
    ALTER TABLE wp2872328615dbx_16230.6619_options ROW_FORMAT=DYNAMIC;
    ALTER TABLE wp2872328615dbx_16230.6619_postmeta ROW_FORMAT=DYNAMIC;
    ALTER TABLE wp2872328615dbx_16230.6619_posts ROW_FORMAT=DYNAMIC;
    ALTER TABLE wp2872328615dbx_16230.6619_term_relationships ROW_FORMAT=DYNAMIC;
    ALTER TABLE wp2872328615dbx_16230.6619_term_taxonomy ROW_FORMAT=DYNAMIC;
    ALTER TABLE wp2872328615dbx_16230.6619_termmeta ROW_FORMAT=DYNAMIC;
    ALTER TABLE wp2872328615dbx_16230.6619_terms ROW_FORMAT=DYNAMIC;
    ALTER TABLE wp2872328615dbx_16230.6619_usermeta ROW_FORMAT=DYNAMIC;
    ALTER TABLE wp2872328615dbx_16230.6619_users ROW_FORMAT=DYNAMIC;
    

    which is correct as those database tables are currently ROW_FORMAT=Compact
    Code (Text):
    +-----------------------------------0-----------+----------------+----------------+-----------+------------+--------+------------+--------------------+
    | Table Name                       0            | Number of Rows | Storage Engine | Data Size | Index Size | Total  | ROW_FORMAT | TABLE_COLLATION    |
    +----------------------------------0------------+----------------+----------------+-----------+------------+--------+------------+--------------------+
    | wp2872328615dbx_16230.6619_commentmeta        | 0 Rows         | InnoDB         | 0.02MB    | 0.03MB     | 0.05MB | Compact    | utf8mb4_unicode_ci |
    | wp2872328615dbx_16230.6619_comments           | 0 Rows         | InnoDB         | 0.02MB    | 0.08MB     | 0.09MB | Compact    | utf8mb4_unicode_ci |
    | wp2872328615dbx_16230.6619_links              | 0 Rows         | InnoDB         | 0.02MB    | 0.02MB     | 0.03MB | Compact    | utf8mb4_unicode_ci |
    | wp2872328615dbx_16230.6619_options            | 116 Rows       | InnoDB         | 0.06MB    | 0.03MB     | 0.09MB | Compact    | utf8mb4_unicode_ci |
    | wp2872328615dbx_16230.6619_postmeta           | 0 Rows         | InnoDB         | 0.02MB    | 0.03MB     | 0.05MB | Compact    | utf8mb4_unicode_ci |
    | wp2872328615dbx_16230.6619_posts              | 2 Rows         | InnoDB         | 0.02MB    | 0.06MB     | 0.08MB | Compact    | utf8mb4_unicode_ci |
    | wp2872328615dbx_16230.6619_term_relationships | 0 Rows         | InnoDB         | 0.02MB    | 0.02MB     | 0.03MB | Compact    | utf8mb4_unicode_ci |
    | wp2872328615dbx_16230.6619_term_taxonomy      | 0 Rows         | InnoDB         | 0.02MB    | 0.03MB     | 0.05MB | Compact    | utf8mb4_unicode_ci |
    | wp2872328615dbx_16230.6619_termmeta           | 0 Rows         | InnoDB         | 0.02MB    | 0.03MB     | 0.05MB | Compact    | utf8mb4_unicode_ci |
    | wp2872328615dbx_16230.6619_terms              | 0 Rows         | InnoDB         | 0.02MB    | 0.03MB     | 0.05MB | Compact    | utf8mb4_unicode_ci |
    | wp2872328615dbx_16230.6619_usermeta           | 15 Rows        | InnoDB         | 0.02MB    | 0.03MB     | 0.05MB | Compact    | utf8mb4_unicode_ci |
    | wp2872328615dbx_16230.6619_users              | 1 Rows         | InnoDB         | 0.02MB    | 0.05MB     | 0.06MB | Compact    | utf8mb4_unicode_ci |
    +----------------------------------------------+----------------+----------------+-----------+------------+--------+------------+--------------------+
    
     
    • Informative Informative x 1
..