Planet MySQL

MySQL User Camp, Bangalore – February 13th, 2019

At MySQL we love opportunities to interact with our users and MySQL User Camp gives us the chance to do just that. The first MySQL User Camp of this year was held on February 13th 2019 at the Oracle Kalyani Magnum Office.…

Facebook Twitter Google+ LinkedIn

Presentation : JSON improvements in MySQL 8.0

MySQL User camp is one of the prestigious MySQL meetup happening in India for the past 6 years. Mydbops DBA’s PonSuresh and Vignesh has presented about the “JSON functions and their improvements in MySQL 8.0” at MySQL User Camp Bangalore on 13-02-2019.

JSON has been more improved a lot in MySQL 8.0 and improvements in LOB storage of MySQL boost it performance further. This presentation covers the JSON performance enhancements in MySQL 8.0 with its basic functions.

JSON improvements in MySQL 8.0 from Mydbops

How to Install Matomo Web Analytics on CentOS 7

Matomo (formerly Piwik) is a free and open source web analytics application developed by a team of international developers, that runs on a PHP/MySQL web server. This tutorial will show you how to install Matomo on a CentOS 7 system using Nginx as the web server and we will secure the website with a Let's Encrypt SSL certificate.

Oracle Open World 2019 – CodeONE Call For Paper

The Oracle Open World 2019 Call For Paper is open until March 13th.

MySQL track will be part of CodeONE, the parallel conference focused on developers.

We encourage you to submit a session related to the following topics:

  • case studies / user stories of your MySQL usage
  • lessons learned in running web scale MySQL
  • production DBA/devops perspectives into MySQL Architecture, Performance, Replication, InnoDB, Security, …
  • Migration to MySQL
  • MySQL 8.0 (Document Store, InnoDB Cluster, new Data Dictionary, …)

Don’t miss the chance to participate to this amazing event. Submit now() here !

Percona XtraBackup Now Supports Dump of InnoDB Buffer Pool

InnoDB keeps hot data in memory on its buffer named InnoDB Buffer Pool. For a long time, when a MySQL instance needed to bounce, this hot cached data was lost and the instance required a warm-up period to perform as well as it did before the service restart.

That is not the case anymore. Newer versions of MySQL/MariaDB allow users to save the state of this buffer by dumping tablespace ID’s and page ID’s to a file on disk that will be loaded automatically on startup, making the newly started server buffer pool as it was prior the restart.

Details about the MySQL implementation can be found at

With that in mind, Percona XtraBackup versions 2.4.13 can now instruct MySQL to dump the content of buffer pool while taking a backup. This means you can restore the backup on a new server and make MySQL perform just like the other instance in terms of InnoDB Buffer Pool data.

How it works

The buffer pool dump happens at the beginning of backup if --dump-innodb-buffer-pool is set.

The user can choose to change the default innodb_buffer_pool_dump_pct. If --dump-innodb-buffer-pool-pct is set, it stores the current MySQL innodb_buffer_pool_dump_pct value, then it changes it to the desired percentage. After the end of the backup, original values is restored back.

The actual file copy happens at the end of the backup.

Percona XtraDB Cluster

A very good use case is PXC/Galera. When a node initiates SST, we would like the joiner to have a copy of InnoDB Buffer Pool from the donor. We can configure PXC nodes to do that:

[xtrabackup] dump-innodb-buffer-pool dump-innodb-buffer-pool-pct=100

Here is an example of a PXC node that just received SST:

Before PXB-1548:

[root@marcelo-altmann-pxb-pxc-3 ~]# systemctl stop mysql && rm -rf /var/lib/mysql/* && systemctl start mysql && mysql -psekret -e "SHOW ENGINE INNODB STATUS\G" | grep 'Database pages' mysql: [Warning] Using a password on the command line interface can be insecure. Database pages 311

Joiner started with a cold buffer pool.

After adding dump-innodb-buffer-pool and dump-innodb-buffer-pool-pct=100 to my.cnf :

[root@marcelo-altmann-pxb-pxc-3 ~]# systemctl stop mysql && rm -rf /var/lib/mysql/* && systemctl start mysql && mysql -psekret -e "SHOW ENGINE INNODB STATUS\G" | grep 'Database pages' mysql: [Warning] Using a password on the command line interface can be insecure. Database pages 30970

Joiner started with a copy of the buffer pool from the donor, which will reduce the joiner warm-up period.


The new version of Percona XtraBackup can help to minimize the time a newly restored backup will take to perform like source server

Photo by Jametlene Reskp on Unsplash

Galera Cluster 4 available for use in the latest MariaDB 10.4.3 Release Candidate!

The much anticipated release of Galera 4 makes its way first in the latest release of MariaDB Server 10.4.3 Release Candidate which you can download now. Congratulations to MariaDB Corporation and MariaDB Foundation on this release candidate — please get testing Galera 4, and share your feedback with us via our Google Group discussion list! Do not forget that you can always reach us via email: and via our contact us form.

The feature we are excited about the most, is a feature request from many users. This is none other than huge transaction support, since large write transactions are processed on the “master” node until commit time, and there have long been configuration options to prevent large transactions. Galera 4 implements streaming replication, which means it is now possible to replicate transactions of any size, while still keeping existing transaction size limits. In MariaDB, it also makes use of the group commit in the binary log (group commit is enabled for both regular replication and streaming transactions), so if you have binary logging enabled, this may be of interest to you from a throughput standpoint. How does all this work? The transaction itself is replicated in small increments, and there is a configurable size threshold. Replicated rows itself are locked and cannot be conflicted. A point of note: LOAD DATA has been refactored to work with streaming replication, so there are no intermediate commits during such processing.

As a MariaDB user, to perform State Transfer Snapshots (SSTs), you will be using Mariabackup. The server itself has backup locks, fixed in MariaDB Server 10.4.2. All in, this should be helpful for mariabackup SST.

If you look at the mysql database, there are several new tables of interest: wsrep_cluster, wsrep_cluster_members, and wsrep_streaming_log. As a DBA, you can utilise wsrep_cluster and wsrep_cluster_members to visualise the currently active nodes in your cluster.

To implement causal reads from the application, there are also 3 new functions that will benefit developers in Galera 4: WSREP_LAST_WRITTEN_GTID(), which returns the Global Transaction ID (GTID) that corresponds to the last write transaction made by the client, WSREP_LAST_SEEN_GTID(), which returns the GTID of the last write transaction that was observed by the client, and WSREP_SYNC_WAIT_UPTO_GTID(), which requires a GTID in the argument, and blocks the client until the GTID provided has been applied and committed on the node, or until a timeout occurs.

Some understated features that have been present since Galera 3.x include intelligent donor selection and cluster crash recovery. These were originally planned for Galera 4, but made it into earlier releases largely due to customer requirements.

When it comes to donor node selection in Galera 3, the State Snapshot Transfer (SST) donor was selected at random. However with Galera 4, you get a much more intelligent choice when it comes to choosing a donor, as it will favour a donor that can provide an Incremental State Transfer (IST), or pick a donor in the same segment. As a database administrator (DBA), you can force this via setting wsrep_sst_donor.

No matter where you host your Galera Cluster, there is a chance that the data center suffers a catastrophic power outage taking out your entire set of nodes in that data center (you of course were running with data center High Availability in mind, and have another, right?) for a timeframe. When power gets restored, you typically had to bootstrap a node to be a new cluster and then start MariaDB on the other nodes (since nodes can automatically join a cluster, but first a node must be elected!). You now have a new option, pc.recovery=ON which means that all nodes maintain cluster group information persistently, and upon a shutdown/restart scenario, the full group continues to start in the same configuration! This is a huge win from an automation standpoint as it means the DBA does not have to be on call during the restart.

By now, you are probably wanting to update a test cluster from an earlier MariaDB version. Currently, rolling upgrades of an idle 10.3 Galera Cluster can be migrated to a 10.4 Galera Cluster with Galera 4. It should also be safe to direct DML only workloads to a mixed cluster during said process, however the 10.4 nodes should be read only and writes should be directed to the 10.3 nodes.

However, the safest way might be to do a node-by-node upgrade from MariaDB 10.3 to the current MariaDB 10.4 release candidate. So the best way to do this, as quoted in the MariaDB release notes is to:

  • Install MariaDB 10.4.2 and Galera 4 version 26.4.0
  • Start MariaDB server, but make sure it is not trying to connect to the cluster by configuring wsrep_provider=none
  • While MariaDB server is running, run mysql_upgrade for the server
  • Restart MariaDB Server with the proper wsrep_provider, and it will join the cluster topology

Note that the update operation should be done fast enough so that joining will happen with Incremental State Transfer (IST). If the 10.4 node joins back with SST, the data directory would be overwritten with the old 10.3 data directory from the donor node.

So, what are you waiting for? Try out MariaDB 10.4.3 Release Candidate with Galera 4 today! As always, don’t forget to drop feedback at our Google Group discussion list. We will also be monitoring the maria-discuss and maria-developers lists, as well as MariaDB’s Jira for any pesky bugs or feature enhancement feedback!

MySQL Master High Availability and Failover: more thoughts

Some months ago, Shlomi Noach published a series about Service Discovery.  In his posts, Shlomi describes many ways for an application to find the master.  He also gives detail on how these solutions cope with failover to a slave, including their integration with Orchestrator. This is a great series, and I recommend its reading for everybody implementing master failover, with or without

Building MySQL on OCI Compute with Scripting

The Oracle Cloud has a lot to offer with it’s Security Focused 2nd Generation OCI environment.  My previous blog on OCI IaaS walks us through some of the great IaaS features such as compartments and VCN Subnets where you can run services across private ip addresses.  What I’d like to look at this time is… Read More »

SSL Encryption at ProxySQL Part 2

This is the second part of a two part series. In SSL Encryption at ProxySQL Part 1 we have seen how to enable backend SSL encryption for ProxySQL. In this second article, we are going to see how to enable frontend SSL, so client can establish a secure remote connection towards a ProxySQL instance.

In a standard configuration, a client connection is unencrypted, which can lead to data being intercepted on the way. In previous versions of ProxySQL it was recommended to deploy ProxySQL on your application servers and use sockets to secure traffic however starting from version 2.0 Frontend SSL connections are available.

Note : Frontend SSL is available from ProxySQL v2.0+

Verify encryption on ProxySQL

Before going ahead let's make sure connections are not using SSL encryption.

By default, ProxySQL listens for MySQL Traffic on and ProxySQL Admin commands on (used for monitoring and configuration).

# When Frontend SSL is disable $ mysql -h127.0.0.1 -P6033 -usysbench -psysbench -e '\s' | grep -P 'SSL|Connection' Connection id: 5 SSL: Not in use Connection: via TCP/IP

As the above output indicates, SSL is not currently in use for our connection, even though we are connected over TCP. Assuming you already have configured backend SSL from last blog post and now we can see SSL is currently in use for backend connections.

# When Backend SSL is enabled : $ mysql -h127.0.0.1 -P6033 -usysbench -psysbench -e 'SHOW SESSION STATUS LIKE "Ssl_cipher"' +---------------+----------------------+ | Variable_name | Value | +---------------+----------------------+ | Ssl_cipher | ECDHE-RSA-AES256-SHA | +---------------+----------------------+ Enabling encryption on ProxySQL

Now lets enable frontend SSL by using ProxySQL's Admin interface.

UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-have_ssl'; OR SET mysql-have_ssl='true';

Load this configuration to runtime and save on disk if you want to make changes persistent.


Note : Changes will only apply on new client connection.

Lets go ahead and verify this by executing the following commands:

$ mysql -h127.0.0.1 -P6032 -uadmin -padmin -e '\s' | grep -P 'SSL|Connection' SSL: Cipher in use is DHE-RSA-AES256-SHA Connection: via TCP/IP $ mysql -h127.0.0.1 -P6033 -usysbench_FB -psysbench -e '\s' | grep -P 'SSL|Connection' SSL: Cipher in use is DHE-RSA-AES256-SHA Connection: via TCP/IP

As we can see SSL Cipher in use while making frontend connections towards ProxySQL.

Supported protocol/ciphers for frontend SSL:

  • SSLv2
  • SSLv3
  • TLSv1
  • TLSv1.1
  • TLSv1.2

Supported ciphers:

  • DHE-RSA-AES256-SHA256
  • AES256-GCM-SHA384
  • AES256-SHA256
  • AES256-SHA
  • DHE-RSA-AES128-SHA256
  • AES128-GCM-SHA256
  • AES128-SHA256
  • AES128-SHA

We have performed a simple read-only test using sysbench against ProxySQL with backend/frontend SSL enabled and disabled. ProxySQL was configured to use 8 internal threads.

From above graphs we can see that encryption has not added any major overhead and not impacted much on performance, making it safe to use encryption and eliminate data flowing insecurely through the network.

For reference, we used following sysbench one-liner commands :

Backend SSL Test : for conn in 1 8 128 ; do for i in $(seq 1 3) ; do echo "${conn}:${i}"; ulimit -n 1048576; ./src/sysbench ./src/lua/oltp_read_only.lua --db-debug=on --report-interval=20 --table-size=70000000 --tables=20 --mysql-db=sbtest_rw --mysql-user=sysbench--mysql-password=sysbench --db-driver=mysql --mysql-host= --max-requests=0 --mysql-port=6033 --db-ps-mode=disable --skip-trx=on --threads=${conn} --max-time=60 run ; ulimit -n 1048576; ./src/sysbench ./src/lua/oltp_read_only.lua --db-debug=on --report-interval=20 --table-size=70000000 --tables=20 --mysql-db=sbtest_rw --mysql-user=sysbench --mysql-password=sysbench --db-driver=mysql --mysql-host= --max-requests=0 --mysql-port=6033 --db-ps-mode=disable --skip-trx=on --threads=${conn} --max-time=120 run |tee /data/benchmark/v2.0/backend_ssl/v2.0_8threads${conn}connections.${i}_line.log ; done ; done Frontend SSL Test : for conn in 1 8 128 ; do for i in $(seq 1 3) ; do echo "${conn}:${i}"; ulimit -n 1048576; ./src/sysbench ./src/lua/oltp_read_only.lua --db-debug=on --report-interval=20 --table-size=70000000 --tables=20 --mysql-db=sbtest_rw --mysql-user=sysbench --mysql-password=sysbench --mysql-ssl=on --db-driver=mysql --mysql-host= --max-requests=0 --mysql-port=6033 --db-ps-mode=disable --skip-trx=on --threads=${conn} --max-time=60 run ; ulimit -n 1048576; ./src/sysbench ./src/lua/oltp_read_only.lua --db-debug=on --report-interval=20 --table-size=70000000 --tables=20 --mysql-db=sbtest_rw --mysql-user=sysbench --mysql-password=sysbench --mysql-ssl=on --db-driver=mysql --mysql-host= --max-requests=0 --mysql-port=6033 --db-ps-mode=disable --skip-trx=on --threads=${conn} --max-time=120 run |tee /data/benchmark/v2.0/frontend_backend/v2.0_8threads${conn}connections.${i}_line.log ; done ; done

Happy ProxySQLing !

Authored by: Ashwini Ahire & Nick Vyzas

If you have any questions please do not hesitate to contact us. Our performance and scalability experts will help you to analyze your infrastructure and help to build fast and reliable architecture. We also offer long term support and DBRE consulting for ProxySQL users.

MySQL Challenge: 100k Connections

In this post, I want to explore a way to establish 100,000 connections to MySQL. Not just idle connections, but executing queries.

100,000 connections. Is that really needed for MySQL, you may ask? Although it may seem excessive, I have seen a lot of different setups in customer deployments. Some deploy an application connection pool, with 100 application servers and 1,000 connections in each pool. Some applications use a “re-connect and repeat if the query is too slow” technique, which is a terrible practice. It can lead to a snowball effect, and could establish thousands of connections to MySQL in a matter of seconds.

So now I want to set an overachieving goal and see if we can achieve it.


For this I will use the following hardware:

Bare metal server provided by, instance size: c2.medium.x86
Physical Cores @ 2.2 GHz
(1 X AMD EPYC 7401P)
Memory: 64 GB of ECC RAM
Storage : INTEL® SSD DC S4500, 480GB

This is a server grade SATA SSD.

I will use five of these boxes, for the reason explained below. One box for the MySQL server and four boxes for client connections.

For the server I will use Percona  Server for MySQL 8.0.13-4 with the thread pool plugin. The plugin will be required to support the thousands of connections.

Initial server setup

Network settings (Ansible format):

- { name: 'net.core.somaxconn', value: 32768 } - { name: 'net.core.rmem_max', value: 134217728 } - { name: 'net.core.wmem_max', value: 134217728 } - { name: 'net.ipv4.tcp_rmem', value: '4096 87380 134217728' } - { name: 'net.ipv4.tcp_wmem', value: '4096 87380 134217728' } - { name: 'net.core.netdev_max_backlog', value: 300000 } - { name: 'net.ipv4.tcp_moderate_rcvbuf', value: 1 } - { name: 'net.ipv4.tcp_no_metrics_save', value: 1 } - { name: 'net.ipv4.tcp_congestion_control', value: 'htcp' } - { name: 'net.ipv4.tcp_mtu_probing', value: 1 } - { name: 'net.ipv4.tcp_timestamps', value: 0 } - { name: 'net.ipv4.tcp_sack', value: 0 } - { name: 'net.ipv4.tcp_syncookies', value: 1 } - { name: 'net.ipv4.tcp_max_syn_backlog', value: 4096 } - { name: 'net.ipv4.tcp_mem', value: '50576   64768 98152' } - { name: 'net.ipv4.ip_local_port_range', value: '4000 65000' } - { name: 'net.ipv4.netdev_max_backlog', value: 2500 } - { name: 'net.ipv4.tcp_tw_reuse', value: 1 } - { name: 'net.ipv4.tcp_fin_timeout', value: 5 }

These are the typical settings recommended for 10Gb networks and high concurrent workloads.

Limits settings for systemd:

[Service] LimitNOFILE=1000000 LimitNPROC=500000

And the relevant setting for MySQL in my.cnf:

back_log=3500 max_connections=110000

For the client I will use sysbench version 0.5 and not 1.0.x, for the reasons explained below.

The workload is

sysbench --test=sysbench/tests/db/select.lua --mysql-host= --mysql-user=sbtest --mysql-password=sbtest --oltp-tables-count=10 --report-interval=1 --num-threads=10000 --max-time=300 --max-requests=0 --oltp-table-size=10000000 --rand-type=uniform --rand-init=on run Step 1. 10,000 connections

This one is very easy, as there is not much to do to achieve this. We can do this with only one client. But you may face the following error on the client side:

FATAL: error 2004: Can't create TCP/IP socket (24)

This is caused by the open file limit, which is also a limit of TCP/IP sockets. This can be fixed by setting  

ulimit -n 100000  on the client.

The performance we observe:

[  26s] threads: 10000, tps: 0.00, reads: 33367.48, writes: 0.00, response time: 3681.42ms (95%), errors: 0.00, reconnects:  0.00 [  27s] threads: 10000, tps: 0.00, reads: 33289.74, writes: 0.00, response time: 3690.25ms (95%), errors: 0.00, reconnects:  0.00

Step 2. 25,000 connections

With 25,000 connections, we hit an error on MySQL side:

Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

If you try to lookup information on this error you might find the following article:

But it does not help in our case, as we have all limits set high enough:

cat /proc/`pidof mysqld`/limits Limit                     Soft Limit Hard Limit           Units Max cpu time              unlimited unlimited            seconds Max file size             unlimited unlimited            bytes Max data size             unlimited unlimited            bytes Max stack size            8388608 unlimited            bytes Max core file size        0 unlimited            bytes Max resident set          unlimited unlimited            bytes Max processes             500000 500000               processes Max open files            1000000 1000000              files Max locked memory         16777216 16777216             bytes Max address space         unlimited unlimited            bytes Max file locks            unlimited unlimited            locks Max pending signals       255051 255051               signals Max msgqueue size         819200 819200               bytes Max nice priority         0 0 Max realtime priority     0 0 Max realtime timeout      unlimited unlimited            us

This is where we start using the thread pool feature:



to the my.cnf and restart Percona Server

The results:

[   7s] threads: 25000, tps: 0.00, reads: 33332.57, writes: 0.00, response time: 974.56ms (95%), errors: 0.00, reconnects:  0.00 [   8s] threads: 25000, tps: 0.00, reads: 33187.01, writes: 0.00, response time: 979.24ms (95%), errors: 0.00, reconnects:  0.00

We have the same throughput, but actually the 95% response time has improved (thanks to the thread pool) from 3690 ms to 979 ms.

Step 3. 50,000 connections

This is where we encountered the biggest challenge. At first, trying to get 50,000 connections in sysbench we hit the following error:

FATAL: error 2003: Can't connect to MySQL server on '' (99)

Error (99) is cryptic and it means: Cannot assign requested address.

It comes from the limit of ports an application can open. By default on my system it is

cat /proc/sys/net/ipv4/ip_local_port_range : 32768   60999

This says there are only 28,231 available ports — 60999 minus 32768 — or the limit of TCP connections you can establish from or to the given IP address.

You can extend this using a wider range, on both the client and the server:

echo 4000 65000 > /proc/sys/net/ipv4/ip_local_port_range

This will give us 61,000 connections, but this is very close to the limit for one IP address (maximal port is 65535). The key takeaway from here is that if we want more connections we need to allocate more IP addresses for MySQL server. In order to achieve 100,000 connections, I will use two IP addresses on the server running MySQL.

After sorting out the port ranges, we hit the following problem with sysbench:

sysbench 0.5:  multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 50000 FATAL: pthread_create() for thread #32352 failed. errno = 12 (Cannot allocate memory)

In this case, it’s a problem with sysbench memory allocation (namely lua subsystem). Sysbench can allocate memory for only 32,351 connections. This is a problem which is even more severe in sysbench 1.0.x.

Sysbench 1.0.x limitation

Sysbench 1.0.x uses a different Lua JIT, which hits memory problems even with 4000 connections, so it is impossible to go over 4000 connection in sysbench 1.0.x

So it seems we hit a limit with sysbench sooner than with Percona Server. In order to use more connections, we need to use multiple sysbench clients, and if 32,351 connections is the limit for sysbench, we have to use at least four sysbench clients to get up to 100,000 connections.

For 50,000 connections I will use 2 servers (each running separate sysbench), each running 25,000 threads from sysbench.

The results for each sysbench looks like:

[  29s] threads: 25000, tps: 0.00, reads: 16794.09, writes: 0.00, response time: 1799.63ms (95%), errors: 0.00, reconnects:  0.00 [  30s] threads: 25000, tps: 0.00, reads: 16491.03, writes: 0.00, response time: 1800.70ms (95%), errors: 0.00, reconnects:  0.00

So we have about the same throughput (16794*2 = 33588 tps in total), however the 95% response time doubled. This is to be expected as we are using twice as many connections compared to the 25,000 connections benchmark.

Step 3. 75,000 connections

To achieve 75,000 connections we will use three servers with sysbench, each running 25,000 threads.

The results for each sysbench:

[ 157s] threads: 25000, tps: 0.00, reads: 11633.87, writes: 0.00, response time: 2651.76ms (95%), errors: 0.00, reconnects:  0.00 [ 158s] threads: 25000, tps: 0.00, reads: 10783.09, writes: 0.00, response time: 2601.44ms (95%), errors: 0.00, reconnects:  0.00

Step 4. 100,000 connections

There is nothing eventful to achieve75k and 100k connections. We just spin up an additional server and start sysbench. For 100,000 connections we need four servers for sysbench, each shows:

[ 101s] threads: 25000, tps: 0.00, reads: 8033.83, writes: 0.00, response time: 3320.21ms (95%), errors: 0.00, reconnects:  0.00 [ 102s] threads: 25000, tps: 0.00, reads: 8065.02, writes: 0.00, response time: 3405.77ms (95%), errors: 0.00, reconnects:  0.00

So we have the same throughput (8065*4=32260 tps in total) with 3405ms 95% response time.

A very important takeaway from this: with 100k connections and using a thread pool, the 95% response time is even better than for 10k connections without a thread pool. The thread pool allows Percona Server to manage resources more efficiently and provides better response times.


100k connections is quite achievable for MySQL, and I am sure we could go even further. There are three components to achieve this:

  • Thread pool in Percona Server
  • Proper tuning of network limits
  • Using multiple IP addresses on the server box (one IP address per approximately 60k connections)
Appendix: full my.cnf

[mysqld] datadir {{ mysqldir }} ssl=0 skip-log-bin log-error=error.log # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 character_set_server=latin1 collation_server=latin1_swedish_ci skip-character-set-client-handshake innodb_undo_log_truncate=off # general table_open_cache = 200000 table_open_cache_instances=64 back_log=3500 max_connections=110000 # files innodb_file_per_table innodb_log_file_size=15G innodb_log_files_in_group=2 innodb_open_files=4000 # buffers innodb_buffer_pool_size= 40G innodb_buffer_pool_instances=8 innodb_log_buffer_size=64M # tune innodb_doublewrite= 1 innodb_thread_concurrency=0 innodb_flush_log_at_trx_commit= 0 innodb_flush_method=O_DIRECT_NO_FSYNC innodb_max_dirty_pages_pct=90 innodb_max_dirty_pages_pct_lwm=10 innodb_lru_scan_depth=2048 innodb_page_cleaners=4 join_buffer_size=256K sort_buffer_size=256K innodb_use_native_aio=1 innodb_stats_persistent = 1 #innodb_spin_wait_delay=96 innodb_adaptive_flushing = 1 innodb_flush_neighbors = 0 innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_io_capacity=1500 innodb_io_capacity_max=2500 innodb_purge_threads=4 innodb_adaptive_hash_index=0 max_prepared_stmt_count=1000000 innodb_monitor_enable = '%' performance_schema = ON

Automating Tungsten upgrades using Ansible

Continuent Tungsten is one of the few all-in-one solutions for MySQL high availability. In this post I will show you how to automate the upgrade process using Ansible. I will walk you through the individual tasks and, finally, give you the complete playbook.

We will use a rolling approach, upgrading the slaves first, and finally upgrading the former master. There is no need for a master switch, as the process is transparent to the application.

I am assuming you are using the .ini based installation method. If you are still using staging host, I suggest you update your set up.

Pre tasks

The first step is ensuring the cluster is healthy, because we don’t want to start taking nodes offline unless we are sure the cluster is in good shape. One way of doing that is by using the built-in script tungsten_monitor. When we run the playbook, we only need to validate the cluster status on one node, so I am adding run_once: true to limit this task to a single node.

- name: verify cluster is healthy shell: "/opt/continuent/tungsten/cluster-home/bin/tungsten_monitor" run_once: true

Next, we may want to do some parameter changes as part of the upgrade. The following tasks will help deal with that. The first one adds a line to the end of the file, while the second will look for a line starting with skip-validation-check, and add the new line right after.

Notice I am using the become: yes so that the tasks will run as root.

- name: Disable bridge mode for connector become: yes lineinfile: path: /etc/tungsten/tungsten.ini line: connector-bridge-mode=false insertafter: EOF - name: Disable the check for modified files become: yes lineinfile: path: /etc/tungsten/tungsten.ini line: skip-validation-check=ModifiedConfigurationFilesCheck insertafter: '^skip-validation-check'

Now we need to know the current master node for the purpose of the upgrade, so we get that from cctrl and store it as a fact so we can reference it further down the road.

- name: Capture Master Node become: yes shell: "su - tungsten -c \"echo 'ls' | cctrl \" | grep -B 5 'REPLICATOR(role=master, state=ONLINE)' | grep progress | cut -d'(' -f1 | sed 's/|//'" changed_when: False run_once: true register: master_node_result

The cluster might have a different master now than when it was initially provisioned, so we need to update the tungsten.ini file accordingly

- name: Replace current master in tungsten.ini file become: yes lineinfile: path: /etc/tungsten/tungsten.ini regexp: '^master=' line: 'master={{ master_node_result.stdout }}'

Now we need to set downtime on the monitoring system. The delegate_to is useful for when you want to run a task on a different host than the one ansible is running against.

- name: Downtime alerts shell: "/usr/local/bin/downtime --host='{{ ansible_hostname }} --service='{{ item }}' --comment='Downtime services for Tungsten Upgrade'" with_items: [ 'Tungsten policy mode', 'Tungsten Replicator Status', 'Tungsten THL Retention' ] run_once: true delegate_to:

Next, we set the cluster policy to maintenance to prevent any automatic operations from interfering with our upgrade.

- name: Set Policy to Maintenance become: yes shell: "su - tungsten -c \"echo 'set policy maintenance' | cctrl\"" run_once: true register: maintenance_results - debug: var=maintenance_results.stdout_lines Upgrade process

Now we are ready to start the upgrade on the slaves. The approach I am taking here is to copy the rpm file from my local machine to the servers and install using yum. The tungsten rpm package does everything automatically, provided the tungsten.ini file exists.

- name: copy Tungsten packages copy: src: /vagrant/tungsten dest: /tmp - name: Install Continuent Tungsten rpm yum: name: - /tmp/tungsten/tungsten-clustering-5.3.5-623.x86_64.rpm state: present

Once the slaves are done, we operate on the master and bring the replicator online on it.

- name: Upgrade Continuent Tungsten rpm on master become: yes yum: name: - tungsten-clustering state: present - name: Online cluster replicator on master shell: "su - tungsten -c \"trepctl online\""

After that is done, we can set the policy to automatic again, which will bring the slave replicators online.

- name: Set Policy to automatic become: yes shell: "su - tungsten -c \"echo 'set policy automatic' | /opt/continuent/tungsten/tungsten-manager/bin/cctrl\"" register: maintenance_results - debug: var=maintenance_results.stdout_lines Wrapping up

Finally, we do a check of the cluster status and bring hosts out of maintenance mode.

- name: Check cluster status shell: "su - tungsten -c \"echo ls | cctrl\"" register: maintenance_results run_once: true - debug: var=maintenance_results.stdout_lines run_once: true - name: Cancel downtime alerts shell: "/usr/local/bin/downtime --cancel --host='{{ ansible_hostname }} --service='{{ item }}' --comment='Downtime services for Tungsten Upgrade'" with_items: [ 'Tungsten policy mode', 'Tungsten Replicator Status', 'Tungsten THL Retention' ] run_once: true delegate_to:

The full playbook is available here.

Closing thoughts

Ansible is a very powerful tool, and when you are dealing with hundreds or thousands of servers, you can save a lot of time by automating repetitive tasks. I hope you have learned something useful from this post and if you have any comments or Ansible tips you’d like to share, do let me know in the section below.

Happy automating!

MySQL Exact Row Count For All The Tables

Getting the row count from mysql tables are not a big deal and even there is no need for a blog for this. Simply go and query the INFORMATION_SCHEMA and get the row count for the tables. But this is not your actual row counts. It’ll show the row count of the tables during the last statistics update. So if you want to track your tables growth then you should do select count(*) from table_name for all the tables and insert the results to somewhere. There are a lot of ways available. Im just make this as a blog post. So others can benefit from it.

Row Count - From Stored Procedure:

We’ll get the list of table names from the information_schema and use cursor to run select count(*) on that table and save the row count value to a table.

In this example, Im going to collect the row count of the tables from the database called prod_db. And this procedure and tracking table will be saved on the database called dbadmin.

use dbadmin; CREATE TABLE table_growth ( id INT (11) NOT NULL AUTO_INCREMENT ,timestamp DATETIME DEFAULT CURRENT_TIMESTAMP ,table_name VARCHAR(50) DEFAULT NULL ,rows INT (11) DEFAULT NULL ,PRIMARY KEY (id) ); delimiter // CREATE PROCEDURE rows_growth() BEGIN DECLARE start INTEGER DEFAULT 0; DECLARE t_name varchar(255); DEClARE table_names CURSOR FOR SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='prod_db'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET start=1; OPEN table_names; get_tablename: table_name FETCH table_names INTO t_name; SET @query =CONCAT('insert into table_growth (table_name, rows) select "',t_name,'" as tablename ', ',', 'count(*) from prod_db.', t_name); select @query; PREPARE insert_stmt FROM @query; EXECUTE insert_stmt; IF start = 1 THEN LEAVE get_tablename; END IF; END table_name get_tablename; CLOSE table_names; END// delimiter ;Row Count - From Shell Script mysql -h IP_ADDRESS -usqladmin -p'password!' Foodie -N -e "SELECT table_name FROM INFORMATION_SCHEMA.tables where table_schema='Foodie';" | while read -r table_name do count=$(mysql -h IP_ADDRESS -usqladmin -p'password' Foodie -N -e "SELECT COUNT(*) FROM $table_name;") mysql -h IP_ADDRESS -usqladmin -p'W0rdsarewind!' dbadmin -e "INSERT INTO table_growth (table_name, rows) VALUES ('$table_name',$count);" done

If your tables are having a huge amount of data and running with one or two read replica, then use replica’s IP address for doing the select count(*).