MySQL offers a few different types of indexes and uses them in a variety of ways. There’s a lot to know about the various kinds of indexes and how they interact with the storage engines, and it’s all very important for query optimization. A few examples are listed below:

• The “leftmost prefix rule”

• Clustered primary indexes versus secondary indexes

• B-Tree and hash

• New types of indexes such as LSM and Fractal Trees

• Newer features in the query optimizer and executor in MySQL 5.6 and 5.7

You will leave this webinar with a better understanding of how MySQL and its storage engines use indexes to speed up queries, and how you can improve query performance with basic and advanced index optimizations. Please register …

One of the great features of Shard-Query is the ability to use MySQL proxy to access resultsets transparently. While this is a great tool, many people have expressed reservations about using MySQL Proxy, an alpha component in their production environment.

I recognize that this is a valid concern, and have implemented an alternate method of retrieving resultsets directly in the MySQL client, without using a proxy. This means that any node can easily act as the “head” node without any extra daemon, instead of having to run many proxies.

The sq_helper() routine has been checked into the git repository and is available now.

The function takes a few parameters:

• sql to run
• shard-query schema name (empty string or null for default schema)
• schema to store temp table in
• temp table name (where results are sent to)
• return result (boolean, 1 returns …

#DBHangOps 03/05/15 -- Group Replication, Multithreaded Replication, and more!

Hello everybody!

Join in #DBHangOps this Thursday, March, 05, 2015 at 11:00am pacific (19:00 GMT), to participate in the discussion about:

• Group Replication
• Multithreaded Replication
• Operational learnings with GTID
• New MySQL 5.7 defaults from Morgan Tocker

You can check out the event page at https://plus.google.com/events/cjbmf109r6d7isr715iupigsrq4 on Thursday to participate.

As always, you can still watch the #DBHangOps twitter search, the @DBHangOps twitter feed, or this blog post to get a link for the google hangout on …

[…] tried to connect to AWS EC2 and RDS instances via SSH following this scheme https://thoughtsandideas.wordpress.com/2012/05/17/monitoring-and-managing-amazon-rds-databases-using-…; […]

I have merged a debugger for MySQL/MariaDB stored procedures and functions into our GUI client and posted the source and binaries on github. It allows breakpoint, clear, continue, next, skip, step, tbreakpoint, and variable displays. Features which are rare or missing in other debuggers include:
its current platform is Linux;
it allows breakpoints on functions which are invoked within SQL statements;
it never changes existing stored procedures or functions;
it is integrated with a general GUI client;
it allows commands like gdb and allows menu items / shortcut keys like ddd;
it is available on github as C++ source with GPL licence.

It's alpha and it's fragile but it works. Here is a demo.

Start the client and connect to a running server, as root. Actually the required privileges are merely for creation of certain objects and SUPER, but …

Technical conferences are flooded with visual [mis]representations of a particular product's performance, compression, cost effectiveness, micro-transactions per flux-capacitor, or whatever two-axis comparison someone dreams up. Lets be honest, benchmarketers like to believe we all suffer from innumeracy.

The Merriam-Webster dictionary defines innumeracy as follows:
innumeracy (noun): marked by an ignorance of mathematics and the scientific approach Mark Callaghan has been a long time advocate of explaining benchmark results, but that's not the point of the bar chart. Oh no, the bar chart only exists to catch your eye and …

From time to time people wonder how to implement roles in MySQL. This can be useful for companies having to deal with many user accounts or for companies with tight security requirements (PCI or HIPAA for instance). Roles do not exist in regular MySQL but here is an example on how to emulate them using Percona Server, the PAM plugin and proxy users.

The goal

Say we have 2 databases: db1 and db2, and we want to be able to create 3 roles:

• db1_dev: can read and write on db1 only.
• db2_dev: can read and write on db2 only.
• stats: can read on db1 and db2

For each role, we will create one user: joe (db1_dev), mike (db2_dev) and tom (stats).

Setting up the Percona PAM plugin

The Percona PAM plugin is distributed with Percona Server 5.5 and 5.6. I will be using …

By courtesy of Intel we had access to a very large cluster of Intel servers for a few
weeks. We took the opportunity to see the improvements of the Intel
servers in the new Haswell implementation on the Intel Xeon chips. We also took
the opportunity to see how far we can now scale flexAsynch, the NoSQL benchmark
we've developed for testing MySQL Cluster.

Last time we tested we were using MySQL Cluster 7.2 and the main bottleneck
then was that the API nodes could not push through more than around 300k reads
per second and we have a limit of up to 255 nodes in total. This meant that we
were able to reach a bit more than 70M reads per second using MySQL Cluster 7.2.

In MySQL Cluster 7.3 we improved the handling of thread contention in the NDB API
which means that we are now able to process much more traffic per API node.
In MySQL Cluster 7.4 we also improved …

Some weeks back I helped a customer lower time to bulk-load data into MySQL, they where at the time using a MySQL dumpfile (containing SQL statements) to populate their tables during nightly jobs.

By using LOAD DATA INFILE command and creating secondary indexes after bulk-load of data load time went down by a factor of almost 2x.

My test environment:
DB: MySQL 5.6.23
OS: Ubuntu 14.04
HW: My Toshiba Portege laptop with 2 cores and SSD disk

Commands/tables used in tests:

I created a file …

The other day we were doing some peer review on a schema change at VividCortex and the topic of character set, column length, and the potential downsides of using utf8 came up. I knew from experience that there are some hidden gotchas with this, and usually I’ll just say a couple words and send a link with further reading. But Google turned up nothing, so I am writing this post to try to help fix that.

TL;DR version is that when MySQL can’t use an index for operations such as ORDER BY, it may allocate fixed-length memory buffers large enough to hold the worst-case values, and the same might apply to temporary tables on disk. This can be seen in EXPLAIN as “Using filesort; using temporary” and similar. And when this happens, you might end up making MySQL do gigabytes worth of work to finish a query on a table that’s only a fraction of that size.

Let’s see why this can happen.

The Theory of UTF8

In …