I want to give you tools that you can use for building databases that can handle complex relationships. In the previous article in this series, I looked at hierarchical data. The classic example of a hierarchy is an org chart. On most org charts I’ve seen, an employee has only one boss, which is a one-to-many relationship. The other kind of segmentation is many-to-many. A good instances of this is your social circle. Most people have many friends, and those friends have multiple friends themselves.

This is a common pattern. When developing a multi-tenanted application, users will often want to see and update information for multiple tenants. Imagine, if you will, a freelance Web developer. He’ll set up Google Analytics accounts for each client’s website, and he’ll add both the client and himself as users to the account. …

MySQL provides commands to see the overall index size versus the data size.

One of them is “show table status” :

mysql> show table status like 't'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 4186170
 Avg_row_length: 34
    Data_length: 143310848
Max_data_length: 0
   Index_length: 146030592
      Data_free: 6291456
 Auto_increment: NULL
    Create_time: 2014-02-04 15:40:54
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

So here, we have these “estimations”, run ANALYZE TABLE before to get more accurate estimates :

Data_length: 143310848,    136Mb  clustered index size.

Index_length: 146030592,  139Mb secondary index size.

In this example, I have …

ClusterControl for Percona XtraDB Cluster is now available in three different versions thanks to our partnership with Severalnines. ClusterControl will make it simpler to manage and monitor Percona XtraDB Cluster, MySQL Cluster, MySQL Replication, or MySQL Galera.

I am very excited about our GA release of Percona XtraDB Cluster 5.6 last week. As Vadim described in his blog post announcing the release, we have brought together the benefits of Percona Server 5.6, Percona XtraBackup and Galera 3 to create a drop-in compatible, open source, state-of-the-art high availability MySQL clustering solution. We could not have done it without the close cooperation and help of our partners at Codership. To learn more, join us on February …

February 4, 2014 By Severalnines

Great news for Percona customers! We are thrilled to announce our partnership with Percona. Effective immediately, Percona customers will be able to enjoy the advanced automation, monitoring and cluster management capabilities of ClusterControl. Percona will be bundling, with its Cluster support contracts, Percona ClusterControl - a privately branded version of ClusterControl Community. Together we're providing support for the full stack, from Percona XtraDB Cluster to management tools, giving customers one number to call. 

For anyone who has deployed, managed or monitored a mission-critical database cluster, you will know that having the right tools can really make a difference. This is exactly our goal with ClusterControl, with a focus on operational management from day one. In only a few clicks, ClusterControl provides customers the ability to deploy Galera-based MySQL Clusters in private …

This continues on from my post yesterday and also contains content from my linux.conf.au 2014 talk (view video here).

Way back in May in the year 2000, a feature was added to MySQL that would keep many people employed for many years – replication. In 3.23.15 you could replicate from one MySQL instance to another. This is commonly cited as the results of two weeks of work by one developer. The idea is simple: create a log of all the SQL queries that modify the database and then replay them on a slave. Remember, this is before there was concurrency and everything was ISAM or MyISAM, so this worked (for certain definitions of worked).

…

I recently blogged about tracking metadata locks in the latest MySQL, and now I want to discuss how to track these metadata locks in MariaDB.

In MySQL 5.7, there is a table named `metadata_locks` added to the performance_schema (performance_schema must be enabled *and* the metadata_locks instrument must be specifically enabled as well.

In the MariaDB 10.0 implementation (as of 10.0.7), there is a table named METADATA_LOCK_INFO added to the *information_schema*. This is a new plugin, so the plugin must be installed, but that is very simple with:

INSTALL SONAME 'metadata_lock_info';

Then, you will have the table.

To see it in action:

Connection #1:

mysql> create table t (id int) engine=myisam;
mysql> begin;
mysql> select * from t;

Connection #2:

mysql> alter table t add index …

MariaDB 10.0.7 was recently released (it is the latest MariaDB 10.0), is currently "beta", and is available for download here:

https://downloads.mariadb.org/mariadb/10.0.7/

I just wanted to note a couple of the main highlights, which are:

1. XtraDB storage engine was upgraded to the 5.6 version. Now one can use XtraDB with MariaDB 10.0. Unlike MariaDB 5.5, in 10.0 XtraDB is not the default engine, the default is InnoDB, and XtraDB is available as a dynamic plugin.

read more

We are pleased to announce that MySQL Enterprise Monitor 3.0.6 is now available for download on the My Oracle Support (MOS) web site. It will also be available via the Oracle Software Delivery Cloud with the February update in about 1 week. This is a maintenance release that includes a few new features and fixes a number of bugs. You can find more information on the contents of this release in the change log.

You will find binaries for the new release on My Oracle Support. Choose the "Patches & Updates" tab, and then choose the "Product or Family (Advanced Search)" side tab in the "Patch Search" portlet.

You will also find the binaries on the Oracle Software Delivery Cloud in approximately 1 week. Choose "MySQL Database" …

William Zola over at MongoDB gave a great talk called “The (Only) Three Reasons for Slow MongoDB Performance”. It reminded me of an interesting characteristic of updates in MongoDB. Because MongoDB’s main data store is a flat file and secondary indexes store offsets into the flat file (as I explain here), if the location of a document changes, corresponding entries in secondary indexes must also change. So, an update to an unindexed field that causes the document to move also causes modifications to every secondary index, which, as William points out, can be expensive. If a document has indexed an array, this problem is exacerbated.

What …

The first blogpost in this series did a quick introduction to MaxScale, but now it is time start getting our hands dirty. We will get a more practical view on MaxScale and begin to put it to work. The following is based on a simple Proof of Concept that I did recently. The application is a mid-size web-based online shop where scalability is becoming an issue. They use PHP and with an old and rather inflexible framework, so even though changing how the database is used is possible, it's not easy and having database routing in the application code isn't a very good idea either.

The scalability issues affects reads, but as all traffic is directed to one database server, when this server gets slow, writes, like when entering an order, gets very slow, and this is unacceptable. So what was needed is a way to redirect reads to …