MySQL

“How to monitor MySQL performance” with Percona Cloud Tools: June 25 webinar

MySQL Performance Blog - Wed, 2014-06-18 13:58

We recently released a new version of Percona Cloud Tools with MySQL monitoring capabilities. Join me June 25 and learn the details about all of the great new features inside Percona Cloud Tools – which is now free in beta. The webinar is titled “Monitoring All (Yes, All!) MySQL Metrics with Percona Cloud Tools” and begins at 10 a.m. Pacific time.

In addition to MySQL metrics, Percona Cloud Tools also monitors OS performance-related stats. The new Percona-agent gathers metrics with fine granularity (up to once per second), so you are able to see any of these metrics updated real-time.

During the webinar I’ll explain how the new Percona-agent works and how to configure it. And I’ll demonstrate the standard dashboard with the most important MySQL metrics and how to read them to understand your MySQL performance.

Our goal with the new implementation was to make installation as easy as possible. Seriously it should not take so much effort as it has in the past to get visibility into your MySQL performance. We also wanted to provide as much visibility as possible.

Please take a moment and register now for the webinar. I also encourage you, if you haven’t already, to sign up for access to the free Percona Cloud Tools beta ahead of time. At the end of the next week’s webinar you’ll know how to use the Percona-agent and will be able to start monitoring MySQL in less than 15 minutes!

See you June 25 and in the meantime you can check out our previous related posts: “From zero to full visibility of MySQL in 3 minutes with Percona Cloud Tools” and “Introducing the 3-Minute MySQL Monitor.”

The post “How to monitor MySQL performance” with Percona Cloud Tools: June 25 webinar appeared first on MySQL Performance Blog.

Categories: MySQL

MySQL Backup Service from Percona

MySQL Performance Blog - Tue, 2014-06-17 12:00

The Percona Backup Service managed service launched today. It ensures properly configured backups run successfully as scheduled on customer provided backup storage – whether on premise, in the cloud, or a hybrid. Backup issues or production data recovery are efficiently handled by Percona Managed Services technicians with deep knowledge of MySQL.

As we state in our white papers, “MySQL backup and recovery are a foundational piece of any infrastructure. A well-tested backup and recovery system can be the difference between a minor outage and the end of a business.” While MySQL backups are “foundational,” they still require constant management, and the most important use of a backup, namely recovery, is often complex.

The Percona MySQL Backup Service is available for companies using any variant of single-node or clustered MySQL — on premise or in the cloud. This includes MySQL, Percona Server, MariaDB, and Percona XtraDB Cluster.

Reliable MySQL Backup Services

Developed by MySQL experts with decades of operational experience, the Percona MySQL Backup Service is based on widely adopted open source database backup software solutions such as Percona XtraBackup. Monitoring software is installed to ensure MySQL backups run as expected and alert the Percona Managed Services team to any issues. Percona experts on duty 24x7x365 resolve problems before the availability of backups is impacted and can implement a full, partial, or point in time recovery to minimize downtime and data loss on production servers.

MySQL backup data sets are secure and compliant with regulatory requirements. 256 bit encryption meets or exceeds common security and compliance requirements. Internal procedures ensure that backups are managed with extreme care and are only stored safely on secure servers. Backups are available for on-demand recovery to comply with HIPAA, SOX, or PCI requirements.

The status of current and past backups is easily accessible through the Percona MySQL Backup Service customer portal. The portal includes instructions on how to use the backups to restore data for routine purposes such as restoration of development databases.

Efficient MySQL Data Recovery

Percona Backup Service technical experts respond within 30 minutes to ensure that production data is recovered quickly and as completely as possible. We can recover:

  • Data back to the last full capture image
  • Specific tables which saves significant time when only a single table or set of tables needs to be recovered
  • Full data to a specific point in time which ensures an application can be recovered to the same state as when the data was lost

Unlike database-as-a-service solutions, the Percona Backup Service can recover specific tables and full data to a specific point in time because we create additional backup points.

Cost Effective and Highly Flexible

Our MySQL backup-as-a-service solution costs less than managing backups in-house. Our 24x7x365 team serves multiple customers so the cost of the Percona MySQL Backup Service is lower than having someone on staff to manage backups. We also use a proprietary backup approach which significantly reduces the size of backup data sets, requiring much less storage space than conventional methods.

The Percona MySQL Backup Service accommodates any combination of MySQL server and backup server locations. We can work with MySQL server and backup servers on premise or in the cloud and can even configure the process to store one backup set on premise and another in the cloud for additional protection. This configuration flexibility means we can meet a wide range of data availability and budget needs.

MySQL Backup Service Features

The Percona Backup Service is designed so backups will run smoothly and reliably. The following features are included:

  • Customer portal for anytime access to current and past backup status as well as instructions on how to restore the MySQL backups for non-production purposes
  • Efficient data recovery for production issues with full, partial, and point in time recovery options
  • A high level of security with 256 bit encryption and backups only stored on the customer’s secure servers
  • Regulatory compliance with backups available for on-demand recovery to comply with HIPAA, SOX, and PCI requirements
  • Lower cost than managing MySQL backups in-house with 24x7x365 monitoring and issue resolution combined with a proprietary backup approach which significantly reduces the size of the backup data set versus conventional methods
  • Ability to accommodate any combination of MySQL server and backup server locations – on premise, in the cloud, or a hybrid
  • Flexible configuration options which enable the service to meet a wide range of data availability and budget requirements
Learn More

Learn more about our MySQL Backup Service solution as well as register for our upcoming “MySQL Backup and Recovery Best Practices” webinar on June 18, 2014. Contact us now to learn more about the Percona Backup Service and how we can ensure your backups are ready when you need them!

The post MySQL Backup Service from Percona appeared first on MySQL Performance Blog.

Categories: MySQL

mydumper [less] locking

MySQL Performance Blog - Fri, 2014-06-13 14:15
In this post I would like to review how my dumper for MySQL works from the point of view of locks. Since 0.6 serie we have different options, so I will try to explain how they work

As you may know mydumper is multithreaded and this adds a lot of complexity compared with other logical backup tools as it also needs to coordinate all threads with the same snapshot to be consistent. So let review how mydumper does this with the default settings.

By default mydumper uses 4 threads to dump data and 1 main thread

Main Thread
  • FLUSH TABLES WITH READ LOCK
Dump Thread X
  • START TRANSACTION WITH CONSISTENT SNAPSHOT;
  • dump non-InnoDB tables
Main Thread
  • UNLOCK TABLES
Dump Thread X
  • dump InnoDB tables
As you can see in this case we need FTWRL for two things, coordinate transaction’s snapshots and dump non-InnoDB tables in a consistent way. So we have have global read lock until we dumped all non-InnoDB tables.What less locking does is this:Main Thread
  • FLUSH TABLES WITH READ LOCK
Dump Thread X
  • START TRANSACTION WITH CONSISTENT SNAPSHOT;
 LL Dump Thread X
  • LOCK TABLES non-InnoDB
Main Thread
  • UNLOCK TABLES
 LL Dump Thread X
  • dump non-InnoDB tables
  • UNLOCK non-InnoDB
Dump Thread X
  • dump InnoDB tables

So now the global read lock its in place until less-locking threads lock non-InnoDB tables, and this is really fast. The only downsize is that it uses double the amount of threads, so for the default (4 threads) we will end up having 9 connections, but always 4 will be running at the same time.

Less-locking really helps when you have MyISAM or ARCHIVE that are not heavily updated by production workload, also you should know that LOCK TABLE … READ LOCAL allows non conflicting INSERTS on MyISAM so if you use that tables to keep logs (append only) you will not notice that lock at all.

For the next release we will implement backup locks that will avoid us to run FTWRL.

The post mydumper [less] locking appeared first on MySQL Performance Blog.

Categories: MySQL

Announcing Percona XtraBackup 2.2.3 GA

MySQL Performance Blog - Thu, 2014-06-12 14:13

Percona is glad to announce the release of Percona XtraBackup 2.2.3 on June 12th 2014. Downloads are available from our download site here and Percona Software Repositories.

Percona XtraBackup enables backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backup. The new 2.2.3 GA version offers improved performance, enterprise-grade security, and lower resource usage.

This release is the first GA (Generally Available) stable release in the 2.2 series.

New Features:

  • Percona XtraBackup implemented support for Backup Locks.
  • Percona XtraBackup can now store backup history on the server itself in a special table created for that purpose.
  • Percona XtraBackup has been rebased on MySQL 5.6.17.

Bugs Fixed:

  • Fixed the InnoDB redo log incompatibility with 5.1/5.5 server and compressed tables which was introduced by the upstream fix in MySQL 5.6.11 that could make InnoDB crash on recovery when replaying redo logs created on earlier versions. Bug fixed #1255476.
  • Percona XtraBackup did not flush the InnoDB REDO log buffer before finalizing the log copy. This would only become a problem when the binary log coordinates were used after restoring from a backup: the actual data files state after recovery could be inconsistent with the binary log coordinates. Bug fixed #1320685.
  • innobackupex now sets wsrep_causal_reads to 0 before executing FLUSH TABLES WITH READ LOCK if the server is a member of the Galera cluster. Bug fixed #1320441.
  • storage/innobase/xtrabackup/CMakeLists.txt now honors the XB_DISTRIBUTION environment variable when configuring innobackupex.pl to innobackupex. Bug fixed #1320856.
  • Percona XtraBackup does not add XtraDB-specific fields when preparing a
    backup of an upstream MySQL server. Bug fixed #988310.
  • Information about backup type and parameters is now stored in the
    xtrabackup_info file in the backup directory. Bug fixed #1133017.
  • When backup locks are used, xtrabackup_slave_info should be written under BINLOG lock instead of TABLE lock. Bug fixed #1328532.

Release notes with all the bugfixes for Percona XtraBackup 2.2.3 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

The post Announcing Percona XtraBackup 2.2.3 GA appeared first on MySQL Performance Blog.

Categories: MySQL

Percona Server 5.6.17-66.0 is now available

MySQL Performance Blog - Wed, 2014-06-11 18:20

 

Percona is glad to announce the release of Percona Server 5.6.17-66.0 on June 11, 2014. Downloads are available here and from the Percona Software Repositories.

Based on MySQL 5.6.17, including all the bug fixes in it, Percona Server 5.6.17-66.0 is the current GA release in the Percona Server 5.6 series. All of Percona’s software is open-source and free, all the details of the release can be found in the 5.6.17-66.0 milestone at Launchpad.

 

New Features:

  • MySQL benchmarks (sql-bench directory in the MySQL distribution) has been made compatible with the TokuDB storage engine.
  • Percona Server has ported MariaDB code enhancement for start transaction with consistent snapshot. This enhancement makes binary log positions consistent with InnoDB transaction snapshots.
  • TokuDB Storage engine is now available as a separate package that can be installed along with the Percona Server 5.6.17-66.0. This feature is currently considered Release Candidate quality.
  • Percona Server has implemented ability to clone a snapshot created by START TRANSACTION WITH CONSISTENT SNAPSHOT in another session.
  • Percona Server 5.6 now includes HandlerSocket in addition to Percona Server 5.5.

Bugs Fixed:

  • Fix for #1225189 introduced a regression in Percona Server 5.6.13-61.0 which could lead to an error when running mysql_install_db. Bug fixed #1314596.
  • InnoDB could crash if workload contained writes to compressed tables. Bug fixed #1305364.
  • GUI clients such as MySQL Workbench could not authenticate with a user defined with auth_pam_compat plugin. Bug fixed #1166938.
  • Help in Percona Server 5.6 command line client was linking to Percona Server 5.1 manual. Bug fixed #1198775.
  • InnoDB redo log resizing could crash when XtraDB changed page tracking was enabled. Bug fixed #1204072.
  • Audit Log wasn’t parsing escape characters correctly in the OLD format. Bug fixed #1313696.
  • Percona Server version was reported incorrectly in Debian/Ubuntu packages. Bug fixed #1319670.

Other bugs fixed: #1318537 (upstream #72615), #1318874, #1285618, #1272732, #1314568, #1271178, and #1323014.

Release notes for Percona Server 5.6.17-66.0 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

The post Percona Server 5.6.17-66.0 is now available appeared first on MySQL Performance Blog.

Categories: MySQL

Measure the impact of MySQL configuration changes with Percona Cloud Tools

MySQL Performance Blog - Wed, 2014-06-11 07:00

When you make a change to your MySQL configuration in production it would be great to know the impact (a “before and after” type of picture). Some changes are obvious. For many variables proper values can be determined beforehand, i.e. innodb_buffer_pool_size or innodb_log_file_size. However, there is 1 configuration variable which is much less obvious for many people working with MySQL: query_cache.

The idea of query cache is great, however, there are a lot of issues with MySQL query cache, one of the most important issues is query cache mutex which can cause a severe contention on the CPU bound workloads. In MySQL 5.6 you can remove the mutex by disabling the query cache completely (set query_cache_type=0).

There is a big issue with disabling query_cache though and it is not a technical issue. The issue is how do you convince your boss or dev team/operations team that query cache is bad. Almost all measurements available in MySQL will tell you that the query cache is utilized: there will be a good number of Qcache_hits. On the other hand you may see the contention in processlist: for example you can see one of those states associates with query cache contention:  Waiting for query cache lock or Waiting on query cache mutex (if you are running Percona Server).

Now you use Percona Cloud Tools (with Percona Server) to actually see how query cache is used globally and on per query basis.

Query cache visualization

Percona Server has this very useful additional feature: if you set log_slow_verbosity”  option to “microtime, query_plan, innodb” it will also log the information if the query was a query cache hit or query cache miss. This can give you a valuable inside for the query cache real utilization.

Percona Cloud Tools will be really helpful here as it will visualize this information and will actually see “before and after” graph. (It will set “log_slow_verbosity” option to “microtime, query_plan, innodb” for you if you set Slow log verbosity = Full in the agent configuration options).

Here is the example. I’ve disabled the query cache and then looked at the total query and lock time.

As we can see the total query time (across all queries) decreased significantly. The Average QPS on this picture is actually a little bit misleading and should be named “Queries running at the same time” (it is calculated as query count / time). The above graphs shows clear contention on the query cache level.

Just to confirm, we can look at the number of query cache hits:

The number of Query Cache “hits” dropped to 0 when we disabled the query cache.

Please note: if your workload is readonly and you are running the same complex query over and over again, query cache may be helpful. Usually, in normal circumstances however, the query cache can cause contention and reduce the response time (as you can see above).

Percona Cloud Tools is a free beta, please give it a try and share your experience in the comments. If you want to learn more about it, join the June 25 webinar hosted by Vadim Tkachenko titled, “Monitoring All (Yes, All!) MySQL Metrics with Percona Cloud Tools.” The webinar, which starts at 10 a.m. Pacific time, is free but I recommend registering now to reserve your spot.

 

The post Measure the impact of MySQL configuration changes with Percona Cloud Tools appeared first on MySQL Performance Blog.

Categories: MySQL

Architecture and Design of MySQL-powered applications: June 11 Webinar

MySQL Performance Blog - Mon, 2014-06-09 20:21

The architecture of MySQL-powered applications is one of my favorite topics to talk about. It’s a very important topic because if you do not get the architecture right then you’re very likely to fail with your project – either from the standpoint of failing with performance, high availability or security requirements… or failing to deliver on time and at the planned cost.

It’s also a great topic because there is so much knowledge available these days about MySQL-powered applications. MySQL has been around for a rather long time compared with many other solutions – and now we know what architectures have enabled people to build successful MySQL-powered applications and grow them to hundreds of millions of users as well as what applications did not work out.

This level of maturity really allows us to essentially take “off-the-shelf” MySQL architectures that can be used to build very successful applications with very low risk, at least when it comes to the database backend.

June 11 at 10 a.m. Pacific

On Wednesday, June 11 at 10 a.m. Pacific I’ll be leading  a webinar titled, “Architecture and Design of MySQL Powered Applications” during which I’ll be covering a lot of high-level scenarios such as “architectures that have been proven to be successful.” I’ll also discuss the architectures best suited for different kinds of applications and different kind of teams. Additionally, I’ll address what’s changed in the MySQL space in the last few years and how those changes have impacted modern MySQL architecture design approaches.

I hope to see you June 11. Register now to reserve your spot!

The post Architecture and Design of MySQL-powered applications: June 11 Webinar appeared first on MySQL Performance Blog.

Categories: MySQL

Using MySQL 5.6 Performance Schema in multi-tenant environments

MySQL Performance Blog - Mon, 2014-06-09 14:33

Hosting a shared MySQL instance for your internal or external clients (“multi-tenant”) was always a challenge. Multi-tenants approach or a “schema-per-customer” approach is pretty common nowadays to host multiple clients on the same MySQL sever. One of issues of this approach, however, is the lack of visibility: it is hard to tell how many resources (queries, disk, cpu, etc) each user will use.

Percona Server contains userstats Google patch, which will allow you to get the resource utilization per user. The new MySQL 5.6 performance_schema has even more instrumentation which can give you a better visibility on per-user or per-schema/per-database level. And if you are running MySQL 5.6.6 or higher, Performance Schema is already enabled (with minimum set of “instrumentation” thou) and ready to use. In this post I will share examples of using Performance Schema for multi-tenant environments.

Overview

If you want to use Performance Schema, make sure it is enabled (enabled by default starting with MySQL 5.6.6):

mysql> show global variables like 'performance_schema'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | performance_schema | ON | +--------------------+-------+ 1 row in set (0.00 sec)

Performance_schema provides you with the “raw” metrics and it may be difficult to select data from it. The good news is that you can use the “sys” schema project by Mark Leith. The “sys” schema (old name: ps_helper) is a collection of views and stored procedures which will provide you with reports in human readable format. The installation is easy, download it from github and run:

$ mysql -u root -p < ./sys_56.sql

(it will only create database “sys” and a set of views and stored procedures/stored functions in it)

Examples

For the multi-tenant environment the most interesting is resource utilization breakdown. Lets say you want to “charge per IO” similar to Amazon RDS for MySQL model. You can now run this simple query against “sys” schema, which will use Performance Schema in MySQL 5.6 to generate report (in my example all users starts with “a”):

mysql sys> select * from user_summary_by_file_io where user like 'a%'; +-------+------------+-------------+ | user | ios | io_latency | +-------+------------+-------------+ | api01 | 3061469563 | 4.04h | | api03 | 912296937 | 1.36h | | api02 | 815473183 | 1.22h | | app01 | 9704381 | 00:01:06.33 | | app02 | 1160149 | 8.18 s | | app03 | 1424065 | 7.23 s | +-------+------------+-------------+ 6 rows in set (0.06 sec)

If you need more extensive metrics you can use this report:

mysql sys> select * from user_summary where user like 'a%' limit 10; +-------+------------+-------------------+-----------------------+-------------+------------+-----------------+---------------------+-------------------+--------------+ | user | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_hosts | +-------+------------+-------------------+-----------------------+-------------+------------+-----------------+---------------------+-------------------+--------------+ | api01 | 1837898366 | 192.29h | 376.65 us | 2221018 | 3064724488 | 4.05h | 22 | 73748519 | 1 | | api02 | 833712609 | 206.83h | 893.11 us | 1164 | 836753563 | 1.26h | 7 | 246730 | 1 | | api03 | 988875004 | 222.03h | 808.29 us | 1156 | 943363030 | 1.43h | 8 | 247639 | 1 | | app01 | 1864159 | 4.01h | 7.75 ms | 46756 | 9704623 | 00:01:06.33 | 0 | 300004 | 1 | | app02 | 812520 | 00:20:51.69 | 1.54 ms | 42513 | 1164954 | 8.37 s | 0 | 122994 | 1 | | app03 | 811558 | 00:21:59.57 | 1.63 ms | 42310 | 1429373 | 7.40 s | 0 | 123110 | 1 | +-------+------------+-------------------+-----------------------+-------------+------------+-----------------+---------------------+-------------------+--------------+ 6 rows in set

Or a breakdown per user per statement:

mysql sys> select user, statement, total, rows_sent, rows_examined, rows_affected, full_scans from user_summary_by_statement_type where user = 'api01' limit 100; +-------+-------------+-----------+-----------+---------------+---------------+------------+ | user | statement | total | rows_sent | rows_examined | rows_affected | full_scans | +-------+-------------+-----------+-----------+---------------+---------------+------------+ | api01 | insert | 368405774 | 0 | 0 | 494179199 | 0 | | api01 | commit | 376276686 | 0 | 0 | 0 | 0 | | api01 | select | 476129532 | 761038004 | 1067363348 | 0 | 0 | | api01 | update | 142896430 | 0 | 142896437 | 141978066 | 0 | | api01 | begin | 376280424 | 0 | 0 | 0 | 0 | | api01 | show_fields | 2215016 | 310102240 | 310102240 | 0 | 2215016 | | api01 | Quit | 73421361 | 0 | 0 | 0 | 0 | | api01 | change_db | 17258767 | 0 | 0 | 0 | 0 | | api01 | delete | 7896 | 0 | 7896 | 7896 | 0 | | api01 | rollback | 3711 | 0 | 0 | 0 | 0 | +-------+-------------+-----------+-----------+---------------+---------------+------------+ 10 rows in set (0.04 sec)

If you are using  “schema per customer” approach you get get the per-schema resource utilization using sys.schema_table_statistics. Example (from Peter Zaitsev’s webinar):

mysql [localhost] {msandbox} > select * from schema_table_statistics where table_schema='sbtest' limit 1 G *************************** 1. row *************************** table_schema: sbtest table_name: sbtest rows_fetched: 158764154 fetch_latency: 1.37h rows_inserted: 378901 insert_latency: 00:07:17.38 rows_updated: 1136714 update_latency: 00:45:40.08 rows_deleted: 378902 delete_latency: 00:03:00.34 io_read_requests: 636003 io_read: 9.70 GiB io_read_latency: 00:28:12.01 io_write_requests: 203925 io_write: 3.11 GiB io_write_latency: 17.26 s io_misc_requests: 2449 io_misc_latency: 3.87 s

This report may be really slow if you have lots of tables. If you are only interested in disk utilization per database you can directly query the performance_schema:

SELECT extract_schema_from_file_name(fsbi.file_name) AS table_schema, SUM(fsbi.count_read) AS io_read_requests, sys.format_bytes(SUM(fsbi.sum_number_of_bytes_read)) AS io_read, sys.format_time(SUM(fsbi.sum_timer_read)) AS io_read_latency, SUM(fsbi.count_write) AS io_write_requests, sys.format_bytes(SUM(fsbi.sum_number_of_bytes_write)) AS io_write, sys.format_time(SUM(fsbi.sum_timer_write)) AS io_write_latency, SUM(fsbi.count_misc) AS io_misc_requests, sys.format_time(SUM(fsbi.sum_timer_misc)) AS io_misc_latency FROM performance_schema.file_summary_by_instance AS fsbi GROUP BY table_schema ORDER BY io_write_requests DESC limit 10; +--------------------+------------------+------------+-----------------+-------------------+------------+------------------+------------------+-----------------+ | table_schema | io_read_requests | io_read | io_read_latency | io_write_requests | io_write | io_write_latency | io_misc_requests | io_misc_latency | +--------------------+------------------+------------+-----------------+-------------------+------------+------------------+------------------+-----------------+ | binlogs | 6943921992 | 19.57 TiB | 2.46h | 1124408959 | 6.60 TiB | 2.18h | 914411 | 00:03:55.75 | | d1 | 146448 | 2.24 GiB | 1.97 s | 22768241 | 241.44 GiB | 00:12:57.97 | 5095817 | 00:12:50.16 | | stats1 | 853 | 1.81 MiB | 97.12 ms | 1042768 | 15.93 GiB | 10.27 s | 292303 | 22.74 s | | stats2 | 3024 | 11.22 MiB | 781.96 ms | 750182 | 11.45 GiB | 9.32 s | 267780 | 50.84 s | | relaylogs | 105065 | 925.71 MiB | 312.82 ms | 447607 | 925.71 MiB | 1.19 s | 72169 | 530.70 ms | | o1268 | 2078 | 3.02 MiB | 7.40 ms | 1591 | 203.97 KiB | 1.17 ms | 2693 | 92.34 ms | +--------------------+------------------+------------+-----------------+-------------------+------------+------------------+------------------+-----------------+

Edit: if you also need to get a quick “hot tables” report with focus on disk utilization (io requests) you can use this query:

SELECT concat(extract_schema_from_file_name(fsbi.file_name), '.', extract_table_from_file_name(fsbi.file_name) ) as unique_table_name, SUM(fsbi.count_read) AS io_read_requests, sys.format_bytes(SUM(fsbi.sum_number_of_bytes_read)) AS io_read, sys.format_time(SUM(fsbi.sum_timer_read)) AS io_read_latency, SUM(fsbi.count_write) AS io_write_requests, sys.format_bytes(SUM(fsbi.sum_number_of_bytes_write)) AS io_write, sys.format_time(SUM(fsbi.sum_timer_write)) AS io_write_latency, SUM(fsbi.count_misc) AS io_misc_requests, sys.format_time(SUM(fsbi.sum_timer_misc)) AS io_misc_latency FROM performance_schema.file_summary_by_instance AS fsbi GROUP BY unique_table_name ORDER BY io_write_requests DESC limit 10;

This will give you a breakdown per table (and will include system files as well).

Conclusion

Performance_schema in MySQL 5.6 is very useful tool and can help you to get better visualization in MySQL multi-user (multi-tenant) installations. It can also do much more, i.e. find all queries with temporary tables or profile locks and mutex. “sys” schema provide you with the useful pre-defined reports, the table (view) names are almost self explanatory:

+-----------------------------------------------+ | Tables_in_sys | +-----------------------------------------------+ | innodb_buffer_stats_by_schema | | innodb_buffer_stats_by_table | ... | processlist | ... | schema_tables_with_full_table_scans | | schema_unused_indexes | | statement_analysis | | statements_with_errors_or_warnings | | statements_with_full_table_scans | | statements_with_runtimes_in_95th_percentile | | statements_with_sorting | | statements_with_temp_tables | ...

If you are using MySQL 5.6, performance_schema will give a additional valuable inside.

The post Using MySQL 5.6 Performance Schema in multi-tenant environments appeared first on MySQL Performance Blog.

Categories: MySQL

Why did we develop percona-agent in Go?

MySQL Performance Blog - Wed, 2014-06-04 15:12

We recently open-sourced our percona-agent and if you check out the source code, you’ll find that it is written in the Go programming language (aka Golang). For those not up to speed, the percona-agent is a real-time client-side agent for Percona Cloud Tools.

Our requirements are quite demanding for our agents. This one is software that works on a real production server, so it must be fast, reliable, lightweight and easy to distribute. Surprisingly enough, binaries compiled by Go fit these characteristics.

There are of course alternatives that we considered. On the scripting side: Perl, Python, PHP, Ruby et al. These are not necessarily fast, and the distribution is also interesting. We have enough experience with Percona Toolkit and Perl’s “modules dependencies hell.”

On a high-end level side, there is C / C++ and I am sure we could produce an efficient agent. However we also have experience in the distribution of Percona Server / Percona XtraDB Cluster / Percona XtraBackup. Well, I have had enough with different versions of Ubuntus, Red Hats, CentOSes and the rest of the flavors of Linux.

And, yeah, there is Java, but let me just say that we are not the Java sort of developers.

So what is so charming about Go? Go feels like a scripting language, but produces executable binaries. I see it as having the ability to attack performance issues on two sides. First is the performance of software developers: They are more productive working with scripting-like languages. Second is the performance of a final product: Native self-executable binaries are more efficient than a script running through a interpreter.

It is worth noting that included batteries (a set of packages that are coming with Go) are excellent, and in many cases that will be just enough to get you going and produce software that is quite complex. And if that is not enough, there is also a list of packages and projects for Go.

Of course, there are some limitations you will find in Go (some of them are temporary I hope). These are:

1. The list of supported platforms is limited… FreeBSD (release 8 and above), Linux (ARM not supported), Mac OS X and Windows. There are no packages for Solaris yet.
2. A good MySQL driver is still a work in progress. the most advanced is Go-MySQL-Driver
3. Go comes with built-in testing capabilities, but our testing enthusiast, Daniel, says it is not enough to build a complicated testing suite.
4. There is no support of “generics” (or “templates” if you are in C++ world). Basically it means that if you developed a data structure that works with integers, you will need to copy-paste-replace to make it working with floats. Yes, there are workarounds like using a “parent-to-everything” type “interface{}”, but often it is not efficient and just looks ugly.

There is also no automatic type-conversion between int and floats, so if you need to do complex math which involves ints and floats, you may end up with a lot back-and-forth conversions, i.e. int(math.Floor(t.epsilon*float64(t.count*2)))

To finish, I would like to invite you to my webinar, “Monitoring All (Yes, All!) MySQL Metrics with Percona Cloud Tools” on Wednesday, June 25 at 10 a.m. Pacific Daylight Time, where I will talk on the new features in Percona Cloud Tools, including our new percona-agent.

The post Why did we develop percona-agent in Go? appeared first on MySQL Performance Blog.

Categories: MySQL

Percona Toolkit 2.2.8 is now available

MySQL Performance Blog - Wed, 2014-06-04 12:53

Percona is glad to announce the release of Percona Toolkit 2.2.8 on June 4th, 2014 (Downloads are available here and from the Percona Software Repositories). This release is the current GA (Generally Available) stable release in the 2.2 series.

New Features:

Bugs Fixed:

All Percona software is free and open-source. Details of the release can be found in the release notes and the 2.2.8 milestone at Launchpad. Bugs can be reported on the Percona Toolkit launchpad bug tracker.

The post Percona Toolkit 2.2.8 is now available appeared first on MySQL Performance Blog.

Categories: MySQL

Do not trust vmstat IOwait numbers

MySQL Performance Blog - Tue, 2014-06-03 10:00

I’ve been running a benchmark today on my old test box with conventional hard drives (no raid with BBU) and noticed something unusual in the CPU utilization statistics being reported.

The benchmark was run like this:

sysbench --num-threads=64 --max-requests=0 --max-time=600000 --report-interval=10 --test=oltp --db-driver=mysql --oltp-dist-type=special --oltp-table-size=1000000 --mysql-user=root --mysql-password=password run

Which means: create 64 threads and hammer the database with queries as quickly as possible. As the test was run on the localhost I would expect the benchmark to completely saturate the system – being either using CPU or being blocked on IO nature of this benchmark so it does not spend a lot on database locks, especially as this system has just 2 cores.

Looking at VMSTAT however I noticed this:

[root@smt1 mysql]# vmstat 10 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 13 0 0 880156 40060 2140824 0 0 12 750 895 1045 32 8 54 6 0 0 0 0 877116 40060 2141312 0 0 0 1783 2185 23112 44 10 41 5 0 15 0 0 872648 40068 2141960 0 0 0 1747 2204 25743 41 11 46 2 0 0 0 0 868056 40068 2142604 0 0 0 1803 2164 26224 40 11 44 5 0 17 1 0 863216 40068 2143160 0 0 0 1875 1948 23020 36 9 50 5 0 0 0 0 858384 40168 2143656 0 0 0 1063 1855 21116 32 9 45 14 0 23 0 0 855848 40176 2144232 0 0 0 1755 2036 23181 36 10 48 6 0 49 0 0 851248 40184 2144648 0 0 0 1679 2313 22832 45 10 40 5 0 10 0 0 846292 40192 2145248 0 0 0 1911 1980 23185 36 9 50 4 0 0 0 0 844260 40196 2145868 0 0 0 1757 2152 26387 39 11 45 5 0 0 3 0 839296 40196 2146520 0 0 0 1439 2104 25096 38 10 50 1 0

Looking at last few columns we have IO wait typically being in single digit percents where the “idle” CPU floats in 40-50% range. You can also see it visually in the graph from Percona Cloud Tools

So the system should be completely saturated with the load but instead we have 50% of CPU idle and iowait is typically in single digits?

Running SHOW PROCESSLIST can give us some good idea in this case:

mysql> show processlist; +-----+---------------+-----------------+--------+---------+------+----------------+--------------------------------------------------------------------+-----------+---------------+-----------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read | +-----+---------------+-----------------+--------+---------+------+----------------+--------------------------------------------------------------------+-----------+---------------+-----------+ | 1 | root | localhost:39624 | NULL | Sleep | 0 | | NULL | 0 | 0 | 0 | | ... | 32 | root | localhost | sbtest | Execute | 0 | NULL | COMMIT | 0 | 0 | 0 | | 33 | root | localhost | sbtest | Execute | 0 | NULL | COMMIT | 0 | 0 | 0 | | 34 | root | localhost | sbtest | Execute | 0 | Sorting result | SELECT c from sbtest where id between 365260 and 365359 order by c | 0 | 0 | 0 | | 35 | root | localhost | sbtest | Execute | 0 | NULL | COMMIT | 0 | 0 | 0 | | 36 | root | localhost | sbtest | Execute | 0 | NULL | COMMIT | 0 | 0 | 0 | | 37 | root | localhost | sbtest | Execute | 0 | NULL | COMMIT | 0 | 0 | 0 | | 38 | root | localhost | sbtest | Execute | 0 | Writing to net | DELETE from sbtest where id=496460 | 0 | 1 | 1 | | 39 | root | localhost | sbtest | Execute | 0 | NULL | COMMIT | 0 | 0 | 0 | ... | 89 | root | localhost | sbtest | Execute | 0 | NULL | COMMIT | 0 | 0 | 0 | | 90 | root | localhost | sbtest | Execute | 0 | NULL | COMMIT | 0 | 0 | 0 | | 91 | root | localhost | sbtest | Execute | 0 | NULL | COMMIT | 0 | 0 | 0 | | 268 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0 | 0 | 0 | +-----+---------------+-----------------+--------+---------+------+----------------+--------------------------------------------------------------------+-----------+---------------+-----------+ 68 rows in set (0.00 sec)

So most of the queries are stalling in COMMIT stage. Checking setting we can see:

mysql> select @@innodb_flush_log_at_trx_commit; +----------------------------------+ | @@innodb_flush_log_at_trx_commit | +----------------------------------+ | 1 | +----------------------------------+ 1 row in set (0.00 sec)

Which means Percona Server is running in the fully ACID mode, which with slow hard drives without BBU means commit is causing syncing data to the disk which is very expensive.

So we figured out what is really happening… yet if we’re really waiting on disk a lot why iowait does not show that?
To answer that lets see how IOwait is really computed.

IOwait is type of Idle time (and in earlier Linux versions it wasincluded in Idle) – when we have the process/thread which would be runable if it would not be waiting on IO we consider this iowait, if it is not runable because of something else (waiting on mutex, network etc) it is considered idle.

Writes to Innodb log file are serialized – so only one process will be doing log write (and sync) while all others waiting to be able to commit will be waiting on some form of synchronization mechanism to be notified when the commit is complete as part of group commit.

What is the take away? Take iowait% numbers from vmstat/iostat with grain of salt. It is possible there is much more to gain than faster IO subsystem (or proper configuration) than iowait numbers show. Consider the iowait%+idle% of the CPU as “cpu available” which could be potentially put to the good use with improving IO subsystem and minimizing contention

To illustrate that lets take a look at the CPU graph again – now as I’ve set innodb_flush_log_at_trx_commit=2

As we can see from the graph we have reclaimed much more than 6-7% of idle time we had – the user CPU usage increased to 70% and idle+iowait is now 10-15%; the throughput of the system has increased about 60% – much more than “eliminating iowait” would allow.

The post Do not trust vmstat IOwait numbers appeared first on MySQL Performance Blog.

Categories: MySQL

Percona Server 5.5.37-35.1 is now available

MySQL Performance Blog - Tue, 2014-06-03 09:30

Percona is glad to announce the release of Percona Server 5.5.37-35.1 on June 3rd, 2014 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.5.37, including all the bug fixes in it, Percona Server 5.5.37-35.1 is now the current stable release in the 5.5 series. All of Percona‘s software is open-source and free, all the details of the release can be found in the 5.5.37-35.1 milestone at Launchpad.

Bugs Fixed:

  • InnoDB could crash if workload contained writes to compressed tables. Bug fixed #1305364.
  • GUI clients such as MySQL Workbench could not authenticate with a user defined with auth_pam_compat plugin. Bug fixed #1166938.
  • Help in Percona Server 5.5 command line client was linking to Percona Server 5.1 manual. Bug fixed #1198775.
  • Audit Log wasn’t parsing escape characters correctly in the OLD format. Bug fixed #1313696.
  • Percona Server version was reported incorrectly in Debian/Ubuntu packages. Bug fixed #1319670.

Other bugs fixed: #1272732, #1219833, #1271178, and #1314568.

Release notes for Percona Server 5.5.37-35.1 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

The post Percona Server 5.5.37-35.1 is now available appeared first on MySQL Performance Blog.

Categories: MySQL

Using InfiniDB MySQL server with Hadoop cluster for data analytics

MySQL Performance Blog - Mon, 2014-06-02 16:58

In my previous post about Hadoop and Impala I benchmarked performance of analytical queries in Impala.

This time I’ve tried InfiniDB for Hadoop (open-source version) on the modern hardware with an 8-node Hadoop cluster. One of the main advantages (at least for me) of InifiniDB for Hadoop is that it stores the data inside the Hadoop cluster but uses the MySQL server to execute queries. This allows for an easy “migration” of existing analytical tools. The results are quite interesting and promising.

Quick How-To

The InfiniDB documentation is not very clear on step-by-step instructions so I’ve created this quick guide:

  1. Install Hadoop cluster (minimum install will work). I’ve used Cloudera Manager (CDH5) to compare the speed of InfiniDB to Cloudera Impala. Install the tools in the “Pre-requirements” sections of InfiniDB for Hadoop Manual
  2. Install the InfiniDB for Hadoop binaries on 1 Hadoop node (you can choose any node).  This will install InfiniDB and its version of MySQL (based on MySQL 5.1).
  3. After installation it will tell you the variables to set and run the postConfigure script. Example:
    export JAVA_HOME=/usr/java/jdk1.6.0_31 export LD_LIBRARY_PATH=/usr/java/jdk1.6.0_31/jre/lib/amd64/server . /root/setenv-hdfs-20 /usr/local/Calpont/bin/postConfigure
  4. The postConfigure script will ask the questions. Couple imfortant notes:
  • Make sure to use HDFS as a “type of Data Storage”.
  • The performance module 1 (pm1) should point to the host (hostname and IP) you are running the postConfigure script on. Other pm(s) should point to other Hadoop nodes

When installation is finished you will be able to login into MySQL server, it uses script called ibdmysql which will call mysql cli with the correct socket and port. Check that the infiniDB is enabled by running “show engines”, InfiniDB should be in the list.

The next step will be importing data.

Data import

First we will need to create a MySQL table with “engine=InfiniDB”:

CREATE TABLE `ontime` ( `YearD` int(11) NOT NULL, `Quarter` tinyint(4) DEFAULT NULL, `MonthD` tinyint(4) DEFAULT NULL, `DayofMonth` tinyint(4) DEFAULT NULL, `DayOfWeek` tinyint(4) DEFAULT NULL, `FlightDate` date DEFAULT NULL, ... ) ENGINE=InfiniDB DEFAULT CHARSET=latin1

Second,  I’ve used the cpimport to load the data. It turned out it is much more efficient and easier to load 1 big file rather than 20×12 smaller files (original “ontime” data is 1 file per month), so I’ve exported the “Ontime” data from MySQL table and created 1 big file “ontime.psv”.

I used the following command to export data into InfiniDB:

[root@n0 ontime]# /usr/local/Calpont/bin/cpimport -s '|' ontime ontime ontime.psv 2014-05-20 15:12:58 (18787) INFO : Running distributed import (mode 1) on all PMs... 2014-05-20 15:25:28 (18787) INFO : For table ontime.ontime: 155083620 rows processed and 155083620 rows inserted. 2014-05-20 15:25:28 (18787) INFO : Bulk load completed, total run time : 751.561 seconds

The data is stored in Hadoop:

[root@n0 ontime]# hdfs dfs -du -h /usr/local/Calpont 1.4 G /usr/local/Calpont/data1 1.4 G /usr/local/Calpont/data2 1.4 G /usr/local/Calpont/data3 1.4 G /usr/local/Calpont/data4 1.4 G /usr/local/Calpont/data5 1.4 G /usr/local/Calpont/data6 1.4 G /usr/local/Calpont/data7 1.4 G /usr/local/Calpont/data8

The total size of the data is 8×1.4G = 11.2G (compressed). To compare the size of the same dataset in Impala Parquet format is 3.6G. Original size was ~60G.

[root@n0 ontime]# hdfs dfs -du -h /user/hive/warehouse 3.6 G /user/hive/warehouse/ontime_parquet_snappy

Now we can run the 2 queries I’ve tested before:

1. Simple group-by

mysql> select yeard, count(*) from ontime group by yeard order by yeard; +-------+----------+ | yeard | count(*) | +-------+----------+ | 1988 | 5202096 | | 1989 | 5041200 | | 1990 | 5270893 | | 1991 | 5076925 | | 1992 | 5092157 | | 1993 | 5070501 | | 1994 | 5180048 | | 1995 | 5327435 | | 1996 | 5351983 | | 1997 | 5411843 | | 1998 | 5384721 | | 1999 | 5527884 | | 2000 | 5683047 | | 2001 | 5967780 | | 2002 | 5271359 | | 2003 | 6488540 | | 2004 | 7129270 | | 2005 | 7140596 | | 2006 | 7141922 | | 2007 | 7455458 | | 2008 | 7009726 | | 2009 | 6450285 | | 2010 | 6450117 | | 2011 | 6085281 | | 2012 | 6096762 | | 2013 | 6369482 | | 2014 | 1406309 | +-------+----------+ 27 rows in set (0.22 sec)

2. The complex query from my original post:

mysql> select min(yeard), max(yeard), Carrier, count(*) as cnt, sum(if(ArrDelayMinutes>30, 1, 0)) as flights_delayed, round(sum(if(ArrDelayMinutes>30, 1, 0))/count(*),2) as rate FROM ontime WHERE DayOfWeek not in (6,7) and OriginState not in ('AK', 'HI', 'PR', 'VI') and DestState not in ('AK', 'HI', 'PR', 'VI') and flightdate < '2010-01-01' GROUP by carrier HAVING cnt > 100000 and max(yeard) > 1990 ORDER by rate DESC, cnt desc LIMIT 1000; +------------+------------+---------+----------+-----------------+------+ | min(yeard) | max(yeard) | Carrier | cnt | flights_delayed | rate | +------------+------------+---------+----------+-----------------+------+ | 2003 | 2009 | EV | 1454777 | 237698 | 0.16 | | 2003 | 2009 | FL | 1082489 | 158748 | 0.15 | | 2006 | 2009 | YV | 740608 | 110389 | 0.15 | | 2006 | 2009 | XE | 1016010 | 152431 | 0.15 | | 2003 | 2009 | B6 | 683874 | 103677 | 0.15 | | 2001 | 2009 | MQ | 3238137 | 448037 | 0.14 | | 2003 | 2005 | DH | 501056 | 69833 | 0.14 | | 2004 | 2009 | OH | 1195868 | 160071 | 0.13 | | 2003 | 2006 | RU | 1007248 | 126733 | 0.13 | | 1988 | 2009 | UA | 9593284 | 1197053 | 0.12 | | 2003 | 2006 | TZ | 136735 | 16496 | 0.12 | | 1988 | 2001 | TW | 2656286 | 280283 | 0.11 | | 1988 | 2009 | AA | 10568437 | 1183786 | 0.11 | | 1988 | 2009 | CO | 6023831 | 673354 | 0.11 | | 1988 | 2009 | DL | 11866515 | 1156048 | 0.10 | | 2003 | 2009 | OO | 2654259 | 257069 | 0.10 | | 1988 | 2009 | AS | 1506003 | 146920 | 0.10 | | 2007 | 2009 | 9E | 577244 | 59440 | 0.10 | | 1988 | 2009 | US | 10276862 | 990995 | 0.10 | | 1988 | 2009 | NW | 7601727 | 725460 | 0.10 | | 1988 | 2005 | HP | 2607603 | 235675 | 0.09 | | 1988 | 2009 | WN | 12722174 | 1107840 | 0.09 | | 2005 | 2009 | F9 | 307569 | 28679 | 0.09 | | 1988 | 1991 | PA | 203401 | 19263 | 0.09 | +------------+------------+---------+----------+-----------------+------+ 24 rows in set (0.86 sec)

The same query in impala (on the same hardware) runs for 7.18 seconds:

[n8.local:21000] > select min(yeard), max(yeard), Carrier, count(*) as cnt, sum(if(ArrDelayMinutes>30, 1, 0)) as flights_delayed, round(sum(if(ArrDelayMinutes>30, 1, 0))/count(*),2) as rate FROM ontime_parquet_snappy WHERE DayOfWeek not in (6,7) and OriginState not in ('AK', 'HI', 'PR', 'VI') and DestState not in ('AK', 'HI', 'PR', 'VI') and flightdate < '2010-01-01' GROUP by carrier HAVING cnt > 100000 and max(yeard) > 1990 ORDER by rate DESC LIMIT 1000; Query: select min(yeard), max(yeard), Carrier, count(*) as cnt, sum(if(ArrDelayMinutes>30, 1, 0)) as flights_delayed, round(sum(if(ArrDelayMinutes>30, 1, 0))/count(*),2) as rate FROM ontime_parquet_snappy WHERE DayOfWeek not in (6,7) and OriginState not in ('AK', 'HI', 'PR', 'VI') and DestState not in ('AK', 'HI', 'PR', 'VI') and flightdate < '2010-01-01' GROUP by carrier HAVING cnt > 100000 and max(yeard) > 1990 ORDER by rate DESC LIMIT 1000 +------------+------------+---------+----------+-----------------+------+ | min(yeard) | max(yeard) | carrier | cnt | flights_delayed | rate | +------------+------------+---------+----------+-----------------+------+ | 2003 | 2009 | EV | 1454777 | 237698 | 0.16 | | 2003 | 2009 | FL | 1082489 | 158748 | 0.15 | | 2006 | 2009 | XE | 1016010 | 152431 | 0.15 | | 2006 | 2009 | YV | 740608 | 110389 | 0.15 | | 2003 | 2009 | B6 | 683874 | 103677 | 0.15 | | 2001 | 2009 | MQ | 3238137 | 448037 | 0.14 | | 2003 | 2005 | DH | 501056 | 69833 | 0.14 | | 2004 | 2009 | OH | 1195868 | 160071 | 0.13 | | 2003 | 2006 | RU | 1007248 | 126733 | 0.13 | | 1988 | 2009 | UA | 9593284 | 1197053 | 0.12 | | 2003 | 2006 | TZ | 136735 | 16496 | 0.12 | | 1988 | 2001 | TW | 2656286 | 280283 | 0.11 | | 1988 | 2009 | CO | 6023831 | 673354 | 0.11 | | 1988 | 2009 | AA | 10568437 | 1183786 | 0.11 | | 1988 | 2009 | US | 10276862 | 990995 | 0.10 | | 2007 | 2009 | 9E | 577244 | 59440 | 0.10 | | 1988 | 2009 | DL | 11866515 | 1156048 | 0.10 | | 2003 | 2009 | OO | 2654259 | 257069 | 0.10 | | 1988 | 2009 | NW | 7601727 | 725460 | 0.10 | | 1988 | 2009 | AS | 1506003 | 146920 | 0.10 | | 1988 | 1991 | PA | 203401 | 19263 | 0.09 | | 1988 | 2009 | WN | 12722174 | 1107840 | 0.09 | | 1988 | 2005 | HP | 2607603 | 235675 | 0.09 | | 2005 | 2009 | F9 | 307569 | 28679 | 0.09 | +------------+------------+---------+----------+-----------------+------+ Returned 24 row(s) in 7.18s

Conclusion and charts

To summaries I’ve created the following charts:

Simple query:

As we can see InfiniDB looks pretty good here. It also uses MySQL protocol, so existing application which uses MySQL will be able to work here without any additional “connectors”.

One note regarding my query example: the “complex” query is designed in a way that will make it hard to use any particular set of index; this query will have to scan the >70% of the table to generate the resultset. That is why it is so slow in MySQL compared to columnar databases. Another “issue” is that the table is very wide and most of the columns are declared as varchar (table is not normalized), which makes it large in MySQL. All this will make it ideal for columnar storage and compression. Other cases may not show that huge of a difference.

So far I was testing with small data (60G), I will plan to run big data benchmark next.

The post Using InfiniDB MySQL server with Hadoop cluster for data analytics appeared first on MySQL Performance Blog.

Categories: MySQL

High Availability with MySQL Fabric: Part II

MySQL Performance Blog - Fri, 2014-05-30 07:00

This is the third post in our MySQL Fabric series. If you missed the previous two, we started with an overall introduction, and then a discussion of MySQL Fabric’s high-availability (HA) features. MySQL Fabric was RC when we started this series, but it went GA recently. You can read the press release here, and see this blog post from Oracle’s Mats Kindahl for more details. In our previous post, we showed a simple HA setup managed with MySQL Fabric, including some basic failure scenarios. Today, we’ll present a similar scenario from an application developer’s point of view, using the Python Connector for the examples. If you’re following the examples on these posts, you’ll notice that the UUID for servers will be changing. That’s because we rebuild the environment between runs. Symbolic names stay the same though. That said, here’s our usual 3 node setup:

[vagrant@store ~]$ mysqlfabric group lookup_servers mycluster Command : { success = True return = [{'status': 'SECONDARY', 'server_uuid': '3084fcf2-df86-11e3-b46c-0800274fb806', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.70.101'}, {'status': 'SECONDARY', 'server_uuid': '35cc3529-df86-11e3-b46c-0800274fb806', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.70.102'}, {'status': 'PRIMARY', 'server_uuid': '3d3f6cda-df86-11e3-b46c-0800274fb806', 'mode': 'READ_WRITE', 'weight': 1.0, 'address': '192.168.70.103'}] activities = }

For our tests, we will be using this simple script:

import mysql.connector from mysql.connector import fabric from mysql.connector import errors import time config = { 'fabric': { 'host': '192.168.70.100', 'port': 8080, 'username': 'admin', 'password': 'admin', 'report_errors': True }, 'user': 'fabric', 'password': 'f4bric', 'database': 'test', 'autocommit': 'true' } fcnx = None print "starting loop" while 1: if fcnx == None: print "connecting" fcnx = mysql.connector.connect(**config) fcnx.set_property(group='mycluster', mode=fabric.MODE_READWRITE) try: print "will run query" cur = fcnx.cursor() cur.execute("select id, sleep(0.2) from test.test limit 1") for (id) in cur: print id print "will sleep 1 second" time.sleep(1) except errors.DatabaseError: print "sleeping 1 second and reconnecting" time.sleep(1) del fcnx fcnx = mysql.connector.connect(**config) fcnx.set_property(group='mycluster', mode=fabric.MODE_READWRITE) fcnx.reset_cache() try: cur = fcnx.cursor() cur.execute("select 1") except errors.InterfaceError: fcnx = mysql.connector.connect(**config) fcnx.set_property(group='mycluster', mode=fabric.MODE_READWRITE) fcnx.reset_cache()

This simple script requests a MODE_READWRITE connection and then issues selects in a loop. The reason it requests a RW connector is that it makes it easier for us to provoke a failure, as we have two SECONDARY nodes that could be used for queries if we requested a MODE_READONLY connection. The select includes a short sleep to make it easier to catch it in SHOW PROCESSLIST. In order to work, this script needs the test.test table to exist in the mycluster group. Running the following statements in the PRIMARY node will do it:

mysql> create database if not exists test; mysql> create table if not exists test.test (id int unsigned not null auto_increment primary key) engine = innodb; mysql> insert into test.test values (null);

Dealing with failure

With everything set up, we can start the script and then cause a PRIMARY failure. In this case, we’ll simulate a failure by shutting down mysqld on it:

mysql> select @@hostname; +-------------+ | @@hostname | +-------------+ | node3.local | +-------------+ 1 row in set (0.00 sec) mysql> show processlist; +----+--------+--------------------+------+------------------+------+-----------------------------------------------------------------------+----------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+--------+--------------------+------+------------------+------+-----------------------------------------------------------------------+----------------------------------------------+ | 5 | fabric | store:39929 | NULL | Sleep | 217 | | NULL | | 6 | fabric | node1:37999 | NULL | Binlog Dump GTID | 217 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | | 7 | fabric | node2:49750 | NULL | Binlog Dump GTID | 216 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | | 16 | root | localhost | NULL | Query | 0 | init | show processlist | | 20 | fabric | 192.168.70.1:55889 | test | Query | 0 | User sleep | select id, sleep(0.2) from test.test limit 1 | +----+--------+--------------------+------+------------------+------+-----------------------------------------------------------------------+----------------------------------------------+ 5 rows in set (0.00 sec) [vagrant@node3 ~]$ sudo service mysqld stop Stopping mysqld: [ OK ]

While this happens, here’s the output from the script:

will sleep 1 second will run query (1, 0) will sleep 1 second will run query (1, 0) will sleep 1 second will run query (1, 0) will sleep 1 second will run query sleeping 1 second and reconnecting will run query (1, 0) will sleep 1 second will run query (1, 0) will sleep 1 second will run query (1, 0)

The ‘sleeping 1 second and reconnecting’ line means the script got an exception while running a query (when the PRIMARY node was stopped, waited one second and then reconnected. The next lines confirm that everything went back to normal after the reconnection. The relevant piece of code that handles the reconnection is this:

fcnx = mysql.connector.connect(**config) fcnx.set_property(group='mycluster', mode=fabric.MODE_READWRITE) fcnx.reset_cache()

If fcnx.reset_cache() is not invoked, then the driver won’t connect to the xml-rpc server again, but will use it’s local cache of the group’s status instead. As the PRIMARY node is offline, this will cause the reconnect attempt to fail. By reseting the cache, we’re forcing the driver to connect to the xml-rpc server and fetch up to date group status information. If more failures happen and there is no PRIMARY (or candidate for promotion) node in the group, the following error is received:

will run query (1, 0) will sleep 1 second will run query sleeping 1 second and reconnecting will run query Traceback (most recent call last): File "./reader_test.py", line 34, in cur = fcnx.cursor() File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 1062, in cursor self._connect() File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 1012, in _connect exc)) mysql.connector.errors.InterfaceError: Error getting connection: No MySQL server available for group 'mycluster'

Running without MySQL Fabric

As we have discussed in previous posts, the XML-PRC server can become a single point of failure under certain circumstances. Specifically, there are at least two problem scenarios when this server is down:

  • When a node goes down
  • When new connection attempts are made

The first case is obvious enough. If MySQL Fabric is not running and a node fails, there won’t be any action, and clients will get an error whenever they send a query to the failed node. This is worse if the PRIMARY fails, as failover won’t happen and the cluster will be unavailable for writes. The second case means that while MySQL Fabric is not running, no new connections to the group can be established. This is because when connecting to a group, MySQL Fabric-aware clients first connect to the XML-RPC server to get a list of nodes and roles, and only then use their local cache for decisions. A way to mitigate this is to use connection pooling, which reduces the need for establishing new connections, and therefore minimises the chance of failure due to MySQL Fabric being down. This, of course, is assuming that something is monitoring MySQL Fabric ensuring some host provides the XML-PRC service. If that is not the case, failure will be delayed, but it will eventually happen anyway. Here is an example of what happens when MySQL Fabric is down and the PRIMARY node goes down:

Traceback (most recent call last): File "./reader_test.py", line 35, in cur.execute("select id, sleep(0.2) from test.test limit 1") File "/Library/Python/2.7/site-packages/mysql/connector/cursor.py", line 491, in execute self._handle_result(self._connection.cmd_query(stmt)) File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 1144, in cmd_query self.handle_mysql_error(exc) File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 1099, in handle_mysql_error self.reset_cache() File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 832, in reset_cache self._fabric.reset_cache(group=group) File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 369, in reset_cache self.get_group_servers(group, use_cache=False) File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 478, in get_group_servers inst = self.get_instance() File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 390, in get_instance if not inst.is_connected: File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 772, in is_connected self._proxy._some_nonexisting_method() # pylint: disable=W0212 File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/xmlrpclib.py", line 1224, in __call__ return self.__send(self.__name, args) File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/xmlrpclib.py", line 1578, in __request verbose=self.__verbose File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 272, in request raise InterfaceError("Connection with Fabric failed: " + msg) mysql.connector.errors.InterfaceError: Connection with Fabric failed:

This happens when a new connection attempt is made after resetting the local cache.

Making sure MySQL Fabric stays up

As of this writing, it is the user’s responsibility to make sure MySQL Fabric is up and running. This means you can use whatever you feel comfortable with in terms of HA, like Pacemaker. While it does add some complexity to the setup, the XML-RPC server is very simple to manage and so a simple resource manager should work. For the backend, MySQL Fabric is storage engine agnostic, so an easy way to resolve this could be to use a small MySQL Cluster set up to ensure the backend is available. MySQL’s team blogged about such a set up here. We think the ndb approach is probably the simplest for providing HA at the MySQL Fabric store level, but believe that MySQL Fabric itself should provide or make it easy to achieve HA at the XML-RPC server level. If ndb is used as store, this means any node can take a write, which in turns means multiple XML-PRC instances should be able to write to the store concurrently. This means that in theory, improving this could be as easy as allowing Fabric-aware drivers to get a list of Fabric servers instead of a single IP and port to connect to.

What’s next

In the past two posts, we’ve presented MySQL Fabric’s HA features, seen how it handles failures at the node level, how to use MySQL databases with a MySQL Fabric-aware driver, and what remains unresolved for now. In our next post, we’ll review MySQL Fabric’s Sharding features.

The post High Availability with MySQL Fabric: Part II appeared first on MySQL Performance Blog.

Categories: MySQL

How MySQL ‘queries’ and ‘questions’ are measured

MySQL Performance Blog - Thu, 2014-05-29 10:00

MySQL has status variables “questions” and “queries” which are rather close but also a bit different, making it confusing for many people. The manual describing it might not be very easy to understand:

Queries The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. It does not count COM_PING or COM_STATISTICS commands. Questions The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.

In a nutshell if you’re not using prepared statements the big difference between those is what “Questions” would count stored procedure calls as a single statement where “Queries” will count all statements called inside of stored procedures as well.

There seems to be also more subtle difference between them:

Running on MySQL 5.6.17 having created a new connection I see…

mysql> show status like "questions"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Questions | 2 | +---------------+-------+ 1 row in set (0.00 sec) mysql> show status like "queries"; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | Queries | 21241470 | +---------------+----------+ 1 row in set (0.00 sec)

Which tells me what “queries” is a global status variable while “questions” is a session and can be used to see how many statements were issued to the server through the current connection.

There is also a global questions variable that shows the number for a server since the start:

mysql> show global status like "questions"; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | Questions | 23375398 | +---------------+----------+ 1 row in set (0.00 sec)

When it comes to global values neither “queries” nor “questions” are reset when FLUSH STATUS is called.

What the manual does not describe in detail though is: When exactly those counters are incremented? This might look irrelevant but really it is not, especially if you’re capturing those values with high resolution and using them to diagnose non trivial MySQL performance incidents.

If you would count queries when they start – when a spike in the amount of queries in the given second could be due to the spike in the traffic, however, and if you measure queries at the completion – spikes could also mean that some critical resource became available which allowed for many queries to complete. Think for example of table-level locks or row-level locks with Innodb as very common cause.

So what is the case with MySQL? MySQL increments this counter Before executing the query so you may well see a very uniform rate of questions when actually a lot of queries were started but were not completing quickly due to waiting on some resource.

To check for unusual numbers of queries running concurrently and struggling to complete in time looking at threads_running status variable is a great idea.

The post How MySQL ‘queries’ and ‘questions’ are measured appeared first on MySQL Performance Blog.

Categories: MySQL

From zero to full visibility of MySQL in 3 minutes with Percona Cloud Tools

MySQL Performance Blog - Wed, 2014-05-28 19:16

First, I would like to invite you to my webinar, “Monitoring All (Yes, All!) MySQL Metrics with Percona Cloud Tools,” on Wednesday, June 25 at 10 a.m. Pacific Daylight Time, where I will talk on the
new features in Percona Cloud Tools, including monitoring capabilities.

In this post I’d like to show the cool and interesting things we’ve implemented in Percona Cloud Tools, including the recently released agent that Daniel also talks about here in this post.

Basically our agent allows users to collect ALL MySQL metrics plus important environment’s metrics, like CPU, memory, IO stats.

And when I talk all MySQL it is:

  • Metrics from SHOW GLOBAL STATUS (I counted 571 entries on my Percona Server 5.6 with TokuDB)
  • Metrics from INFORMATION_SCHEMA.INNODB_METRICS ( 214 entries)
  • Data from SHOW GLOBAL VARIABLES ( 522 entries).

So you see we collect more than 1000 points. We collect data every second, aggregate per minute and it becomes available with a 1-minute resolution, but with descriptive stats like min, max, avg, median, 5%-percentile, 95%-percentile of collected data. We found this represents data better than 1-sec points, which can be a quite noisy.

So for example this is how a chart with MySQL command counters looks like:

If you do not have MySQL, in fact, you can just monitor your OS without MySQL if you need to.
For example CPU stats

Please note you can see a value at any point in the past. We even can go to week range and see values several days ago

You can choose a custom timerange back to hours, days, weeks, etc with zoom-in capabilities.

Why do we need avg, min, max stats? Let see Peter’s graph from a server with periodical stalls.

Averaging metric smooths the line, and really hides the problem, while with min we are able to see that throughput drops to zero, that means that during some seconds the server did not execute any queries (which is essentially stalls)

More about the agent.

Our percona-agent is open source. This is our way of sharing our Go knowledge, and also you can check that we do not do anything insane on your server (like bitcoin mining or black magic regression modeling math).

You can see source code here https://github.com/percona/percona-agent:
and pre-compiled binaries are available from our website:

What is also interesting about our technology is that we use a permanent connection (based on WebSockets technology, so it looks like a connection to web browser) between an agent and our servers. This way
we support a bi-directional real-time communication between https://cloud.percona.com/ and an agent.

That means you can manage an agent and receive data updates at the same time. Pretty cool, yeah?

Our agent comes with “minimal efforts” in mind.

  • 1. There is minimal efforts to install agent. Basically it takes 3-minutes
    to download binaries, install them, and start seeing real-time updates of charts with MySQL metrics.
  • 2. Agent comes with self-update capabilities (not activated at this moment). Later you will need
    to worry if there is new version of agent is available, it will updated itself. We thought if Android Apps can do that, why can’t we?
  • 3. Minimal efforts for a maintenance: you do not need to install a dedicated server, configure and maintain database, care about its backups and availability. Basically no more hassle with Cacti configuration and managing a storage for it.

The goal of Percona Cloud Tools is to provide you with FULL visibility on what’s going on inside your system right now or at some point in the past, and to gain additional insights on your database server.

Our tools are useful not only if you have hundreds of database servers to manage, but pretty much for single installations, too. Well, of course, we always can run mysql -e "SHOW GLOBAL STATUS" , vmstat 10 ; iostat -dxm 10 manually when we need to troubleshoot something, but is it not useful to collect all this data automatically and be able to go to any point in the past?

You can register for the Beta right now. No invitation is needed, but it may take sometime for us to activate your account, we see a quite a demand right now and we need to prime our servers.

And what’s important, eventually our tools will require a payment, but we will always provide a free level, which will be useful enough for small accounts (this is not a bait-and-switch 30-days trial approach).

The post From zero to full visibility of MySQL in 3 minutes with Percona Cloud Tools appeared first on MySQL Performance Blog.

Categories: MySQL

Location for InnoDB tablespace in MySQL 5.6.6

MySQL Performance Blog - Wed, 2014-05-28 10:00

There is one new feature in MySQL 5.6 that didn’t get the attention it deserved (at least from me ) : “DATA DIRECTORY” for InnoDB tables.

This is implemented since MySQL 5.6.6 and can be used only at the creation of the table. It’s not possible to change the DATA DIRECTORY with an ALTER for a normal table (but it’s in some case with partitioned ones as you will see below). If you do so, the option will be just ignored:

mysql> CREATE TABLE `sales_figures` ( -> `region_id` int(11) DEFAULT NULL, -> `sales_date` date DEFAULT NULL, -> `amount` int(11) DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -> DATA DIRECTORY = '/tb1/'; Query OK, 0 rows affected (0.11 sec) mysql> alter table sales_figures engine=innodb data directory='/tb2/'; Query OK, 0 rows affected, 1 warning (0.21 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> show warnings; +---------+------+---------------------------------+ | Level | Code | Message | +---------+------+---------------------------------+ | Warning | 1618 | option ignored | +---------+------+---------------------------------+

You can read more information in the MySQL Manual: Specifying the Location of a Tablespace.

So it’s now possible if for example you use SSD or FusionIO disks to have the large log or archived table to cheaper disks as you won’t require fast random access for those table and then save some expensive diskspace.

The syntax is very simple:

mysql> CREATE TABLE `sales_figures` ( `region_id` int(11) DEFAULT NULL, `sales_date` date DEFAULT NULL, `amount` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='/tmp/tb1/' mysql> select @@datadir; +-----------------+ | @@datadir | +-----------------+ | /var/lib/mysql/ | +-----------------+

And in fact if we check on the filesystem:

# ls -lh /var/lib/mysql/fred/
total 20K
-rw-r--r-- 1 mysql mysql 65 May 23 22:30 db.opt
-rw-r--r-- 1 mysql mysql 8.5K May 23 22:30 sales_figures.frm
-rw-r--r-- 1 mysql mysql 31 May 23 22:30 sales_figures.isl

Not the new file .isl (referred as a link to the RemoteDatafile in the source code) that contains the location of the tablespace:

[root@imac2 tmp]# cat /var/lib/mysql/fred/sales_figures.isl
/tmp/tb1/fred/sales_figures.ibd

And indeed the tablespace is there:

[root@imac2 tmp]# ls -lh /tmp/tb1/fred/
total 96K
-rw-r--r-- 1 mysql mysql 96K May 23 22:30 sales_figures.ibd

This is really great ! And something even nicer, it finally works with partitioning too (before that was only possible for MyISAM tables):

mysql> CREATE TABLE sales_figures (region_id INT, sales_date DATE, amount INT) PARTITION BY LIST (region_id) ( PARTITION US_DATA VALUES IN(100,200,300) DATA DIRECTORY = '/tmp/tb1', PARTITION EU_DATA VALUES IN(400,500) DATA DIRECTORY = '/tmp/tb2/' );


[root@imac2 mysql]# ls -l /tmp/tb1/fred/sales_figures#P#US_DATA.ibd
-rw-rw---- 1 mysql mysql 98304 May 23 16:19 /tmp/tb1/fred/sales_figures#P#US_DATA.ibd

[root@imac2 mysql]# ls -l /tmp/tb2/fred/sales_figures#P#EU_DATA.ibd
-rw-rw—- 1 mysql mysql 98304 May 23 16:19 /tmp/tb2/fred/sales_figures#P#EU_DATA.ibd

So now you can have some partitions on fast disks and some on slower disks. This is great for historical partitioning.

For example you have a table orders partitioned by years as follow:

create table orders (id int, purchased DATE) partition by range (YEAR(purchased)) ( partition pre2012 values less than (2012) DATA DIRECTORY '/hdd/', partition pre2013 values less than (2013) DATA DIRECTORY '/hdd/', partition pre2014 values less than (2014) DATA DIRECTORY '/hdd/', partition current values less than MAXVALUE DATA DIRECTORY '/ssd/' );

Only the partition handling the orders for the current year is on SSD.
At the end of the year, you can recreate a new partition and move all the data for 2014 on slower disks:

mysql> ALTER TABLE orders REORGANIZE PARTITION `current` INTO ( partition pre2015 values less than (2015) DATA DIRECTORY '/hdd/', partition current values less than MAXVALUE DATA DIRECTORY '/ssd');

Notice that XtraBackup is also aware of these tablespaces on different locations and is able to deal with them.

There is currently only one issue is that with –copy-back, you need to have the full path created for the tablespaces not in the MySQL data directory.

So in the example above I had to create /tmp/tb1/fred and /tmp/tb2/fred before being able to run innobackupex –copy-back
(see bug 1322658).

I hope now that this important feature got some more visibility as it deserves it.

The post Location for InnoDB tablespace in MySQL 5.6.6 appeared first on MySQL Performance Blog.

Categories: MySQL

Semi-Sync replication performance in MySQL 5.7.4 DMR

MySQL Performance Blog - Tue, 2014-05-27 13:24

I was interested to hear about semi-sync replication improvements in MySQL’s 5.7.4 DMR release and decided to check it out.  I previously blogged about poor semi-sync performance and was pretty disappointed from semi-sync’s performance across WAN distances back then, particularly with many client threads.

The Test

The basic environment of these tests was:

  • AWS EC2 m3.medium instances
  • Master in us-east-1, slave in us-west-1 (~78ms ping RTT)
  • CentOS 6.5
  • innodb_flush_log_at_trx_commit=1
  • sync_binlog=1
  • Semi-sync replication plugin installed and enabled.
  • GTID’s enabled (except on 5.5)
  • sysbench 0.5 update_index.lua test, 60 seconds, 250k table size.
  • MySQL 5.7 was tested with both AFTER_SYNC and AFTER_COMMIT settings for rpl_semi_sync_master_wait_point
  • I tested Percona XtraDB Cluster 5.6 / Galera 3.5 as well by means of comparison

Without further ado, here’s the TpmC results I got for a single client thread:

 

These graphs are interactive, so mouse-over for more details. I’m using log scales to better highlight the differences.

The blue bars represent transactions per second (more is better).  The red bars represent average latency per transaction per client (less is better).  Remember these transactions are synchronously being copied across the US before the client can execute another.

The first test is our control:  Async allows ~273 TPS on a single thread.  Once we introduce synchronicity, we clearly see the bulk of the time is that round trip.  Note that MySQL 5.5 and 5.6 are a bit higher than MySQL 5.7 and Percona XtraDB Cluster, the latter of which show pretty similar results.

Adding parallelism

This gets more interesting to see if we redo the same tests, but with 32 test threads:

In the MySQL 5.5 and 5.6 tests, we can clearly see nasty serialization.  Both really don’t allow more performance than single threaded sysbench.  I was happy to see, however, that this seems to be dramatically improved in MySQL 5.7, nice job Oracle!

AFTER_SYNC and AFTER_COMMIT vary, but AFTER_SYNC is the default and is preferred over AFTER_COMMIT.  The reasoning here is AFTER_SYNC forces the semi-sync wait BEFORE the transaction is committed on the master.  The client still must wait for the semi-sync in AFTER_COMMIT, but other transactions may see its data on the master BEFORE we confirm the semi-sync slave has received it.  This is potentially bad because if the master crashed at that instant, clients may have read data from the master that did not make it to a failover slave.    This is a type of ‘phantom read’ and Yoshinori explains it in more detail here.

What about Percona XtraDB Cluster?

I also want to discuss the Percona XtraDB Cluster results, Galera here is somewhat slower than MySQL 5.7 semi-sync.  There may be some enhancements to Galera that can be made (competition is a good thing), but there are still some significant differences here:

  • Galera allows for writing on any and all nodes, semi-sync does not
  • Galera introduces the certification process to check for conflicts, Semi-sync does not
  • Galera is not 2-phase commit and transactions are not committed synchronously anywhere except the node originating the transaction.  So, it is similar to Semi-sync in this way.
  • I ran the Galera tests with no log-bin (Galera does not require it)
  • I ran the Galera tests with innodb_flush_log_at_trx_commit=1
  • I set the fc_limit on the second node really high to eliminate Flow control as a bottleneck.  In a live cluster, it would typically be needed.
  • Galera provides parallel slave threads for faster apply, but it doesn’t matter here because I set the fc_limit so high
TL;DR

Semi-sync in MySQL 5.7 looks like a great improvement.  Any form of synchronicity is always going to be expensive, particularly over 10s and 100s of milliseconds of latency.  With MySQL 5.7, I’d be much more apt to recommend semi-sync as an option than in previous releases.  Thanks to Oracle for investing here.

The post Semi-Sync replication performance in MySQL 5.7.4 DMR appeared first on MySQL Performance Blog.

Categories: MySQL

Installing Percona XtraDB Cluster 5.6 with the Docker open-source engine

MySQL Performance Blog - Mon, 2014-05-26 07:00

In my previous post, I blogged about using Percona Server with Docker and have shown you how fast and easy it was to create a virtual environment with just a few commands.

This time I will be showing you how to setup a three-node Percona XtraDB Cluster (PXC) 5.6 on the Docker open-source engine. Just to review Docker… “is an open-source engine that automates the deployment of any application as a lightweight, portable, self-sufficient container that will run virtually anywhere.”

In this case we will make use of a Dockerfile, think of this more like the Vagrantfile, it is a build script with a set of commands automating the creation of a new docker container.

For this case, we will use the following Dockerfile contents and use Ubuntu 12.04 instead of CentOS 6.5 as guest OS:

FROM ubuntu:precise MAINTAINER Percona.com <info@percona.com> # Upgrade RUN apt-get update && apt-get upgrade -y && apt-get dist-upgrade -y ENV DEBIAN_FRONTEND noninteractive RUN echo "deb http://repo.percona.com/apt precise main" >> /etc/apt/sources.list.d/percona.list RUN echo "deb-src http://repo.percona.com/apt precise main" >> /etc/apt/sources.list.d/percona.list RUN apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A RUN apt-get update RUN apt-get install -y percona-xtradb-cluster-56 qpress xtrabackup RUN apt-get install -y python-software-properties vim wget curl netcat

Create a my.cnf file and add the following:

[MYSQLD] user = mysql default_storage_engine = InnoDB basedir = /usr datadir = /var/lib/mysql socket = /var/run/mysqld/mysqld.sock port = 3306 innodb_autoinc_lock_mode = 2 log_queries_not_using_indexes = 1 max_allowed_packet = 128M binlog_format = ROW wsrep_provider = /usr/lib/libgalera_smm.so wsrep_node_address ={node_IP} wsrep_cluster_name="mydockerpxc" wsrep_cluster_address = gcomm://{node1_ip},{node2_ip},{node3_ip} wsrep_node_name = {node_name} wsrep_slave_threads = 4 wsrep_sst_method = xtrabackup-v2 wsrep_sst_auth = pxcuser:pxcpass [sst] streamfmt = xbstream [xtrabackup] compress compact parallel = 2 compress_threads = 2 rebuild_threads = 2

Build an image from the Dockerfile we just created.

root@Perconallc-Support /home/jericho.rivera/docker-test # docker build -rm -t ubuntu_1204/percona:galera56 .

The ‘docker build’ command will create a new image from the Dockerfile build script. This will take a few minutes to complete. You can check if the new image was successfully built:

root@Perconallc-Support /home/jericho.rivera/docker-test # docker images | grep ubuntu ubuntu_1204/percona galera56 c2adc932aaec 9 minutes ago 669.4 MB ubuntu 13.10 5e019ab7bf6d 13 days ago 180 MB ubuntu saucy 5e019ab7bf6d 13 days ago 180 MB ubuntu 12.04 74fe38d11401 13 days ago 209.6 MB ubuntu precise 74fe38d11401 13 days ago 209.6 MB

Now we will launch three containers with Percona XtraDB Cluster using the new docker image we have just created.

root@Perconallc-Support /home/jericho.rivera/docker-test # for n in {1..3}; do docker run --name dockerpxc$n -i -t -d ubuntu_1204/percona:galera56 bash; done

Check if the new containers were created:

root@Perconallc-Support /home/jericho.rivera/docker-test # docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES dd5bafb99108 ubuntu_1204/percona:galera56 bash 5 minutes ago Up 5 minutes dockerpxc3 01664cfcbeb7 ubuntu_1204/percona:galera56 bash 5 minutes ago Up 5 minutes dockerpxc2 2e44d8baee99 ubuntu_1204/percona:galera56 bash 5 minutes ago Up 5 minutes dockerpxc1

Get relevant information from the container using ‘docker inspect’ command which by default will show a JSON-format output on the terminal. Since we only need to get the IP address for each container just run the following commands:

root@Perconallc-Support /home/jericho.rivera/docker-test # docker inspect dockerpxc1 | grep IPAddress "IPAddress": "172.17.0.2", root@Perconallc-Support /home/jericho.rivera/docker-test # docker inspect dockerpxc2 | grep IPAddress "IPAddress": "172.17.0.3", root@Perconallc-Support /home/jericho.rivera/docker-test # docker inspect dockerpxc3 | grep IPAddress "IPAddress": "172.17.0.4",

Take note of the IP address because we will need them later.

wsrep_cluster_address=gcomm://172.17.0.2,172.17.0.3,172.17.0.4

Do the same for dockerpxc2 and dockerpxc3 nodes. To attach to a container you need to run ‘docker attach {node_name}’ (eg # docker attach dockerpxc1). To exit without stopping the containers you need to hit CTRL+p/CTRL+q, otherwise an explicit ‘exit’ command on the prompt will drop you out of the container and stop the container as well, as much as possible we try to avoid this. Also make sure to edit wsrep_node_name and wsrep_node_address accordingly.

Bootstrapping the Cluster

Next step is to start the first node in the cluster, or bootstrapping.

root@Perconallc-Support /home/jericho.rivera/docker-test # docker attach dockerpxc1 root@2e44d8baee99:# /etc/init.d/mysql bootstrap-pxc

After bootstrapping the first node, we can then prepare the first node for SST. That means we need to create the SST auth user, and in this case it is wsrep_ss_auth=pxcuser:pxcpass.

After adding the SST auth user on the first node the next step would be to start dockerpxc2 and dockerpxc3:

root@Perconallc-Support /home/jericho.rivera/docker-test # docker attach dockerpxc2 root@01664cfcbeb7:/# /etc/init.d/mysql start

After starting all nodes, check the status of the entire cluster:

root@2e44d8baee99:/# mysql -e "show global status like 'wsrep%';" +------------------------------+--------------------------------------+ | Variable_name | Value | +------------------------------+--------------------------------------+ | wsrep_local_state_uuid | 7ae2a03e-d71e-11e3-9e00-2f7c2c79edda | | wsrep_protocol_version | 5 | | wsrep_last_committed | 1 | | wsrep_replicated | 1 | | wsrep_replicated_bytes | 270 | | wsrep_repl_keys | 1 | | wsrep_repl_keys_bytes | 31 | | wsrep_repl_data_bytes | 175 | | wsrep_repl_other_bytes | 0 | | wsrep_received | 18 | | wsrep_received_bytes | 2230 | | wsrep_local_commits | 0 | | wsrep_local_cert_failures | 0 | | wsrep_local_replays | 0 | | wsrep_local_send_queue | 0 | | wsrep_local_send_queue_avg | 0.333333 | | wsrep_local_recv_queue | 0 | | wsrep_local_recv_queue_avg | 0.000000 | | wsrep_local_cached_downto | 1 | | wsrep_flow_control_paused_ns | 0 | | wsrep_flow_control_paused | 0.000000 | | wsrep_flow_control_sent | 0 | | wsrep_flow_control_recv | 0 | | wsrep_cert_deps_distance | 1.000000 | | wsrep_apply_oooe | 0.000000 | | wsrep_apply_oool | 0.000000 | | wsrep_apply_window | 1.000000 | | wsrep_commit_oooe | 0.000000 | | wsrep_commit_oool | 0.000000 | | wsrep_commit_window | 1.000000 | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_cert_index_size | 1 | | wsrep_causal_reads | 0 | | wsrep_cert_interval | 0.000000 | | wsrep_incoming_addresses | ,, | | wsrep_cluster_conf_id | 11 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | 7ae2a03e-d71e-11e3-9e00-2f7c2c79edda | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | wsrep_local_bf_aborts | 0 | | wsrep_local_index | 2 | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy <info@codership.com> | | wsrep_provider_version | 3.5(r178) | | wsrep_ready | ON | +------------------------------+--------------------------------------+

All three nodes are in the cluster!

Install net-tools to verify the default port for Galera.

root@dd5bafb99108:/# netstat -ant Active Internet connections (servers and established) Proto Recv-Q Send-Q Local Address Foreign Address State tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN tcp 0 0 172.17.0.4:33875 91.189.88.153:80 TIME_WAIT tcp 0 0 172.17.0.4:56956 172.17.0.2:4567 ESTABLISHED tcp 0 0 172.17.0.4:57475 172.17.0.3:4567 ESTABLISHED tcp6 0 0 :::3306 :::* LISTEN

Summary

I’ve shown you the following:

* Create a Dockerfile and my.cnf file
* Build a docker container using the created Dockerfile
* Made a few changes on some wsrep_ options on my.cnf
* Bootstrap the first node
* Start the other nodes of the cluster
* Check cluster status and confirmed all nodes are in the cluster

There are other ways to setup Docker with Percona XtraDB Cluster 5.6 such as using vagrant + docker which further automates the whole process or by using shell scripts, this article however shows you the basics of how to accomplish the same task.

On my next post, I will show how to setup Percona ClusterControl on another Docker container and adding this three-node PXC 5.6 cluster to it.

Read related posts here:

* Using MySQL Sandbox with Percona Server
* Testing Percona XtraDB Cluster with Vagrant
* Percona XtraDB Cluster: Setting up a simple cluster

The post Installing Percona XtraDB Cluster 5.6 with the Docker open-source engine appeared first on MySQL Performance Blog.

Categories: MySQL

Introducing the 3-Minute MySQL Monitor

MySQL Performance Blog - Fri, 2014-05-23 17:01

There are many cool, new things happening with Percona Cloud Tools.  To avoid “tl;dr” I will highlight only one new feature after a brief, general announcement.  The new feature is a 3-minute MySQL monitor.  I’ll blog later about other features.

The general announcement is: Last week we quietly released a brand-new agent called percona-agent, and we added MySQL and system monitoring to Percona Cloud Tools.  We also wrote a brand-new API from the ground up.  We call it all “PCT v2″.  For you it means a better experience and more features, all still free while we’re in beta.

One new feature in Percona Cloud Tools v2 is MySQL monitoring in 3 minutes, i.e. a 3-minute MySQL monitor.  Let’s be honest about monitoring: We know we should but we often don’t.  It’s like software testing and other best-practices.  As Monitorama earlier this month highlighted, the problem is not a lack of sufficiently advanced technology.  Products like Grok demonstrate this.  The problem is that setting up a meaningful MySQL monitor can be difficult.  The reason is simple: genearl-purpose monitoring solutions leave the user to answer and implement important questions like “which MySQL metrics should we monitor?”  To make an analogy: General-purpose monitoring solutions are sledge hammers and MySQL is a nail.  You can drive a nail with a sledge hammer, but it’s a lot easier (and probably safer) with a hammer.  The 3-minute MySQL monitor in Percona Cloud Tools v2 is a hammer.

The “3-minute” qualifier means that you can download and install percona-agent and have charts with MySQL metrics 3 minutes later.  The initial setup is really that quick and easy.  Give it a try; it’s free!  Just sign updownload percona-agent, and run its install script.

The Percona Cloud Tools MySQL monitor is still new and in development.  It cannot replace a general-purpose monitoring solution like Zabbix, and it does not have alerts, but that’s ok because its raison d’être is different: to make monitoring MySQL quick, easy, and meaningful.

We use Percona Cloud Tools internally for our production servers, but we’re developing it for you. (One reason for which percona-agent is free and open-source.)  Try it out and let us know what you think, especially if you run into problems.  Thanks in advanced, and stay tuned for more blog posts and webinars about Percona Cloud Tools, like Vadim’s upcoming webinar on June 25th: Monitoring All (Yes, All!) MySQL Metrics with Percona Cloud Tools.

The post Introducing the 3-Minute MySQL Monitor appeared first on MySQL Performance Blog.

Categories: MySQL
Syndicate content