Thursday, April 25, 2019

Slack

Just a simple post here to give support to the slack community channels.

If you have not joined some of these... this is a great way to communicate with the community.

MySQL -- https://mysqlcommunity.slack.com  https://mysql-casual.slack.com/

PostgreSQL - https://postgresteam.slack.com  https://postgres-slack.herokuapp.com/

MongoDB -- https://mongo-db.slack.com

Of course, Freenode is still available as well -- http://chat.freenode.net (https://freenode.net/)

Sunday, April 14, 2019

Simple KeepaliveD set up

So keepalived has been around for quite a while now .... however it is still a mystery to many.
So this is a very simple example of how keepalived can work with MySQL. Hopefully, this can help those with questions.

We will have a Simple master to slave set up. Meaning.. we write to one unless we have failover to the second for some event.

1st - install keepalived


# yum search keepalived
keepalived.x86_64 : Load balancer and high availability service

  Name and summary matches only, use "search all" for everything.
# yum -y install keepalived

You should now have an config file 

# ls -ltr /etc/keepalived/keepalived.conf 

Keep the original as you always backup .. right....
# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.orig

So you need to figure out an ipaddress you can use for your virtual ip.  I picked 192.168.0.123 for this example. 

Next, we will set up a script to be used for our new config file. 

A few things I did here..
I left a .cnf file for keepalived and a log all in the /etc/keepalived.
This makes it simple for the example so you can do this or use your own cnf files.

A script:

cat /etc/keepalived/keepalived_check.sh 
#!/bin/bash

# monitor mysql status

# if this node mysql is dead

# and its slave is alive , then stop its keepalived. The other node will bind the IP.



export MYSQL_HOME=/etc/keepalived/

export PATH=$MYSQL_HOME/bin:$PATH



mysql="/usr/bin/mysql"

mysqladmin="/usr/bin/mysqladmin"

delay_file="$MYSQL_HOME/slave_delay_second.log"

slave_host=$1





ALIVE=$($mysqladmin --defaults-file=$MYSQL_HOME/.my.localhost.cnf  ping | grep alive | wc -l );

REMOTEALIVE=$($mysqladmin --defaults-file=$MYSQL_HOME/.my.remotehost.cnf  ping | grep alive | wc -l );



if [[ $ALIVE -ne 1 ]]

then

#echo "MySQL is down"

        if [[ $REMOTEALIVE -eq 1 ]]

        then

#        echo "Shutdown keep alive "

            systemctl stop keepalived  

#       echo " keepalived stop "

        fi

#else

#echo "MySQL is up"

#date

fi



exit 0 #all done

New config file

cat /etc/keepalived/keepalived.conf
global_defs {



      notification_email {

        anothermysqldba@gmail.com 

      }



      notification_email_from anothermysqldba@gmail.com 

      smtp_server localhost

      smtp_connect_timeout 30



      }







vrrp_script check_mysql {

   script "/etc/keepalived/keepalived_check.sh "

   interval 2

   weight 2

}







vrrp_instance VI_1 {



      state MASTER

      interface enp0s8  # <--- WHAT INTERFACE NAME HOLDS YOUR REAL IP /sbin/ifconfig

        # found with ip link show

      virtual_router_id 51

      priority 101

      advert_int 1

      nopreempt  # only needed on higher priority node

      authentication {

        auth_type PASS

        auth_pass 1111

      }





      track_script {

        check_mysql

      }



      virtual_ipaddress {

        192.168.0.123 

      }




}



This is all great but does it work....

So we have 2 hosts

[root@centosa keepalived]# hostname

centosa

[root@centosb keepalived]# hostname
centosb

Start keepalived  

[root@centosa keepalived]# systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability Monitor
   Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
[root@centosa keepalived]# systemctl restart keepalived
[root@centosa keepalived]# systemctl status keepalived
keepalived.service - LVS and VRRP High Availability Monitor
   Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)
   Active: active (running)

[root@centosa keepalived]# ssh 192.168.0.123 'hostname'
root@192.168.0.123's password: 

centosa

Prove the connections work already

[root@centosa keepalived]# mysql --defaults-file=.my.remotehost.cnf --host=192.168.0.101   -e "select @@hostname"
+------------+
| @@hostname |
+------------+
| centosb    |
+------------+
[root@centosa keepalived]# mysql --defaults-file=.my.remotehost.cnf --host=192.168.0.102   -e "select @@hostname"
+------------+
| @@hostname |
+------------+
| centosa    |
+------------+

Double check that it is running... 

[root@centosa keepalived]# systemctl status keepalived | grep active
   Active: active 

[root@centosb keepalived]# systemctl status keepalived | grep active
   Active: active 

Test current VIP .. stop mysql and watch same VIP change hosts ... 

[root@centosa keepalived]# mysql --defaults-file=.my.remotehost.cnf --host=192.168.0.123   -e "select @@hostname"
+------------+
| @@hostname |
+------------+
| centosa    |
+------------+
[root@centosa keepalived]# systemctl stop mysqld 
[root@centosa keepalived]# mysql --defaults-file=.my.remotehost.cnf --host=192.168.0.123   -e "select @@hostname"
+------------+
| @@hostname |
+------------+
| centosb    |
+------------+




Tuesday, April 9, 2019

Sometimes the slow database.. is not the database...

So I was recently asked to look into why the updated MySQL 5,.6 was slower than the older 5.5 

So I started by poking around looking over the standard variables and caches and etc.

The test case was a simple routine that took about twice as long to run on 5.6 than it did on 5.5. 

To add to the mix.. the 5.6 version had double the Innodb_buffer_pool_size and of course more ram overall.  

So I started some tests with MySQLslap...

Mysqlslap tests show it slower on 5.6 

5.6:
mysqlslap --defaults-file=./.my.cnf --concurrency=150 --iterations=130 -query=/test.sql --create-schema=applicationdata --verbose 
Benchmark
Average number of seconds to run all queries: 0.028 seconds
Minimum number of seconds to run all queries: 0.019 seconds
Maximum number of seconds to run all queries: 0.071 seconds
Number of clients running queries: 150
Average number of queries per client: 1

5.5:
mysqlslap --defaults-file=./.my.cnf --concurrency=150 --iterations=130 --query=/test.sql --create-schema=applicationdata --verbose 
Benchmark
Average number of seconds to run all queries: 0.015 seconds
Minimum number of seconds to run all queries: 0.011 seconds
Maximum number of seconds to run all queries: 0.024 seconds
Number of clients running queries: 150
Average number of queries per client: 1


All of this goes against the public benchmarks 
http://dimitrik.free.fr/blog/archives/2013/02/mysql-performance-mysql-56-ga-vs-mysql-55-32cores.html 

So I checked disk level --

5.6:
# dd if=/dev/zero of=test bs=1048576 count=2048
2048+0 records in
2048+0 records out
2147483648 bytes (2.1 GB) copied, 25.7401 s, 83.4 MB/s

# dd if=test of=/dev/null bs=1048576
2048+0 records in
2048+0 records out
2147483648 bytes (2.1 GB) copied, 29.1527 s, 73.7 MB/s

5.5:
# dd if=/dev/zero of=test bs=1048576 count=2048
2048+0 records in
2048+0 records out
2147483648 bytes (2.1 GB) copied, 19.9214 seconds, 108 MB/s

# dd if=test of=/dev/null bs=1048576
2048+0 records in
2048+0 records out
2147483648 bytes (2.1 GB) copied, 20.0243 seconds, 107 MB/s



Here the disks with 5.5 is slower regardless of MySQL. So in this case.... Look to fixing the disk speed.. MySQL  was running fine and will. 

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



Wednesday, May 23, 2018

Proxy MySQL :: HAproxy || ProxySQL & KeepAlived

So when it comes to routing your MySQL traffic several options exist.

Now I have seen HAproxy used more often with clients, it is pretty straight forward to set up. Percona has an example for those interested: 

Personally I like ProxySQL. Percona also has  few blogs on this as well
Percona also has ProxySQL version available 

I was thinking I would write up some examples but overall Percona has explained it all very well.  I do not want to take anything away from those posts, instead point out that a lot of good information is available via those urls. So instead of rewriting what has already been written, I will create a collection of information for those interested. 

First compare and decide for yourself what you need and want. The following link of course is going to be biased towards ProxySQL but it gives you an overall scope for you to consider. 
If you have a cluster or master to master and you do not care which server the writes vs reads go onto, just as long as you have a connection; then HAproxy is likely a simple fast set up for you. 

The bonus with ProxySQL is the ability to sort traffic in a weighted fashion, EASY. So you can have writes go to node 1, and selects pull from node 2 and node 3. Documentation on this can be found here:
Yes it can be done with HAproxy but you have to instruct the application accordingly. 
This is handled in ProxySQL based on your query rules.

Now the obvious question here: OK so how do you keep ProxySQL from becoming the single point of failure?  

You can invest is a robust load balancer and etc etc etc ... Toss hardware at it.... Or make it easy on yourself and support open source and use KeepAlived.  This is VERY easy to set up and all of it is documented again well here: 
If you ever dealt with lua and mysql-proxy, ProxySQL and Keepalived should be very simple for you. If you still want it for some reason: https://launchpad.net/mysql-proxy

Regardless if you choose HAproxy, ProxySQL or another solution, you need to ensure not to replace once single point of failure with another and keepalived is a great for that. So little reason to not do this if you are using a proxy. 

So a few more things on ProxySQL.