Percona is glad to announce the release of Percona XtraBackup 2.1.2 for MySQL on May 18, 2013. Downloads are available from our download site here and Percona Software Repositories.

This release fixes number of high-priority bugs since version 2.1 became GA. It’s advised to upgrade your latest 2.1 version to 2.1.2. This release is the latest stable release in the 2.1 series.

Bugs Fixed:

• Using Perl’s DBD::MySQL package for server communication instead of spawning the MySQL command line client introduced a regression which caused innobackupex –galera-info option to fail. Bug fixed #1180672.
• The format of xtrabackup_galera_info was missing the ‘:’ separator between the values of wsrep_local_state_uuid and wsrep_last_committed. Bug fixed #1181222.
• innobackupex automatic version detection did not work correctly for latest Percona Server and MySQL 5.1 releases which could cause innobackupex to fail. Bugs fixed #1181092, #1181099 and #1180905.
• When backing up a server that is not a replication slave with the innobackupex –slave-info option, innobackupex failed with a fatal error. Replaced the fatal error with a diagnostic message about innobackupex –slave-info being ignored in such a case. Bug fixed #1180662.
• Low values for wait_timeout on the server could cause server to close the connection while backup is being taken. Fixed by setting the bigger value for wait_timeout option on the server to prevent server from closing connections if the global wait_timeout value is set too low. Bug fixed #1180922.

Other bug fixes: bug fixed #1177182.

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

The post Percona XtraBackup 2.1.2 for MySQL available for download appeared first on MySQL Performance Blog.

This is the second part in a two-part series comparing Virident’s vCache to FlashCache. The first part was focused on usability and feature comparison; in this post, we’ll look at some sysbench test results.

Disclosure: The research and testing conducted for this post were sponsored by Virident.

First, some background information. All tests were conducted on Percona’s Cisco UCS C250 test machine, and both the vCache and FlashCache tests used the same 2.2TB Virident FlashMAX II as the cache storage device. EXT4 is the filesystem, and CentOS 6.4 the operating system, although the pre-release modules I received from Virident required the use of the CentOS 6.2 kernel, 2.6.32-220, so that was the kernel in use for all of the benchmarks on both systems. The benchmark tool used was sysbench 0.5 and the version of MySQL used was Percona Server 5.5.30-rel30.1-465. Each test was allowed to run for 7200 seconds, and the first 3600 seconds were discarded as warmup time; the remaining 3600 seconds were averaged into 10-second intervals. All tests were conducted with approximately 78GiB of data (32 tables, 10M rows each) and a 4GiB buffer pool. The cache devices were flushed to disk immediately prior to and immediately following each test run.

With that out of the way, let’s look at some numbers.

vCache vs. vCache – MySQL parameter testing

The first test was designed to look solely at vCache performance under some different sets of MySQL configuration parameters. For example, given that the front-end device is a very fast PCIe SSD, would it make more sense to configure MySQL as if it were using SSD storage or to just use an optimized HDD storage configuration? After creating a vCache device with the default configuration, I started with a baseline HDD configuration for MySQL (configuration A, listed at the bottom of this post) and then tried three additional sets of experiments. First, the baseline configuration plus:

innodb_read_io_threads = 16
innodb_write_io_threads = 16

We call this configuration B. The next one contained four SSD-specific optimizations based partially on some earlier work that I’d done with this Virident card (configuration C):

innodb_io_capacity = 30000
innodb_adaptive_flushing_method = keep_average
innodb_flush_neighbor_pages=none
innodb_max_dirty_pages_pct = 60

And then finally, a fourth test (configuration D) which combined the parameter changes from tests B and C. The graph below shows the sysbench throughput (tps) for these four configurations:

As we can see, all of the configuration options produce numbers that, in the absence of outliers, are roughly identical, but it’s configuration C (shown in the graph as the blue line – SSD config) which shows the most consistent performance. The others all have assorted performance drops scattered throughout the graph. We see the exact same pattern when looking at transaction latency; the baseline numbers are roughly identical for all four configurations, but configuration C avoids the spikes and produces a very constant and predictable result.

vCache vs. FlashCache – the basics

Once I’d determined that configuration C appeared to produce the most optimal results, I moved on to reviewing FlashCache performance versus that of vCache, and I also included a “no cache” test run as well using the base HDD MySQL configuration for purposes of comparison. Given the apparent differences in time-based flushing in vCache and FlashCache, both cache devices were set up so that time-based flushing was disabled. Also, both devices were set up such that all IO would be cached (i.e., no special treatment of sequential writes) and with a 50% dirty page threshold. Again, for comparison purposes, I also include the numbers from the vCache test where the time-based flushing is enabled.

Truncated by Planet PHP, read more at the original (another 7107 bytes)

(This is part one of a two part series) Over the past few weeks I have been looking at a preview release of Virident’s vCache software, which is a kernel module and set of utilities designed to provide functionality similar to that of FlashCache. In particular, Virident engaged Percona to do a usability and feature-set comparison between vCache and FlashCache and also to conduct some benchmarks for the use case where the MySQL working set is significantly larger than the InnoDB buffer pool (thus leading to a lot of buffer pool disk reads) but still small enough to fit into the cache device. In this post and the next, I’ll present some of those results.

Disclosure: The research and testing for this post series was sponsored by Virident.

Usability is, to some extent, a subjective call, as I may have preferences for or against a certain mode of operation that others may not share, so readers may have a different opinion than mine, but on this point I call it an overall draw between vCache and FlashCache.

Ease of basic installation. The setup process was simply a matter of installing two RPMs and running a couple of commands to enable vCache on the PCIe flash card (a Virident FlashMAX II) and set up the cache device with the command-line utilities supplied with one of the RPMs. Moreover, the vCache software is built in to the Virident driver, so there is no additional module to install. FlashCache, on the other hand, requires building a separate kernel module in addition to whatever flash memory driver you’ve already had to install, and then further configuration requires modification to assorted sysctls. I would also argue that the vCache documentation is superior. Winner: vCache.

Ease of post-setup modification / advanced installation. Many of the FlashCache device parameters can be easily modified by echoing the desired value to the appropriate sysctl setting; with vCache, there is a command-line binary which can modify many of the same parameters, but doing so requires a cache flush, detach, and reattach. Winner: FlashCache.

Operational Flexibility: Both solutions share many features here; both of them allow whitelisting and blacklisting of PIDs or simply running in a “cache everything” mode. Both of them have support for not caching sequential IO, adjusting the dirty page threshold, flushing the cache on demand, or having a time-based cache flushing mechanism, but some of these features operate differently with vCache than with FlashCache. For example, when doing a manual cache flush with vCache, this is a blocking operation. With FlashCache, echoing “1″ to the do_sync sysctl of the cache device triggers a cache flush, but it happens in the background, and while countdown messages are written to syslog as the operation proceeds, the device never reports that it’s actually finished. I think both kinds of flushing are useful in different situations, and I’d like to see a non-blocking background flush in vCache, but if I had to choose one or the other, I’ll take blocking and modal over fire-and-forget any day. FlashCache does have the nice ability to switch between FIFO and LRU for its flushing algorithm; vCache does not. This is something that could prove useful in certain situations. Winner: FlashCache.

Operational Monitoring: Both solutions offer plenty of statistics; the main difference is that FlashCache stats can be pulled from /proc but vCache stats have to be retrieved by running the vgc-vcache-monitor command. Personally, I prefer “cat /proc/something” but I’m not sure that’s sufficient to award this category to FlashCache. Winner: None.

Time-based Flushing: This wouldn’t seem like it should be a separate category, but because the behavior seems to be so different between the two cache solutions, I’m listing it here. The vCache manual indicates that “flush period” specifies the time after which dirty blocks will be written to the backing store, whereas FlashCache has a setting called “fallow_delay”, defined in the documentation as the time period before “idle” dirty blocks are cleaned from the cache device. It is not entirely clear whether or not these mechanisms operate in the same fashion, but based on the documentation, it appears that they do not. I find the vCache implementation more useful than the one present in FlashCache. Winner: vCache.

Although nobody likes a tie, if you add up the scores, usability is a 2-2-1 draw between vCache and FlashCache.

Truncated by Planet PHP, read more at the original (another 910 bytes)

This is part 2, you can find part 1 here.

So in part 1 we learned how to calculate the free space within InnoDB. But unfortunately that won't always work perfectly.

The first issue: the DATA_FREE column in the INFORMATION_SCHEMA.TABLES table will not show a sum of the free space of each partition. This means that if you have innodb_file_per_table disabled and are using partitioning then you must divide DATA_FREE by the number of partitions.
This is Bug #36312.

Example:

mysql> SELECT CONCAT(T.TABLE_SCHEMA,'.',T.TABLE_NAME) AS TABLE_NAME,
    -> P.PARTITION_NAME AS PART,IBT.SPACE,IBD.PATH,T.DATA_FREE AS T_DATA_FREE,
    -> P.DATA_FREE AS P_DATA_FREE FROM INFORMATION_SCHEMA.TABLES T 
    -> LEFT JOIN INFORMATION_SCHEMA.PARTITIONS P ON P.TABLE_SCHEMA=T.TABLE_SCHEMA 
    -> AND P.TABLE_NAME=T.TABLE_NAME 
    -> LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES IBT 
    -> ON IBT.NAME=CONCAT(T.TABLE_SCHEMA,'/',T.TABLE_NAME) 
    -> OR IBT.NAME=CONCAT(T.TABLE_SCHEMA,'/',T.TABLE_NAME,'#P#',P.PARTITION_NAME) 
    -> LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_DATAFILES IBD 
    -> ON IBD.SPACE=IBT.SPACE WHERE ENGINE='InnoDB' ORDER BY T.TABLE_SCHEMA,T.TABLE_NAME;
+----------------------------+------+-------+-----------------------------------+-------------+-------------+
| TABLE_NAME                 | PART | SPACE | PATH                              | T_DATA_FREE | P_DATA_FREE |
+----------------------------+------+-------+-----------------------------------+-------------+-------------+
| innodbfreespacetest.t1     | NULL |     6 | ./innodbfreespacetest/t1.ibd      |     4194304 |     4194304 |
| innodbfreespacetest.t11    | p2   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t11    | p1   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t11    | p0   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t11    | p4   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t11    | p3   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t13    | NULL |    46 | ./innodbfreespacetest/t13.ibd     |     4194304 |     4194304 |
| innodbfreespacetest.t2     | NULL |     0 | NULL                              |    80740352 |    80740352 |
| innodbfreespacetest.t5     | p1   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t5     | p0   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t5     | p4   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t5     | p3   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t5     | p2   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t6     | p4   |    15 | ./innodbfreespacetest/t6#P#p4.ibd |           0 |           0 |
| innodbfreespacetest.t6     | p3   |    14 | ./innodbfreespacetest/t6#P#p3.ibd |           0 |           0 |
| innodbfreespacetest.t6     | p2   |    13 | ./innodbfreespacetest/t6#P#p2.ibd |           0 |           0 |
| innodbfreespacetest.t6     | p1   |    12 | ./innodbfreespacetest/t6#P#p1.ibd |           0 |           0 |
| innodbfreespacetest.t6     | p0   |    11 | ./innodbfreespacetest/t6#P#p0.ibd |           0 |           0 |
| mysql.innodb_index_stats   | NULL |     2 | ./mysql/innodb_index_stats.ibd    |           0 |           0 |
| mysql.innodb_table_stats   | NULL |     1 | ./mysql/innodb_table_stats.ibd    |           0 |           0 |
| mysql.slave_master_info    | NULL |     4 | ./mysql/slave_master_info.ibd     |           0 |           0 |
| mysql.slave_relay_log_info | NULL |     3 | ./mysql/slave_relay_log_info.ibd  |           0 |           0 |
| mysql.slave_worker_info    | NULL |     5 | ./mysql/slave_worker_info.ibd     |           0 |           0 |
+----------------------------+------+-------+-----------------------------------+-------------+-------------+
23 rows in set (0.05 sec)

This example is on MySQL 5.6.10.
Tables t1 and t13 have their own tablespace.
Table t2 is in the system tablespace.
Tables t5 and t11 are partitioned and in the system tablespace, these tables show the real DATA_FREE multiplied by the number of partitions. The DATA_FREE for individual partitions is correct.
 
For some old 5.1 versions the DATA_FREE might be show in kilobytes instead of bytes. (and there is no column in which the measurement unit size is stored)

Percona is glad to announce the release of Percona XtraBackup 2.1.1 on May 15th 2013. 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, XtraBackup drives down backup costs while providing unique features for MySQL backup. The new 2.1.1 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.1 series.

New Features:

• Percona XtraBackup now has support for Compact Backups. This feature can be used for taking the backups that will take less amount of disk space. GA release now contains new innobackupex –rebuild-threads option that can be used to specify the number of threads started by XtraBackup when rebuilding secondary indexes on innobackupex --apply-log --rebuild-indexes. This allows parallel processing of individual tables when rebuilding the index.
• Percona XtraBackup has implemented Encrypted Backups. This feature can be used to encrypt/decrypt both local and streamed backups in order to add another layer of protection to the backups.
• innobackupex now uses Perl’s DBD::MySQL package for server communication instead of spawning the MySQL command line client.
• Support for InnoDB 5.0 and InnoDB 5.1 builtin has been removed from Percona XtraBackup.
• After being deprecated in previous version, option --remote-host has been completely removed in Percona XtraBackup 2.1.
• Percona XtraBackup can use XtraDB changed page tracking feature to perform the Incremental Backups now.

Bugs Fixed:

• innobackupex is using SHOW MASTER STATUS to obtain binlog file and position. This could trigger a bug if the server being backed up was standalone server (neither master nor slave in replication) and binlog information wasn’t available. Fixed by not creating xtrabackup_binlog_info file when binlog isn’t available. Bug fixed #1168513.
• Percona XtraBackup would leave xbtemp temp files behind due to a typo. Bug fixed #1172016.
• Percona XtraBackup would assume the table has been dropped if the tablespace was renamed after it was scanned by XtraBackup on startup and before XtraBackup attempted to copy it. Bug fixed #1079700.
• Orphaned xtrabackup_pid file left inside tmpdir could cause SST to fail. Fixed by fix checking if xtrabackup_pid file exists once innobackupex starts, and try to remove it or fail if it cannot be removed. Bug fixed #1175860.
• xtrabackup –stats option would not work with server datadir if the server isn’t running and logs were in a separate directory. Bug fixed

Truncated by Planet PHP, read more at the original (another 1236 bytes)

Recently someone asked my if it's possible to find the total free space within InnoDB. I thought this would be very easy as the INFORMATION_SCHEMA.TABLES table has a DATA_FREE column. So we could just use SELECT SUM(DATA_FREE) FROM INFORMATION_SCHEMA.TABLES couldn't we?
 
So what does the DATA_FREE column tell us? It tells us the free data within InnoDB for that particular table. A table can share a tablespace with multiple other tables.
 
The tablespace which is used by a table depends on whether the innodb_file_per_table was enabled during table creation and/or at the last time the table was rebuild (e.g. by OPTIMIZE TABLE).
 
If innodb_file_per_table was always disabled then this query probably reports the correct free space:

SELECT DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB' LIMIT 1;

This is because all tables will share 1 tablespace.
 
If innodb_file_per_table was always enabled (new default for 5.6!) then this would report the free space:

SELECT SUM(DATA_FREE) FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB';

This is because eache table will have it's own tablespace.
 
But how could we combine these two?
 
To give this a try I create a MySQL 5.6.10 sandbox with 4 tables of which only 2 have their own tablespace.
 
My first try is to use my udf_fileexists UDF:
 

mysql> SELECT TABLE_NAME,DATA_FREE,
    -> udf_fileexists(CONCAT(TABLE_SCHEMA,'/',TABLE_NAME,'.ibd')) AS ibd_file 
    -> FROM information_schema.tables WHERE ENGINE='InnoDB';
+----------------------+-----------+----------+
| TABLE_NAME           | DATA_FREE | ibd_file |
+----------------------+-----------+----------+
| t1                   |   4194304 |        1 |
| t2                   |   4194304 |        1 |
| t3                   |  66060288 |        0 |
| t4                   |  66060288 |        0 |
| innodb_index_stats   |         0 |        1 |
| innodb_table_stats   |         0 |        1 |
| slave_master_info    |         0 |        1 |
| slave_relay_log_info |         0 |        1 |
| slave_worker_info    |         0 |        1 |
+----------------------+-----------+----------+
9 rows in set (0.02 sec)

mysql> SELECT (SELECT DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE 
    -> NOT udf_fileexists(CONCAT(TABLE_SCHEMA,'/',TABLE_NAME,'.ibd')) 
    -> AND ENGINE='InnoDB' LIMIT 1) + (SELECT SUM(DATA_FREE) 
    -> FROM INFORMATION_SCHEMA.TABLES WHERE 
    -> udf_fileexists(CONCAT(TABLE_SCHEMA,'/',TABLE_NAME,'.ibd')) 
    -> AND ENGINE='InnoDB') AS TOTAL_DATA_FREE;
+-----------------+
| TOTAL_DATA_FREE |
+-----------------+
|        74448896 |
+-----------------+
1 row in set (0.03 sec)

 
So that works, but it requires loading a UDF. Luckily it's also possible to only use INFORMATION_SCHEMA by using the INNODB_SYS_TABLES table. This works only for 5.6 and newer.
 

mysql> SELECT T.TABLE_SCHEMA,T.TABLE_NAME,T.DATA_FREE,ST.SPACE,SD.PATH 
    -> FROM INFORMATION_SCHEMA.TABLES T LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES ST 
    -> ON ST.NAME=CONCAT(T.TABLE_SCHEMA,'/',T.TABLE_NAME) 
    -> LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_DATAFILES SD ON SD.SPACE=ST.SPACE WHERE T.ENGINE='InnoDB';
+---------------------+----------------------+-----------+-------+----------------------------------+
| TABLE_SCHEMA        | TABLE_NAME           | DATA_FREE | SPACE | PATH                             |
+---------------------+----------------------+-----------+-------+----------------------------------+
| mysql               | innodb_table_stats   |         0 |     1 | ./mysql/innodb_table_stats.ibd   |
| mysql               | innodb_index_stats   |         0 |     2 | ./mysql/innodb_index_stats.ibd   |
| mysql               | slave_relay_log_info |         0 |     3 | ./mysql/slave_relay_log_info.ibd |
| mysql               | slave_master_info    |         0 |     4 | ./mysql/slave_master_info.ibd    |
| mysql               | slave_worker_info    |         0 |     5 | ./mysql/slave_worker_info.ibd    |
| innodbfreespacetest | t1                   |   4194304 |     6 | ./innodbfreespacetest/t1.ibd     |
| innodbfreespacetest | t2                   |   4194304 |     7 | ./innodbfreespacetest/t2.ibd     |
| innodbfreespacetest | t3                   |  66060288 |     0 | NULL                             |
| innodbfreespacetest | t4                   |  66060288 |     0 | NULL                             |
+---------------------+----------------------+-----------+-------+----------------------------------+

Truncated by Planet PHP, read more at the original (another 1142 bytes)

Occasionally it is useful to know what a MySQL command is doing internally. Just looking into the MySQL source directory can be overwhelming. Knowing the basics of the handler interface and the sql parser can be a great start for reading the source code to understand what MySQL does under the hood. Here I will cover a little bit about how the SQL syntax is defined.

Everything starts with lex.h and sql_yacc.yy in the sql/ dir. lex.h contains all the functions and symbols used to make up the SQL syntax. The sql_yacc.yy file describes the relationships between these symbols and the C functions responsible for executing them. I’m not sure why some symbol definitions end in _SYM and others don’t. Looking in lex.h “FLUSH” is defined as FLUSH_SYM. To see all the places where flush is allowed in the SQL go back to sql_yacc.yy and grep for it.

The first important section looks like this:

/* flush things */                                                                      

flush:                                                                                  
          FLUSH_SYM opt_no_write_to_binlog                                              
          {                                                                             
            LEX *lex=Lex;                                                               
            lex->sql_command= SQLCOM_FLUSH;                                             
            lex->type= 0;                                                               
            lex->no_write_to_binlog= $2;                                                
          }                                                                             
          flush_options                                                                 
          {}                                                                            
        ;

This is where things can get a bit nested and weird. The flush: section is saying that flush can have opt_no_write_to_binlog optionally after it. The first section in curly braces defines the sql command and also sets the flag no_write_to_binlog. SQLCOM_FLUSH is important in the next phase where we get into actual C code.

flush_options used to define all of the possible options for a flush command. Going one step further down flush_options_list basically says that a flush command can contain more than one option.

flush_options_list:
          flush_options_list ',' flush_option
        | flush_option
          {}
        ;

Notice that flush_options_list can contain a flush_options_list. I don’t know the specifics of this but it is the yacc way of saying things can be repeated. In this case the | is saying that there can be multiple flush_option separated by a comma or just one option.

With flush_option: things start to make more sense. This is all of the different types of flush commands. Looking at the first part

flush_option:
          ERROR_SYM LOGS_SYM
          { Lex->type|= REFRESH_ERROR_LOG; }
        | ENGINE_SYM LOGS_SYM
          { Lex->type|= REFRESH_ENGINE_LOG; }
        | GENERAL LOGS_SYM
          { Lex->type|= REFRESH_GENERAL_LOG; }
        | SLOW LOGS_SYM

Reading it in english this is basically saying  ”Error logs OR engine logs OR general logs OR slow logs” Combining this with the previous section allowing multiple flush options this is a valid query:

MariaDB [test]> flush error logs, slow logs;
Query OK, 0 rows affected (0.00 sec)

The flush command is quite a bit improved in MariaDB 10. Comparing this to part of the flush_option: section from MariaDB 5.2 shows how much it has improved:

flush_option:
          table_or_tables
          { Lex->type|= REFRESH_TABLES; }
          opt_table_list {}
        | TABLES WITH READ_SYM LOCK_SYM
          { Lex->type|= REFRESH_TABLES | REFRESH_READ_LOCK; }
        | QUERY_SYM CACHE_SYM
          { Lex->type|= REFRESH_QUERY_CACHE_FREE; }
        | HOSTS_SYM
          { Lex->type|= REFRESH_HOSTS; }
        | PRIVILEGES
          { Lex->type|= REFRESH_GRANT; }
        | LOGS_SYM
          { Lex->type|= REFRESH_LOG; }
        | STATUS_SYM
          { Lex->type|= REFRESH_STATUS; }

In 5.2 the LOGS_SYM is alone which makes flush error logs an invalid query. By scanning the grammar in sql_yacc.yy it is easy to see which syntax is and isn’t supported between versions.

Now that a command of SQLCOM_FLUSH has been specified. The flush_option is passed in via Lex->type. Each option is bitwise ORed into type. It is time to switch over to C++ code and see how these are executed.

sql_parse.cc has a huge switch case statement that contains every possible command type that MySQL can process. For this example look for case SQLCOM_FLUSH: The SQLCOM_FLUSH is the same option from t

Truncated by Planet PHP, read more at the original (another 1447 bytes)

The third meeting for the MySQL User Group NL will be hosted by Snow B.V. in the Snow office in Geldermalsen.
 
The Agenda:

• Choosing the Best Sharding Policy - Doran Levari (ScaleBase, using a video link)
• Performance Monitoring with Statsd and Graphite - Art van Scheppingen (Spil Games)
• Basic MySQL performance tuning for sysadmins - Daniël van Eeden (Snow)

Please RSVP on the meetup.com page.

The user group now has more than 100 members!

I talk with lot of people who are really interested in Percona XtraDB Cluster (PXC) and mostly they are interested in PXC as a high-availability solution.  But, what they tend not to think too much about is if moving from async to synchronous replication is right for their application or not.

Facts about Galera replication

There’s a lot of different facts about Galera that come into play here, and it isn’t always obvious how they will affect your database workload.  For example:

• Transaction commit takes approximately the worst packet round trip time (RTT) between any two nodes in your cluster.
• Transaction apply on slave nodes is still asynchronous from client commit (except on the original node where the transaction is committed)
• Galera prevents writing conflicts to these pending transactions while they are inflight in the form of deadlock errors.  (This is actually a form of Eventual Consistency where the client is forced to correct the problem before it can commit.  It is NOT the typical form of Eventual Consistency, known as asynchronous repair, that most people think of).
  

Callaghan’s Law

But what does that all actually mean?  Well, at the Percona Live conference a few weeks ago I heard a great maxim that really helps encapsulate a lot of this information and puts it into context with your application workload:

[In a Galera cluster] a given row can’t be modified more than once per RTT

This was attributed to Mark Callaghan from Facebook by Alexey Yurchenko from Codership at his conference talk.  Henceforth this will be known as “Callaghan’s law” in Galera circles forever, though Mark didn’t immediately recall saying it.

Applied to a standalone Innodb instance

Let’s break it down a bit.  Our unit of locking in Innodb is a single row (well, the PRIMARY KEY index entry for that row).  This means typically on a single Innodb node we can have all sorts modifications floating around as long as they don’t touch the same row.  Row locks are held for modifications until the transaction commits and that takes an fsync to the redo log by default, so applying Callaghan’s law to single-server Innodb, we’d get:

[On a single node Innodb server] a given row can’t be modified more than the time to fsync

You can obviously relax that by simply not fsyncing every transaction (innodb_flush_log_at_trx_commit != 1), or work around it with by fsyncing to memory (Battery or capacitor-backed write cache), etc., but the principle is basically the same.  If we want this transaction to persist after a crash, it has to get to disk.

This has no effect on standard MySQL replication from this instance, since MySQL replication is asynchronous.

What about semi-sync MySQL replication?

It’s actually much worse than Galera.  As I illustrated in a blog post last year, semi-sync must serialize all transactions and wait for them one at a time.  So, Callaghan’s law applied to semi-sync is:

[On a semi-sync replication master] you can’t commit (at all) more than once per RTT. 

Applied to a Galera cluster

In the cluster we’re protecting the data as well, though not by ensuring it goes to disk (though you can do that).  We protect the data by ensuring it gets to every node in the cluster.

But why every node and not just a quorum?  Well, it turns out transaction ordering really, really matters (really!).  By enforcing replication to all nodes, we can (simultaneously) establish global ordering for the transaction, so by the time the original node gets acknowledgement of the transaction back from all the other nodes, a GTID will also (by design) be established.  We’ll never end up with non-deterministic ordering of transactions as a result.

So this brings us back to Callaghan’s law for Galera.  We must have group communication to r

Truncated by Planet PHP, read more at the original (another 7525 bytes)

This Wednesday, May 15 at 10 a.m. Pacific, I’ll be leading  a Webinar titled, “Using MySQL 5.6 Performance Schema to Troubleshoot Typical Workload Bottlenecks.”

In this Webinar I will offer an overview of Performance Schema, focusing on new features that have been added in MySQL 5.6, go over the configuration and spend most time showing how you can use the wealth of information Performance Schema gathers to understand some of the typical performance bottlenecks.

 

Other areas of focus include:

• Bottlenecks with Disk IO
• Problems with excessive temporary tables and external sorts
• Excessive internal mutex contention
• Slow queries due to waits on InnoDB locks and Meta Data locks

Interested ? Sign up today!

The post Webinar: MySQL 5.6 Performance Schema appeared first on MySQL Performance Blog.