Titles in this page

Wednesday, December 12, 2012

Non-persistent connection performance improvements in 5.6

One of my favorite performance improvements in 5.6 is "faster non-persistent connections". I couldn't find this information from release notes, but non-persistent connection is really faster in 5.6.

Here are sysbench micro-benchmark results. I tested from 100 concurrent clients, running remotely. Clients simply connected and disconnected repeatedly.
versionconnections per second
5.1.6520712
5.5.2824000
5.5.25Maria28073
5.5.25Maria,ThreadPool27653
5.6.937800

The whole sysbench command I used is as below.
[remote_client]$ sysbench --test=oltp --oltp-table-size=2000000 
 --max-requests=1000000 --mysql-table-engine=innodb --db-ps-mode=disable
 --mysql-engine-trx=yes --oltp-read-only --oltp-skip-trx --oltp-dist-type=special
 --oltp-reconnect-mode=query --oltp-connect-delay=0 --db-driver=mysql
 --mysql-user=root --mysql-host=$server_host --mysql-port=$server_port
 --num-threads=100 run
I tested 1000+ connections and got similar numbers.

As you can see, 5.6 is nearly 2x faster than 5.1.

There are many performance improvements in 5.6, so I'm not sure which fix contributed the most. I assume the biggest one is that THD (quite a large C++ class inside MySQL) destructor is no longer called during holding a global mutex. Prior to 5.6, THD destructor is called under a global LOCK_thread_count mutex. This is not efficient. I experienced some serious connection/disconnection problems caused by the global mutex in MySQL Cluster a few years ago, and there were some fixes in MySQL Cluster source tree. Now in 5.6, THD destructor is called outside of the LOCK_thread_count mutex as below. This is great news.
5.1/5.5: one_thread_per_connection_end() -> unlink_thd()
  (void) pthread_mutex_lock(&LOCK_thread_count);
  thread_count--;
  delete thd;
  ...

5.6.9: one_thread_per_connection_end()
  ...
  mysql_mutex_unlock(&LOCK_thread_count);
  delete thd;
5.6 also fixed some connection performance issues reported by Domas. Some of major issues are still ongoing. So once all of them are fixed, we can expect even better performance:)

Apparently persistent connection is much more efficient than non-persistent, but this is not always possible. In addition to that, 5.6 improves query performance in high concurrency as well. I believe many production engineers will welcome these performance improvements.

Tuesday, September 18, 2012

Automating master failover is possible but needs care

I was asked from a few people about my opinion of the Github's recent service outage. As a creator of MHA, I have lots of MySQL failover experiences.
Here are my points about failover design. Most of them duplicate with Robert's points.


- "Too Many Connections" is not a reason to start automated failover
- Do not repeat failover

I know some unsuccessful failover stories that "1. failover happens because master is unreachable (getting too many connections errors) due to heavy loads 2. failover happens again because the new master is unreachable due to heavy loads 3. failover happens again....". On database servers, newly promoted master is slower because of poor cache hit rate. On traditional active/standby environment, database cache on the new master is empty so you'll suffer from 10x or even worse performance for the time being. On master/slave environment, slave has cache so performance is much better than standby server, but you can't expect better performance than master.
It does not make any sense to repeat failover within short time, and automated failover should not happen just because master is overloaded. If master is overloaded due to H/W problems (i.e. raid battery failure, disk block failure, etc), failover will need to be performed, but I think this can be manually done.

MHA does not start failover if specific error codes are returned (i.e. 1203: ER_TOO_MANY_USER_CONNECTIONS). And MHA does not repeat failover if 1. last failover failed with errors or 2. last failover happened within N minutes (480 minutes by default) ago.


- Do not failover if it is unclear master is dead

This is very important to avoid split brain. In many cases data inconsistency is more problematic than longer downtime. You need to make sure on the master that no mysqld process is running / will not run. Even though master is not reachable via TCP/IP connection attempts, mysqld may be just during crash recovery. Forcing shutdown on the mater (power off) is my favorite approach, but may take long time depending on H/W.
MHA has a helper script to kill (i.e. power off) master. When I developed MHA, I spent long time for investigating how to speed up shutting down machines.


- Prepare tools for manual failover

There are some cases that automating failover is really scary - typical example is a datacenter failure. If the whole datacenter is not reachable, it is not easy to automatically check master's status, and probably remotely shutting down master is not possible. And it would be unclear when the datacenter is recovered. In such cases I think automated failover should not be performed, but manual failover should be done. Proper alerts should be sent immediately, so that DBAs can start analyzing problems and start manual failover quickly. On master/slave environments, slaves' relay log positions might be different each other. Checking all slaves' status and if needed fixing consistency by parsing relay logs is painful. MHA will be helpful in such situations, and actually I have used MHA many more times for manual failover than automated failover.


Monday, March 26, 2012

Relocating to US and joining Facebook

I recently joined Facebook. I relocated to Redwood City (less than 10km from Facebook Menlo Park office) and I commute by bicycle (or caltrain + shuttle on rainy days). I'm currently taking a bootcamp training with Lachlan and other new employees, and will soon join database team.

I really enjoyed MySQL life at DeNA. This was actually the first time experience for me to manage hundreds to thousands of rapidly growing servers. I believe DeNA is the most technically advanced MySQL users in Japan. As you may know, DeNA is a creator of HandlerSocket NoSQL plugin. I created MHA for automating master failover and semi-online master switch. When I worked as a consultant at MySQL/Sun/Oracle, it was very difficult to allocate enough time for creating new products since I spent most of the time for 1-5 day consulting delivery to achieve sales/billing targets. I like short-term consulting, but I like long-term development/enhancement engagements, too. This was the biggest reason why I joined DeNA in 2010. Profitable and large services companies like DeNA have had many technically interesting problems, and it makes sense to allocate enough time for creating useful tools. I really appreciate all database and infrastructure team members, and social gaming developers at DeNA. I can still recommend Japanese software geeks to join DeNA.

As you know, Facebook is the most advanced MySQL users in the world. I'm excited to work on huge global environments and to work with world-famous colleagues. I need some time to get accustomed to a life in the Bay Area, but I hope it won't take long time. I'm looking forward to seeing many ex-MySQL colleagues and friends based in San Francisco and Bay Area. See you at upcoming MySQL Conference in Santa Clara!

Monday, February 27, 2012

Speaking at Game Developer Conference 2012

I believe most of my blog readers are not game developers, but I'll have a talk "Scaling and Stabilizing Large Server Side Infrastructure" in March 8th at Game Developer Conference (GDC) 2012.
Social games are very interesting from devops perspective. Social games tend to grow (and sometimes shrink) rapidly. To keep high enough ARPU (Average Revenue Per User), high availability and non-stop master maintenance are important for us. Users are very strict for immediate response, so performance optimizations (including minimal replication delay) are also high priority. I'd like to share our experiences with audiences, and I'd like to get feedbacks.

Sunday, January 8, 2012

MHA for MySQL 0.53 released

 MHA for MySQL (Master High Availability Manager and tools for MySQL) version 0.53 has been released. Downloads are available here.

 Here are release notes.

New features:
* Supporting RESET SLAVE ALL from MySQL 5.5.16
* Supporting "skip_reset_slave" parameter to avoid running CHANGE MASTER TO on the promoted slave
* Doing master's health checks optionally via MySQL CONNECT, in addition to SELECT
* Supporting "mysql --binary-mode" from MySQL 5.6.3
* Supporting ssh_host and ssh_port parameters
* Supporting ssh_options parameter
* Supporting --check_only for online master switch (dry-run)
* When doing online master switch, MHA checks whether long queries are running on the new master. This is important to reduce workloads on the new master. Query time limit can be controlled via --running_seconds_limit.
When executing SIGINT on online master switch, MHA tries to disconnect established connections via MHA.
* Additionally checking replication filtering rules on online master switch

Bug fixes:
* MHA Manager looks for relay-log.info in wrong location when setting relay_log_info_file
* Wrong option for master_ip_failover_script
* Timeout settings for SSH connection health check does not always work
* Modifying a rpm spec file to create valid rpm package for 64bit RHEL6
* Forcing more strict ssh checking. Originally MHA checks master's reachability by just connecting via SSH and exiting with return code 0. This in some cases does not work especially if SSH works but data files are not accessible. In this fix, MHA checks master's ssh reachability by executing save_binary_logs command (dry run). MHA Client also needs to be updated to 0.53.
* Zombie process remains on master ping timeout
* Do not execute SET GLOBAL read_only=(0|1) if not needed

How to upgrade


 Upgrading MHA from older version to 0.53 is easy. Simply stop masterha_manager program, replace all MHA Node and MHA Manager packages to 0.53, and restart masterha_manager. You do not need to stop mysqld or MySQL Replication.


MHA for MySQL, project Info


Project top page: http://code.google.com/p/mysql-master-ha/

Documentation: http://code.google.com/p/mysql-master-ha/wiki/TableOfContents?tm=6

Source tarball, deb and rpm packages (stable release): http://code.google.com/p/mysql-master-ha/downloads/list

The latest source repository (development tree, url changed): https://github.com/yoshinorim/mha4mysql-manager (Manager source) and https://github.com/yoshinorim/mha4mysql-node (Per-MySQL server source)

Commercial support for MHA is available from SkySQL.