Oli SennhauserGalera Cluster last inactive check and VMware snapshots (11.4.2015, 09:46 UTC)
Taxonomy upgrade extras: 

From time to time we see at Galera Cluster customer engagements the following, for me scary, warning in the MySQL error log:

[Warning] WSREP: last inactive check more than PT1.5S ago (PT7.06159S), skipping check

We mostly see this in VMware set-ups. Some further enquiry with the Galera developers did not give a satisfying answer:

This can be seen on bare metal as well - with poorly configured mysqld, O/S, or simply being overloaded. All it means is that this thread could not get CPU time for 7.1 seconds. You can imagine that access to resources in virtual machines is even harder (especially I/O) than on bare metal, so you will see this in virtual machines more often.

This is not a Galera specific issue (it just reports being stuck, other mysqld threads are equally stuck) so there is no configuration options for that. You simply must make sure that your system and mysqld are properly configured, that there is enough RAM (buffer pool not over provisioned), that there is swap, that there are proper I/O drivers installed on guest and so on.

Basically, Galera runs in virtual machines as well as well virtual machines approximates bare metal.

We were still suspecting that this is somehow VMware related. This week we had the chance to investigate... At 01:36 am node Galera2 lost connection to the Cluster and became NON-PRIMARY. This is basically a bad sign:

150401  1:36:15 [Warning] WSREP: last inactive check more than PT1.5S ago (PT5.08325S), skipping check
150401  1:36:15 [Note] WSREP: (09c6b2f2, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://192.168.42.2:4567
150401  1:36:16 [Note] WSREP: view(view_id(NON_PRIM,09c6b2f2,30) memb {
        09c6b2f2,0
} joined {
} left {
} partitioned {
        ce6bf2e1,0
        d1f9bee0,0
})
150401  1:36:16 [Note] WSREP: view(view_id(NON_PRIM,09c6b2f2,31) memb {
        09c6b2f2,0
} joined {
} left {
} partitioned {
        ce6bf2e1,0
        d1f9bee0,0
})
150401  1:36:16 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1
150401  1:36:16 [Note] WSREP: Flow-control interval: [16, 16]
150401  1:36:16 [Note] WSREP: Received NON-PRIMARY.
150401  1:36:16 [Note] WSREP: Shifting SYNCED -> OPEN (TO: 26304132)
150401  1:36:16 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1
150401  1:36:16 [Note] WSREP: Flow-control interval: [16, 16]
150401  1:36:16 [Note] WSREP: Received NON-PRIMARY.
150401  1:36:16 [Warning] WSREP: Send action {(nil), 328, TORDERED} returned -107 (Transport endpoint is not connected)
150401  1:36:16 [Note] WSREP: New cluster view: global state: dcca768c-b5ad-11e3-bbc0-fb576fb3c451:26304132, view# -1: non-Primary, number of nodes: 1, my index: 0, protocol version 3
150401  1:36:17 [Note] WSREP: (09c6b2f2, 'tcp://0.0.0.0:4567') reconnecting to d1f9bee0 (tcp://192.168.42.1:4567), attempt 0

I suspected, after some investigation with the FromDual Performance Monitor for MySQL and MariaDB, that the database backup (mysqldump) could be the reason. It was not. But the customer explained, that after the database backup they do a VMware snapshot.

And when we compared our problem with the backup log file:

2015/04/01 01:35:08 [3] backup.fromdual.com: Creating a snapshot of galera3
2015/04/01 01:35:16 [3] backup.fromdual.com: Created a snapshot of galera3
2015/04/01 01:35:23 [3] backup.fromdual.com: galera3: backup the changed blocks of disk 'Festplatte 1' using NBD transport
2015/04/01 01:36:10 [3] backup.fromdual.com: galera3: saving the Change Block Tracking's reference for disk 'Festplatte 1'
2015/04/01 01:36:10 [3] backup.fromdual.com: Removing Arkeia's snapshot of galera3

we can see that our problem pretty much started with the end of the WMware snapshot (01:36:10 + 5.08 = 1:36:15). By the way: For such kind of investigations it is always good to have a ntp daemon for time synchronization running. Otherwise problem investigation becomes much

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

Link
Peter ZaitsevMeasuring the impact of tcpdump on Very Busy Hosts (10.4.2015, 14:19 UTC)

A few years back Deva wrote about how to use tcpdump on very busy hosts. That post sparked my interest about exploring how to measure the impact of tcpdump on very busy hosts. In this post, I wanted to highlight how much of an impact there really is and what options you have to make the query collection much more effective.

Some things you need to know:

  • The test is a sysbench read-only workload, 8 tables, 8 threads, 1000000 rows each with 16G of buffer pool. Dataset fully in memory.
  • sysbench is ran on the same host, on 1Gbps connection, sysbench can saturate the network and therefore affect my network test with netcat so I decided to run locally.
  • There are 13 tests, 5 minutes each with 1 minute interval, varying on how the dump file is captured.
    • First one as baseline is the MySQL slow query log.
      A:
      mysql -e 'set global long_query_time=0, slow_query_log=1; select sleep(300); set global long_query_time=1, slow_query_log=0;'
      
    • Second group is tcpdump with -w option, which means tcpdump itself is writing to the capture file.
      B:
      $DUMPCMD -i any -G 300 -W 1 -Z root -w tcpdump.pcap port 3306
      
      C:
      $DUMPCMD -i any -G 300 -W 1 -Z root -w tcpdump.pcap 'port 3306 and tcp[1] & 7 == 2 and tcp[3] & 7 == 2'
      
      D:
      $DUMPCMD -i any -G 300 -W 1 -Z root -w tcpdump.pcap 'port 3306 and ( tcp[1] & 7 == 2 or tcp[3] & 7 == 2 )'
      
    • Third group, is using “packet-buffered” (-U option) to see if there will be improvement on response time.
      E:
      $DUMPCMD -i any -G 300 -W 1 -Z root -U -w tcpdump.pcap port 3306
      
      F:
      $DUMPCMD -i any -G 300 -W 1 -Z root -U -w tcpdump.pcap 'port 3306 and tcp[1] & 7 == 2 and tcp[3] & 7 == 2'
      
      G:
      $DUMPCMD -i any -G 300 -W 1 -Z root -U -w tcpdump.pcap 'port 3306 and ( tcp[1] & 7 == 2 or tcp[3] & 7 == 2 )'
      
    • Next streams the backup to a remote location via netcat.
      H:
      $DUMPCMD -i any -G 300 -W 1 -Z root -w - port 3306 | nc remote_ip 33061
      
      I:
      $DUMPCMD -i any -G 300 -W 1 -Z root -U -w - port 3306 | nc remote_ip 33062
      
      J:
      $DUMPCMD -i any -G 300 -W 1 -Z root -U -w - 'port 3306 and tcp[1] & 7 == 2 and tcp[3] & 7 == 2' | nc remote_ip 33063
      
    • The last group, the one most of us are probably accustomed with is piping the dumped packets to file.
      K:
      timeout -s KILL 300 $DUMPCMD -i any port 3306 > tcpdump.pcap
      
      L:
      timeout -s KILL 300 $DUMPCMD -i any 'port 3306 and tcp[1] & 7 == 2 and tcp[3] & 7 == 2' > tcpdump.pcap
      
      M:
      timeout -s KILL 300 $DUMPCMD -i any 'port 3306 and ( tcp[1] & 7 == 2 or tcp[3] & 7 == 2 )' > tcpdump.pcap
      
    • $DUMPCMD
      
        is defined as
      tcpdump -s 65535 -x -nn -q -tttt
      
  • On each group there is an AND and OR variation in port filtering. I wanted to see whether how much of additional impact port expressions have. And as you will see below, they do not have significant impact on performance, but on number of queries captured.

I’ve graphed the sysbench data during the test and labeled each test for easy comparison on the graphs.

Sysbench_TPS-legend Sysbench_Response_Time-legened

Of course, I’ve also compared the size of resulting tcpdump capture and total queries identified when run through pt-query-digest.

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

Link
Jean-Jerome SchmidtAutomation & Management of Galera Clusters for MySQL, MariaDB & Percona XtraDB: New 1-Day Online Training Course (10.4.2015, 11:48 UTC)

Galera Cluster For System Administrators, DBAs And DevOps

Galera Cluster for MySQL, MariaDB and Percona XtraDB involves more effort and resource to administer than standalone systems. If you would like to learn how to best deploy, monitor, manage and scale your database cluster(s), then this new online training course is for you!

The course is designed for system administrators & database administrators looking to gain more in depth expertise in the automation and management of Galera Clusters.

What: A one-day, instructor-led, Galera Cluster management training course

When: The first training course will take place on June 12th 2015 - European time zone
Please register your interest also if you’re outside of that time zone, as we will be scheduling further dates/courses

Where: In a virtual classroom as well as a virtual lab for hands-on lab exercises

How: Reserve your seat online and we will contact you back with all the relevant details

Who: The training is delivered by Severalnines & BOS-it GmbH 

  • You will learn about:
  • Galera Cluster, system architecture & multi-data centre setups
  • Automated deployment & node / cluster recovery
  • How to best migrate data into Galera Cluster
  • Monitoring & troubleshooting basics
  • Load balancing and cluster management techniques

GaleraCluster_logo.png

This course is all about hands-on lab exercises! Learn from the experts without having to leave your home or office!

High availability cluster configurations tend to be complex, but once designed, they tend to be duplicated many times with minimal variation. Automation can be applied to provisioning, upgrading, patching and scaling. DBAs and Sysadmins can then focus on more critical tasks, such as performance tuning, query design, data modeling or providing architectural advice to application developers. A well managed system can mitigate operational risk, that can result in significant savings and reduced downtime. 

To learn how to best deploy, monitor, manage and scale Galera Cluster, click here for more information and to sign up. 

The number of seats is limited, so make sure you register soon!

severalnines_logo.jpgScreen Shot 2015-04-10 at 10.32.24.png

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

Link
Peter ZaitsevInnoDB locks and deadlocks with or without index for different isolation level (9.4.2015, 18:27 UTC)

Recently, I was working on one of the issue related to locks and deadlocks with InnoDB tables and I found very interesting details about how InnoDB locks and deadlocks works with or without index for different Isolation levels.

Here, I would like to describe a small test case about how SELECT ..FOR UPDATE (with and without limit) behave with INSERT/UPDATE and with READ-COMMITED and REPEATABLE-READ Isolation levels. I’m creating a small table data_col with few records. Initially, this test case was written by Bill Karwin to explain details to customer, but here I have used a bit modified test case.

CREATE TABLE data_col (dataname VARCHAR(10), period INT, expires DATE, host VARCHAR(10));

INSERT INTO data_col VALUES (‘med1′, 1,’2014-01-01 00:00:00′, ‘server1′);
INSERT INTO data_col VALUES (‘med2′, 1,’2014-02-15 00:00:00′, ‘server2′);
INSERT INTO data_col VALUES (‘med3′, 1,’2014-03-20 00:00:00′, ‘server3′);
INSERT INTO data_col VALUES (‘med4′, 1,’2014-04-10 00:00:00′, ‘server4′);
INSERT INTO data_col VALUES (‘med5′, 1,’2014-05-01 00:00:00′, ‘server5′);

Case 1: No index on expires, tx_isolation=READ-COMMITTED.

Session 1: SET tx_isolation=’READ-COMMITTED'; START TRANSACTION;
Session 2: SET tx_isolation=’READ-COMMITTED'; START TRANSACTION;
Session 1: SELECT * FROM data_col WHERE expires < ‘2014-03-01′ ORDER BY expires LIMIT 1 FOR UPDATE;
Session 2: INSERT INTO data_col VALUES (‘med6′, 1,’2014-06-03 00:00:00′, ‘server6′); /* success */
Session 1: SELECT * FROM data_col WHERE expires < ‘2014-07-01′ ORDER BY expires LIMIT 1 FOR UPDATE; /* hangs */
Session 2: SELECT * FROM data_col WHERE expires < ‘2014-07-01′ ORDER BY expires LIMIT 1 FOR UPDATE; /* deadlocks */

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-04-04 12:35:45 7f3f0a084700
*** (1) TRANSACTION:
TRANSACTION 28960, ACTIVE 24 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 7 row lock(s)
MySQL thread id 39, OS thread handle 0x7f3f0a0b5700, query id 158 localhost root Creating sort index
SELECT * FROM data_col WHERE expires < '2014-07-01' ORDER BY expires LIMIT 1 FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 14 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `nil`.`data_col` trx id 28960 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 28961, ACTIVE 17 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 40, OS thread handle 0x7f3f0a084700, query id 159 localhost root Creating sort index
SELECT * FROM data_col WHERE expires < '2014-07-01' ORDER BY expires LIMIT 1 FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 14 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `nil`.`data_col` trx id 28961 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 14 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `nil`.`data_col` trx id 28961 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)

With READ-COMMITTED, even If Session 1 locks records with condition “expires < ‘2014-03-01′ “, Session 2 can Insert the record as Session 1 is not using gap lock (lock_mode X locks rec but not gap waiting) and we can insert/update the records outside of set of Session 1 examined. But when Session 1 tried to acquire locks on higher range (“expires < ‘2014-07-01′ “), it will be hanged and if we do the same thing from Session 2, it will turn to deadlock.

Here, When there is no primary key, InnoDB table will create it’s own cluster index, which is GEN_CLUST_INDEX.

Case 2: No index on expires, tx_isolation=REPEATABLE-READ.

Session 1: SET tx_isolation=’REPEATABLE-READ'; START TRANSACTION;
Session 2: SET tx_isolation=’REPEATABLE-READ'; START TRANSACTION;
Session 1: SELECT * FROM data_col WHERE expires < ‘2014-03-01′ ORDER BY expires LIMIT 1 FOR UPDATE;
Session 2: INSERT INTO data_col V

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

Link
Peter Zaitsevpquery binaries with statically included client libs now available! (9.4.2015, 18:00 UTC)

pquery LogoAfter we released pquery to the community, and as we started logging bug reports with pquery testcases, it quickly became clear that pquery binaries with statically compiled-in client libraries would be of great convenience, both for ourselves and for the community.

(If you haven’t heard about pquery yet, read the pquery introduction blog post, come and join the pquery introduction lightning talk at Percona Live (15 April just around 6PM in Hall A), or keep an eye out for some of the upcoming episodes in the MySQL QA Series.)

While we were in the process of creating these binaries (which turned out to be not as straighforward as we thought it would be), we also disovered a rather significant memory de-allocation bug in pquery, which would at times have caused pquery to crash (segfault). I want to especially thank Sergei for helping get the client libraries compiled into pquery, as well as fixing the segfault memory de-allocation bug and some other problems we found, and Ramesh who helped with binary testing and debugging.

I also want to thank Sveta (previously at Oracle, now at Percona), Umesh (Oracle), and Satya Bodapati (Oracle) for not giving up too easily when we logged some bug reports with dynamically linked (i.e. client libs not included) and at times failing pquery binaries!

Now, the pquery binaries with statically included client binaries are finally ready! We have pquery-ps (with static Percona Server 5.6 client libs), pquery-ms (with static MySQL 5.6 client libs), and pquery-md (with static MariaDB 5.5 client libs). The pquery binaries can also be used to test  any other community solution or product. For example, we use it to test our beloved Percona XtraDB Cluster (PXC) using specially developed pquery+Docker+PXC scripts.

Things have also continued to evolve quickly in the pquery framework, as well as in the accompanying reducer.sh ($ bzr branch lp:randgen – available as randgen/util/reducer/reducer.sh), so check out the many updates now! To get it, $ bzr branch lp:percona-qa and start by having a look at pquery-run.sh

You may also like to checkout our latest pquery-reach.sh and pquery-reach++.sh – which are a wrapper around most of the major pquery framework tools. Not as straightforward to use and setup as pquery-run.sh (as it requires setup within the sub-scripts it uses…), but reviewing pquery-reach.sh will give you a good idea on how to setup pquery-run.sh and get into things.

Stay tuned for the upcoming MySQL QA episodes (link above), and you’ll soon be hunting bugs like Mr. Nuclear! (To meet (the nice) Mr. Nuclear, come and see our lightning talk at Percona Live!)

Enjoy!

The post pquery binaries with statically included client libs now available! appeared first on MySQL Performance Blog.

Link
Valeriy KravchukUsing gdb to understand what locks (and when) are really set by InnoDB. Part II. (8.4.2015, 20:23 UTC)
In the previous post we checked lock requests while executing INSERT INTO t ... SELECT FROM tt where there is an auto_increment column for which the value is generated in the destination table with default innodb_autoinc_lock_mode=1. Based on it I've reported Bug #76563 that is already verified.

Let's continue to study a special case of the statement mentioned above, INSERT INTO t ... SELECT FROM t, that is, when source and destination table is the same. We again start with table t having just 4 rows:

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
 

mysql> select * from t;
+----+------+
| id | val  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)


mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t(val) select 100 from t;


Tracing in gdb with the same breakpoints set shows the following:

[Switching to Thread 0x7fd74cf79700 (LWP 1866)]

Breakpoint 1, lock_table (flags=0, table=0x7fd7233f69e8, mode=LOCK_IS, thr=0x7fd7233de6f0) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/lock0lock.cc:4426
4426            if (flags & BTR_NO_LOCKING_FLAG) {
(gdb) p table->name
$41 = 0x7fd7383feac0 "test/t"

So, we start with IS lock on the table. Probably we plan to read some rows. Let's continue:

(gdb) c
Continuing.


Breakpoint 2, lock_rec_lock (impl=0, mode=2, block=0x7fd7249af000, heap_no=2, index=0x7fd7233f7b68, thr=0x7fd7233de6f0) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/lock0lock.cc:2329
2329            switch (lock_rec_lock_fast(impl, mode, block, heap_no, index, thr)) {
(gdb) p index->table_name
$42 = 0x7fd7383feac0 "test/t"
(gdb) c
Continuing.

Breakpoint 2, lock_rec_lock (impl=0, mode=2, block=0x7fd7249af000, heap_no=3, index=0x7fd7233f7b68, thr=0x7fd7233de6f0) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/lock0lock.cc:2329
2329            switch (lock_rec_lock_fast(impl, mode, block, heap_no, index, thr)) {
(gdb) c
Continuing.

Breakpoint 2, lock_rec_lock (impl=0, mode=2, block=0x7fd7249af000, heap_no=4, index=0x7fd7233f7b68, thr=0x7fd7233de6f0) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/lock0lock.cc:2329
2329            switch (lock_rec_lock_fast(impl, mode, block, heap_no, index, thr)) {
(gdb) c
Continuing.

Breakpoint 2, lock_rec_lock (impl=0, mode=2, block=0x7fd7249af000, heap_no=5, index=0x7fd7233f7b68, thr=0x7fd7233de6f0) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/lock0lock.cc:2329
2329            switch (lock_rec_lock_fast(impl, mode, block, heap_no, index, thr)) {
(gdb) c
Continuing.

Breakpoint 2, lock_rec_lock (impl=0, mode=2, block=0x7fd7249af000, heap_no=1, index=0x7fd7233f7b68, thr=0x7fd7233de6f0) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/lock0lock.cc:2329
2329            switch (lock_rec_lock_fast(impl, mode, block, heap_no, index, thr)) {


We set S (mode=2) locks on each row in the "source" (t) table and supremum record there, to begin with. We read all rows that we plan to insert into the temporary table be

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

Link
Peter ZaitsevMore on OpenStack Live and our talks at OpenStack Summit Vancouver (8.4.2015, 13:34 UTC)

In April and May, Percona will hold and participate in two OpenStack events: OpenStack Live and the OpenStack Summit. Join our talks at these events in Santa Clara and Vancouver for new insights into the MySQL operations of the core of OpenStack as well as the latest information on MySQL guest instances.

Bay Area OpenStack Live 2015Next week (April 13-14), Percona will host OpenStack Live at the Santa Clara Convention Center. Conveniently located for those in the Bay Area, this two day, user-focused event will cover many core aspects of OpenStack including Nova, Swift, Neutron, and Trove as well as related technologies like Ceph and Docker. (OpenStack Live passes include access to keynotes and the expo hall at the Percona Live MySQL Conference.)

OpenStack Live will be a packed two days with 6 hands-on tutorials, 18 sessions, and keynotes and panel discussions featuring speakers from Facebook, EMC, ObjectRocket, Percona, Yahoo, VMware, Deep Information Sciences, and special guest, Steve Wozniak. Yes, the real Steve Wozniak.

Of course I’m looking forward to my session, “An introduction to Database as a Service with an emphasis on OpenStack using Trove,” with Amrith Kumar, Founder and CTO of Tesora, on Tuesday from 11:30am – 12:20pm in Room 209. Some other topics you shouldn’t miss include:

Tutorial: Deploying, Configuring and Operating OpenStack Trove
Monday, April 13: 9:30am – 12:30pm in Room 203
Amrith Kumar (Tesora) and Sriram Kalyanasundaram (Tesora) will lead a 3 hour hands-on tutorial on Trove DBaaS. It’s rare to get such a focused tutorial on this key component of OpenStack and led by Tesora, major contributors to the Trove project. If you are even considering implementing Trove for your cloud, this is a must-attend class.

Tutorial: How to Get Your Groove on with OpenStack Swift Object Storage
Monday, April 13: 1:30pm – 4:30pm in Room 204
John Dickinson (OpenStack Swift), Manzoor Brar (SwiftStack), and Sergei Glushenko (Percona) will lead the audience on a journey into Swift, OpenStack’s object store project. John, the Swift “Project Team Lead” (PTL), and Manzoor will show you how to deploy a Swift cluster, use it with real applications, and, with the assistance of Sergei from Percona, how to properly backup MySQL databases to a Swift cluster.

Sessions which I’d recommend attending are:

Session: MySQL and OpenStack deep dive
Tuesday, April 14: 11:30am – 12:20pm in Room 204
Peter Boros (Percona)

Session: Deploying a OpenStack Cloud at Scale at Time Warner Cable
Tuesday, April 14: 1:20pm – 2:10pm in Room 203
Matthew Fischer (Time Warner Cable), Clayton O’Neill (Time Warner Cable)

Session: Designing a highly resilient Network Infrastructure for OpenStack Clouds
Tuesday, April 14: 3:50pm – 4:40pm in Room 203
Pere Monclus (PLUMgrid)

This is only a small sample of the tutorials and talks which you will hear at OpenStack Live. If you’re in the Bay Area next week and are interested in OpenStack or just want to learn more about some of the core components,

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

Link
Jean-Jerome SchmidtDeploy an asynchronous slave to Galera Cluster for MySQL - The Easy Way (7.4.2015, 09:18 UTC)

Due to its synchronous nature, Galera performance can be limited by the slowest node in the cluster. So running heavy reporting queries or making frequent backups on one node, or putting a node across a slow WAN link to a remote data center might indirectly affect cluster performance. Combining Galera and asynchronous MySQL replication in the same setup, aka Hybrid Replication, can help. A slave is loosely coupled to the cluster, and will be able to handle a heavy load without affecting the performance of the cluster. The slave can also be a live copy of the Galera cluster for disaster recovery purposes.

We had explained the different steps to set this up in a previous post. With ClusterControl 1.2.9 though, this can be automated via the web interface. A slave can be bootstrapped with a Percona XtraBackup stream from a chosen Galera master node. In case of master failure, the slave can be failed over to replicate from another Galera node. Note that master failover is available if you are using Percona XtraDB Cluster or the Codership build of Galera Cluster with GTID. If you are using MariaDB, ClusterControl supports adding a slave but not performing master failover. 

 

Preparing the Master (Galera Cluster)

MySQL replication slave requires at least a master with GTID enabled on the Galera nodes. However, we would recommend users to configure all Galera nodes as master for better failover. GTID is required as it is used to do master failover. If you are running on MySQL 5.5, you might need to upgrade to MySQL 5.6

The following must be true for the masters:

  • At least one master among the Galera nodes
  • MySQL GTID must be enabled
  • log_slave_updates must be enabled
  • Master’s MySQL port is accessible by ClusterControl and slaves

To configure a Galera node as master, change the MySQL configuration file for that node as per below:

server_id=<must be unique across all mysql servers participating in replication>
binlog_format=ROW
log_slave_updates=1
log_bin=binlog
gtid_mode=ON
enforce_gtid_consistency=1

 

Preparing the Slave

For the slave, you would need a separate host or VM, with or without MySQL installed. If you do not have a MySQL installed, and choose ClusterControl to install the MySQL on the slave, ClusterControl will perform the necessary actions to prepare the slave, for example, configure root password (based on monitored_mysql_root_password), create slave user (based on repl_user, repl_password), configure MySQL, start the server and also start replication. The MySQL package used will be based on the Galera vendor used, for example, if you are running Percona XtraDB Cluster, ClusterControl will prepare the slave using Percona Server.

In short, we must perform following actions beforehand:

  • The slave node must be accessible using passwordless SSH from the ClusterControl server
  • MySQL port (default 3306) and netcat port 9999 on the slave are open for connections.
  • You must configure the following options in the ClusterControl configuration file for the respective cluster ID under /etc/cmon.cnf or /etc/cmon.d/cmon_<cluster ID>.cnf:
    • repl_user=<the replication user>
    • repl_password=<password for replication user>
    • monitored_mysql_root_password=<the mysql root password of all nodes including slave>
  • The slave configuration template file must be configured beforehand, and must have at least the following variables defined in the MySQL configuration template:
    • server_id
    • basedir
    • datadir

To prepare the MySQL configuration file for the slave, go to ClusterControl > Manage > Configurations > Template Configuration files > edit my.cnf.slave and add the following lines:

[mysqld]
bind-address=0.0.0.0
gtid_mode=ON
log_bin=binlog
log_s

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

Link
Open Querymysql-cli on Kickstarter (7.4.2015, 07:16 UTC)

Open Query is supporting the mysql-cli Kickstarter project (for MySQL and MariaDB) by Amjith Ramanujam who already successfully completed a similar tool for PostgreSQL.

It is a new MySQL client with Auto-Completion and Syntax Highlighting. From the info provided, it’s Python based, thus portable, and can be installed without root access. Could be a very useful tool. The good old mysql command line client does lack some things, yet a relatively low-level command line client is often useful for remote tasks (as opposed to graphical tools) so we reckon it’s good that this realm gets a bit of attention!

Link
Peter ZaitsevMore on (transactional) MySQL metadata locks (7.4.2015, 07:00 UTC)

Two years ago Ovais Tariq had explained in detail what kinds of problems existed before MySQL introduced metadata locks in 5.5.3 and how these locks help to prevent them. Still, some implications of metadata locking in MySQL remain unclear for users – DBAs and even software developers that target recent MySQL versions. I’ve decided to include a slide or two into the presentation about InnoDB locks and deadlocks I plan to make (with my colleague Nilnandan Joshi) on April 16 at Percona Live 2015.

I decided to do this as recently I’ve got an issue to work on where it was claimed that the behavior of SELECT blocking TRUNCATE TABLE is wrong, just because transaction isolation level was set to READ COMMITTED and thus there should be no locks set by SELECT and transaction should not even start no matter what the value of autocommit is (it was explicitly set to 0 by smart software).

The MySQL manual clearly says:

“To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table’s structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.”

So, the real challenge was to show these metadata locks still set in a transaction that started implicitly, by SELECT immediately following SET autocommit=0 in a session. It was a good chance to check how metadata locks are exposed in MySQL 5.7 via Performance Schema, so I’ve set up a simple test.

First of all, I’ve enabled instrumentation for metadata locks:

[openxs@centos 5.7]$ bin/mysql --no-defaults -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.7.6-m16 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'global_instrumentation';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

Then I’ve set up a simple test based on the details from the issue (I’ve create the InnoDB table, t, and added a row to it before this):

mysql> set session autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit, @@tx_isolation;
+--------------+----------------+
| @@autocommit | @@tx_isolation |
+--------------+----------------+
| 0 | READ-COMMITTED |
+--------------+----------------+
1 row in set (0.00 sec)
mysql> select * from t limit 1;
+----+------+
| id | val |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.00 sec)

Now, from another session I tried to TRUNCATE the table before the fist session got a chance to do explicit or implicit COMMIT (In the issue I mentioned software used just had not cared to do this, assuming transaction had not started. It worked with MySQL 5.1 really well that way.)

mysql> truncate table t;

I was not surprised that TRUNCATE hung. Manual clearly says that until transaction is committed we do not release

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

Link
LinksRSS 0.92   RDF 1.
Atom Feed