Let’s look at the index from the wp_posts table in a standard WordPress installation.

SHOW KEYS FROM wp_posts;
+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name         | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_posts |          0 | PRIMARY          |            1 | ID           | A         |        1772 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_name        |            1 | post_name    | A         |        1772 |     NULL | NULL   |      | BTREE      |         |               |
| …

“Working with empirical genomic data and modern computational models, the laboratory addresses questions relevant to how genetics and the environment influence the frequency and severity of diseases in human populations” –Thibault de Malliard. Big Data for Genomic Sequencing. On this subject, I have interviewed Thibault de Malliard, researcher at the University of Montreal’s Philip Awadalla [...]

If you haven’t explored the MySQL Performance Schema yet, this is a good place to start.  This is Performance Schema 101, a basic introduction to the MySQL 5.6 performance_schema, which records runtime statistics from the MySQL database. The performance_schema is intended to provide access to useful information about server execution while having minimal impact on server performance.  Performance_schema is the name of both the storage engine and the database itself, and it was initially implemented  in MySQL 5.5. In MySQL 5.6 the engineers added quite a bit of new instrumentation.

The performance_schema database uses views or temporary tables that actually use little to no persistent disk storage .Memory allocation is all done at server startup, so there is no ongoing memory reallocation or sizing, which is great for performance.

I categorize the performance_schema …

Jaime at the MySQL Performance Blog had an interesting post regarding some unexpected behavior from the MySQL optimizer.  Although this particular case probably doesn’t affect most people, it does give some insight into how the optimizer works, and how subtle changes to a query can change performance.

I was made aware today by a post on the MySQL Performance Blog that the mysqlnd driver for PHP has support for asynchronous PHP queries.  At the Midwest PHP Conference, I gave a talk on database optimization for web developers, and in a follow-up discussion with an attendee, I apparently misinformed him.

Thanks for attending my presentation at the Midwest PHP conference!

Slides as PDF
Leave feedback on joind.in

Useful links:

• Explain Analyzer – for those times that you can’t figure out what EXPLAIN is telling you
• Percona Toolkit – lots of useful tools for managing a MySQL server
• query-digest-helper – my utility for formatting the results of pt-query-digest 
• …

I was at FOSDEM for the first time this year and it was very exciting.
I was very glad to see Percona, MariaDB and Oracle together.
If you’ve never been at FOSDEM, I recommend you to join us next year, this is truly a magical event!

You can find some of the slides on [Plus], enjoy! :

If you would like to add your slides, post a comment with the link.
And remember to visit this page for more slides about MySQL : http://www.mysqlplus.net/mysql-docs/

…

In MySQL 5.5 and earlier, the steps to resize the InnoDB log files were a bit involved and for example included manually moving the log files out of the way as InnoDB would only create new files, if none existed.

In MySQL 5.6 a not so much talked about feature is the support to resize the log files in a way much more similar to changing other settings in MySQL. Now you simply update your MySQL configuration file and restart MySQL.

Let us look at an example. In MySQL 5.5 and earlier the total size of the InnoDB log files has to be less than 4G in total, so one way of staying within this limit is to have two files each 2047M large:

innodb $ ls -1s ib_logfile*
2096132 ib_logfile0
2096144 ib_logfile1

Now update the configuration file to take advantage of the fact that MySQL 5.6 allows much larger InnoDB log files; the actual limit is a total size of 512G, but here I will use two files each 4G large:

In my last few posts, I wrote about “How to install MySQL replication using GTID’s” (Part One, Part Two). In this post, I will show you how to install MySQL 5.6 and set up replication between two MySQL servers the “old fashioned way” using the binary log and binary log position.

I am going to create some virtual machines instead of using individual servers. But, you can also use these instructions to create a MySQL replication (master/slave) setup with real servers.

Here is …

During the last year, I was asked a number of times when/if MariaDB will support identifying dynamic columns by names instead of numbers. I am happy to say that named Dynamic Columns feature is in MariaDB 10.0.1 release. Now, after some literary effort, here is updated documentation: Dynamic Columns in MariaDB.