The impact of enabling MySQL sync_binlog – really high disk IO

If you enable this feature in your MySQL you could

increase your disk IO time and write by 8-10x times.

Generally this feature could save your replication scheme if a power failure occurs or OS crash and it could guarantee that no transaction is lost from the binary log. When enabled the binary log is synchronized on disk before transactions are committed. You can check the manual here: https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_sync_binlog and it also says there could be a great impact on disk writes but how many?
So here are two setups:

SETUP 1) 2 x 3T hard drives TOSHIBA DT01ACA300 in software RAID1

The impact of setting the sync_binlog=1 is 8-10 times the IO time and IO writes. Here is images of several hours of sync_binlog=1 and then we disabled it online:

SCREENSHOT 1) Enable the binary log synchronization with sync_binlog=1.

As you can see the increase in the disk IO time and disk write IOPS are significant – somewhere between 5 and 6 times more! The load is not increased more than 1.5x as normal, but it should be noted the server is off-peak and it has plenty of RAM 32G. Still mush load when some other IO appears.

main menu
Set MySQL sync_binlog=1 in a software raid of two hard drives

SCREENSHOT 2) Disabling the binary log synchronization with sync_binlog=0.

The decrease of the disk IO time and disk write IOPS are significant – somewhere between 5 and 6 times more! Everything back to normal.

main menu
Set MySQL sync_binlog=0 in a software raid of two hard drives.

SCREENSHOT 3) Enable the binary log synchronization with sync_binlog=1.

As you can see the increase in the disk IO time and disk write IOPS are significant – somewhere between 8 and 10 times more! The load is not increased, but it should be noted the server is off-peak and it has plenty of RAM 192G. Still mush load when some other IO appears.

SET GLOBAL sync_binlog=0;

SETUP 2) 2 x 960G SSD SAMSUNG SM863 in software RAID1

The impact of setting the sync_binlog=1 is also 8-10 times the IO time and IO writes. Here is images of several hours of sync_binlog=1 and then we disabled it online:

main menu
Set MySQL sync_binlog=1 in a software raid of two enterprise SSDs

SCREENSHOT 4) Disabling the binary log synchronization with sync_binlog=0.

The decrease of the disk IO time and disk write IOPS are significant – somewhere between 8 and 10 times more and even more! Everything back to normal.

main menu
Set MySQL sync_binlog=0 in a software raid of two enterprise SSDs

SCREENSHOT 5) Enable the binary log synchronization with sync_binlog=1.

Just the period of the graphs are bigger. As you can see the increase in the disk IO time and disk write IOPS are significant – somewhere between 8 and 10 times more! The load is not increased, but it should be noted the server is off-peak and it has plenty of RAM 192G. Still mush load when some other IO appears.

main menu
Set MySQL sync_binlog=1 in a software raid of two enterprise SSDs (big period)

SCREENSHOT 6) Disabling the binary log synchronization with sync_binlog=0.

Just the period of the graphs are bigger. The decrease of the disk IO time and disk write IOPS are significant – somewhere between 8 and 10 times more and even more! Everything back to normal.

main menu
Set MySQL sync_binlog=0 in a software raid of two enterprise SSDs (big period)

BONUS – MySQL changed the default value from 0 (disabled) to 1 (enabled) from 5.7 (in fact MySQL >= 5.7.7).

SO BE CAREFUL now when upgrading from older versions like MySQL 5.1, 5.5, 5.6 – you would probably need to disable it in the MySQL configuration file my.cnf.

Upgrade MySQL 5.6 to 5.7 what problems to expect with old my.cnf configuration file

Finally we do not have any more MySQL 5.6 servers. We upgraded our last part of the system with MySQL 5.6 to 5.7. In our opinion this upgrade is one of the major referred to MySQL configuration file my.cnf – multiple deprecated directives are removed in this new 5.7 version so when upgrading you should removed them before restarting or starting the new version if you want to have running MySQL server instance.
Keep in mind our my.cnf are old, they are created with MySQL 5.0 and they are edited in every upgrade to a new version (5.0 to 5.1, 5.1 to 5.5 and 5.5 to 5.6) and when we needed a specific optimization for our work load. And this is only for our configuration, there surely are more deprecated/removed variables in the new version. Here is a good starting point – https://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html This article is not how to upgrade your old MySQL 5.6 to the new MySQL 5.7 it shows what problems you might have after you upgrade MySQL 5.6 to the new MySQL 5.7.
There are two parts of this article:

  1. Removed variables, which were perfectly OK in the old version 5.6
  2. Changed default value of variables, which impact greatly the IO or the the SQL execution

The error messages are included, too.

PART 1) Removed variables.

Some MySQL variables first get deprecated and then removed in later versions (some are just renamed) and if they are contained in the my.cnf configuration file your server will not start up at all. The MySQL log shows that the server starts and then throws an error about “unknown variable” and starts a shutdown procedure. So you end up without database server and it is important to remove them from the configuration or find the new name of a renamed one.

2019-02-26T09:50:12.612950Z 0 [ERROR] unknown variable 'key_buffer=512M'
2019-02-26T09:50:36.361870Z 0 [ERROR] unknown variable 'thread_concurrency=6'
2019-02-26T09:51:17.658546Z 0 [ERROR] unknown variable 'thread_cache=10'
2019-02-26T09:51:32.473210Z 0 [ERROR] unknown variable 'innodb_additional_mem_pool_size=256M'

All four

key_buffer, thread_concurrency, thread_cache, innodb_additional_mem_pool_size

MySQL variables were removed and your server won’t start up if they are contained in the configuration. The “key_buffer” has been renamed to “key_buffer_size so replace it with key_buffer_size in your my.cnf. It’s important to replace it, because commenting it out would activate the default value and in this case 8M key_buffer_size, which is pretty low (in fact almost all default values of the MySQL variables are really low and it is a problem and a topic of discussions in many forums).
The “thread_cache” also renamed long ago to “thread_cache_size“, so replace it with thread_cache_size.
thread_concurrency and innodb_additional_mem_pool_size were removed long ago they first stopped doing anything and with this version they removed the variables. As you can see old configuration files could carry on many old names along the years.

The important thing here is you must renamed the ones, which got renamed and remove the ones, which got removed, because your server is not going start up with them in the configuration.

PART 2) Changed default value

Some default values of MySQL variables got changed and if you have not included it in the my.cnf configuration you might be really surprised how big impact they have on the IO or even on the behavior of the SQL statements.

2.1) Our first MySQL variables is

sync_binlog

– the default value was “0” (deactivated synchronization) and now it is “1” (bin log synchronization). This could greatly impact the performance of your MySQL database server with like 8-10 times more writes and IO disk wait time (really!!!) – you can see it here: (coming soon). So if you haven’t used this variable before you should put it in your my.cnf configuration for sure (in [mysqld] section):

sync_binlog=0

do not need to restart the server, just put in the my.cnf configuration file and open a mysql root console and execute:

SET GLOBAL sync_binlog=0;

it can be live changed.

2.2) And the second example is

sql_mode

– the default value was “NO_ENGINE_SUBSTITUTION” and now it is “ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”, which is a pretty substantial difference. You can lose INSERTs and UPDATEs easily because a much strict mode is activated by default.
For example with an INSERT if you do not set value to a field, which column does not have default value (yes, it is wrong, but it was OK before), your insert won’t be executed and you’ll get an error (or just a FALSE after execution of your query like with PHP PDO). Here is the MySQL explanation:

A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition.

And more in https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict
So if you haven’t used this variable before you should put it in your my.cnf configuration for sure (in [mysqld] section):

sql_mode=NO_ENGINE_SUBSTITUTION

do not need to restart the server, just put in the my.cnf configuration file and open a mysql root console and execute:

SET GLOBAL sql_mode='NO_ENGINE_SUBSTITUTION'

mysql – Error ‘Your password does not satisfy the current policy requirements’ or zero length mysql password

We got this error when granting permissions for one of our new slave server (it could be for an ordinary MySQL server, too):

Error 'Your password does not satisfy the current policy requirements' on query. Default database: ''. Query: 'GRANT REPLICATION SLAVE ON *.* TO 'reusr'@'127.0.01''

It appeared that MySQL has activated by default a password checking plugin and our password in the GRANT (or SET PASSWORD) option didn’t meet the requirements.
So here is what you can do:

OPTION 1) Lower the password policy level

Check the policy level and lower it if it is MEDIUM or HIGH (they are there options LOW=0, MEDIUM=1 the default and HIGH=2). The policy level controls how to check and what is involved in the complexity algorithm for the passwords. More details here – https://dev.mysql.com/doc/refman/5.7/en/validate-password-options-variables.html#sysvar_validate_password_policy. Here is what you have:

[myuser@mysql1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
....
....
mysql> SHOW VARIABLES LIKE 'validate%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

So set the validate_password_policy=0 and try again your query:

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

If you still get the error your password is lower than the validate_password_length (=8 by default) so you need to change it at last to 8 characters. But what if you what zero password (or with 1,2,3 characters)? Setting validate_password_length to 0 won’t work, because there is a hard limit to 4, so you cannot set it to 0 event the set query is not reporting error when using 0 with validate_password_length.

You should uninstall the plugin.

OPTION 2) Uninstall the MySQL Validation Plugin

You can uninstall the validation plugin on-the-fly in a working server without restarting or reloading and then you can set whatever password you like.
Here is how to do it:

[myuser@mysql1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
....
....
mysql> UNINSTALL PLUGIN validate_password;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW VARIABLES LIKE 'validate%';
Empty set (0.01 sec)

As you can see no “validate_password” variables are available any more! Now set your password.
But there is a catch, if you have started the server with “–validate-password=FORCE_PLUS_PERMANENT” (you can check it with “ps axuf|grep mysqld” in the command line) you won’t be able to uninstall the plugin live even with the root MySQL user. So at the end if you do not have root permissions to restart the MySQL service without this option it might be better to change your password or skip the query if it is received by the slave in the MySQL replication bin log.
You can install the plugin again with:

[myuser@mysql1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
....
....
mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Query OK, 0 rows affected (0.00 sec)

And it will be available over restarts, too, because it is registered in “mysql.plugin” table.

mysql – Error ‘Column count of mysql.user is wrong. Expected 45, found 43. The table is probably corrupted’ on query.

If you

upgraded your MySQL server (from 5.6 to 5.7 or above)

or

imported a MySQL dump SQL file from older version

than your current server you may encounter when granting permissions to a user:

Error 'Column count of mysql.user is wrong. Expected 45, found 43. The table is probably corrupted' on query. Default database: ''. Query: 'GRANT REPLICATION SLAVE ON *.* TO 'replusr'@'144.76.156.182''

Do not panic probably it is not corrupted just continue reading.

There is the simple fix, just

execute mysql_upgrade

It will automatically detect what to upgrade and it will upgrade it:

[myuser@mysql1 ~]# screen -R upgrade
[myuser@mysql1 ~]# mysql_upgrade 
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
The sys schema is already up to date (version 1.5.1).
Found 0 sys functions, but expected 22. Re-installing the sys schema.
Upgrading the sys schema.
Checking databases.
phpmyadmin.pma__bookmark                           OK
phpmyadmin.pma__central_columns                    OK
phpmyadmin.pma__column_info                        OK
phpmyadmin.pma__designer_settings                  OK
phpmyadmin.pma__export_templates                   OK
phpmyadmin.pma__favorite                           OK
phpmyadmin.pma__history                            OK
phpmyadmin.pma__navigationhiding                   OK
phpmyadmin.pma__pdf_pages                          OK
phpmyadmin.pma__recent                             OK
phpmyadmin.pma__relation                           OK
phpmyadmin.pma__savedsearches                      OK
phpmyadmin.pma__table_coords                       OK
phpmyadmin.pma__table_info                         OK
phpmyadmin.pma__table_uiprefs                      OK
phpmyadmin.pma__tracking                           OK
phpmyadmin.pma__userconfig                         OK
phpmyadmin.pma__usergroups                         OK
phpmyadmin.pma__users                              OK
sys.sys_config                                     OK
db1.access                                         OK
db1.users                                          OK
db1.objects                                        OK
db1.isp                                            OK
db1.desc                                           OK
Upgrade process completed successfully.
Checking if update is needed.

It works when the server is up and running and it is a good idea to execute the command in a screen.
It does not need to be logged as root, but mysql_upgrade does need to have the root MySQL password. In the example above it did not asked for password, because we have it in ~/.my.cnf file.

Just to note you might upgraded a long before this error to appear!

If you do not use a certain functionality you could live up happily with the old mysql.user scheme (and all old mysql.* tables). In our case we upgraded one of our slaves and several days after when a grant command on the master was issued the replication just stopped with this error! Of course, if someone were used the command in our slave the error would have appeared there sooner.
We also had case where old MySQL SQL dump file (5.6) was imported in a newer MySQL server 5.7 and there had been no issues for weeks till the GRANT command.

perror

Th error code is 1805.

[myuser@mysql1 ~]# perror 1805
MySQL error code 1805 (ER_COL_COUNT_DOESNT_MATCH_CORRUPTED_V2): Column count of %s.%s is wrong. Expected %d, found %d. The table is probably corrupted

Replication hangs with Relay_Master_Log_File mysql-bin.999999 -wrong variables in mariadb (MySQL) show slave status

Several days after another .999999 hang out – mariadb (MySQL) stopped flushing relay-bin log after mysqld-relay-bin.999999, the monitoring of one of the slaves got critical with replication delayed. Then several hours it kept delaying without any apparent reason.
The slave status was weird, the

  • “Relay_Master_Log_File: mysql-bin.999999” and
  • Exec_Master_Log_Pos: 104858214” were not changing,

which is essential for the slave server! In fact without these two values we do not know the real position, which is executed in the slave! And we cannot recover the slave if anything happened.
Keep on reading!

mysql slave requested master to start replication from position greater than the file size

If you happen to reset your master mysql server without shutting down the mysql process (probably because of your super collocation cut the power!!!) you could have a slave server, which have the following error:

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size; the first event 'mysql-bin.005173' at 644642725, the last event read from './mysql-bin.005173' at 4, the last byte read from './mysql-bin.005173' at 4.'

The slave server wants to read a position in the master, which does not exist probably because it was not committed to the file.

It is fairly easy (and in most cases safe) to just correct the position and restart the replication! Take the Master_Log_File and Read_Master_Log_Pos on the slave with:

[root@mysql1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 50497
Server version: 5.6.38-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 10.10.10.10
                  Master_User: replusr
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.005173
          Read_Master_Log_Pos: 644642725
               Relay_Log_File: mysqld-relay-bin.000479
                Relay_Log_Pos: 644642888
        Relay_Master_Log_File: mysql-bin.005173
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 644642725
              Relay_Log_Space: 644643109
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size; the first event 'mysql-bin.005173' at 644642725, the last event read from './mysql-bin.005173' at 4, the last byte read from './mysql-bin.005173' at 4.'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: ce8a6c29-cf8e-11e5-9d39-000000000001
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 180525 16:27:22
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

As you can see the highlighted variables are important, that is the file and the position you should check in your master mysql server. So go to your master datadir (or the binlog directory, which could be changed with “binlog-dir”) and check whether there is Master_Log_File: “mysql-bin.005173” with position Read_Master_Log_Pos: “644642725”

srv@local-master ~ # cd /var/lib/mysql/
srv@local-master mysql # ls -altr mysql-bin.005173
-rw-rw---- 1 mysql mysql 644639026 24 may 12,22 mysql-bin.005173
srv@local-master mysql # mysqlbinlog mysql-bin.005173|tail -n 15
# at 644638722
#180524 12:03:31 server id 2  end_log_pos 644638930 CRC32 0xfeabe1ab    Query   thread_id=88263388      exec_time=0     error_code=0
SET TIMESTAMP=1527152611/*!*/;
UPDATE `group_desc` SET `id` = '153357',`name` = 'Test Group Name',`tags` = '|Test Group |' WHERE  `id` = '153357'
/*!*/;
# at 644638930
#180524 12:03:31 server id 2  end_log_pos 644639026 CRC32 0x5ca1b693    Query   thread_id=88263388      exec_time=0     error_code=0
SET TIMESTAMP=1527152611/*!*/;
COMMIT
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
srv@local-master ~ # 

And yes, there is no position Read_Master_Log_Pos: “644642725” as the slave requested! The position number starts with

# at

for example

# at 644638930

As you can see from the bash commands above we got the last 15 lines of our binlog mysql file and the last position was 644638930. Here is what is going on: slave requested to continue from master’s position at 644642725, but master has last position 644638930:

644642725 > 644638930

To fix it just use the next binlog file and position 1 and your slave will continue normally. Let’s say there is a possibility your master missed to write the last commands to the master’s binlog because of the reset and in this situation your slave could be out of sync and in this case you should recover your slave from a full mysql dump and import in the slave. But in most cases it is fairly safe to continue.

[root@mysql1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 50506
Server version: 5.6.38-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.12 sec)

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.005174',MASTER_LOG_POS=1;
Query OK, 0 rows affected (0.47 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.04 sec)

And if everything is OK your slave will continue with no errors (just with a big delay – behind your master):

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.10
                  Master_User: replusr
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.005175
          Read_Master_Log_Pos: 328685690
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 36999
        Relay_Master_Log_File: mysql-bin.005175
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 36836
              Relay_Log_Space: 334719008
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 103216
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: ce8a6c29-cf8e-11e5-9d39-000000000001
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Repair by sorting
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

But if you get:

Duplicate entry

Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '422987' for key 'PRIMARY'' on query. Default database: 'mydb'. Query: 'INSERT INTO `spec_cookie` (`userid`, `userip`, `cookie`, `added`) VALUES ('96201', '2591115382', 'f3b81be45a484c652d38a2c70f8c44c30d4d04d1293918c9071e052ffd9c76f7', NOW())'

You might get into troubles if you continue, be careful!!! Examine the query, select the data in the slave and in the master, if they are equal you can skip it the error with:

[srv@local-slave ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 50506
Server version: 5.6.38-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.09 sec)

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

mysql> START SLAVE;
Query OK, 0 rows affected (0.12 sec)

mysql>

If they are not equal you can change them manually and continue again. More about Duplicate entry you can check here (similar mysql binlog problems): mysql slave reset and fixing relay log read failure

Review of netdata graphs – nginx, php-fpm, mysql, memcached, redis, mail (postfix)

Here we show what to expect from the netdata graphics when using it in a web server. So we included here only the specific graphs for a web server:

  1. nginx – the web server
  2. php-fpm – the application, fastcgi php
  3. mysql – the database server
  4. memcached – memory cache
  5. redis – more sophisticated memory/disk cache
  6. mail – postfix mail server to send and receive mails

You can also visit our review of the generic graphs like system overview, cpu, memory and disks here: Review of netdata graphs – system overview, cpu, memory, disks and nfs

So here are the graphs netdata 1.10 offers to us:

CHART 1) Nginx Graphs

1) all active connections; 2) requests per second to nginx

main menu
Nginx Graphs

CHART 2) Nginx Graphs 2

1) nginx active connections by their status – reading (from client), writing (from client), idle (doing nothing, but opened to the client); 2) connections rate – accepted and handled

main menu
Nginx Graphs 2

CHART 3) PHP-FPM – FastCGI PHP performance metrics

1) active connections – active (executing PHP code on the CPU right now – “php running”), max active, idle; 2) requests; 3) performance – max children reached or slow requests (it depends on your version of netdata).

main menu
PHP-FPM – FastCGI PHP performance metrics

CHART 4) PHP-FPM – request information

1) reuqest duration – minimum, maximum, avarage – how much time do a request take time – very useful to see how fast is your backend application. 2) request CPU in procentages; 3) request memory – reuested memory by your php fpm processes.

main menu
PHP-FPM – request information

CHART 5) MySQL – performance metrics

1) bandwidth – The amount of data sent to mysql clients (out) and received from mysql clients (in); 2) queries – The number of statements executed by the server. To see a slow queries the slow query log should be enabled.

main menu
MySQL – performance metrics

CHART 6) MySQL – handlers and locks

1) handlers – netdata Quotation: “Usage of the internal handlers of mysql. This chart provides very good insights of what the mysql server is actually doing. – commit, the number of internal COMMIT statements; delete, the number of times that rows have been deleted from tables; prepare, a counter for the prepare phase of two-phase commit operations; read first, the number of times the first entry in an index was read. A high value suggests that the server is doing a lot of full index scans; e.g. SELECT col1 FROM foo, with col1 indexed; read key, the number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries; read next, the number of requests to read the next row in key order. This value is incremented if you are querying an index column with a range constraint or if you are doing an index scan; read prev, the number of requests to read the previous row in key order. This read method is mainly used to optimize ORDER BY … DESC; read rnd, the number of requests to read a row based on a fixed position. A high value indicates you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that do not use keys properly; read rnd next, the number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have; rollback, the number of requests for a storage engine to perform a rollback operation; savepoint, the number of requests for a storage engine to place a savepoint; savepoint rollback, the number of requests for a storage engine to roll back to a savepoint; update, the number of requests to update a row in a table; write, the number of requests to insert a row in a table.” 2) MySQL table locks counters, netdata Quotation: ” immediate, the number of times that a request for a table lock could be granted immediately – waited, the number of times that a request for a table lock could not be granted immediately and a wait was needed. If this is high and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.”

main menu
MySQL – handlers and locks

CHART 7) MySQL – sorts, selects and temporaries

1) mysql SELECT JOIN – full range, range, scan; 2) mysql sorts – range and scan; 3) temporaries – disk tables (writing to the disk is slow and should be avoided!!!) and tables.

main menu
MySQL – sorts, selects and temporaries

CHART 8) MySQL – connections and binlog

1) connections in seconds – all and aborted – if you are using persistent connections to MySQL you can see a busy MySQL server could have 2-3 new connections in a minute, because all the application backend uses the pool of already opened connections to the server. 2) connection errors – accepted, internal, max, peer_addr, select, tcpwrap; 3) binlog transactions per second

main menu
MySQL – connections and binlog

CHART 9) MySQL – binlog and threads

1) binlog statement cache; 2) MySQL threads – connected, cached, running; 3) threads cache misses

main menu
MySQL – binlog and threads

CHART 10) MySQL – Innodb engine infromation

1) Innodb I/O bandwidth – reads and writes; 2) Innodb I/O Operations – reads, writes and fsyncs; 3) Innodb Pending I/O Operations – reads and fsyncs; 4) Innodb Log Operations – write requests and writes.

main menu
MySQL – Innodb engine infromation

CHART 11) MySQL – Innodb engine infromation 2

1) Innodb OS Log Operations – fsyncs; 2) Innodb OS Log bandwidth – write (megabytes/s); 3) Innodb current row locks – current_waits; 4) Innodb row operations – inserted, read, updated and deleted.

main menu
MySQL – Innodb engine infromation 2

CHART 12) MySQL – Innodb engine infromation 3

1) Innodb buffer pool pages – data, dirty, free, flushed, misc, total; 2) Innodb buffer pool bytes – data and dirty; 3) Innodb buffer pool read ahaed – all, evicted, random; 4) Innodb buffer pool requests – reads and writes per second.

main menu
MySQL – Innodb engine infromation 3

CHART 13) MySQL – Innodb engine infromation 4

1) Innodb buffer pool operations – disk reads – operations per second.

main menu
MySQL – Innodb engine infromation 4

CHART 14) MySQL – query cache (qcache)

1) query cache operations – hits, low memory prunes, inserts, not cached; 2) queries in the cache; 3) query cache free memory; 4) query cache memory blocks – free and total.

main menu
MySQL – query cache (qcache)

CHART 15) MySQL – myisam engine information

This server does not uses MyISAM engine, so you can see almost everything is zero – 1) MyISAM key cache blocks – unused and used; 2) MyISAM key cache requests – reads and writes; 3) MyISAM key cache disk operation – reads and writes.

main menu
MySQL – myisam engine information

CHART 16) MySQL – files

1) open files – how many files are opened at the moment; 2) opened file rate – files per second.

main menu
MySQL – files

CHART 17) Memcached – distributed memory caching system. A key-value memory storage.

1) cache size – available and used; 2) network – in and out megabytes per second.

main menu
Memcached – distributed memory caching system. A key-value memory storage.

CHART 18) Memcached – connections and items

1) connections – current and total. Persistent connections are used, so no new connections often; 2) items cached – current and total. 3) items – evicted (forced removed – be careful here, this means your cached items are forcedly removed by the server because of lack of memory?) and reclaims (expired items).

main menu
Memcached – connections and items

CHART 19) Memcached – get and set operations

1) get operation requests – hits and misses; 2) get operations rate – requests per second; 3) set operation requests – requests per second.

main menu
Memcached – get and set operations

CHART 20) Memcached – check and set ops, delete ops, increment ops

1) check and set operation requests – hits, misses, bad value; 2) delete operation requests – hits and misses; increment operation requests – hits and misses

main menu
Memcached – check and set ops, delete ops, increment ops

CHART 21) Memcached – decrement ops, touch ops

1) decrement operation request – hits and misses; 2) touch operation requests – hits and misses; 3) touch operation requests rate – requests per second.

main menu
Memcached – decrement ops, touch ops

CHART 22) Postfix – mail service

1) Postfix Queue Emails – the emails in the queue of the mail transfer agent, these mails are in transfer state; 2) Postfix Queue Emails size – size.

main menu
Postfix – mail service

CHART 23) Redis – performance metrics for in-memory data structure store, used as a database, cache and message broker.

1) operations – commands and operations per second; 2) hit rate – persentage, the effectiveness of the cache.

main menu
Redis – performance metrics for in-memory data structure store, used as a database, cache and message broker.

CHART 24) Redis – memory, keys, network

1) Redis memory utilization – total and lua; 2) keys – how many keys does each database have – keys per database name; 3) network – Redis network bandwidth – in and out in megabytes per second.

main menu
Redis – memory, keys, network

CHART 25) Redis – connections and replication

1) Redis connections – received per second – it’s like new connections and if you use persistent connections no new connections are opened often; 2) Redis clients – connected processes to the redis server; 3) replication – connected slave servers.

main menu
Redis – connections and replication

CHART 26) Redis – persistence (save the databases to the disks)

1) Persistence changes since last save – changes – how many changed items have been there since last save of the databases to the disks. 2) Duration of the RDB Save operation – rdb save in time; 3) Status of the last RDB Save Operation – rdb status.

main menu
Redis – persistence (save the databases to the disks)

CHART 27) Web server access logs information

Live parsing of the access logs – be careful here, because this could take a good deal of CPU and I/O of your busy server. Here we included only the default nginx log, which does not save many records. netdata Quotation: “Information extracted from a server log file. web_log plugin incrementally parses the server log file to provide, in real-time, a break down of key server performance metrics. For web servers, an extended log file format may optionally be used (for nginx and apache) offering timing information and bandwidth for both requests and responses. web_log plugin may also be configured to provide a break down of requests per URL pattern (check /etc/netdata/python.d/web_log.conf).” – 1) responses – success and bad requests per second; 2) Response codes – 1xx and 4xx and more if any in the logs.

main menu
Web server access logs information

CHART 28) Web server access logs information – detailed response code, bandwidth, http methods

1) detailed response code – requests per second; 2) bandwidth of the requests and reponses; 3) Requests per HTTP Method – GET, POST, PUT, DELETE and so on if they present in the logs.

main menu
Web server access logs information – detailed response code, bandwidth, http methods

CHART 29) Web server access logs information – http versions, ip protocols, clients

1) Requests per HTTP Version – 1.0, 1.1 and 2.0 if any in the logs; 2) Requests per IP protocol – IPv4 and IPv6 (if used); 3) clients – unique client IPs per data collection.

main menu
Web server access logs information – http versions, ip protocols, clients

CHART 30) Web server access logs information – unique client IPs

Unique client IPs since last restart of netdata

main menu
Web server access logs information – unique client IPs

Speeding up the receiving of mysql binlog when replication is configured

Have you aver set up a mysql replication between two host with more than 100ms latency between. Let’s say one of your server is in US and the other is in Europe. The latency between the servers could reach more than 100ms and your MySQL replication (or galera cluster, or master-master configuration) which could easily turn into problems when you have really busy servers. In our case we have really busy servers with more than 7G binlog of every minute, which means 7G/minute transfer between the servers with a latency of 100ms and it could get worse your connectivity just hiccup for a minute or two and you would have a replication behind with more than 20 binlog files, which could take up to an hour to advance. There is a good chance to get the binlog files a lot faster – between 5-8 times faster

just compress them!

You can enable compression between your servers so they will transfer the binlog in a compressed format, the compression algo is really fast and almost no CPU consuming you’ll not observe any penalties (even if you enable it between servers on “the same switch”) and your traffic will decrease with almost 5-8 times it depends on your queries but in general it is around such values! So you now 1G binlog could be transferred 5-8 times faster using only 150Mbytes of your connection!
Here is how to enable this option

  1. Enter MySQL console
  2. set the compression to true
  3. verify the compression is set
  4. STOP and then START the slave – the compression won’t happen between the servers if you DO NOT DO this!!!
srv@local ~ # mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 55856
Server version: 10.1.30-MariaDB-1~xenial mariadb.org binary distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> set global slave_compressed_protocol=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show global variables like 'slave_compressed_protocol';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| slave_compressed_protocol | ON    |
+---------------------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> STOP SLAVE;
Query OK, 0 rows affected (0.15 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

Then check your network:

main menu
Network Statistics

main menu
Network Packets

mysql slave reset and fixing relay log read failure

Suddenly your slave server reset without a clean shutdown and when it came up again you saw the error of this kind:

2016-02-26 10:41:50 876 [ERROR] Slave SQL: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594
2016-02-26 10:41:50 876 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.005014' position 152146793

So what we know, our master server is OK, our slave server was reset by unknown issue, so the problem is only in out slave logs. Mysql sever shows the status with:

mysql> SHOW SLAVE STATUS\G;

There are multiple lines of information, but the most important in our situation is these two lines:

Relay_Master_Log_File: mysql-bin.005014
  Exec_Master_Log_Pos: 152146793

This is the place where the slave server stopped at (as you can see from the logs above, newer versions of MySQL print these two values in the log, but older versions do not print them in the log, so check them with the above command!).
The slave server stopped at file mysql-bin.005014 and position 152146793 and could not continue, because its files are corrupted. We can reset the position issuing a CHANGE MASTER command, which will clean up the relay logs and the slave will start the replication from this position – no data will be lost. Before issuing the following commands save the relay log files, they can be useful if you have later errors. Here is the command:

STOP SLAVE;              
CHANGE MASTER TO
         MASTER_HOST='1.1.1.1',
         MASTER_USER='replusr',
         MASTER_LOG_FILE='mysql-bin.005014',
         MASTER_LOG_POS=152146793;
START SLAVE;

There three commands above

  • Stop the replication in the slave, because the replication is still running and the slave is logging the binary log received from the master
  • Change master command to reset the logs with the right position
  • Start the replication in the slave

The replication must continue without errors!

In some cases after we issue the above commands and the replication starts it immediately stops with error of

Duplicate entry

.

Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '3918722' for key 'PRIMARY'' on query. Default database: 'testdb'. Query: 'INSERT INTO `testtable` (`tabid`, `tabip`, `stat`, `ins`) VALUES ('83908', '2591777309', '1', NOW())'

So we did everything right, but our replication is again broken? The problem is that when there is such reset, it could happen the autoincrement of the table is reserved,but not used, because the server was reset just in the middle of the insert operation or it could be inserted properly, but the server was reset in the middle of updating the replication metadata! So you have two options:

  • Change the autoincrement value of the table if there is no record with ID of the duplicate entry, just select it:
    SELECT * FROM testdb WHERE id=[ID_FROM_THE_ERROR]
    

    If there is no ID with such value, change the autoicrement of the table with

    ALTER TABLE tbl AUTO_INCREMENT = [ID_FROM_THE_ERROR];
    
  • Skip the duplicate entry query with
    STOP SLAVE;
    SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
    START SLAVE;
    

    or parallel replication use

    STOP SLAVE;
    START SLAVE UNTIL sql_after_mts_gaps;
    SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
    START SLAVE;
    

You could trace the problem reading the relay logs at the position it stopped.
Often there is an issue with the last recorded position, so you should examine why you have duplicate entry. Check if the entry is inserted and if it is, just skip it! But then if you hit again a duplicate entry or another error, you should reinitialize the slave dumping the replicated databases from the master!

Here is the full log of status command, when there is a problem with the corrupted mysql relay logs:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 1.1.1.1
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.005014
          Read_Master_Log_Pos: 246696051
               Relay_Log_File: mysqld-relay-bin.009911
                Relay_Log_Pos: 152146956
        Relay_Master_Log_File: mysql-bin.005014
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 152146793
              Relay_Log_Space: 246698113
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1594
               Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: ce8a6c29-cf8e-11e5-9d39-000000000001
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 180226 11:54:51
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)