Wednesday, March 27, 2019

Every MySQL should have these variables set ...

So over the years, we all learn more and more about what we like and use often in MySQL. 

Currently, I step in and out of a robust about of different systems. I love it being able to see how different companies use MySQL.  I also see several aspect and settings that often get missed. So here are a few things I think should always be set and they not impact your MySQL database. 

At a high level:

  • >Move the Slow log to a table 
  • Set report_host_name 
  • Set master & slaves to use tables
  • Turn off log_queries_not_using_indexes until needed 
  • Side note -- USE  ALGORITHM=INPLACE
  • Side note -- USE mysql_config_editor
  • Side note -- USE  mysql_upgrade  --upgrade-system-tables






Move the Slow log to a table 

This is a very simple process with a great return. YES you can use Percona toolkit to analyze the slow logs. However, I like being able to query against the table and find duplicate queries or by times and etc with a simple query call. 



mysql> select count(*) from mysql.slow_log;
+----------+
| count(*) |
+----------+
|       0 |
+----------+
1 row in set (0.00 sec)

mysql> select @@slow_query_log,@@sql_log_off;
+------------------+---------------+
| @@slow_query_log | @@sql_log_off |
+------------------+---------------+
|                1 |            0 |
+------------------+---------------+

mysql> set GLOBAL slow_query_log=0;
Query OK, 0 rows affected (0.04 sec)

mysql> set GLOBAL sql_log_off=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE mysql.slow_log ENGINE = MyISAM;
Query OK, 0 rows affected (0.39 sec)

mysql> set GLOBAL slow_query_log=0;
Query OK, 0 rows affected (0.04 sec)

mysql> set GLOBAL sql_log_off=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE mysql.slow_log ENGINE = MyISAM;
Query OK, 0 rows affected (0.39 sec)

mysql> set GLOBAL slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set GLOBAL sql_log_off=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL log_output = 'TABLE';
Query OK, 0 rows affected (0.00 sec)

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

mysql> select count(*) from mysql.slow_log;
+----------+
| count(*) |
+----------+
|       0 |
+----------+
1 row in set (0.00 sec)
mysql> select @@slow_launch_time;
+--------------------+
| @@slow_launch_time |
+--------------------+
|                   2 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT SLEEP(10);
+-----------+
| SLEEP(10) |
+-----------+
|         0 |
+-----------+
1 row in set (9.97 sec)

mysql> select count(*) from mysql.slow_log;
+----------+
| count(*) |
+----------+
|         1 |
+----------+
1 row in set (0.00 sec)

mysql> select * from   mysql.slow_log\G
*************************** 1. row ***************************
    start_time: 2019-03-27 18:02:32
     user_host: klarson[klarson] @ localhost []
    query_time: 00:00:10
     lock_time: 00:00:00
     rows_sent: 1
rows_examined: 0
            db:
last_insert_id: 0
     insert_id: 0
     server_id: 502
      sql_text: SELECT SLEEP(10)
     thread_id: 16586457

Now you can truncate it or dump it or whatever you like to do with this data easily also.
Note variable values into your my.cnf file to enable upon restart.


Set report_host_name 


This is a simple my.cnf file edit in all my.cnf files but certainly the slaves my.cnf files. On a master.. this is just set for when it ever gets flipped and becomes a slave.



report_host                     = <hostname>  <or whatever you want to call it>
This allows you from the master to do



mysql> show slave hosts;
+-----------+-------------+------+-----------+--------------------------------------+
| Server_id | Host         | Port | Master_id | Slave_UUID                           |
+-----------+-------------+------+-----------+--------------------------------------+
|   21235302 | <hostname>  | 3306 |   
21235301| a55faa32-c832-22e8-b6fb-e51f15b76554 |
+-----------+-------------+------+-----------+--------------------------------------+

Set master & slaves to use tables

mysql> show variables like '%repository';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| master_info_repository     | FILE   |
| relay_log_info_repository | FILE   |
+---------------------------+-------+

mysql_slave> stop slave;
mysql_slave> SET GLOBAL master_info_repository = 'TABLE'; 
mysql_slave> SET GLOBAL relay_log_info_repository = 'TABLE'; 
mysql_slave> start slave;

Make sure you add to my.cnf to you do not lose binlog and position at a restart. It will default to FILE otherwise.

  • master-info-repository =TABLE 
  • relay-log-info-repository =TABLE

mysql> show variables like '%repository';
---------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| master_info_repository     | TABLE |
| relay_log_info_repository | TABLE |
+---------------------------+-------+


All data is available in tables now and easily stored with backups



mysql> desc mysql.slave_master_info;
+------------------------+---------------------+------+-----+---------+-------+
| Field                   | Type                 | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+-------+
| Number_of_lines         | int(10) unsigned     | NO   |     | NULL     |       |
| Master_log_name         | text                 | NO   |     | NULL     |       |
| Master_log_pos         | bigint(20) unsigned | NO   |     | NULL     |       |
| Host                   | char(64)             | YES   |     | NULL     |       |
| User_name               | text                 | YES   |     | NULL     |       |
| User_password           | text                 | YES   |     | NULL     |       |
| Port                   | int(10) unsigned     | NO   |     | NULL     |       |
| Connect_retry           | int(10) unsigned     | NO   |     | NULL     |       |
| Enabled_ssl             | tinyint(1)           | NO   |     | NULL     |       |
| Ssl_ca                 | text                 | YES   |     | NULL     |       |
| Ssl_capath             | text                 | YES   |     | NULL     |       |
| Ssl_cert               | text                 | YES   |     | NULL     |       |
| Ssl_cipher             | text                 | YES   |     | NULL     |       |
| Ssl_key                 | text                 | YES   |     | NULL     |       |
| Ssl_verify_server_cert | tinyint(1)           | NO   |     | NULL     |       |
| Heartbeat               | float               | NO   |     | NULL     |       |
| Bind                   | text                 | YES   |     | NULL     |       |
| Ignored_server_ids     | text                 | YES   |     | NULL     |       |
| Uuid                   | text                 | YES   |     | NULL     |       |
| Retry_count             | bigint(20) unsigned | NO   |     | NULL     |       |
| Ssl_crl                 | text                 | YES   |     | NULL     |       |
| Ssl_crlpath             | text                 | YES   |     | NULL     |       |
| Enabled_auto_position   | tinyint(1)           | NO   |     | NULL     |       |
| Channel_name           | char(64)             | NO   | PRI | NULL     |       |
| Tls_version             | text                 | YES   |     | NULL     |       |
| Public_key_path         | text                 | YES   |     | NULL     |       |
| Get_public_key         | tinyint(1)           | NO   |     | NULL     |       |
+------------------------+---------------------+------+-----+---------+-------+
27 rows in set (0.05 sec)

mysql> desc mysql.slave_relay_log_info;
+-------------------+---------------------+------+-----+---------+-------+
| Field             | Type                 | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| Number_of_lines   | int(10) unsigned     | NO   |     | NULL     |       |
| Relay_log_name     | text                 | NO   |     | NULL     |       |
| Relay_log_pos     | bigint(20) unsigned | NO   |     | NULL     |       |
| Master_log_name   | text                 | NO   |     | NULL     |       |
| Master_log_pos     | bigint(20) unsigned | NO   |     | NULL     |       |
| Sql_delay         | int(11)             | NO   |     | NULL     |       |
| Number_of_workers | int(10) unsigned     | NO   |     | NULL     |       |
| Id                 | int(10) unsigned     | NO   |     | NULL     |       |
| Channel_name       | char(64)             | NO   | PRI | NULL     |       |

+-------------------+---------------------+------+-----+---------+-------+


Turn off log_queries_not_using_indexes until needed 


This was shown above also. This is a valid variable .. but depending on application it can load a slow log with useless info. Some tables might have 5 rows in it, you use it for some random drop down and you never put an index on it. With this enabled every time you query that table it gets logged. Now.. I am a big believer in you should put an index on it anyway. But focus this variable when you are looking to troubleshoot and optimize things. Let it run for at least 24hours so you get a full scope of a system if not a week.


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


To turn on 

mysql> SET GLOBAL log_queries_not_using_indexes=1;

Query OK, 0 rows affected (0.00 sec)

Note variable values into your my.cnf file to enable upon restart. 


Side note -- USE  ALGORITHM=INPLACE 


OK this is not a variable but more of a best practice. You should already be using EXPLAIN before you run a query, This shows you the query plan and lets you be sure all syntax is valid.  I have seen more than once a Delete query executed without an WHERE by mistake. So 1st always use EXPLAIN to double check what you plan to do.  Not the other process you should always do is try to use an ALGORITHM=INPLACE or  ALGORITHM=COPY when altering tables. 



mysql> ALTER TABLE TABLE_DEMO   ALGORITHM=INPLACE, ADD INDEX `datetime`(`datetime`);
Query OK, 0 rows affected (1.49 sec)
Records: 0   Duplicates: 0   Warnings: 0




mysql> ALTER TABLE TABLE_DEMO   ALGORITHM=INPLACE, ADD INDEX `datetime`(`datetime`);
Query OK, 0 rows affected (1.49 sec)
Records: 0   Duplicates: 0   Warnings: 0


A list of online DLL operations is here







Side note -- USE mysql_config_editor

Previous blog post about this is here 


The simple example

mysql_config_editor set  --login-path=local --host=localhost --user=root --password
Enter password:
# mysql_config_editor print --all
[local]
user = root
password = *****
host = localhost

# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)


# mysql  --login-path=local
Welcome to the MySQL monitor.

# mysql  --login-path=local -e 'SELECT NOW()';


Side note -- USE  mysql_upgrade  --upgrade-system-tables

Don't forget to use mysql_upgrade after you actually upgrade. 
This is often forgotten and leads to issues and errors at start up. You do not have to run upgrade across every table that exists though.  The focus of most upgrade are the system tables. So at the very least focus on those. 

mysql_upgrade --login-path=local  --upgrade-system-tables