How to do troubleshooting in case of failure to log in to the RDS?

• Ping if the RDS domain name is accessible;
• Whether or not the account and password are mistaken;
• Whether or not the whitelist authorization is correct;
• Whether or not the number of connections is full;
• If the error prompt “the port is inaccessible (110, 111, and 104 error code) is returned, please submit the ticket.

How to restart instance?

If the occupancy of the RDS memory is overly high, or if mysql parameter is modified, you need to restart the instance. Log in to the Console, enter the Instance’s “Basic Information” page, click “Restart Instance” at the top right corner to restart the instance.

How to modify the RDS for MySQL’s system parameters?

Log in to the Console, enter the instance’s “Parameter Configuration” tab to edit and modify the parameters. You can enable some parameters immediately without restarting. But, you cannot enable some parameters until being restarted. See the description on the tab for details.

Are there requirements and influences in configuration upgrading/downgrading?

• Support to configuration upgrading/downgrading:

  Configuration change type	Prepaid	Postpaid	Memory	Disk	Instance Status
  Upgrading	At any time	At any time	Support	Support	Running
  Downgrading	Support after expiry	At any time	Support	Not supported.	Running

• Execution time: related to the instance’s data size, average data transmission speed: 100G/30 minutes
• Influence on services: It is related to the write conditions in the upgrading process, but a brief pause takes place. Thus, recommend you to enable this action in the low peak of traffic.

How to scale up instances?

• Create a read-only instance and expand read.
• Create a proxy instance, realize read/write splitting, load balancing, and connection pool.
• Create a distributed database, and expand write and single-computer capacity limit.

How to recover the database?

Note: The database recovery may lead to data overriding and failure in data recovery, and database services may be interrupted in recovery. Thus, Recommend you to operate with great care.

• Log in to the console, enter the instance’s “backup” tab, select the “Recover” link next to the backup awaiting recovery to start the recovery task. Recovery time length is contingent upon the backup file size.
• Recommend you to recover the database using the “Clone Instance” feature. See Clone Instance for details.

How to migrate data, for example, how to synchronize the self-built database onto the RDS?

Baidu AI Cloud provides DTS service, helping you realize data migration. See Data Migration for details.

How to conduct regular all-around checkup to the RDS’s resources status?

You can enable the "Cloud Advisor" service to obtain the test reports on security, availability, performance, and cost of cloud resources regularly. In the report, there are many RDS-related checkup items, for example, RDS-instance stop, RDS-slow SQL report form/diagnosis, RDS-whitelist access risk, RDS-disk full, RDS-idle instance, RDS-high usage rate, and RDS-multi-AZ. To learn about or activate the Cloud Advisor service, go to the Cloud Advisor homepage.

How to deal with the error information prompt “Too many connections in ...”?

This error information denotes the number of connections in the database is full. In this case, first locate the cause for a full number of connections: service growth or SQL blocking:

• Service growth: recommend you to upgrade the package or scale up the instances.
• SQL blocking: find the blocked SQL, control parallel connections, or optimize SQL.

How to deal with 100% or a sharp rise in CPU occupancy?

First, check if there are massive slow queries and find the slow query with the largest quantity for SQL optimization.

Why does database write-in account turn into a read-only account suddenly?

• Cause: The disk quota is full. Remove some write privileges to prevent the local disk from being fully occupied.
• Disposal measures: 1: delete useless data to release resources; 2: scale up the disk quota.
• Pro-active measure: add disk alarm monitoring.

How to calculate database usage?

Database usage consists of the Ibdata file +data file, excluding the binlog and general log.

Do the RDS’s binlog data take up the disk space? How many days are they saved by default?

binlog data take up the disk space, and binlog of 7 days is saved locally by default.

Why is there a delay for read-only instance data?

• Cause: Concurrent writing into the master database and serial synchronization with the slave database result in synchronization speed on the slave database being lower than the writing speed on the master database.
• Influence: After the read/write separation, you cannot query the updated data on the read-only instance.
• Pro-active measure: control the concurrent writing on the master database (TPS, not higher than 2000), and ensure the slave database’s configuration is not lower than the master database’s.

How to modify the RDS isolation level by default?

The default RDS isolation level is REPEATABLE-READ. You cannot modify the transaction isolation level by default or GLOBAL transaction isolation level.

Why are all migrated data turned into messy code when using the RDS to migrate the data?

Please check up the target RDS database and source the self-built database’s character set information. Usually, this problem results from forced trans-coding when different character sets bring about data migration. Solution: execute data migration after creating the target database with the same character set as the source database.

Why does the MySQL database table name become lowercase letters after the table gets imported into the RDS? How to deal with this?

It might take place when setting the “lower_case_table_names” (namely, case sensitive for table name) in the instance’s “Parameter Settings”. Modify this parameter and export it into the RDS again. The explanation of the parameter values is as follows:

Why does MySQL space fail to be released after deleting the data by the “delete” method? How to deal with this?

After the data in datasheets of MySQL are deleted using the “delete” command, the disk space cannot be directly released. There are data holes in the datasheet so that you can use some spaces for new data. If you need to release disk space immediately, you must complete table arrangement or table recreation.

1. Release space directly through the “sql” statement: Way 1: Execute OPTIMIZE TABLE 'table_name' to realize release of table space; Way 2: View the table’s storage engine and recreate the table. For example, InnoDB table executes ALTER TABLE 'table_name' ENGINE = InnoDB.
2. Release space via page console

1. Select the database management tool in the RDS instance.

2. Enter the management tool page
3. click the table for release of space
4. Click action
5. Click “De-fragment Table” or “Optimize Table”.

How to ensure there is no mess coded in MySQL character set?

Settings to the character set of database phrase is a consideration the user needs to take into account during database application.

You can execute the following command to learn about configurations of the character set:

mysql> show variables like '%character%';

When the client connects to the server, the client sends its desired character set to the mysql server. In turn, you can set the three values (namely, character_set_client, character_set_connection, and character_set_results) using this character set. Here, the client may assign the three values by executing “set names XX”.

1. How to ensure correct write-in.

   Ensure the consistency of the to-be-written data’s actual character set, character_set_client, and current field’s character set. In some cases, you only need to keep consistency between the first two character sets, and mysql transforms the client character set and field character set. In most cases, you can perform the normal conversion between utf8 and gbk. However, the conversion failure may result in the loss of characters sometimes. So, recommend you to ensure consistency in the three character sets.

2. How to ensure correct reading.

   Ensure the consistency of client character set and character set of the current field of the data on the premise of correct data write-in.

3. How to deal with messy code in reading?

   If there is still messy code on the premise of actions taken in “2’”, you can infer that the data’s actual storage is not consistent with the field’s character set. In other words, the problem exists at the time of write-in. In this case, you need to first check if the data’s actual storage character set is correct, and view the actual storage of the field with messy code by use of mysql hex() function. Taking Chinese as an example, one utf8 character (Chinese character) takes up three bytes, as follows:

   mysql> select hex('We');
   +---------------+
   | hex('We') |
   +---------------+
   | E68891E4BBAC  |
   +---------------+

   One gbk Chinese character takes up two bytes, as follows:

   mysql> select hex('We');
   +-------------+
   | hex('We') |
   +-------------+
   | CED2C3C7    |
   +-------------+
   1 row in set (0.00 sec)

   The actual conditions are relatively complicated, for example, mixed arrangements in English and Chinese.

   After confirming the data’s actual storage structure, export the data via hex(), and repair messy codes through unhex importing.

GTID Usage Limits

GTID (Global Transaction Identifier) is the unique identifier assigned to every transaction on Master, and it is of the uniqueness in one Master/Slave’s topology structure replica. GTID’s structure is as follows:

GTID = source_id:transaction_id, of which source_id is usually server_id, and the transaction_id is a serial number that increases progressively from 1. GTID’s most remarkable strength: in the GTID-based Master/Slave replication scenario, CHANGE MASTER TO may be “automatically” replicated without the need of designating the synchronization point (MASTER_LOG_FILE/MASTER_LOG_POS). However, GTID-based replication mode does not support the following scenarios:

• Updating to non-transaction engine table in the transaction

  Updating the transaction/non-transaction engine tables at the same time in a transaction may result in the assignment of multiple GTIDs to the transaction.

• CREATE TABLE … SELECT statement

  To statement-based replication, CREATE TABLE … SELECT is not secure. To row-based replication, you can record this statement into two events, i.e., creation of table and data import into the created table from the source table. When executing this statement in a transaction, some scenarios make the two events have identical transaction ID, resulting in skipping the “Import Data” process in the Slave.

• Temporary Table

  CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE does not support use in the GTID-based replication mode. Actions to the temporary table must be put beyond the transaction, with parameter autocommit=1.

How to deal with the error information prompt “The table 'XXXX' is full”?

When using the RDS, you might see the error information: The table 'XXXX' is full

1. Cause for the error:

When executing aggregation actions like group by, count(distinct), sorting actions like order by and distinct, union, group_concat, sub-queries, or multi-table association, MySQL might use the internal temporary table. First, use the temporary memory table. If the required memory exceeds the memory set for “tmp_table_size” and “max_heap_table_size”, it turns temporary memory table into the temporary disk table. In turn, you can save it under the directory marked with “tmpdir” variable. If the query involves fields of Blob or Text, MySQL skips the temporary memory table and directly accesses the temporary disk table. Since the available space of the physical disk is limited, it is impossible to scale up physical files in the temporary disk table in case of a lack of space. Thus, the error of this kind takes place. If the error of this kind takes place, view the “Disk Usage Rate” monitoring item under the “Monitoring”. Then, you can see the “Disk is full instantly”. “full” is relevant to the frequency of the monitoring and sampling data. The monitoring item cannot sample the “Instantly Full” case.

2. Countermeasures for this error

• Avoid using the field of Blob and Text in the query whenever possible.
• Optimize the query logic, and avoid overmuch operation to intermediate result sets. For example, optimize queries of this kind by reasonable use of the index, and avoid the use of the temporary table, etc.

3. How to check if the internal temporary table is used in the query.

   View the execution plan by use of explain. If there is “Using temporary” in the Extra part of the execution plan, it means the internal temporary table is used in the query.

How to deal with MySQL’s long transaction execution time?

Concept

MySQL Large Transaction refers to the transaction with relatively long execution time in MySQL. For example, the following transaction execution time is 1000s:

BEGIN;
SELECT sleep(1000);
COMMIT;

Influence

• Influence of a large transaction on MySQL: During the large transaction, MySQL performance decreases, likely leading to problems, such as the growth of the number of connections to MySQL, an increase of slow queries, the rise of lock waiting time, and overly high usage rate of CPU and memory, etc.
• Influence of large transactions on users: slower read/write access, error report on access timeout, and even “Not Available” risk.

Cause

• Program problems canse failure to submit the enabled transaction. So, this transaction consumes much time, or “Lock Table” generated from this transaction brings about lock waiting of other transactions. Thus, this transaction turns into a large transaction.
• DDL statement: DDL statement of the Big Table leads to long-time execution of DDL SQL, for example, adding a field to table of 100G leads to the generation of large transactions of at least 100 minutes;
• Low-efficiency SQL statement: The read/write statement influencing many rows, the multi-table query statement, and the massive data query statement performed not according to indexes are at low efficiency, and may cause a large transaction.

Processing

You should avoid a large transaction whenever possible. For example, you can enable the timely submission of the transaction, test if there are low-efficiency SQL and DDL statements offline before the transaction is put online, and operate in the low peak of services whenever possible.

If there is a large transaction, the disposal plan is as follows:

• Stop the session. That is to say, kill large transactions, search the corresponding transaction via show process list, and get the process ID (first column), for example, if id=202020, execute kill 202020.
• Enable automated submission of a transaction. To enable automated submission, execute set autocommit = 1; to disable automated submission, execute set autocommit = 0.
• Set the maximum transaction time. If the set transaction is executed for up to 100s, execute the following command: set global wait_timeout=100; set global interactive_timeout=100;

Causes and disposals of high MySQL CPU loading

High loading of MySQL CPU: it might result from high “rows_read” due to unreasonable SQL or lack of a proper index. Also, it might result from overly high QPS or lock contention due to hot spot data read/write.

CPU Utilization

Cause:

1. The application has a high load. Some tables in small data size have overly high read/write frequency and high concurrent volume (e.g., hot spot data), which may be prone to a high usage rate of CPU.
2. High cost in the query execution: QPS currently running in the data set is not high, but the query execution efficiency is low, massive data in the table needs to be scanned in execution. In other words, there is slow query SQL.

Solution:

1. Use the “show processlist” statement to search the SQL statement with a heavy load, and optimize this statement, for example, create a proper index.
2. Open slow query configuration, find SQL statement that exerts influence on performance, and then find the corresponding optimization plan using “EXPLAIN”.
3. In case of high concurrency, check if you can add a cache (like memcache/redis) to alleviate the pressure of “db”.
4. Modify the dataset architecture, add the slave database appropriately to improve the frequent read action to hot spot data.
5. Check if the tmp_table_size and max_heap_table_size are relatively small. If permitted, scale up the table appropriately. In MySQL’s configuration file, tmp_table_size is 32M by default. If one temporary table exceeds this size, MySQL generates an error in the form of The table tbl_name is full. If the service needs a lot of advanced “group by” query, you may increase the tmp_table_size value.
6. As to fields used in conditional statements like WHERE, JOIN, MAX(), MIN(), and ORDER By, create corresponding “INDEX”. You can use the index for quickly locating the row using a given value in one column. Without the index, MySQL has to begin with the first record and read through the entire table until it finds relevant rows. A larger table means more time consumed. If the table has one index for the column queried, MySQL can quickly go to one location to search among the data files without the need to get through all data. If one table has 1000 rows, such a move is at least 100 times faster than the sequential reading. All MySQL indexes (PRIMARY, UNIQUE, and INDEX) are saved in B-tree.
7. Optimize files and indexes regularly (once every week or once every month ) as per official suggestions from MYSQL.

Preparatory work against high usage rate of CPU:

1. Use pressure measuring tool in advance for pressure measuring to new features and modules;
2. During the application design and development, observe the RDS for MySQL optimization principle whenever possible, reduce the query’s logic IO, and improve the scalability.

The RDS for MySQL Metadata lock’s generation and disposal

1. Meaning and usages of Metadata lock

In Version 5.5, MySQL introduces Metadata lock, which is used in maintaining the consistency of table structure in the concurrent environment, and Metadata lock has been in use to this day since Version 5.5.

Metadata lock is the table structure’s mutex lock. Only after getting Metadata lock can the transaction modify (alter)or delete(drop) the table, etc. If there is a yet-to-be-submitted transaction on the current table, this transaction holds the Metadata lock. And, the transaction for modifying or deleting table structure cannot get Metadata lock until submission of the yet-to-be-submitted transaction.

Metadata lock resolves two problems:

• Transaction isolation: When the RDS for MySQL stays at “Repeatable read” isolation level, if Transaction A conducts two queries to the same table and Transaction B is to modify the table structure, Metadata lock ensures the consistency of Transaction A’s two query results.
• Master-slave synchronization: Before the introduction of Metadata lock, Transaction A inserts data into a table, and Transaction B is to delete the table. If Transaction B is submitted first, the binary log first records the deletion action and then the insertion action. If the slave database executes actions in the relay log, there is a mistake.

2. Frequent operations related to Metadata lock

The following operations cannot be executed until Metadata lock is got:

• Modify table (alter table)
• Delete table (drop table)
• Add /delete index (alter table add/drop index, create/drop index)
• Maintain table (optimize/repair table)
• Get table-level write lock (lock table tblname write)

See the figure below. You may see the session awaiting “Metadata lock” via “show processlist;” command.

If one transaction holds the Metadata lock, this table read/write is blocked, and the subsequent read/write actions to this table have to wait for the lock. Thus, improper use might bring about a huge loss to the zRDS user.

The following introduces frequent problems and solutions on “Waiting for table Metadata lock” in the use of the RDS.

3. Frequent problems when a transaction on the RDS stays in “ Waiting for table Metadata lock“.

• Long-time query exists on the table;
• Yet-to-be-submitted or rollback transactions exist on the table.
• There is a failed query transaction on the table, and this transaction has yet to be submitted or is rolled back.

4. Solution

1. View long-time queries using “show processlist;” command.

Then, end this query session using the “kill” command.

2. In MySQL, query yet-to-be-submitted/rollback transaction holding this table metadata lock by use of the following statement.

    select i.trx_mysql_thread_id metadata_lock_thread_id from information_schema.innodb_trx i, (select id, time from information_schema.processlist where time = (select max(time) from information_schema.processlist where state = 'Waiting for table metadata lock' and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat'))) p where timestampdiff(second, i.trx_started, now()) > p.time and i.trx_mysql_thread_id  not in (connection_id(),p.id);

Paste and run the aforesaid SQL statement directly and get the following results:

Execute “kill” statement to close this session

3. The third case may be deemed as a special case of the first case. However, since the failed query is returned before the execution, there is no ongoing query. Thus, relevant information cannot be found in aforesaid “show processlist;” and “information_schema.innodb_trx” table.

At this time, the following SQL statement may be used for the query of session IDs holding metadata lock.

    select p1.id metadata_lock_thread_id from information_schema.processlist p1, (select id, time from information_schema.processlist where time = (select max(time) from information_schema.processlist where state = 'Waiting for table metadata lock' and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat', 'trunc'))) p2 where p1.time >= p2.time and p1.command in ('Sleep' , 'Query') and p1.id not in (connection_id() , p2.id);

See figure below for execution results:

Execute “kill” statement to close this session

5. Precautions for preventing Metadata lock from influencing services:

1. At the low peak of services, execute the operation in Section 2, for example, modify/delete the table, and create/delete the index.
2. “Explicit open session” is automatically submitted. After the database connection to the RDS is established, execute “set autocommit=1;” or “set autocommit=on”;
3. Set the SQL statement’s lock wait timeout parameter and avoid the influence of long-time waiting for metadata lock on other service queries on the table. For example, you may set the maximum time of “metadata lock wait” as 30s by use of “set lock_wait_timeout=30;” command.

4. End the transaction in a long-time running by use of the event. For example, the following SQL statement may end the transaction with the execution time of more than 30 minutes.

    create event my_long_running_trx_monitor
    on schedule every 30 minute
    starts '2016-01-01 00:00:00'
    on completion preserve enable do
    begin
      declare v_sql varchar(500);
      declare no_more_long_running_trx integer default 0;
      declare c_tid cursor for
        select concat ('kill ',trx_mysql_thread_id,';')
        from information_schema.innodb_trx
        where timestampdiff(minute,trx_started,now()) >= 60;
      declare continue handler for not found
        set no_more_long_running_trx=1;
      open c_tid;
      repeat
        fetch c_tid into v_sql;
    set @v_sql=v_sql;
    prepare stmt from @v_sql;
    execute stmt;
    deallocate prepare stmt;
      until no_more_long_running_trx end repeat;
      close c_tid;
    end;

Analysis of the RDS MySQL InnoDB lock

Background

Isolation of transactions in a relational database is realized via Lock. In general, Lock falls into two types, namely, read lock (also called shared lock) and write lock (also called mutex lock). Read locks are compatible with one another, but write lock is not compatible with any lock type, That is to say, after one transaction gets one database object’s read lock, another transaction may get this object with a read lock, rather than with write lock. In case of lock conflict, the transaction, which sends a lock request later, usually enters the “Lock Wait” state. Once the wait duration reaches the set timeout value, the current transaction is rolled back due to the waiting timeout.

Locks in MySQL InnoDB engine conform to the aforesaid rules. Out of performance optimization and concurrency improvement, lock handling is optimized. On the one hand, maximize the transaction concurrency by realizing the rowlock. On the other hand, make read lock and write lock compatible with one another by use of MVCC (Multiple Version Concurrent Control), and in turn, reduce lock waiting between transactions.

Lock timeout configuration in InnoDB

In InnoDB, transaction lock wait timeout is designated via the system configuration parameter “innodb_lock_wait_timeout”. To view this parameter value, you may complete the settings via the “Parameter Configuration” page of the RDS’s control management interface, or execute the following commands via MySQL client:

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
innodb_lock_wait_timeout
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_lock_wait_timeout = 100;
Query OK, 0 rows affected (0.00 sec)

mysql> set session innodb_lock_wait_timeout = 100;
Query OK, 0 rows affected (0.00 sec)

This parameter’s value is 50(unit: s) by default. In other words, when the transaction is waiting for a write lock, the maximum waiting time is 50s by default. In case of failure in getting the lock after 50s, this transaction is rolled back, and the application gets the following error message: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction Generally, the application’s disposal to this error is “Retry transaction”. Normally, it’s difficult to reach the lock timeout value by default, so there should be no massive lock timeouts or retrials. If execution time does exceed such timeout value in the application and there is a transaction with write-in action, recommend you to optimize the application, shorten the transaction execution time as much as possible, and avoid long-time possession of the write lock.

Processing to lock timeout

If there are massive continued waits and timeouts in the system, you need to locate the transaction in long-time possession of write lock and take into account ending to its execution. To list out lock wait condition in current InnoDB, you may execute the following SQL statements:

select l.* from
    (select 'Blocker' role, p.id, p.user,
            left(p.host, locate(':', p.host) - 1) host,
            tx.trx_id, tx.trx_state, tx.trx_started,
            timestampdiff(second, tx.trx_started, now()) duration,
            lo.lock_mode, lo.lock_type, lo.lock_table, lo.lock_index,
            tx.trx_query, lw.requested_lock_id Blockee_id,
            lw.requesting_trx_id Blockee_trx
      from information_schema.innodb_trx tx,
           information_schema.innodb_lock_waits lw,
           information_schema.innodb_locks lo,
           information_schema.processlist p
      where lw.blocking_trx_id = tx.trx_id
        and p.id = tx.trx_mysql_thread_id
        and lo.lock_id = lw.blocking_lock_id
      union
      select 'Blockee' role, p.id, p.user,
          left(p.host, locate(':', p.host) - 1) host,
          tx.trx_id, tx.trx_state, tx.trx_started,
          timestampdiff(second, tx.trx_started,
          now()) duration, lo.lock_mode, lo.lock_type,
          lo.lock_table, lo.lock_index, tx.trx_query,
          null, null
      from information_schema.innodb_trx tx,
           information_schema.innodb_lock_waits lw,
           information_schema.innodb_locks lo,
           information_schema.processlist p
      where lw.requesting_trx_id = tx.trx_id
        and p.id = tx.trx_mysql_thread_id
        and lo.lock_id = lw.requested_lock_id)
l order by role desc, trx_state desc;

The output of this SQL’s execution result is as follows (for your reference):

Via this output, you may end the transaction in the role of Blocker, release the mutex lock it occupies. Then, transactions in the role of Blockee get the desired mutex lock. To end the designated transaction, you may use the “kill connection” command in MySQL client.

mysql> kill connection 2;
Query OK, 0 rows affected (0.00 sec)

If there are still massive transactions with lock wait timeout after the transaction in long-time possession of mutex lock is ended, you need to optimize the processing logic in applications, reduce the time for transaction’s possession of mutex lock. Also, you may optimize the database to increase the SQL execution speed. If this problem is still there, contact online technical support.

Analysis of MySQL MyISAM engine lock

Background

MyISAM is one of the most time-honored storage engines in MySQL. Since the engine has a long history, it has a lot of inborn defects and weaknesses. Of which, the most remarkable weakness of MyISAM, relative to InnoDB storage engine, is:

• Not support to the transaction
• Only support row lock.

In MySQL of the current version, all tables should adopt InnoDB rather than MyISAM, unless otherwise stipulated. However, in view of forwarding compatibility, there are still some user tables that take MyISAM as the storage engine.

Since MyISAM storage engine only supports table lock, any action to data in the table leads to locking to the entire table. In addition, since MyISAM storage engine has no MVCC-like technology in InnoDB, read lock and write lock on MyISAM are not compatible with one another.

Lock timeout settings in MyIsam

Different from InnoDB, MyISAM engine has no corresponding lock_wait_timeout configuration parameter. That is to say, a transaction might wait without any time limit when waiting for the lock on one MyISAM table. Accordingly, the application is hung at this moment. Therefore, in an application using MyISAM table, wait timeout mechanism should be set to prevent the application from being hung without any time limit.

Disposal of lock wait in MyISAM

If the application using MyISAM table is hung for waiting for data return, it might result from the database access in the lock wait state. To check if the transaction execution stays in the lock wait state, you may run “show processlist” command via MySQL client, and the corresponding output is as follows:

Since the MyISAM engine does not support the transaction, a table lock is released as soon as SQL statement execution is completed. Thus, it’s difficult for table lock to be occupied for a long time in general. Here, long-time possession of MyISAM table lock is simulated via a given statement with SLEEP. From the case, we can see that even the read lock is incompatible with the write lock, and the session (connection ID: 5) is waiting for the table lock on the table named myisam_tab.

If there are massive transactions awaiting table lock in MySQL, you may release this lock by ending the transaction in possession of the table lock. To end the designated transaction, you may use the “kill connection” command in MySQL client.

mysql> kill connection 2;
Query OK, 0 rows affected (0.00 sec)

If there are still massive transactions awaiting table lock after the transaction in long-time possession of table lock is ended, you need to optimize the processing logic in applications, reduce the time for transaction’s possession of table lock. Also, you may optimize the database to increase the SQL execution speed. If this problem is still there, contact online technical support.

How to deal with read-only instance’s synchronous delay?

What is the synchronous delay?

MySQL’s synchronization mechanism is realized via asynchronous mode, in which, IO thread is responsible for receiving “relay log” generated from binlog of master database dump to slave database, and then “SQL thread” is responsible for parsing relay log and replaying it on the slave to compete for the synchronization. Such a mechanism brings about a delay in the synchronization between the master database and the slave database.

Generally, we quantify the delay extent via “seconds_behind_master” value from “show slave status” command and according to states of “io thread” and “sql thread”.

Causes of and countermeasures for synchronization delay

1. A relatively large event exists on the master database

   Relatively large transaction and ddi operation are executed on the master database, but such transaction’s execution time on the slave database is the same, which in turn lead to synchronization delay.

   Countermeasures: optimize services, reduce the transaction scale, and use online ddl.

2. Master database’s high-concurrency write-in

   The slave database is synchronized via single-thread “sql thread”. If there is high-concurrency write-in on the master database, single-thread synchronization performance usually encounters bottleneck, and you may make a judgment by viewing the master database’s concurrent write-in.

   Countermeasures: carry out cluster splitting and optimize the write-in, or take into account multi-thread synchronization.

3. Slave database (or slave)performance bottleneck

   If the slave load is overly high, it easily leads to high pressure on IO, and the slave delay frequently takes place at this moment.

   Countermeasures: expand the slave, share the read traffic, and reduce the slave load.

4. myisam table

   As to datasheet of myisam engine, a relatively long query on the slave might bring about this table’s synchronous blocking, and in turn, lead to master-slave delay.

   Countermeasures: you are strongly advised to replace myisam with innodb engine.

5. Network jitter

   Network jitter between slave and master might bring about IO thread’s failure in frequent pulling to binlog, which accordingly results in synchronization delay. To this kind of delay, “seconds_behind_master” is always “0”.

   Countermeasures: restart the slave’s IO thread and add a heartbeat mechanism to detect this problem.

Synchronization delay troubleshooting flow

When the delay occurs

1. Check if the master write-in pressure rises sharply;
2. Check the slave’s load and IO pressure;
3. Check if there were large transaction and “ddi” operation on master;
4. Check the write-in qps differences between master and slave.

How to set relevant timeout parameters of MySQL?

MySQL’s timeout parameters are set mainly for the timeout limits in the cases like control/connection authentication and conflict of transaction locks, and they may be moderately adjusted according to different network or service environments, for meeting the service needs. Content below introduces these parameters:

connect_timeout

This parameter controls the wait timeout value for privilege validation and interaction after the connection between the client and TCP of “mysql” is established and before the data access.

     mysql> show variables like '%timeout%';
    +-----------------------------+----------+
    | Variable_name               | Value    |
    +-----------------------------+----------+
    | connect_timeout             | 5        |

For example, you can perform simple tests, time telnet mysqlip port.

Connected to testserver1 (192.168.1.1).
Escape character is '^]'.
xxx_passwordConnection closed by foreign host.

real    0m5.009s
user    0m0.001s
sys     0m0.001s

In a less stable environment where the network quality is not very good, you can adjust this parameter to a larger value.
The modification method is set global connect_timeout = 10;

innodb_flush_log_at_timeout

Time for flushing log is 1s by default.

To ensure high-efficiency flushing to online logs, InnoDB adopts a memory log buffer, and the log is saved in “log_buffer” and flushed from log buffer to Linux file system’s buffer.

This parameter is used together with “innodb_flush_log_at_trx_commit” for control over purging to the two caches. “Innodb_flush_log_at_trx_commit=1” indicates that when each transaction is submitted, the log buffer is flushed into the file system (OS buffer), and the file system’s “Flush” action of the file system is invoked for flushing the cache onto the disk.

“Innodb_flush_log_at_trx_commit=0” indicates that log buffer is flushed into the file system (OS buffer) every other second, and the “Flush” action of the file system is invoked for flushing cache onto the disk. That is to say, all logs over the last one second are saved in the log buffer (namely, memory). In case of downtime, transaction data over the last one second might incur a loss.

“Innodb_flush_log_at_trx_commit=2” indicates when each transaction is submitted, the log buffer is flushed into the file system (OS buffer), but the “Flush” action of the file system (OS buffer) is invoked for flushing cache onto the disk every other second.

innodb_lock_wait_timeout

Means the transaction lock wait timeout or Query timeout while the lock is waiting. Different from deadlock, InnoDB rolls back the transaction of low cost as soon as it detects a deadlock. In fact, the lock waits for the lock resources one transaction needs, but another transaction holds, and the query of the request lock is bound to be rolled back. Since this parameter is read-only, it is not enabled until being restarted.

For example:

• T1 Table id1 is the unique index key,
• In Transaction 1, sql update t1 set t1.id2=100 where id1=10; yet to be submitted;
• In Transaction 2, sql update t1 set t1.id2=200 where id1=10; this SQL waits for the release of row lock;
• This parameter’s default value is 50. If Transaction has not been submitted or rolled back after Transaction 2 waits for 50s, Transaction 2 SQL does not wait for lock any longer but quit executing SQL.

innodb_rollback_on_timeout

This parameter’s default value is OFF. If the transaction incurs lock timeout, it rolls back the action the previous statement executes. If “ON” is set, the entire transaction is rolled back. Since this parameter is read-only, it is not enabled until being restarted;

Example: innodb_rollback_on_timeout = 1

session1

    mysql> create table t1( id1 int ,id2 varchar(100));
    mysql> insert into t1 values(1,'1row');
    mysql> create index idx_t1 on t1(id1);
    mysql> insert into t1 values(2,'2row');
    mysql> select * from t1;
    +------+------+
    | id1  | id2  |
    +------+------+
    |    1 | 1row |
    |    2 | 2row |
    +------+------+
    2 rows in set (0.00 sec)

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    mysql> update t1 set id2='22row' where id1=2;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    mysql> select * from t1;
    +------+-------+
    | id1  | id2   |
    +------+-------+
    |    1 | 1row  |
    |    2 | 22row |
    +------+-------+
    2 rows in set (0.00 sec)
    mysql>

    **session2**
    mysql> use doctest;
    Database changed
    mysql> begin ;
    Query OK, 0 rows affected (0.00 sec)
    mysql> select * from t1;
    +------+------+
    | id1  | id2  |
    +------+------+
    |    1 | 1row |
    |    2 | 2row |
    +------+------+
    2 rows in set (0.00 sec)

    mysql> update t1 set id2='11row' where id1=1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    mysql> select * from t1;
    +------+-------+
    | id1  | id2   |
    +------+-------+
    |    1 | 11row |
    |    2 | 2row  |
    +------+-------+
    2 rows in set (0.00 sec)

    mysql>  update t1 set id2='222row' where id1=2;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    mysql> select * from t1;
    +------+------+
    | id1  | id2  |
    +------+------+
    |    1 | 1row |
    |    2 | 2row |
    +------+------+
    2 rows in set (0.00 sec)
    From the above results, it rolls back the entire transaction of session2;

interactive_timeout/wait_timeout

“Interactive_timeout/wait_timeout” is designed for the timeout in case of interactive/non-interactive connection. If such timeout is exceeded, the connection is released, for example, connection to the database via the “mysql” client belongs to interactive connection, and the SQL in execution does not calculate this timeout.

Example: mysql> set global interactive_timeout=2; Query OK, 0 rows affected (0.00 sec) wait for more than 2s ... mysql> select sleep(1); ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 73 Current database: _ NONE _

net_read_timeout /net_write_timeout

“Mysql” server side waits for the maximum time limit for data read/write on the client side. In the case of the relatively poor network environment, adjust high this parameter to reduce the probability of timeout.

Example: set global net_read_timeout =60;

How to use MySQL BINLOG?

Overview

MySQL’s binlog is used in recording the database change actions or recording actions that may result in the change (binlog of row-based format has no latter feature). Also, it records other information like action execution time, etc.

“Binlog” is used for two important purposes, as detailed below:

• Used in master-slave replication: since “binlog” records all change actions (called events), the master sends “binlog” to the slave, which replays events in “binlog” to realize the data change that keeps consistent with the master.
• Used in data recovery: if there is one full backup, replay events behind the backup points to realize database recovery at any time point.

The enabled “binlog” brings down MySQL performance, but features (like master-slave replication and data recovery) arising therefrom are of more values relative to the performance decline.

Configurations

1. Actions related to “binlog”

• When starting MySQL, set -log-bin[=base_name] parameter, enable binlog.
• If MySQL enables and executes the “flush logs” statement or the current “binlog” file size exceeds “max_binlog_size” value, it leads to the generation of a new “binlog” file. Despite this, one transaction does not span over multiple binlog files, so the size of a single “binlog” file might exceed “max_binlog_size”.

• When the client has super privilege, you may disable “binlog” at the current connection via the following statement (no influence on other connections).

  mysql> SET sql_log_bin=0;

• The slave does not record the events in respect of slave-master synchronization by default unless you set “--log-slave-updates” parameter.

2. “Binlog” check mechanism

• When recording the event, MySQL records the event length simultaneously. When replaying “binlog”, the length is used by default to check if the event is correctly written.
• By setting “binlog_checksum”, MySQL writes the event checksum into binlog. If “master_verify_checksum” is set for the master, the master checks the event correctness with checksum in the replay of binlog. Likewise, if “slave_sql_verify_checksum” is set for the slave, I/O thread conducts check with checksum in the replay of the event.

3. binlog format

• statement-based: in binlog, events record statements. In the configuration file, set “--binlog-format=STATEMENT” or execute the statement.

  mysql> SET GLOBAL/SESSION binlog_format = 'STATEMENT';

• row-based: in “binlog”, events record changes in every row of the table. In the configuration file, set “--binlog-format=ROW” or execute the following statements.

  mysql> SET GLOBAL/SESSION binlog_format = 'ROW';

• Mixed-base: “statement-based” format is used by default, and it is automatically converted into a row-based format in case of an uncertain statement. In the configuration file, set “--binlog-format=MIXED” or execute the following statements:

  mysql> SET GLOBAL/SESSION binlog_format = 'MIXED';

• If the master’s binlog format is set as “row-based”, the slave’s “binlog” format should be set as “row-based” accordingly, or the master-slave synchronization is interrupted: Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'.
• As to actions (GRANT, REVOKE, SET PASSWORD, RENAME USER) for indirect modification of table data in “mysql” database and the table structure change statements (CREATE, ALTER, DROP), even if “binlog” format is set as “row-based”, the corresponding events still record statements.
• If the table’s storage engine is InnoDB and the transaction isolation level is “READ COMMITTED/READ UNCOMMITTED”, or the table contains binary type of data, “binlog” format can be only set as “row-base”.

4. Delete binlog

• Delete all binlogs

  mysql> RESET MASTER;

• Delete some binlogs

  mysql> PURGE BINARY LOGS TO 'mysql-bin.010'; mysql> PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

5. Binlog cache

• After one statement/transaction is completed, and before the release of the lock or submission, all change actions involved are written into “binlog”; changes in the non-transaction table are written into “binlog” as soon as the statement execution ends. As to changes in the transaction table, corresponding actions are saved in the cache first and then written into “binlog” file till MySQL receives “commit”.
• Since it is impossible to roll back changes in the non-transaction table, if one transaction involves the modification to the non-transaction table and it is rolled back, the entire transaction is recorded into “binlog”(including ROLLBACK statement).
• After one thread enables the transaction, a cache of “binlog_cache_size” is allocated to the current thread. If the change statement in the transaction exceeds the cache size, the thread saves these statements using a temporary file. Refer to state variable “Binlog_cache_use” and “Binlog_cache_disk_use” to adjust the “binlog_cache_size”, avoiding the use of temporary rile.
• As per the parameter “sync_binlog=N”, “binlog” is synchronized to disk every N submissions of transactions (if N=0, the operating system controls “binlog” synchronization); the safest value is 1, or “N-1” binlogs of submitted transactions might be lost every time when the operating system or equipment breaks down.

6. Binlog and InnoDB failure recovery

• As to InnoDB engine’s table, MySQL, after receiving “commit” statement, first writes transactions in “prepared” status into binlog in sequence, then synchronizes “binlog” onto the disk, and finally submit these transactions at the storage engine level. If MySQL breaks down between the latter two actions, “prepared” transaction is rolled back at the time of restarting, but change statements in the transaction are left over in the “binlog” file.
• To overcome the aforesaid problems, you need to enable “--innodb_support_xa” to support XA transaction: when MySQL is restarted, first roll back transactions beyond “prepared” status, then scan the last one binlog file, collect the transaction xid, and calculate the last valid location in binlog file. MySQL informs InnoDB of submitting those transactions that have been synchronized into “binlog”, and clears the events behind the last valid location in “binlog” file.
• After the master is restarted, if the error log contains error information “The binary log file_name is shorter than its expected size”, it implies that the “binlog” lacks at least one transaction that has been successfully submitted, which likely brings about the slave’s failure in synchronizing the corresponding events. To ensure the master-slave data consistency, you need to use the master’s updated data snapshot for redoing data in the slave.

MySQL binlog tool

MySQL’s binlog is saved in binary form. To display the text form of “binlog”, you need to use “mysqlbinlog” tool. As to “binlog” of “statement-based” format, the output contains SQL statement. As to “binlog” of “row-based” format, the output contains changes at every row (-v parameter needs to be added).

1. Usage of command

   shell> mysqlbinlog binlog.0000001

2. Result output

   # at 106
   #171018 19:14:37 server id 1672120943  end_log_pos 182  Query   thread_id=64    exec_time=0     error_code=0

Time and “server id” in the aforesaid event are transferred from the master to the slave, and “end_log_pos” denotes the start position of the next event (used to check if the event is correctly written), “exec_time” denotes the statement execution time (as to the slave, this time equals to end time of this statement in the slave minus start time of this statement in the master, reflecting the master-slave delay), “error_code” indicates the error number in event execution (0 denotes no error).

“Mysqlbinlog” output, also taken as “mysql” input, is used in re-executing statements in binlog, and realizing database recovery at any time point.

shell> mysqlbinlog binlog.0000001 | mysql -u root -p

3. Important parameters

• “--database=db_name, -d db_name”: When the “binlog” format is “statement-based”, “mysqlbinlog” only outputs items of the “db_name” database by default (as to statements for modifying other database data, if the database by default is db_name, they are outputted as well. Likewise, if the database by default is not db_name, modification to data in db_name database does not lead to output). When the “binlog” format is “row-based”, “mysqlbinlog” only outputs data items of the modified “db_name” database.
• --exclude-gtids=gtid_set: not output “gtid_set items” in gtid.
• --include-gtids=gtid_set: only output “gtid_set items” in gtid.
• --local-load=dir_name, -l dir_name: directory for storage of temporary files generated by designated “LOAD DATA INFILE”.
• --result-file=name, -r name: designated file for storage of output results.
• --short-form, -s: only output statements in binlog, exclusive of additional information, and “row-based” events.
• --skip-gtids[=(truefalse)
• --start-datetime=datetime: read “binlog” from the first event with timestamp equal to/greater than “datetime”.
• --start-position=N, -j N: read “binlog” from the first event with the offset equal to/greater than N.
• --stop-datetime=datetime: stop reading “binlog” when the first event’s timestamp is equal to/greater than datetime.
• --stop-position=N: stop reading “binlog” when the first event with offset greater than/equal to N.
• --verbose, -v: refactor “row-based” events, and display its content in the form of SQL comments.

Best practice

1. Under all conditions, “binlog” should be enabled, even if the enabling brings down the performance. In the meantime, you need to set “--log-slave-updates” parameter for the slave whenever possible, to ensure the delayed slave makes up the data from other slaves in case of the master fault.
2. You are advised to use a “row-based” binlog whenever possible to ensure the master-slave data consistency. In some scenarios, “row-base” binlog helps improve the slave’s event replay efficiency. Before conversion from “binlog” format to “row-based” format, ensure that all tables have primary key (for quickly locating records in the table), and single statement/transaction does not update records in massive size (or a large number of events are generated, which influences SQL execution and synchronization efficiency).
3. When “binlog” is parsed using “mysqlbinlog” tool, “TMPDIR” environment variable may be used to designate the directory for the storage of temporary files, avoiding full occupancy of the temporary directory.
4. If “binlog” contains a “LOAD DATA INFILE” statement, “mysqlbinlog” first copies the data into a temporary file and then rewrites them into “LOAD DATA LOCAL INFILE” statement pointing to a temporary file. To avoid full occupancy of a temporary directory, you may designate the directory for the storage of temporary files using “--local-load” parameter. Since there is no automated deletion feature for generated temporary files, the files need to be deleted manually.
5. Via such parameters as “start-datetime”, “stop-datetime”, and “start-position” and “stop-position” and by use of full backup, you may realize data recovery at any time point.

Percona Toolkit

Introduction

Percona Toolkit is a toolset for maintaining MySQL database services, and it provides a lot of features, for example, “pt-update” is used in comparing the difference of SQL executions under different MySQL versions; “pt-mysql-summary” may be compatible with MySQL server to generate a piece of exhaustive configuration data and status information; “pt-online-schame-change” may support online change to table structure, etc.

Operational principle of “Pt-online-schame-change”

Use Limits

1. The modified table must have a primary key.
2. The modified table cannot be targeted at the “insert/update/delete” trigger.
3. If the modified table has a foreign key, you need to use “--alter-foreign-keys-method” option.

Operational principle

Provided that the table name is “tbl_name”, “clone” table name is “_tbl_name_new”

1. “check”, namely, conduct the check according to the use limits and the table status.
2. Create a table. Create a table according to the original table definition, and “alter” the new table using the statement “alter _tbl_name_new” (current new table has conformed to the definition of the modified table).
3. Create trigger. Based on the original table, create one “after” trigger respectively for insert/update/delete. The trigger is used in synchronizing actions of the original table to the new table on the premise that “insert/update/delete” exists.
4. Import data. Import the data of the original table into a new table. Import the data through “insert low_priority ignore into _tbl_new (…) select (…) from tbl_old lock in share mode”.
5. Rename table. rename table tbl_name to _tbl_name_old, _tbl_name_new to tbl_name;
6. Clean up environment Clean up “_tbl_name_old” table and its trigger.

How to deal with the prompt “too many connections”?

Concept

When the application side connects to MySQL, if there is prompt “too many connections”, it implies that current mysql instance’s connections exceed the maximum connections (max_connections), and the service has begun to generate traffic rejection.

“Maximum connections” usually is due to two causes: Too many idle connections or too many active connections.

Too many idle connections

It may waste the “mysql” connection resources.

Cause:

• As to “long connection” mode (like Java application), the application side provides the connection pool. The number of initial connections of the connection pool is set overly high (or each instance’s number of connections is not high, but multi-instance deployment is adopted). After the application is started, multiple idle connections to the “mysql” instance are established.
• As to “short connection” mode (like PHP application), there are a large number of idle connections, implying that there is no explicit “Disable Connection” after the application completes the query execution. The users should check if the application proactively and explicitly disables the connection to the “mysql” instance by invoking the “Disable Connection” command after the page query ends.

Solution:

• End current idle session via the “Kill” command.
• Modify the configuration on the service side, enable the “Connection Reuse in Connection Pool” feature (or adjust low the connection pool’s initial connections). For detailed settings, see the configuration file of the framework connection tool used on the service side.
• Modify “service-side program”, and ensure timely disabling of database connections no longer in use.
• Parameter optimization: according to service characteristics, adjust timeout parameter (wait_timeout, interactive_timeout) moderately, and set it as the minimum value. You may proactively release idle connections via “mysql” instance to reduce the number of idle connections. The specific modification method is as follows:

Non-interactive connection idle time (wait_timeout)

Interactive mode connection idle time (interactive_timeout)

Suggestions and Instructions

• If the connections to MySQL instance are full, it’s impossible to connect to the instance via the “mysql” client. As to “Long connection” mode, you are strongly advised to make maximum connections of the connection pool less than the upper limit of the connections in an instance. In case the number of connections rises sharply, you may log in via “mysql” client, and then “kill” idle connections manually. You are advised to first modify “wait_timeout” parameter as a relatively small value on the console, making “mysql” instance actively kill connections with idle time exceeding threshold.
• Generally, the non-interactive mode is adopted for the connection from applications to “mysql” instance (Of course, the specific mode is contingent upon the application’s connection configuration, for example, PHP, through “MYSQL_CLIENT_INTERACTIVE” constant, enables the connection of interactive mode for “mysql_connect()”).
• As to modification to “wait_timeout” and “interactive_timeout” parameters, existing sessions keep the settings before modification. After modification, newly created sessions adopt new parameter settings.

Too many active connections

Relatively high consumption of system resources usually has something to do with a sharp rise in the traffic, SQL performance, concurrency, and lock.

Cause:

• Lock wait (including InnoDB lock wait, MyISAM table lock wait, and table metadata lock wait)
• CPU Utilization
• CPU Utilization

Solution:

• Lock wait processing: find the query that exists for a long time and has yet to end, and kill it.
• High usage rate of CPU: locate the cause for optimization, with optimized content including CPU capacity expansion, SQL optimization, reduction of request frequency on the service side, and logic optimization, etc.
• High usage rate of IOPS: analyze the causes (e.g., small memory and SQL optimization, etc.) for high usage rate of IOPS. You are advised to expand the slave and optimize SQL(adding index), etc.

How to set and use MySQL “Query Cache”?

Overview

MySQL’s “Query Cache” is used for caching of “SELECT” statement text and corresponding query result. If the same query statement is received later, there is no need to parse and execute this statement anew, and MySQL directly returns the caching results to the client. ‘Query Cache” does not return expired data. If the table is modified, all caches in relation to this table are deleted from “Query Cache”. “ “Query Cache”, abandoned in MySQL5.7.20 and deleted in MySQL 8.0.

Operational principle

When receiving the query statement, MySQL first compares the query statement with a statement in Query Cache byte by byte, thus only when the statement is fully consistent with a given statement in Query Cache, can it be cached (including uppercase/lowercase letters and blank characters, etc.) In addition, if the database, protocol version, and character set used in query differ, it is deemed as a different statement. Also, subqueries and query statements in the storage process, trigger, and event are not cached.

If the following statements run, it might lead to disabling of caches of relevant tables, and the statements include INSERT, UPDATE, DELETE, TRUNCATE TABLE, ALTER TABLE, DROP TABLE, and DROP DATABASE.

In one of the following situations, it’s impossible to cache the query.

• Query involves some system functions: UUID(), NOW(), ...
• Query involves user-defined function (UDFs) or storage process
• Query involves user variable or locally saved program variable
• Query involves tables in “mysql”, “information_schema”, and “performance_schema” database.
• Query involves some partitioned tables
• Use of temporary table in a query
• No table used in a query
• Warnings from query

• Query modes:

  SELECT ... LOCK IN SHARE MODE SELECT ... FOR UPDATE SELECT ... INTO OUTFILE ... SELECT ... INTO DUMPFILE ... SELECT * FROM ... WHERE autoincrement_col IS NULL

Configurations

1. Check if "Query Cache" is available.

• If "have_query_cache" variable is "YES", it means the "Query Cache" feature is available.

  mysql> SHOW VARIABLES LIKE 'have_query_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+

2. System variables related to "Query Cache"

• "query_cache_size": used in designating the "Query Cache" size; When "query_cache_size" is set as non-zero value, at least 40KB is required for distribution of the data structure. If this value is set overly small, the following warning is given:

  mysql> SET GLOBAL query_cache_size = 40000; Query OK, 0 rows affected, 1 warning (0.00 sec)

  mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Warning Code: 1282 Message: Query cache failed to set size 39936; new query cache size is 0

  mysql> SET GLOBAL query_cache_size = 41984; Query OK, 0 rows affected (0.00 sec)

  mysql> SHOW VARIABLES LIKE 'query_cache_size'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | query_cache_size | 41984 | +------------------+-------+

• "query_cache_type": the operation means influencing "Query Cache" may be set as the following value - 0 or "OFF": prevent Query Cache from caching results and guard against searching results from Query Cache - 1 or ON: cache query statements and the results, exclusive of those statements beginning with "SELECT SQL_NO_CACHE" - 2 or DEMAND: only cache query statements beginning with "SELECT SQL_CACHE" and the results.
• query_cache_limit: control the maximum value of a single result set that can be cached.
• query_cache_min_res_unit: specify the size of a memory block for each distribution when "Query Cache" caches results.

3. Enable "Query Cache" feature

• Before starting, set "query_cache_size=1M, query_cache_type=1" in configuration file, or execute the following statements on the command line:

  mysql> SET GLOBAL query_cache_size = 1000000; mysql> SET GLOBAL query_cache_type = ON;

4. Disable "Query Cache" feature

• Before starting, set "query_cache_size=0, query_cache_type=0" in configuration file, or execute the following statements on the command line:

  mysql> SET GLOBAL query_cache_size = 0; mysql> SET GLOBAL query_cache_type = OFF;

5. Other operations

• De-fragment Query Cache (cached statements and results are not deleted in this operation)

  mysql> FLUSH QUERY CACHE;

• Refresh "Query Cache" (deletion of all cached statements and results, which may be completed via "FLUSH TABLES" as well)

  mysql> RESET QUERY CACHE

6. Query Cache status variable

• Qcache_free_blocks: Number of idle memory blocks in Query Cache
• Qcache_free_memory: the size of idle memory in Query Cache
• Qcache_hits: number of queries that hit Query Cache
• Qcache_inserts: number of queries inserted in Query Cache
• Qcache_lowmem_prunes: number of queries deleted in Query Cache
• Qcache_not_cached: number of queries that do not hit Query Cache
• Qcache_queries_in_cache: number of queries registered in Query Cache

Best practice

1. Since Query Cache is deleted after MySQL 8.0 and enabling of Query Cache in most application scenarios leads to additional expenses, recommend you to disable this feature (unless there are few table modifications and a large number of identical query statements).
2. Do not set an overly high value for "Query Cache", for overmuch Query Cache leads to an increase of expenses in cache maintenance and reduction of query performance (size of tens of MB is recommended).
3. You are advised to selectively specify the queries (cached or not) using "SELECT SQL_NO_CACHE/SELECT SQL_CACHE", and check the change of MySQL performance before and after enabling of Query Cache.
4. When the query result is cached, the last distributed memory block is cut as per the actual space in use, and the idle space is released. If the query's result set is very small, and the "Qcache_free_blocks" and "Qcache_lowmem_prunes" values are relatively large, it means too many memory fragmentations lead to continuous deletion of queries from the cache, so recommend you to reduce the size of "query_cache_min_res_unit", or vice versa.

How to deal with the situation that the RDS instance is turned into read-only and inaccessible for data write-in (disk usage exceeds limit)?

Cause

The RDS instance is turned into read-only and inaccessible for data write-in, likely resulting from full occupancy of local disk. Generally, the physical space the RDS applies for is slightly larger than the disk space the user purchases. That ensures users can make full use of the disk space they purchase and reserve additional space for the instance log and monitoring log. However, local disk space is not for use without limits. If you fully use the space you purchase, continued increase of user data leads to service unavailability and, in turn, more influences. Thus your RDS instance is locked.

Troubleshooting

If the RDS instance is locked, the console displays the instance's locking status. See the figure below:

After the instance is locked, the write privilege account reserves "select", "delete", "drop", and "show view" privilege, but such privileges as "insert" and "update" are revoked.

Solutions

In this case, it needs to be restored into the normal state. You may get larger disk space by upgrading the package, or delete useless data via "drop table". If the instance's data space is smaller than the disk space you purchase, the system completes the unlocking and returns to the normal state within 5 minutes.

To ensure your service's stability and availability, you may keep abreast of the disk usage by adding "Disk Monitoring Policy". When the disk usage reaches the limit, you should prevent the instance from being locked by upgrading the package or clearing useless data.

Troubleshooting for database front-end connection error

During the use of the RDS database, you might incur connection failure for some reasons. In this case, recommend you to check frequent error codes and locate the cause at the front end on your own. Causes and solutions for six connection errors are listed below:

2006 MySQL server has gone away: connection has been lost when the execution of SQL is launched. Frequent causes:

1. Idle connection timeout: when SQL gives a command, the connection has been ended by MySQL. You are advised to detect if the connection still exists at the time of the query, or establish DB connection anew.
2. Connection interruption due to the poor network condition.
3. The connection is ended for MySQL instance fault or human restarting.

2003 Can't connect to MySQL server on '10.26.211.22' (4)

Timeout for front-end connection to MySQL leads to failure of some connections, frequent causes:

1. Front-end computer or MySQL server in overmuch load leads to NIC anomaly. You may check the NIC rate monitoring and monitoring items (like "_tcp_overflow_" and "_tcp_retran_") on your own or contact DBA.
2. Network condition between front-end computer and MySQL is poor. You are advised to provide DBA with front-end computer IP and data access entry for troubleshooting of the network condition between computers.
3. There must be some packet loss probability in network transmission, so recommend you to add "Retry connection", and the time interval for retrying connection is set as 100ms, to ensure the service's successful connection to the database.

Can't connect to MySQL server on '10.26.211.22' (110)

Timeout for front-end connection to MySQL, frequent causes:

1. Configured database entry is wrong, leading to failure in connection to the database in the prescribed time. You may check if the configuration information is correct.
2. Connection timeout is set overly short.
3. Network condition between front-end computer and MySQL is poor. You are advised to do troubleshooting on front-end computer's NIC rate and monitoring items like "_overflow_", as well as to contact DBA for help.

2013: Lost connection to MySQL server during query

The connection is lost in the SQL execution process, frequent causes:

1. The client timeout setting leads to a lost connection.
2. SQL consumes overmuch time. Thus it is ended by MySQL server large transaction monitoring script.
3. Network condition between front-end computer and MySQL is poor. You are advised to do troubleshooting on front-end computer's NIC rate and monitoring items like "_overflow_", as well as to contact DBA for help.
4. If the connections at MySQL end are full or the account and password are wrong, recommend you to contact DBA for troubleshooting.

Lost connection to MySQL server at 'reading initial communication packet', system error: 111

Monitoring queue overflow, frequent causes:

1. Mass result set search leads to monitoring queue overflow.
2. Network or computer NIC in poor condition. You are advised to do troubleshooting on front-end computer NIC rate and monitoring items like "_overflow_", as well as to contact DBA for help.

Lost connection to MySQL server at 'reading initial communication packet', system error: 104

This port does not exist at MySQL's corresponding server. You are advised to check if the database entry configuration is correct and to perform the test with MySQL client. If it does not work, contact DBA for help.

Troubleshooting to MySQL Slow Query

During the use of the database, the usage rate of memory and CPU might increase sharply beyond the traffic peak of the services, which likely results from an overly long execution time of some SQL statements. Through the troubleshooting to Slow Query Log you can learn about the database system's abnormal running, optimize SQL statement, and improve database performance.

What is Slow Query Log

MySQL's Slow Query Log is a kind of log record MySQL provides, and it is used in recording the statements with response time exceeding threshold value in MySQL. To be precise, SQL with running time exceeding "long_query_time" value is recorded into Slow Query Log. "long_query_time" value is 1 by default, meaning the statement with running time of more than 1s.

Slow Query's low performance usually results from too many access data; During the execution of the Slow Query, the occupied system resources are not released. In most cases, contention for resources frequently takes place for high concurrency, so that execution time is longer relative to the low peak of services. As a result, CPU, network, and IO, etc. all bear big pressures and execution efficiency of all queries declines in a short time. What's worse, MySQL crash sometimes happens.

How to enable Slow Query Log

Important parameters are as follows:

• slow_query_log Slow Query Enabled State
• slow_query_log_file Slow Query Log saving path
• long_query_time Record after the query time exceeds specified seconds

Recommended configuration: [mysqld] slow_query_log = ON slow_query_log_file = /home/mysql/mysql/log/slow.log long_query_time = 1

Example: one segment of Slow Query Log:

# Time: 101126 6:12:06 PM
# User@Host: noah[noah] @ localhost []
# Query_time: 0.001778  Lock_time: 0.000128 Rows_sent: 0  Rows_examined: 235
SET timestamp=1290766326;
SELECT id FROM log_task  WHERE (state = "DONE") AND ( TIME_TO_SEC(TIMEDIFF(NOW(),`refreshTime`)) > 5)  LIMIT 0 , 99999999;

The first row displays the time for query recording, the second row displays the user executing the query, and the third row displays the time required for query execution, time for waiting for table lock on MySQL server, number of rows returned by the query, and number of rows checked by the query. Then, the query in execution is displayed.

View "Slow Query" statistics information using the auxiliary tool "mysqldumpslow"

Usage of "Mysqldumpslow":

Meanings of frequent parameters: -s: sort the log records; "t, l, r, c" and "at, al, ar, ac" are sorted respectively according to query time, lock time, number of records returned, and query times. The letter prefixed with "a" denotes sorting by average value. Descending sort. -a: Do not replace digit in query statement with ’N’, or replace character string in query statement with ’S’. -g: Only analyze queries matched. -t: It means "top n", namely, "n" pieces of data are returned. -r: Ascending sort.

Calculation method and processing plan for the RDS disk usage rate

When using MySQL instance, the users might encounter "Space Usage Warning" and even "Space Usage Exceeds the Instance Limit, and the Instance Locked". You may view the disk usage rate in the Basic Information of the instance on the RDS console. See the figure below: When the RDS's disk usage rate exceeds 80%, the users receive relevant SMS notifications. If the disk usage rate reaches 100%, the instance is locked. The content below introduces the frequent causes and solutions for an overly high disk usage rate.

Disk usage rate calculation method

The RDS instance's used disk space includes "binlog" and data size. Disk usage rate calculation method: (binlog size +data file size)/disk package size.

Frequent causes

An overly high space usage rate of MySQL instance is mainly attributed to the reasons below:

1. High occupancy by "Binlog" file. The "Binlog" file records the instance's transaction information and serves as the foundation for MySQL instance's synchronization architecture, high availability, and recoverability, thus it must not be disabled. The RDS instance automatically cleans up "Binlog" files beyond the last seven days in a given time interval.
2. High occupancy by the data file.

Check the space usage condition

You may check the usage curve of the instance's disk space via the "Monitoring" on the RDS console.

Solution:

1. Upgrade instance specification: upgrading instance specification is an effective way to resolve the disk usage rate issue. Presently, the maximum storage space supported by the RDS has reached 3000TB. You are advised to upgrade the instance specification to package with larger disk size according to the following operation steps:

1. Log in to the RDS console.
2. select the region of the target instance.
3. Enter the "Basic Information" page with a single click to target instance ID.
4. In the "Configuration Information" bar, a single click to the "Configuration Change".

5. Select the disk size you upgrade to. You are advised to upgrade the memory size simultaneously. Then, confirm the change with a single click.

2. Clean up data: if the data file occupies large space, you may reduce the space occupancy by cleaning up the data. To be precise, recommend you to clean up the data you do no need via "drop table" and "truncate table" commands and delete them with the "delete" command.

How to deal with the error information prompt “ERROR 1888……”?

How to deal with error information prompt “ERROR 1888 (HY000): Modify mysql.* tables with DML statement denied, please use other none DML commands instead.”？

Main cause: to prevent some user actions from bringing about unrecoverable "mysql" fault, we do not grant "super" account the privilege of directly modifying "mysql.*" table. If the users have enabled the "super" privilege, they cannot realize actions like "Delete Account" by modifying "mysql.user" table. Solution: delete the "drop user" statement that is available for the users. For details, see Link.

What if the statement "CREATE TABLE ... SELECT" is not available？

Since the RDS enables the GTID-based consistency check, you are unable to use the statement "CREATE TABLE ... SELECT", and it is not secure for it is DDL.

Alternatively, you may insert data using DML with the same feature after creating the table structure.

• Create a table

    CREATE TABLE `common_house_bak` xxxxx;

• Insert data

    INSERT INTO `common_house_bak` SELECT * FROM `common_house`;