MySQL information_schema comes with useful information about the database instance, status, … etc. which is needed for daily DBA work.
There are some simple queries on the information_schema that I use on my daily basis in which I’m writing this post for my reference and maybe a good reference for someone else too …

Finding tables without Primary or Unique Keys:

PKs are so important, especially, for InnoDB tables as MySQL uses PKs as a clustered index and having no PKs might lead to severe performance problems.

Also having no PKs is one of the main causes of slave lagging problems mainly when using RBR (Row-Based Replication), e.g. if a delete statement on the master will delete 1 million rows on a table without PK, a full table scan will take place. This “might” not be a problem on the master but on the slave 1 million full table scan will take place – because changes to the individual rows are being …

English: Madrid MySQL Users Group will be holding their next meeting on 17th June at 18:00h at EIE Spain in Madrid. Dimitri Vanoverbeke and Stéphane Combaudon from Percona will be offering two presentations for us: Practical MySQL optimisations Galera Cluster – introduction and where it fits in the MySQL eco-system I think this is an excellent moment … Continue reading MMUG13: Practical MySQL Optimisation y Galera Cluster presentations

The post MMUG13: Practical MySQL Optimisation y Galera Cluster presentations first appeared on Simon J Mudd's Blog.

February 4, 2015 By Severalnines Check Out Our Latest Technical Resources for MySQL, MariaDB & MongoDB Clusters

Like every month, we have created new content and tools for you; here is a summary of what we’ve published. Please do check it out and let us know if you have any comments or feedback.

New Live Technical Webinars

A DevOps Guide to Database Infrastructure Automation for eCommerce

Tuesday, February 17th

Infrastructure automation isn’t easy, but it’s not rocket science either, says Riaan Nolan. Automation is a worthwhile investment for retailers serious about eCommerce, but deciding on which …

January 30, 2015 By Severalnines

Thanks to everyone who participated in these two sessions this week! Please see below for details on next week’s follow up session 'Performance Tuning for HAProxy & MySQL'.

The topics covered this week included: 

• What is HAProxy?
• SQL Load balancing for MySQL
• Failure detection using MySQL health checks
• High Availability with Keepalived and Virtual IP
• Use cases: MySQL/MariaDB Galera Cluster, MySQL NDB …

January 27, 2015 By Severalnines

Unlike standard MySQL server and MySQL Cluster, the way to start a MySQL/MariaDB Galera Cluster is a bit different. Galera requires you to start a node in a cluster as a reference point, before the remaining nodes are able to join and form the cluster. This process is known as cluster bootstrap. Bootstrapping is an initial step to introduce a database node as primary component, before others see it as a reference point to sync up data.

How does it work?

When Galera starts with the bootstrap command on a node, that particular node will reach Primary state (check the value of wsrep_cluster_status). The remaining nodes will just require a normal start command and they will automatically look for existing Primary Component (PC) in the cluster and join to form a cluster. Data synchronization then happens through either incremental state transfer (IST) or …

A Percona Xtradb (Galera) Cluster node may fail to join it due to many possible mistakes causing SST to fail. It could be a configuration item or purely setup requirement. In this article we will be troubleshooting step by step the SST issues faced.

The post Debugging Percona Xtradb (Galera) Cluster node startup / SST errors first appeared on Change Is Inevitable.

Check Out Our Latest Technical Resources for MySQL, MariaDB & MongoDB Clusters

Like every month this year, we have created new content and tools for you; here is a summary of what we’ve published this December. Please do check it out and let us know if you have any comments or feedback.

And thank you for following us in the past 12 months and for your fidelity; we look forward to “seeing” you next year as well and wish you a great start to 2015!

New Live Technical Webinars

Infrastructure automation isn’t easy, but it’s not rocket science either, says Riaan Nolan. Riaan has been in operations for the past decade, and has built over a dozen eCommerce …

November 20, 2014 By Severalnines

Multi-source replication means that one server can have multiple masters from which it replicates. Why multi-source? One good reason is to consolidate databases (e.g. merge your shards) for analytical reporting or as a centralized backup server. MariaDB 10 already has this feature, and MySQL 5.7 will also support it. 

It is possible to set up your Galera Cluster as an aggregator of your masters in a multi-source replication setup, we’ll walk you through the steps in this blog. Note that the howto is for Galera Cluster for MySQL (Codership) and Percona XtraDB Cluster. In a separate post, we’ll show you how to configure MariaDB Cluster 10 instead. If you would like to use MySQL Cluster (NDB) as aggregator, then check out this blog.

Galera …

October 31, 2014 By Severalnines

When running a MariaDB Cluster or Percona XtraDB Cluster, it is common to use a load balancer to distribute client requests across multiple database nodes. Load balancing SQL requests aims to optimize the usage of the database nodes, maximize throughput, minimize response times and avoid overload of the Galera nodes. 

In this blog post, we’ll take a look at four different open source load balancers, and do a quick benchmark to compare performance:

• HAproxy by HAproxy Technologies
• IPVS by Linux Virtual Server Project
• Galera Load Balancer by Codership
• mysqlproxy by Oracle (alpha)

Note that there are other options out there, e.g. MaxScale from the MariaDB team, that we plan to cover in a future post.

…

A few weeks ago, we received an interesting Galera Cluster support case from one of our customers that the application is not working well and they face a lot of troubles in their Galera Cluster setup.

After some investigations, we found a lot of insert queries in state "query end" and lasting for long time without being completed. Also some other queries which were sleeping for long time having the info of "XA COMMIT":

SQL> SHOW PROCESSLIST;

27      user    host:33214      foodmart        Query   14440   sleeping        XA COMMIT 0x31302e31312e31332e34372e746d30303336383030303031,0x31302e31312e31332e34372e746d333638,0x

SQL> SHOW ENGINE INNODB STATUS;

TRANSACTIONS
============
---TRANSACTION 2DE71D, ACTIVE 14459 sec
9 lock struct(s), heap size 1248, 1 row lock(s), undo log entries 115
MySQL thread id 27, OS thread handle 0x7fc21a42c700, query id 96187 host host-ip foodmart …