You are here
FromDual TechFeed (en)
One of our customers had a question related to the right value of Galera Cache size (gcache.size) in Galera Cluster for MySQL which I would like to share with you.
The question was: My maintenance window takes 4 hours for my 5TB DB. How can I avoid an SST ?!
Basically, having too small GCache size will lead to SST (Snapshot State Transfer) instead of IST (Incremental State Transfer), thus we can avoid the SST by setting the GCache to the appropriate value.
To check the current value of the GCache size:mysql> SHOW GLOBAL VARIABLES LIKE 'wsrep_provider_options'\G Variable_name: wsrep_provider_options Value: base_host = 192.168.1.12; . . . gcache.page_size = 128M; gcache.size = 128M; gcs.fc_debug = 0; . . .
The value of GCache size could be changed by adding the following line in the my.cnf file and restarting the node (it could NOT be changed online):#my.cnf [mysqld] wsrep_provider_options="gcache.size=256M"
But the question is how can we calculate the right value for GCache size to cover the maintenance window and at the same time not larger than what it needs?
To answer that question we should first find out how much GCache can handle which could be calculated by the following formula:
Hold time = GCache size / Replication Rate.
- Replication Rate = Amount of replicated data / time.
- Amount of replicated data = (wsrep_replicated_bytes + wsrep_received_bytes) after the maintenance window - (wsrep_replicated_bytes + wsrep_received_bytes) before the maintenance window.
The amount of replicated data for the customer's case = 7200MB.
Now, we can find out how much GCache (default 128M) can handle for the customer's case:
Hold time = 128MB / (7200MB / 4h) = 128MB / 0.5 MB = 256s.
Then, we can calculate the right GCache size value to handle the maintenance window by the following formula:
GCache = Maintenance window * Replication Rate = 14400s * 0.5 MB.
GCache = 7200MB.
In other words, the right GCache size should be equivalent to (or not less than) the amount of replicated data.A shorter way using the binary logs size
Is there any relation between (wsrep_replicated_bytes + wsrep_received_bytes) and the binary log traffic? Lets check that by the following test scenario:
- Starting a Galera Cluster where the newest binary log file size will be empty (120 bytes) and the same for the status variables: mysql> SHOW GLOBAL STATUS LIKE 'wsrep_replicated_bytes'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | wsrep_replicated_bytes | 0 | +------------------------+-------+ 1 row in set (0.00 sec) mysql> SHOW GLOBAL STATUS LIKE 'wsrep_received_bytes'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | wsrep_received_bytes | 368 | +----------------------+-------+ 1 row in set (0.00 sec)
- Execute some DML statements on the current node (to increase wsrep_replicated_bytes) and on another node (to increase wsrep_received_bytes), then check the status values:
mysql> SHOW GLOBAL STATUS LIKE 'wsrep_replicated_bytes'; +------------------------+----------+ | Variable_name | Value | +------------------------+----------+ | wsrep_replicated_bytes | 80125192 | +------------------------+----------+ 1 row in set (0.00 sec) mysql> SHOW GLOBAL STATUS LIKE 'wsrep_received_bytes'; +------------------------+----------+ | Variable_name | Value | +------------------------+----------+ | wsrep_received_bytes | 40062948 | +------------------------+----------+ 1 row in set (0.00 sec)
The Amount of replicated data = (80125192 + 40062948) - (0 + 368) = 120187772 Bytes.
- Checking the increase of the binary log file: shell> ll /var/lib/mysql/mysql-bin.000243 -rw-rw---- 1 mysql mysql 113769454 Mar 20 13:42 mysql-bin.000243
- The variable log_slave_updates MUST to be enabled on ALL nodes, otherwise, the option wsrep_received_bytes will not be reflected on the binary logs, thus will lead to WRONG calculations!!
- Since the cluster is freshly started, MySQL started to write into a new binary log file while in an already running cluster we can force MySQL to start from new binary log file by issuing the SQL command "FLUSH BINARY LOGS;" so that we can use the total size of the binary logs generated after that during the maintenance.
- GCache size = Maintenance window * Replication Rate.
- GCache size = (wsrep_replicated_bytes + wsrep_received_bytes) after the maintenance window - (wsrep_replicated_bytes + wsrep_received_bytes) before the maintenance window.
- GCache size = binary log traffic which occurs during the maintenance window.
Although the binary log traffic will be always less than the amount of replicated data but they are nearly close, thus we can use it to get a rough estimation about the right GCache size value, hence the formula will be as follows:
Sometimes, it is required to enable the general query log (which is disabled by default). If the general query log is enabled the server writes to this log information when clients connect or disconnect, and each SQL statement received from the client.
The question is, does enabling the general query log affects the MySQL performance ?
Also, it is possible to record the output of this log into either file or table in the mysql database (mysql.general_log), what is the performance impact of each one?
Let's do some simple benchmark for those scenarios to measure the actual impact on the mysql performance.System Information: HW configurations:
- CPU: Intel® Core™ i7-3520M Processor (4M Cache, up to 3.60 GHz).
- 2 cores, 4 threads, HT enabled.
- Memory: 8GB RAM (1600).
- Storage: HDD 1TB/ 5400RPM.
- OS: Ubuntu 12.04
- MySQL Server: 5.6.17
- Sysbench: 0.4.12
- Sysbench command: sysbench --num-threads=1 --max-requests=1000 --db-driver=mysql --test=oltp --mysql-table-engine=InnoDB --mysql-user=root run .
- Table structure which was used by sysbench tests: mysql> show create table sbtest.sbtest\G CREATE TABLE `sbtest` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=8574 DEFAULT CHARSET=latin1
The test had been made against 1,2,4,8,16 and 32 threads, each throughput/response time value for each thread's number for each test case is generated by the average of ten (10) times execution.General log Disabled:
To make sure that the general query log is disabled:mysql> show global variables like'general_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | OFF | +---------------+-------+
The general query log is a dynamic variable which means that it could be enabled or disabled ONLINE without restarting MySQL (since MySQL 5.1):mysql> SET GLOBAL general_log=ON;
We can choose the output for this log to be either in a log file (by default) or in a MySQL table (mysql.general_log).
What are the benefits that we might get if we have the log output to be stored in a table not in a file?
- We can use the normal SQL statements to access the log contents to get information about specific criteria (e.g. using WHERE condition) and this is a little harder when dealing with files.
- The log contents could be accessed remotely if someone can connect to the MySQL server.
- Standard format for the log entries.
- If the CSV engine is used for the log table, it will be easy to import the CSV file into spreadsheets.
- It is easy to expire the logs by simply TRUNCATE the log table.
- Log rotation is possible by using RENAME TABLE statement.
- Log entries are not replicated to the slave because they are not written to the binary logs.
- The mysqldump does not include the log tables contents (general_log or slow_log) in the backup even if --all-databases backup option is used.
So, let's check the performance impact then of each log output.Output is FILE:
To check the output destination of the general log, the following command should be used:mysql> show global variables like'log_output'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | FILE | +---------------+-------+
To change the output destination of the general log from file to table (CSV by default), the following command should be used:mysql> SET GLOBAL log_output='TABLE'; mysql> show global variables like'log_output'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | TABLE | +---------------+-------+
Maybe due to the nature of the CSV storage ENGINE we faced performance issues in the previous case. Is it possible to change the table engine of the general_log table?
The answer is yes, but unfortunately, we are restricted to use only MyISAM storage engine and no engines other than CSV or MyISAM are allowed. Check this link for more information.
To alter the log table, you must first disable the logging:mysql> alter table mysql.general_log engine=MYISAM; ERROR 1580 (HY000): You cannot 'ALTER' a log table if logging is enabled mysql> SET GLOBAL general_log=OFF; mysql> alter table mysql.general_log engine=MYISAM; mysql> SET GLOBAL general_log=ON;
In general, to make any SQL query work faster, we might need to optimize the table structure, add indexes, re-write the query, .. etc.
The following is the general log table structure:mysql> show create table mysql.general_log\G CREATE TABLE `general_log` ( `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `user_host` mediumtext NOT NULL, `thread_id` bigint(21) unsigned NOT NULL, `server_id` int(10) unsigned NOT NULL, `command_type` varchar(64) NOT NULL, `argument` mediumtext NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log'
Let's check what can we do to optimize the general_log table structure (other suggestions are welcomed):
- Can we create partitions on that table which might boost our search?
Although this is a normal MyISAM table but partitioning IS NOT allowed for the log tables (by the way, partitioning is not permitted also for CSV tables).
- Can we change the datatype of the `user_host` column from mediumtext to e.g. varchar(100)? (The max length of this column's data on my machine didn't exceed 50 characters)
While it is - syntax wise - accepted but no logs will be stored in the table after that and the following error will be printed in the error log file: 2014-03-06 18:44:21 6987 [ERROR] Failed to write to mysql.general_log: 2014-03-06 18:44:23 6987 [ERROR] Incorrect definition of table mysql.general_log: expected column 'user_host' at position 1 to have type mediumtext, found type varchar(100).
- What about creating indexes on the columns we will use for most of our searches (`event_time` and `argument`)? lets try that!
- Add index on `event_time` column:
mysql> SET GLOBAL general_log=OFF; mysql> alter table mysql.general_log add index ev_tm_idx(`event_time`); mysql> show create table mysql.general_log\G CREATE TABLE `general_log` ( `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `user_host` mediumtext NOT NULL, `thread_id` bigint(21) unsigned NOT NULL, `server_id` int(10) unsigned NOT NULL, `command_type` varchar(64) NOT NULL, `argument` mediumtext NOT NULL, KEY `ev_tm_idx` (`event_time`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log' mysql> SET GLOBAL general_log=ON;
- Add index on `event_time` column:
- Add FULLTEXT index on `argument` column:
mysql> SET GLOBAL general_log=OFF; mysql> alter table mysql.general_log add fulltext index (`argument`); mysql> show create table mysql.general_log\G CREATE TABLE `general_log` ( `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `user_host` mediumtext NOT NULL, `thread_id` bigint(21) unsigned NOT NULL, `server_id` int(10) unsigned NOT NULL, `command_type` varchar(64) NOT NULL, `argument` mediumtext NOT NULL, KEY `ev_tm_idx` (`event_time`), FULLTEXT KEY `argument` (`argument`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log' mysql> SET GLOBAL general_log=ON;
To make it more clear, the following is the combination of all results in one graph followed by response time comparison:
The raw results in Transactions / Sec might be useful:
Threads12481632General log disabled383.996814.7591421.2881674.7331414.9851071.189General log enabled (File)281.642521.391230.7431406.1271095.896923.986General log enabled (CSV Table)231.659447.173787.578507.846426.324439.992General log enabled (MyISAM Table)249.47536.379933.304532.912476.454454.015General log enabled (MyISAM Table + index)238.508430.05875.209465.464465.464395.063General log enabled (MyISAM Table + Fulltext index)157.436236.156210.968212.273218.617220.701
- The best MySQL performance - among all above test cases - could be achieved by disabling the general query log, e.g. if we compared the above results for the case 4 concurrent threads (the highest value for most scenarios) we would find that:
- Using the general log enabled (general_log = ON) and the log destination is file (log_output = FILE) decreased the throughput by 13.4% and increased the response time by 17.5%.
- Using the general log enabled and the log destination is CSV table decreased the throughput by 44.6% and increased the response time by 90%.
- Using the general log enabled and the log destination is MyISAM table decreased the throughput by 34.3% and increased the response time by 59%.
- Using the general log enabled and the log destination is MyISAM having an index added on column `event_time` decreased the throughput by 38.4% and increased the response time by 73%.
- Using the general log enabled and the log destination is MyISAM having an index added on column `event_time` and FULLTEXT index added on column `argument` decreased the throughput by 85% and increased the response time by 542%.
- Although using table as a log output destination has many benefits - as described above - but it has more negative impact on the MySQL performance as compared to log file.
- Increasing the number of concurrently running threads - in the case of log_output=TABLE - will increase the general_log table contention which is controlled by the table locking level for MyISAM or CSV ENGINES.
- Like any other MySQL table - as many rows inserted in the log table as more negative performance impact.
- Finally, it is preferred to only enable the general query log when it is really needed and it is not recommended to enable it in a production system. It could be enabled (dynamically) for a while then should be disabled again once we have got what we are searching for.
One of the most expensive database operations is performing Data Definition Language (DDL, e.g. CREATE, DROP, ALTER, etc.) statements, specially, the ALTER statements because MySQL blocks the entire table for both reads and writes while modifying the table.
For the huge tables, this might take hours to get the table changed which affects the application, so that, a good planning is required for such operations in order to avoid doing these changes during the peak times. For those people who have 24/7 services or limited maintenance window, DDL on huge tables is a really nightmare.
Percona developed a very good tool called pt-online-schema-change (version 2.2.6 at the time of writing this article) to perform such operations online without blocking/affecting the application and read/write operations to the table being changed is available.
Also MySQL made some enhancements for DDL statements and introduced the Online DDL feature in MySQL 5.6.
In this article, I will talk about an overview of both ways (Online DDL & pt-online-schema-change) alongside with an example and which one of them should be used in different scenarios.pt-online-schema-change Overview
This tool is developed by Percona to alter tables without locking them during the ALTER operation.
Simply, this tool creates a new empty table like the original table with the needed structure change, copy the data from the original table in small chunks to the new table, drop the original table and then rename the new table to the original name. During the copy process all new changes to the original table are being applied to the new one because a trigger is created on the original table which ensure that all new changes will be applied on the new table.
For more information about pt-online-schema-change tool, check out the manual documentation.Example
Altering a table called "test.test1" by adding an index (name_idx) on column "name":[root@gcservera ~]# pt-online-schema-change --execute --alter "add index name_idx (name)" D=test,t=test1,h=localhost Operation, tries, wait: copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering `test`.`test1`... Creating new table... Created new table test._test1_new OK. Altering new table... Altered `test`.`_test1_new` OK. 2014-02-09T15:33:27 Creating triggers... 2014-02-09T15:33:27 Created triggers OK. 2014-02-09T15:33:27 Copying approximately 1 rows... 2014-02-09T15:33:27 Copied rows OK. 2014-02-09T15:33:27 Swapping tables... 2014-02-09T15:33:27 Swapped original and new tables OK. 2014-02-09T15:33:27 Dropping old table... 2014-02-09T15:33:27 Dropped old table `test`.`_test1_old` OK. 2014-02-09T15:33:27 Dropping triggers... 2014-02-09T15:33:27 Dropped triggers OK. Successfully altered `test`.`test1`.
The output is perfectly describing all steps that the tool is doing in the background.Limitations
- A PRIMARY KEY or a unique index should be defined for the table before using this tool because it is required for the DELETE trigger.
- Not supported if the table has already triggers defined.
- The tool become complicate a little if the table has a foreign key constraint and an additional option --alter-foreign-keys-method should be used.
- Also because of the foreign keys, the object names might be changed (indexes names , .. etc).
- In Galera Cluster environment, altering MyISAM tables is not supported and the system variable "wsrep_OSU_method" must be set to "TOI" (total order isolation).
In MySQL 5.5 and 5.1 with the InnoDB plugin, a new feature known as Fast Index Creation was introduced to avoid copying the tables data - when adding or removing secondary indexes - using the optimized CREATE INDEX and DROP INDEX statements.
In MySQL 5.6, the Online DDL method was introduced to allow more changes to be made on the table while accessing and writing to the table being changed is available.
The Online DDL syntax is exactly the same like the normal alter statement after specifying two parameters:ALGORITHM:
- INPLACE: the table change will be made in-place without rebuilding the entire table (in most cases, no copying data to temporary table is required).
- COPY: copying data to a temporary table, rebuilding the table and reconstructing the secondary indexes will be made (equivalent to the traditional method).
- NONE: Read and write operations are allowed during the altering process.
- SHARED: Only read operations are allowed during the altering operations (DML is not allowed).
- EXCLUSIVE: The entire table will be locked for both reading and writing (neither select nor DML are allowed).
This topic is perfectly explained in the online manual documentation, you can check it out here for more information.Example
Altering a table called "test.test2" by adding an index (name_idx) on column "name":mysql> alter table test2 -> add index name_idx (name),algorithm=inplace, lock=none; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 Limitations
- Works only with InnoDB (syntax wise it could be used with other storage engines "like MyISAM" but only "algorithm=copy" is allowed which is equivalent to the traditional method).
- Regardless of the locking used (none,shared or exclusive) a brief period at the beginning and at the end of the process is requiring an exclusive lock on the table.
- foreign_key_checks should be disabled when adding/dropping foreign keys to avoid table copying behavior.
- Still some alter operations require data copy to a temp table or table locking in order to make the change (the old behavior), for more details on which table change require table-copying or table locking, check out this manual page.
The following is a comparison results between Online DDL and pt-online-schema-change for some alter operations applied on a table contains 1,078,880 rows:
Online DDLpt-online-schema-changeChange OperationRow(s) affectedIs table locked?Time (sec)Row(s) affectedIs table locked?Time (sec)Add Index0No3.76All rowsNo38.12Drop Index0No0.34All rowsNo36.04Add Column0No27.61All rowsNo37.21Rename Column0No0.06All rowsNo34.16Rename Column + change its data typeAll rowsYes30.21All rowsNo34.23Drop Column0No22.41All rowsNo31.57Change table ENGINEAll rowsYes25.30All rowsNo35.54Which method should be used?
Now the question is, which method should we use to perform alter table statements?
While pt-online-schema-change allows read and write operations to the table being altered, it still copies the tables data to the temporary table in the background which adds overhead on the MySQL server. So basically, we should use pt-online-schema-change only if the Online DDL will not work efficiently. In other words, if the Online DDL will require copying data to a temporary table (algorithm=copy) and the table will be locked for long time (lock=exclusive) then we should use pt-online-schema-change tool.