A few things to note recently, amongst MariaDB in distributions. 

1. Ubuntu keeps MySQL 5.5 despite MariaDB’s success. There’s a lot of reasons for this, but remember the key takeaway here is MySQL 5.5 & the fact that MariaDB wasn’t even in Debian yet when the decision was made.
2. MariaDB is now inside of Debian/sid – check out the packages.
3. RHEL 7 comes with MariaDB 5.5 as a default; this is a good thing.

Now, from a distribution standpoint, we’re looking at starting to ship 10.0 as …

Your MySQL server is overloaded. You see hundreds of running queries in the SHOW PROCESSLIST taking many seconds to run, or can’t connect at all because all connections slots are busy. If you have worked with MySQL long enough you surely have seen it, probably more than once. This is what I would call “MySQL server overload” – having more work than the server can possibly handle. At this point I see people often jumping to the conclusion that something went wrong with MySQL and focus all their effort on this belief. This is also often how we see questions framed when they are filed with our Support or to Emergency Consulting.

In fact there are two very distinct causes for such a situation – and to find the resolution most effectively you need to understand what …

Busy database-backed websites often hit scalability limits in the database first. In tuning MySQL, one of the first things to look at is the max_connections parameter, which is often too low. (Of course another thing to look at is appropriate fragment caching in your app server, HTTP object caching in your web server, and a CDN in front of it all.)

When using MySQL 5.5 from Oracle's RPMs through cPanel (MySQL55-server-5.5.32-1.cp1136) on RHEL 5.10 x86_64, there is an interesting problem if you try to increase the max_connections setting beyond 214 in /etc/my.cnf. It will silently be ignored, and the limit remains 214:

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

The problem is that the maximum number of open files allowed is too small, by default 1024, to …

One of the routine tasks for a DBA is renaming database schemas, and as such MySQL added a command to carry out that purpose called “RENAME DATABASE <database_name>”. However this command just made it through a few minor releases before being discontinued (from MySQL 5.1.7 to 5.1.23). Here’s a link to the reference manual regarding the command http://dev.mysql.com/doc/refman/5.1/en/rename-database.html. Vadim wrote a MySQL Performance Blog post about this a few years ago where he mentions the dangerous nature of this command – that post was appropriately headlined, “Dangerous Command.” Today we will see what are the ways in which a database schema can be renamed and which of them is the quickest.

Method 1: A …

As noted in a previous post, MySQL Connector/Java supports multi-master replication topographies as of version 5.1.27, allowing you to scale read load to slaves while directing write traffic to multi-master (or replication ring) servers.  The new release of version 5.1.28 builds upon this, allowing live management of replication host (single or multi-master) topographies.  This parallels functionality that has long existed for load-balanced connections, and enables users to add or remove hosts – or now promote slaves – for Java applications without requiring application restart.  This post aims to …

December 23, 2013 By Severalnines

This post is a continuation of our previous post on Online Schema Upgrade in Galera using TOI method. We will now show you how to perform a schema upgrade using the Rolling Schema Upgrade (RSU) method. This requires that the new schema is backward compatible with the old schema, so it requires a bit more planning than the TOI method. In some cases, you might need to turn off any connections to the target node. 

If you are using non-uniform hardware across your Galera cluster, RSU might be preferable as the more predictable process. RSU does not slow down your cluster.

As described in details in the previous …

It is wonderful to see some of my original pet peeves fixed in MySQL 5.7.3! It has not even taken 10 years

I remember when starting my work at MySQL Support that I would recommend using UNION ALL instead of plain UNION because it would not require duplicate elimination, and as such, would not require the creation of a temporary table in basic cases. Guess what? MySQL actually did it anyway. The learning experience from this incident was to never use common sense alone when thinking about MySQL – test and validate your assumptions because for one reason or another things may work differently from what “obviously makes sense.” I do not want to single out MySQL – this approach has helped me to be successful with problem analyses of all kinds of software.

The Multi Column IN optimization is another one that …

The third, and final article in my series on migrating data to and from Hadoop and SQL databases is now available:

Big data is a term that has been used regularly now for almost a decade, and it — along with technologies like NoSQL — are seen as the replacements for the long-successful RDBMS solutions that use SQL. Today, DB2®, Oracle, Microsoft® SQL Server MySQL, and PostgreSQL dominate the SQL space and still make up a considerable proportion of the overall market. In this final article of the series, we will look at more automated solutions for migrating data to and from Hadoop. In the previous articles, we concentrated on methods that take exports or otherwise formatted and extracted data from your SQL source, load that into Hadoop in some way, then process or parse it. But if you want to analyze big data, you probably don’t want to wait while exporting the data. Here, we’re going to look at some methods and tools that enable a …

Several users have reported that certain queries with IN predicates can’t use index scans even though all the columns in the query are indexed. What’s worse, if you reformulate your query without IN, the indexes are used. Let’s take some example query. Suppose we have a table with two indexed columns:CREATE TABLE t1 ( 
  col1 INTEGER,
  col2 INTEGER,
  …   KEY key1( col1, col2 ) ); Let’s take a look at some queries that could take advantage of the key1 index to read rows without accessing the table.

1. SELECT col1, col2 FROM t1 WHERE col1 = 100;
2. SELECT col1, col2 FROM t1 WHERE col1 > 100 AND col1 < 200;
3. SELECT col1, col2 FROM t1 WHERE col1 > 100 AND col1 < 200 OR col1 > 300 AND col1 < 400;
4. SELECT col1, col2 FROM t1 WHERE col1 = 100 AND col2 > 100 AND cold2 < 200;

These queries will use what MySQL …

The MySQL@Oracle announced 5.7.3 development milestone release, now available for download at dev.mysql.com. You can find the full list of changes and bug fixes in the 5.7.3 Release Notes. One of the changes which went in this milestone release was MAKE –REPLICATION-* FILTER SETTINGS DYNAMIC. With this work the slave options –replicate-* can be changed dynamically through the new command CHANGE REPLICATION FILTER. These new options allow slave replication filters to be changed while the server is running, without requiring a server stop and restart.
This blog is intended to explain the design decision of choosing a “new command” over “extending the existing SET command”.

Alternatives:
…