This question was asked at support by a customer to solve a difficult issue.

How to identify a sporadic burst of queries coming from one of the hosts accessing the database ?

If there are hundreds of hosts, it can be challenging, especially if the queries are fast. No chance for them to get logged in the famous slow query log !

Here is the solution using the performance_schema in MySQL 5.6 :

SELECT
host,
SUM(essbben.count_star) AS total_statements,
format_time(SUM(essbben.sum_timer_wait)) AS total_latency,
format_time(SUM(essbben.sum_timer_wait) / SUM(count_star))
AS avg_latency
FROM
performance_schema.events_statements_summary_by_host_by_event_name essbben
GROUP BY
host
ORDER BY
SUM(sum_timer_wait) DESC;

Here is the result :

+---------------+------------------+---------------+-------------+
| host          | total_statements | total_latency | avg_latency | …

Thank you for attending the webinar !  Here are the ppt slides.

If you missed it, you can still join the archived event by clicking the URL below.
http://w.on24.com/r.htm?e=748845&s=1&k=171F8C0CECD105B0F4ED721CA6F2C704

There were a lot of attendees and a lot of questions. I could not answer everything during the limited time. But here are finally the answers !

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 …

MySQL uses internal temporary tables to execute some queries. Usually the tables are stored in memory and on disk if some conditions are met :

Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

• Presence of a BLOB or TEXT column in the table

• Presence of any string column in a GROUP BY or DISTINCT clause larger than 512 bytes

• Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the …

Let’s say that you want to measure something in your database, and for that you need several operations to happen in parallel. If you have a capable programming language at your disposal (Perl, Python, Ruby, PHP, or Java would fit the bill) you can code a test that sends several transactions in parallel.

But if all you have is the shell and the mysql client, things can be trickier. Today I needed such a parallel result, and I only had mysql and bash to accomplish the task.

In the shell, it’s easy to run a loop:

for N in $(seq 1 10)
do
    mysql -h host1 -e "insert into sometable values($N)" 
done

But this does run queries sequentially, and each session will open and close before the next one starts. Therefore there is no concurrency at all.
Then I thought that the method for parallel execution in the shell is to run things in the background, and then collect the results. …

Table space fragmentation has generally 2 origins :

• File System fragmentation : the data file is spread physically on many non contiguous locations on the disk.
• Internal Fragmentation : the data and index pages have “holes” : this happens when  rows are deleted or updated, especially at random.

As a result, performance is affected by table space fragmentation. Data typically takes more space on disk and in memory. The disk is more busy than it should.

File System fragmentation can be detected using the filefrag command on Linux (and similar on different OS). When using MyISAM, MYI files are usually very fragmented on the FS, much more than the MYD files.

ls -al frag
total 883304
drwx------  2 aadant common      4096 Sep 30 18:41 .
drwxr-xr-x 17 aadant common      4096 Sep 30 18:59 ..
-rw-rw----  1 aadant common …

I have just seen Limiting table disk quota in MySQL by Shlomi Noach, and I could not resist.
You can actually implement a disk quota using an updatable view with the CHECK OPTION.
Instead of giving the user access to the table, you give access to the view (at least for inserting, see the caveat at the end), and you will get a genuine MySQL error when the limit is reached.

drop table if exists logs;
create table logs (t mediumtext) engine=innodb;

drop function if exists exceeded_logs_quota ;
create function exceeded_logs_quota() 
returns boolean
deterministic
return (
    select CASE 
           WHEN (DATA_LENGTH + INDEX_LENGTH) > (25*1024) 
           THEN TRUE ELSE FALSE 
 …

I was listening today to the OurSQL Episode 36: It's Not Our (De)fault! Part 1. As usual, Sheeri and Sarah are very informational and entertaining while explaining the innards of MySQL and their best practices.
Being a DBA oriented show, there was an omission in this podcast. There was no mention of custom groups that you can have for your my.cnf. This is mostly useful for developers. If your application requires some specific settings, instead of using a separated configuration file, you can use a different group, and then instruct your client applications to use that group.
By default, all client applications read the "[client]" group.
But you can tell your client to read a group that you can call whatever you like.
For example, with this configuration file,

[client] …

When you are importing large amounts of data from other sources LOAD DATA is a common method of inserting data into a table.
It is one of the old commands implemented in MySQL. As such it is very fast, and it has been optimized for both MyISAM and InnoDB.
All is well when you are loading data into a standalone server. All is almost well when you are using replication. LOAD DATA used to be a problem in old versions of MYSQL, prior to 4.1. With recent versions of MySQL, it is replicated correctly, and sometimes efficiently.
The trouble starts when the data file is big. The definition of big and the amount of trouble that you can get depends on many factors. That's why users may not realize that this problem exists, even with fairly large files, and then being hit by this disaster when the file is only a little larger than the previous …

TO_DAYS() prunes two partitions instead of one
If you are partitioning by date, chances are that you are using TO_DAYS(). And depending on how you have partitioned your table, your queries are as fast as you expect them to be. However, there are cases where your query takes twice as long as it should, and of course this will not make you happy.

For example, in a table partitioned by month, when …