Peter ZaitsevProfiling MySQL queries from Performance Schema (16.4.2015, 17:49 UTC)

When optimizing queries and investigating performance issues, MySQL comes with built in support for profiling queries aka

SET profiling = 1;
 . This is already awesome and simple to use, but why the PERFORMANCE_SCHEMA alternative?

Because profiling will be removed soon (already deprecated on MySQL 5.6 ad 5.7); the built-in profiling capability can only be enabled per session. This means that you cannot capture profiling information for queries running from other connections. If you are using Percona Server, the profiling option for log_slow_verbosity is a nice alternative, unfortunately, not everyone is using Percona Server.

Now, for a quick demo: I execute a simple query and profile it below. Note that all of these commands are executed from a single session to my test instance.

mysql> SHOW PROFILES;
+----------+------------+----------------------------------------+
| Query_ID | Duration   | Query                                  |
+----------+------------+----------------------------------------+
|        1 | 0.00011150 | SELECT * FROM sysbench.sbtest1 LIMIT 1 |
+----------+------------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW PROFILE SOURCE FOR QUERY 1;
+----------------------+----------+-----------------------+------------------+-------------+
| Status               | Duration | Source_function       | Source_file      | Source_line |
+----------------------+----------+-----------------------+------------------+-------------+
| starting             | 0.000017 | NULL                  | NULL             |        NULL |
| checking permissions | 0.000003 | check_access          | sql_parse.cc     |        5797 |
| Opening tables       | 0.000021 | open_tables           | sql_base.cc      |        5156 |
| init                 | 0.000009 | mysql_prepare_select  | sql_select.cc    |        1050 |
| System lock          | 0.000005 | mysql_lock_tables     | lock.cc          |         306 |
| optimizing           | 0.000002 | optimize              | sql_optimizer.cc |         138 |
| statistics           | 0.000006 | optimize              | sql_optimizer.cc |         381 |
| preparing            | 0.000005 | optimize              | sql_optimizer.cc |         504 |
| executing            | 0.000001 | exec                  | sql_executor.cc  |         110 |
| Sending data         | 0.000025 | exec                  | sql_executor.cc  |         190 |
| end                  | 0.000002 | mysql_execute_select  | sql_select.cc    |        1105 |
| query end            | 0.000003 | mysql_execute_command | sql_parse.cc     |        5465 |
| closing tables       | 0.000004 | mysql_execute_command | sql_parse.cc     |        5544 |
| freeing items        | 0.000005 | mysql_parse           | sql_parse.cc     |        6969 |
| cleaning up          | 0.000006 | dispatch_command      | sql_parse.cc     |        1874 |
+----------------------+----------+-----------------------+------------------+-------------+
15 rows in set, 1 warning (0.00 sec)

To demonstrate how we can achieve the same with Performance Schema, we first identify our current connection id. In the real world, you might want to get the connection/processlist id of the thread you want to watch i.e. from

SHOW PROCESSLIST
 .
mysql> SELECT THREAD_ID INTO @my_thread_id
    -> FROM threads WHERE PROCESSLIST_ID = CONNECTION_ID();
Query OK, 1 row affected (0.00 sec)

Next, we identify the bounding EVENT_IDs for the statement stages. We will look for the statement we wanted to profile using the query below from the

events_statements_history_long
table. Your LIMIT clause may vary depending on how much queries the server might be getting.
mysql> SELECT THREAD_ID, EVENT_ID, END_EVENT_ID, SQL_TEXT, NESTING_EVENT_ID
    -> FROM events_statements_history_long
    -> WHERE THREAD_ID = @my_thread_id
    ->   AND EVENT_NAME = 'statement/sql/select'
    -> ORDER BY EVENT_ID DESC LIMIT 3 G
*************************** 1. row ***************************

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

Link
Jean-Jerome SchmidtDatabase Security - How to fully SSL-encrypt MySQL Galera Cluster and ClusterControl (16.4.2015, 07:39 UTC)

Data security is a hot topic for many companies these days. But for those who need to adhere to security standards like PCI DSS or HIPAA, security is not an option. We showed you sometime back how to encrypt Galera replication traffic, but for a more complete solution, you’ll want to encrypt all database connections from client applications and any management/monitoring infrastructure. With ClusterControl 1.2.9, we introduced a number of features to facilitate this, including the ability to add new nodes to an encrypted Galera Cluster.

The following are the new relevant configuration options:

  • cmondb_ssl_key - path to SSL key, for SSL encryption between CMON and the CMON DB.
  • cmondb_ssl_cert - path to SSL cert, for SSL encryption between CMON and the CMON DB
  • cmondb_ssl_ca - path to SSL CA, for SSL encryption between CMON and the CMON DB
  • cluster_ssl_key - path to SSL key, for SSL encryption between CMON and managed MySQL Servers.
  • cluster_ssl_cert - path to SSL cert, for SSL encryption between CMON and managed MySQL Servers.
  • cluster_ssl_ca - path to SSL CA, for SSL encryption between CMON and managed MySQL Servers.
  • cluster_certs_store - path to storage location of SSL related files, defaults to /etc/ssl/<clustertype>/<cluster_id>

Details on the configuration options above is explained in our ClusterControl Administration Guide under Configuration File section.

In this blog post, we are going to show you how to deploy a fully encrypted Galera Cluster. This includes:

  • MySQL clients to MySQL servers
  • ClusterControl to managed MySQL servers
  • ClusterControl to CMON DB
  • Galera replication traffic

The following diagram shows our architecture, before and after the deployment of SSL:

 

Upgrade to ClusterControl latest version

Please upgrade to ClusterControl controller version 1.2.9-708 or above before performing the exercise explained in this blog post. Upgrade instructions are available here.

 

Generating SSL with OpenSSL

The following steps should be performed on the ClusterControl node.

1. To make things simpler, we are going to create keys and certificates under a directory, /etc/ssl/mysql on the ClusterControl node and transfer them over to the managed MySQL nodes. Firstly, create the directory:

$ mkdir /etc/ssl/mysql
$ cd /etc/ssl/mysql

2. Generate Certificate Authority (CA) key and certificate:

$ openssl genrsa 2048 > ca-key.pem
$ openssl req -new -x509 -nodes -days 3600 -key ca-key.pem > ca-cert.pem

3. Create the MySQL server’s certificate:

$ openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem > server-req.pem
$ openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

4. Create the MySQL client’s certificate:

$ openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem > client-req.pem
$ openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

5. Remove the passphrase for the key files:

$ openssl rsa -in client-key.pem -out client-key.pem
$ openssl rsa -in server-key.pem -out

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

Link
Peter ZaitsevChecking table definition consistency with mysqldiff (15.4.2015, 20:45 UTC)

Data inconsistencies in replication environments are a pretty common. There are lots of posts that explain how to fix those using pt-table-checksum and pt-table-sync. Usually we only care about the data but from time to time we receive this question in support:

How can I check the table definition consistency between servers?

Replication also allow us to have different table definition between master and slaves. For example, there are some cases that you need some indexes on slaves for querying purposes but are not really needed on the master. There are some other cases where those differences are just a mistake that needs to be fixed.

mysqldiff, included in Oracle’s MySQL Utilities, can help us to find those differences and get the information we need to fix those them. In this post I’m going to show you how to use it with an example.

Find table definition inconsistencies

mysqldiff allows us to find those inconsistencies checking the differences between the tables on the same server (different databases) or on different servers (also possible on different databases). In this example I’m going to search for differences in table definitions between two different servers, server1 and server2.

The command line is pretty simple. This is used to compare the tables on “test” database:

mysqldiff --server1=user@host1 --server2=user@host2 test:test

If the database name is different:

mysqldiff --server1=user@host1 --server2=user@host2 testdb:anotherdb

If the table name is different:

mysqldiff --server1=user@host1 --server2=user@host2 testdb.table1:anotherdb.anothertable

Now I want to check the table definition consistency between two servers. The database’s name is “employees”:

# mysqldiff --force --server1=root:msandbox@127.0.0.1:21489 --server2=root:msandbox@127.0.0.1:21490 employees:employees
# WARNING: Using a password on the command line interface can be insecure.
# server1 on 127.0.0.1: ... connected.
# server2 on 127.0.0.1: ... connected.
# Comparing `employees` to `employees`                             [PASS]
# Comparing `employees`.`departments` to `employees`.`departments`   [FAIL]
# Object definitions differ. (--changes-for=server1)
#
--- `employees`.`departments`
+++ `employees`.`departments`
@@ -1,6 +1,6 @@
 CREATE TABLE `departments` (
   `dept_no` char(4) NOT NULL,
-  `dept_name` varchar(40) NOT NULL,
+  `dept_name` varchar(256) DEFAULT NULL,
   PRIMARY KEY (`dept_no`),
   UNIQUE KEY `dept_name` (`dept_name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
# Comparing `employees`.`dept_emp` to `employees`.`dept_emp`       [PASS]
# Comparing `employees`.`dept_manager` to `employees`.`dept_manager`   [PASS]
# Comparing `employees`.`employees` to `employees`.`employees`     [FAIL]
# Object definitions differ. (--changes-for=server1)
#
--- `employees`.`employees`
+++ `employees`.`employees`
@@ -5,5 +5,6 @@
   `last_name` varchar(16) NOT NULL,
   `gender` enum('M','F') NOT NULL,
   `hire_date` date NOT NULL,
-  PRIMARY KEY (`emp_no`)
+  PRIMARY KEY (`emp_no`),
+  KEY `last_name` (`last_name`,`first_name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
# Comparing `employees`.`salaries` to `employees`.`salaries`       [PASS]
# Comparing `employees`.`titles` to `employees`.`titles`           [PASS]
Compare failed. One or more differences found.

There are at least two differences. One in departments table and another one in employees table. The output is similar to diff. By default the tool stops after finding the first difference. That’s why we use –force, to tell the tool to continue checking all the tables.

It shows us that on departments the dept_name is varchar(40) on server1 and varchar(256) on server2. For “employees” table, it has a KEY (last_name, first_name) on the server2 that is not present on server1. Why is it taking server2 as a reference? Because of this line:

# Object definitions differ. (--changes-for=server1)

So, the changes shown on the diff are for server1. If you want server2 to be the one to be changed and server1 used as reference, then –changes-for=server2 would be needed.

In some cases the diff output is not really useful. We actually need a SQL query to do the changes on the server. W

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

Link
Jean-Jerome SchmidtDeep Dive Into How To Monitor MySQL or MariaDB Galera Cluster - Live Webinar April 21st (15.4.2015, 02:13 UTC)

MySQL provides hundreds of status counters, but how do you make sense of all that monitoring data? 

If you’re in Operations and your job is to monitor the health of MySQL/MariaDB Galera Cluster or Percona XtraDB Cluster, then this webinar is for you. Setting up a Galera Cluster is fairly straightforward, but keeping it in a good shape and knowing what to look for when it’s having production issues can be a challenge.

Status counters can be tricky to read … 

  • Which of them are more important than others? 
  • How do you find your way in a labyrinth of different variables? 
  • Which of them can make a significant difference? 
  • How might a host’s health impact MySQL performance?
  • How to identify problematic nodes in your cluster?

To find out more, please register for this new live webinar.

When: Tuesday April 21st
Who: Krzysztof Książek, Senior Support Engineer, Severalnines
Where: 

Join our colleague Krzysztof Książek for this deep-dive session. Krzysztof is a MySQL DBA with experience managing complex database environments for companies like Zendesk, Chegg, Pinterest and Flipboard. This webinar builds upon recent blog posts by Krzysztof on OS and database monitoring.

Register for the webinar

mag_glass_ccui.PNG

 

Blog category:

Link
Peter ZaitsevTeam Tokutek is proud to join Team Percona! (14.4.2015, 18:18 UTC)

If you haven’t already heard, on the Tuesday morning of the 2015 Percona Live MySQL Conference and Expo it was announced that Tokutek is now part of the Percona family.  This means TokuDB® for MySQL, and TokuMX™ for MongoDB are Percona products now; and that the Tokutek  team is now part of the Percona team.

Percona’s well-deserved reputation for unparalleled customer service and support in the MySQL market makes them the perfect home for Tokutek’s ground-breaking products.  And with the Tokutek acquisition, Percona can expand and extend their activities and offerings into the MongoDB market.

This is a win/win for NoSQL and MySQL fans alike.

More About Tokutek

Tokutek is the company that productized a new and revolutionary form of database indexing designed specifically for modern, Big Data applications.  Based on data science research on new methods for high-performance data processing for data sets that no longer fit in memory, Fractal Tree® indexing is the secret sauce inside TokuDB and TokuMX.

Unlike the 40-year-old B-tree indexing found in other MySQL and MongoDB solutions, Fractal Tree indexing enables: up to 50x better performance; as much as 90% data compression; and 95% better write-optimization.  That translates into significant customer satisfaction gains as well as major cost savings.

In addition, drawing upon their experience in the MySQL world, Tokutek developers introduced full ACID and MVCC transaction compliance, better concurrency, and an improved failover protocol to the MongoDB marketplace with TokuMX. And that means better reliability for mission-critical big data applications built with MongoDB.

Next Steps

The Tokutek team is very excited to be joining the Percona team as we move into the next phase of growth on the MySQL and NoSQL market.

For now, if you want to learn more about TokuDB and TokuMX please visit www.tokutek.com.  (In the coming weeks, the Tokutek site will be folded into the Percona site.)

If you want to strike up a conversation about enterprise subscriptions for either product drop us a line at tokutek@percona.com.

Regards,
Craig Clark
Vice President, Percona Sales

The post Team Tokutek is proud to join Team Percona! appeared first on MySQL Performance Blog.

Link
Peter ZaitsevTokutek now part of the Percona family (14.4.2015, 18:13 UTC)

It is my pleasure to announce that Percona has acquired Tokutek and will take over development and support for TokuDB® and TokuMX™ as well as the revolutionary Fractal Tree® indexing technology that enables those products to deliver improved performance, reliability and compression for modern Big Data applications.

At Percona we have been working with the Tokutek team since 2009, helping to improve performance and scalability. The TokuDB storage engine has been available for Percona Server for about a year, so joining forces is quite a natural step for us.

Fractal Tree indexing technology—developed by years of data science research at MIT, Stony Brook University and Rutgers University—is the new generation data structure which, for many workloads, leapfrogs traditional B-tree technology which was invented in 1972 (over 40 years ago!).  It is also often superior to LSM indexing, especially for mixed workloads.

But as we all know in software engineering, an idea alone is not enough.  There are hundreds of databases which have data structures based on essentially the same B-Tree idea, but their performance and scalability differs dramatically. The Tokutek engineering team has spent more than 50 man years designing, implementing and polishing this technology, which resulted  (in my opinion) in the only production-ready Open Source transactional alternative to the InnoDB storage engine in the MySQL space – TokuDB; and the only viable alternative distribution of MongoDB  – TokuMX.

Designed for Modern World –  TokuDB and TokuMX were designed keeping in mind modern database workloads, modern hardware and modern operating system properties which allowed for much more clean and scalable architecture, leading to great performance and scalability.

Compression at Speed  – As part of it, compression was an early part of design, so a very high level of compression can be achieved with low performance overhead. In fact, chances are with fast compression you will get better performance with compression enabled.

Great Read/Write Balance  – You find databases (or storage engines) are often classified into read optimized and write optimized, and even though you most likely heard about much better insert speed with Fractal Tree indexing, both for MySQL and MongoDB  you may not know that this is achieved with Read performance being in the same ballpark or better for many workloads. The difference is just not so drastic.

Multiple Clustered Keys  –  This is a great feature, which together with compression and low cost index maintenance, allows  TokuDB and TokuMX to reach much better performance for performance critical queries by clustering the data needed by such query together.

Messages    – When we’re speaking about conventional data structure such as B-trees or Hash tables, it is essentially a way data is stored and operations are being performed in it.  Fractal Tree indexing operates with a different paradigm which is focused around “Messages” being delivered towards the data to perform operations in questions.  This allows it to do a lot of clever stuff, such as implement more complex operations with the same message,  merge multiple messages together to optimize performance and use messages for internal purposes such as low overhead online optimization, table structure changes etc.

Low Overhead Maintenance  –  One of obvious uses of such Messages is  Low Overhead Maintenance.  The InnoDB storage engine allows you to add column “online,” which internally requires a full table rebuild, requiring a lot of time and resources for copy of the table.  TokuDB however, can use “broadcast message” to add the column which will become available almost immediately and will gradually physically propagate when data is modified. It is quite a difference!

Smart No-Read Updates –  Messages allow you to do smart complex updates without reading the data, dramatically improving performance.  For example this is used to implement “Read Free Replication”

Optimized In Memory Data Structures –  You may have heard a lot about in-memory databases, which are faster because they are using data structure optimized for properties on memory rather just caching the pages from disk, as, for example,  MyISAM and InnoDB do.   TokuDB and  TokuMX offer you the best of both worlds  by using memory optimized data structures for resident data and disk optimized data structures when data is pushed to disk.

Optimized IO  –  Whether you’re using legacy spinning media or Solid State Storage you will appreciate TokuDB having optimized IO – doing less and more sequential IO which helps spinning media performance, as well as dramatically reducing wear on flash, so you can improve longevity for your media or use lower cost storage.

Between the Tokutek engineering team and Percona we have a lot

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

Link
Peter ZaitsevPercona Toolkit 2.2.14 is now available (14.4.2015, 16:33 UTC)

Percona ToolkitPercona is pleased to announce the availability of Percona Toolkit 2.2.14.  Released April 14, 2015. Percona Toolkit is a collection of advanced command-line tools to perform a variety of MySQL server and system tasks that are too difficult or complex for DBAs to perform manually. Percona Toolkit, like all Percona software, is free and open source.

This release is the current GA (Generally Available) stable release in the 2.2 series. It includes multiple bug fixes for pt-table-checksum with better support for Percona XtraDB Cluster, various other fixes, as well as continued preparation for MySQL 5.7 compatibility. Full details are below. Downloads are available here and from the Percona Software Repositories.

New Features:

  • pt-slave-find can now resolve the IP address and show the slave’s hostname. This can be done with the new --resolve-address option.
  • pt-table-sync can now ignore the tables whose names match a specific Perl regex with the new --ignore-tables-regex option.

Bugs Fixed:

  • Fixed bug 925781: Inserting non-BMP characters into a column with utf8 charset would cause the Incorrect string value error when running the pt-table-checksum.
  • Fixed bug 1368244: pt-online-schema-change --alter-foreign-keys-method=drop-swap` was not atomic and thus it could be interrupted. Fixed by disabling common interrupt signals during the critical drop-rename phase.
  • Fixed bug 1381280: pt-table-checksum was failing on BINARY field in Primary Key. Fixed by implementing new --binary-index flag to optionally create checksum table using BLOB data type.
  • Fixed bug 1421405: Running pt-upgrade against a log with many identical (or similar) queries was producing repeated sections with the same fingerprint.
  • Fixed bug 1402730: pt-duplicate-key-checker was not checking for duplicate keys when --verbose option was set.
  • Fixed bug 1406390: A race condition was causing pt-heartbeat to crash with sleep argument error.
  • Fixed bug 1417558: pt-stalk when used along with --collect-strace didn’t write the strace output to the expected destination file.
  • Fixed bug 1421025: Missing dependency for perl-TermReadKey RPM package was causing toolkit commands to fail when they were run with --ask-pass option.
  • Fixed bug 1421781: pt-upgrade would fail when log contained SELECT...INTO queries. Fixed by ignoring/skipping those queries.
  • Fixed bug 1425478: pt-stalk was removin

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

Link
Valeriy KravchukFun with Bugs #35 - Bugs fixed in MySQL 5.6.24 (13.4.2015, 23:48 UTC)
I had not reviewed bug fixes in MySQL 5.6 for quite a some time, so I decided to check what bugs reported by MySQL Community were fixed in recently released MySQL 5.6.24. I'll mention both a bug reporter and engineer who verified the bug in the list below, because I still think that in MySQL world names should matter.

So, MySQL 5.6.24 includes fixes for the following bugs from http://bugs.mysql.com. I'd start with InnoDB and memcached-related fixes:
  • Bug #72080 - truncate temporary table crash: !DICT_TF2_FLAG_IS_SET(table, DICT_TF2_TEMPORARY). Reported by Doug Warner and verified by Shane Bester after a lot of testing. Note how fast it was fixed after verification!
  • Bug #75755 - Fulltext search behaviour with MyISAM vs. InnoDB (wrong result with InnoDB). Reported by Elena Stepanova from MariaDB and confirmed by my former boss Miguel Solorzano, this wrong results bug was also promptly fixed.
  • Bug #70055 - Expiration time ignored. This memcached-related bug was reported by Miljenko Brkic and verified by Umesh
  • Bug #74956 - Can not stop mysql with memcached plugin. This regression bug was reported by my colleague Nilnandan Joshi and verified by Umesh
  • Bug #75200 - MySQL crashed because of append operation. Reported by
    by already famous bug reporter (and developer) Zhai Weixiang, it was verified by Umesh and fixed fast enough.
    As you can see MySQL 5.6.24 fixed several more memcached-related bugs (reported internally), so if you use memcached it really makes sense to upgrade.
  • Bug #73361 - mutex contention caused by dummy table/index creation/free. Reported by Zhai Weixiang (who also suggested a patch) and verified by my dear friend and teacher Sinisa Milivojevic.  
Let's move on to partitioning. Just a couple of fixes there that fixed a long list of bugs reported by Percona QA engineers:
  •  Bug #74841 - handle_fatal_signal (sig=11) in cmp_rec_and_tuple | sql/sql_partition.cc:7610. This was reported by Percona's recent QA super star, Ramesh Sivaraman, and verified by Miguel Solorzano.
  • Bug #74860 - handle_fatal_signal (sig=11) in generate_partition_syntax. This was reported by Percona's all times QA superstar, Roel Van de Paar, and verified by Umesh.
  • Bug #74869 - handle_fatal_signal (sig=11) in ha_partition::handle_opt_partitions. It was reported by Ramesh Sivaraman, and verified by Miguel Solorzano.
  • Bug #74288 - Assertion `part_share->partitions_share_refs->num_parts >= m_tot_parts' failed. Reported by Roel Van de Paar and verified by Umesh.
  • Several other bugs mentioned remain private and not visible to us: Bug #74451, Bug #74478, Bug #74491, Bug #74560, Bug #74746, Bug #74634. I am not sure why they are private (or why the previous ones are still public, and for how long). Let's assume they were reported as private (and/or security ones) by my colleagues.
Now, only one replication bug reported at http://bugs.mysql.com was fixed, but serious one:
  • Bug #74607 - slave io_thread may get stuck when using GTID and low slave_net_timeouts. This bug was reported by Santosh Praneeth Banda and verified by Umesh.
There were several other bugs fixed in several categories:
  • Bug #74037 - group_concat_max_len=18446744073709547520 not accep

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

Link
Peter ZaitsevStaying ahead of MySQL operational problems at Percona Live (13.4.2015, 14:35 UTC)

Introducing 'MySQL 101,' a new 2-day track at Percona Live 2015!I’ve started my long journey from Florianópolis, Brazil, to Santa Clara, California and I type this words while waiting for a connection flight. Next Wednesday, Daniel Guzmán Burgos and I will be presenting in the Percona Live MySQL Conference and Expo (PLMCE).

I’m so excited with the new MySQL 101 program that has been added to this year’s event! Along the years I’ve been working as a Support Engineer at Percona I’ve heard two very distinct types of comments amongst others from some people, customers and community in general, about PLMCE:

1) That they went and it was awesome but they found it hard to follow as most of the contents in the program were high level for them;

2) that they wanted to attend the event but just didn’t felt experienced enough with MySQL just yet, so they would wait a year or two before committing to it.

Sometimes I’ve also been asked: “Don’t you guys organize a similar conference, but for beginners ?” Not exactly, I’d tell them, pointing them to the on-demand training that Percona offers, but this isn’t always a good fit for everybody.

Well, I’m glad that this request was reconsidered this year and that we now have an intensive MySQL 101 2-day program: “You send us developers and admins, and we’ll send you back MySQL DBAs.

Daniel and I will be talking about the use of a few key tools of the Percona Toolkit from the viewpoint of day-to-day operations of a MySQL DBA – we’ll showcase how some of them can be integrated to Nagios to improve monitoring. The goal: “stay ahead of MySQL operational problems,“which is also the base title of our talk.

Percona Toolkit is a rich collection of more than 30 command-line tools for MySQL, Percona Server and MariaDB that can help database administrators perform and automate a variety of database and system tasks. Some of the tools focus on diagnostic, some on performance improvement and some others are actually used to fix things. I just love them; they make my day-to-day work with MySQL that much easier!

I hope to see you in Santa Clara this week!

The post Staying ahead of MySQL operational problems at Percona Live appeared first on MySQL Performance Blog.

Link
Oli SennhauserLogging Galera Cluster conflicts (11.4.2015, 10:30 UTC)
Taxonomy upgrade extras: 

We typically suggest our customers to use our MySQL/Galera Cluster my.cnf configuration template to avoid MySQL configuration and performance problems.

And we are paranoid as well. Thus we enable all useful logging:

wsrep_log_conflicts = 1

But this has also some consequences of more visibility...

If you monitor carefully your Galera Cluster for example with the FromDual Performance Monitor for MySQL and MariaDB, you might probably see some strange values increasing from time to time:

mysql< SHOW GLOBAL STATUS LIKE 'wsrep_local_%r_s';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| wsrep_local_cert_failures | 42    |
| wsrep_local_bf_aborts     | 13    |
+---------------------------+-------+

Those values are indicators that some transactions (Galera write sets) did to not succeed and were aborted by Galera. In this case the paranoid logging helps to find, what exactly was aborted and possibly helps to find out, if this can or should be fixed:

150410  1:44:18 [Note] WSREP: cluster conflict due to certification failure for threads:
150410  1:44:18 [Note] WSREP: Victim thread:
   THD: 151856, mode: local, state: executing, conflict: cert failure, seqno: 30399304
   SQL: UPDATE login SET lTsexpire = UNIX_TIMESTAMP(NOW()) + lTimeout WHERE lSessionId = 'va3ta7besku82k56ncv3bnhlj5'

*** Priority TRANSACTION:
TRANSACTION 464359568, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
1 lock struct(s), heap size 360, 0 row lock(s)
MySQL thread id 4, OS thread handle 0x7f1c0916c700, query id 8190690 Update_rows_log_event::find_row(30399302)

*** Victim TRANSACTION:
TRANSACTION 464359562, ACTIVE 0 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 151856, OS thread handle 0x7f1c09091700, query id 8190614 172.20.100.11 sam_angiz query end
UPDATE login SET lTsexpire = UNIX_TIMESTAMP(now()) + lTimeout WHERE lSessionId = 'va3ta7besku82k56ncv3bnhlj5'
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 835205 page no 3 n bits 72 index `PRIMARY` of table `fromdual`.`login` trx table locks 1 total table locks 2  trx id 464359562 lock_mode X locks rec but not gap lock hold time 0 wait time before grant 0
150410  1:44:18 [Note] WSREP: cluster conflict due to high priority abort for threads:
150410  1:44:18 [Note] WSREP: Winning thread:
   THD: 4, mode: applier, state: executing, conflict: no conflict, seqno: 30399302
   SQL: (null)
150410  1:44:18 [Note] WSREP: Victim thread:
   THD: 151856, mode: local, state: committing, conflict: no conflict, seqno: -1
   SQL: UPDATE login SET lTsexpire = UNIX_TIMESTAMP(now()) + lTimeout WHERE lSessionId = 'va3ta7besku82k56ncv3bnhlj5'

In the above Galera conflict 2 login transactions where running at the same time. They both come with the same Session ID and want to update the expiry timestamp. Now how to solve or fix this:

  • First check, if this table has a Primary Key (tables without a PK causes full table scans which can last for long time, increasing the chance for conflicts).
  • Second check, if there is a (UNIQUE?) index on lSessionId. A missing index leads to full table scans which increases the chance for conflicts.
  • Third check WHY 2 logins from the same Session ID can arrive at the same time (within 1 second) on 2 different Galera nodes (Ajax requests, etc...). Try to avoid such situations.
Link
LinksRSS 0.92   RDF 1.
Atom Feed