But the change goes farther than that; there is also a security change. Oracle created a pitfall of 2 changes at the same time:

1. Variables/status moved to a different table
2. Privileges required on said table

As an example, my non-root user gets:

mysql> show session variables like 'tx_isolation';
ERROR 1142 (42000): SELECT command denied to user 'normal_user'@'my_host' for table 'session_variables'

Who gets affected by this? Nearly everyone and everything.

• Your Nagios will not be able to read status variables
• Your ORM will not be able to determine session variables
• Your replication user will fail connecting (see this post by Giuseppe)
• And most everyone else.

The problem with the above is that involves two unrelated changes to your setup, which are not entirely simple to coordinate:

1. Change your app code to choose the correct schema (information_schema vs. performance_schema)
2. GRANT the permissions on your database

Perhaps at this point you still do not consider this to be a problem. You may be thinking: well, let’s first prepare by creating the GRANTs, and once that is in place, we can, at our leisure, modify the code.

Not so fast. Can you really that simply create those GRANTs?

Migration woes

How do you migrate to a new MySQL version? You do not reinstall all your servers. You want an easy migration path, and that path is: introduce one or two slaves of a newer version, see that everything works to your satisfaction, slowly upgrade all your other slaves, eventually switchover/upgrade your master.

This should not be any different for 5.7. We would like to provision a 5.7 slave in our topologies and just see that everything works. Well, we have, and things don’t just work. Our Nagios stops working for that 5.7 slave. Orchestrator started complaining (by this time I’ve already fixed it to be more tolerant for the 5.7 problems so no crashes here).

I hope you see the problem by now.

You cannot issue a GRANT SELECT ON performance_schema.global_variables TO ‘…’ on your 5.6 master.

The table simply does not exist there, which means the statement will not go to binary logs, which means it will not replicate on your 5.7 slave, which means you will not be able to SHOW GLOBAL VARIABLES on your slave, which means everything remains broken.

Yes, you can issue this directly on your 5.7 slaves. It’s doable, but undesired. It’s ugly in terms of automation (and will quite possibly break some assumptions and sanity checks your automation uses); in terms of validity testing. It’s unfriendly to GTID (make sure to SET SQL_LOG_BIN=0 before that).

WHY in the first place?

It seems like a security thing. I’m not sure whether this was intended. So you prevent a SHOW GLOBAL VARIABLES for a normal user. Makes sense. And yet:

mysql> show global variables like 'hostname';
ERROR 1142 (42000): SELECT command denied to user 'normal_user'@'my_host' for table 'global_variables'

mysql> select @@global.hostname;
+---------------------+
| @@global.hostname   |
+---------------------+
| myhost.mydomain.com |
+---------------------+

mysql> select @@version;
+--------------+
| @@version    |
+--------------+
| 5.7.8-rc-log |
+--------------+

Seems like I’m allowed access to that info after all. So it’s not strictly a security design decision. For status variable, I admit, I don’t have a similar workaround.

Solutions?

The following are meant to be solutions, but do not really solve the problem:

• SHOW commands. SHOW GLOBAL|SESSION VARIABLES|STATUS will work properly, and will implicitly know whether to provide the results via information_schema or performance_schema tables.
  • But, aren’t we meant to be happier with SELECT queries? So that I can really do stuff that is smarter than LIKE ‘variable_name%’?
  • And of course you cannot use SHOW in server side cursors. Your stored routines are in a mess now.
  • This does not solve the GRANTs problem.
• show_compatibility_56: an introduced variable in 5.7, boolean. It truly is a time-travel-paradox novel in disguise, in multiple respects.
  • Documentation introduces it, and says it is deprecated.
    • time-travel-paradox :O
  • But it actually works in 5.7.8 (latest)
    • time-travel-paradox plot thickens
  • Your automation scripts do not know in advance whether your MySQL has this variable
    • Hence SELECT @@global.show_compatibility_56 will produce an error on 5.6
    • But the “safe” way of SHOW GLOBAL VARIABLES LIKE ‘show_compatibility_56’ will fail on a privilege error on 5.7
    • time-travel-paradox :O
  • Actually advised by my colleague Simon J. Mudd, show_compatibility_56 defaults to OFF. I support this line of thought. Or else it’s old_passwords=1 all over again.
  • show_compatibility_56 doesn’t solve the GRANTs problem.
  • This does not solve any migration path. It just postpones the moment when I will hit the same problem. When I flip the variable from “1” to “0”, I’m back at square one.

Suggestion

I claim security is not the issue, as presented above. I claim Oracle will yet again fall into the trap of no-easy-way-to-migrate-to-GTID in 5.6 if the current solution is unchanged. I claim that there have been too many changes at once. Therefore, I suggest one of the alternative two flows:

1. Flow 1: keep information_schema, later migration into performance_schema
   • In 5.7, information_schema tables should still produce the data.
   • No security constraints on information_schema
   • Generate WARNINGs on reading from information_schema (“…this will be deprecated…”)
   • performance_schema also available. With security constraints, whatever.
   • In 5.8 remove information_schema tables; we are left with performance_schema only.
2. Flow 2: easy migration into performance_schema:
   • In 5.7, performance_schema tables should not require any special privileges. Any user can read from them.
   • Keep show_compatibility_56 as it is.
   • SHOW commands choose between information_schema or performance_schema on their own — just as things are done now.
   • In 5.8, performance_schema tables will require SELECT privileges.

As always, I love the work done by the engineers; and I love how they listen to the community.

Comments are most welcome. Have I missed the simple solution here? Are there even more complications to these features? Thoughts on my suggested two flows?

[UPDATE 2015-08-19]

Please see this followup by Morgan Tocker of Oracle.

• tokudb_analyze_time

This is a boundary on the number of seconds an ANALYZE TABLE will operate on each index on each partition on a TokuDB table.

That is, if tokudb_analyze_time = 5, and your table has 4 indexes (including PRIMARY) and 7 partitions, then the total runtime is limited to 5*4*7 = 140 seconds.

Default in 7.1.0: 5 seconds

• tokudb_cache_size

Similar to innodb_buffer_pool_size, this variable sets the amount of memory allocated by TokuDB for caching pages. Like InnoDB the table is clustered within the index, so the cache includes pages for both indexes and data.

Default: 50% of total memory

• tokudb_directio

Boolean, values are 0/1. Setting tokudb_directio = 1 is like specifying innodb_flush_method = O_DIRECT. Which in turn means the OS should not cache pages requested by TokuDB. Default: 0.

Now here’s the interesting part: we are used to tell InnoDB to get the most memory we can provide (because we want it to cache as much as it can) and to avoid OS caching (because that would mean a page would appear both in the buffer pool and in OS memory, which is a waste). So the following setup is common:

innodb_buffer_pool_size = [as much as you can allocate while leaving room for connection memory]G
innodb_flush_method = O_DIRECT

And my first instinct was to do the same for TokuDB. But after speaking to Gerry Narvaja of Tokutek, I realized it was not that simple. The reason TokuDB’s default memory allocation is 50% and not, say, 90%, is that OS cache caches the data in compressed form, while TokuDB cache caches data in uncompressed form. Which means if you limit the TokuDB cache, you allow for more cache to the OS, that is used to cache compressed data, which means more data (hopefully, pending duplicates) in memory.

I did try both options and did not see an obvious difference, but did not test this thoroughly. My current setup is:

#No setup. just keep to the default for both:
#tokudb_cache_size
#tokudb_directio

• tokudb_commit_sync

• tokudb_fsync_log_period

These two variable are similar in essence to innodb_flush_log_at_trx_commit, but allow for finer tuning. With innodb_flush_log_at_trx_commit you choose between syncing the transaction log to disk upon each commit and once per second. With tokudb_commit_sync = 1 (which is default) you get transaction log sync to disk per commit. When tokudb_commit_sync = 0, then tokudb_fsync_log_period dictates the interval between flushes. So a value of tokudb_fsync_log_period = 1000 means once per second.

Since our original InnoDB installation used innodb_flush_log_at_trx_commit = 2, our TokuDB setup is:

tokudb_commit_sync = 0
tokudb_fsync_log_period = 1000

• tokudb_load_save_space

Turned on (value 1) by default as of TokuDB 7.1.0, this parameter decides whether temporary file created on bulk load operations (e.g. ALTER TABLE) are compressed or uncompressed. Do yourself a big favour (why? read here) and keep it on. Our setup is:

tokudb_load_save_space = 1

TokuDB’s general recommendation is: don’t change the variables; the engine should work well right out of the box. I like the approach (by MySQL 5.5 I already lost count of InnoDB variables that can have noticeable impact; with 5.6 I’m all but lost). The complete list of configuration variables is found in TokuDB’s Users Guide.

I wish to discuss the creation and use of global variables for plugins.

Consider the following declaration of a global variable in audit_login:

static MYSQL_SYSVAR_BOOL(enabled, plugin_enabled, PLUGIN_VAR_NOCMDARG,
"enable/disable the plugin's operation, namely writing to file", NULL, NULL, 1);

static struct st_mysql_sys_var * audit_login_sysvars[] = {
    MYSQL_SYSVAR(enabled),
    NULL
};

The above creates a new global variables called “simple_login_audit_enabled”: it is composed of the plugin name (known to be “simple_login_audit” in our example) and declared name (“enabled”). It is a boolean, defaults to 1, and is associated with the internal plugin_enabled variable.

Once this variable is declared, you can expect to be able to:

mysql> show global variables like '%audit%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| simple_login_audit_enabled | ON    |
+----------------------------+-------+

mysql> set global simple_login_audit_enabled := 0;
Query OK, 0 rows affected (0.00 sec)

and you can expect using the following in your my.cnf file:

[mysqld]
...
simple_login_audit_enabled=1

Assuming your server agrees to start

Here’s the catch: the simple_login_audit_enabled variable is only recognized as long as the plugin is installed. As you may know, plugins can be loaded upon startup time using an explicit my.cnf entry such as:

plugin_load=audit_login.so

But you may also, at any given time, INSTALL or UNINSTALL the plugin dynamically.

install plugin SIMPLE_LOGIN_AUDIT soname 'audit_login.so';
uninstall plugin SIMPLE_LOGIN_AUDIT;

In fact there are good reasons to do so: you may be upgrading your plugin. You can’t just throw in the new binary (it’s a guaranteed crash on next server shutdown). You need to first UNINSTALL it; you then put the new binary, and re-INSTALL. This works well, and the price is some downtime for your plugin.

But what happens if you restart the server while your plugin is uninstalled? Yep: the global variable is unrecognised, and your MySQL server refuses to start:

130919  8:11:30 [ERROR] /usr/bin/mysqld: unknown variable 'simple_login_audit_enabled=1'
130919  8:11:30 [ERROR] Aborting
130919  8:11:30  InnoDB: Starting shutdown...
130919  8:11:31  InnoDB: Shutdown completed; log sequence number 40185651
130919  8:11:31 [Note] /usr/bin/mysqld: Shutdown complete

I did happen on this case a couple times; it is frustrating.

What are the alternatives?

So adding variables to my.cnf may prevent MySQL from starting. In my dictionary, this spells “NO GO”.

With audit_login I chose to (additionally) support an external config file, audit_login.cnf, expected to be found in the @@datadir. It is similar in essence to the master.info file which is expected by replication. The plugin reads this file (if existing) upon init(), which makes it execute upon server startup or upon INSTALL PLUGIN. I can’t argue that this is the best solution, but it is a solution that does not interfere with anyone. The file is ignored by all and does not disturb the public peace. The plugin does not require it to exist.

I was hoping to be able to directly read from my.cnf, but am unsure if there is a definitive way to do so from within the plugin. I did not go deep into this.

What would be best?

Hopefully I’m not missing on anything. But it would be nice to have plugin-dedicated variables in my.cnf which are ignored by the server. These could take the form of:

[mysql_plugin]
simple_login_audit_enabled=0

or similar. It would be the server’s responsibility to pass these declarations to the plugins, but ignore them itself (or just pass warning).

These are shared for a few reasons:

• Maybe I didn’t understand it well, and someone could correct me
• Or I understood it well, and my input could be of service to the developers
• Or it could be of service to the users

InnoDB tables in mysql schema

The introduction of InnoDB tables in mysql makes for crash-safe replication information: the exact replication position (master log file+pos, relay log file+pos etc.) is updated on InnoDB tables; with innodb_flush_logs_at_trx_commit=1 this means replication status is durable and consistent with server data. This is great news!

However, the introduction of InnoDB tables to the mysql schema also breaks some common usage on installation and setup of MySQL servers. You can’t just drop your ib_data1 file upon dump+restore, since it also contains internal data. Giuseppe outlines the workaround for that.

I was thinking: would it be possible to have a completely different tablespace for MySQL’s internal InnoDB tables? That could be a single tablespace file (who cares about file-per-table on a few internal tables). And I’m throwing an idea without being intimate with the internals: you know how it is possible to span the shared tablespace across multiple files, as in:

[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

Would it be possible to, for example, force the first file in this setup to be the internal database? It would look like:

[mysqld]
innodb_data_file_path=ibdata_internal_do_not_touch:2M;ibdata1_this_one_is_yours:50M:autoextend

Only the user would not have to actually set this thing up: the internal tablespace would be there by default (and always first).

Then we would be able to drop our own table space as much as we would like to, but never touch the internal tablespace. It would always extend into our own ibdata1 file.

I’m wondering if I’m making sense at all and if this is possible.

GTID and settings

The fact that you have to specify both gtid_mode=ON as well as disable-gtid-unsafe-statements is a bit of a bummer. I wouldn’t mind as much if error messages would be informative. But as it turned out, when I wanted to test GTID I did the following:

mysql> STOP SLAVE;
mysql> change master to MASTER_AUTO_POSITION=1;
ERROR 1777 (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION = 1 can only be executed when GTID_MODE = ON.

-- OK, setting gtid_mode=ON in config file, restarting server.
--
-- Oooops, server won't restart!
-- Getting this error message in log: "--gtid-mode=UPGRADE_STEP_1 or --gtid-mode=UPGRADE_STEP_2 are not yet supported"
-- What?

Checking up on Giuseppe’s post I realized I didn’t set the disable-gtid-unsafe-statements param. But this was not mentioned on the above ERROR 1777, and the log error was quite cryptic.

TODO: just mention this other variable.

GTID, internal InnoDB tables & wreckage

OK, I managed to completely crash my replication setup. I setup GTID, and then:

set global master_info_repository:='table';
set global relay_log_info_repository='table';

Then shut down mysql; I wanted to see how reverting back to gtid_mode=OFF works. Oh, I didn’t set the two params in the config file, so their effect was lost.

Starting mysql, I get:

ERROR 1794 (HY000) at line 1: Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.

The error log says:

121015  9:38:58 [ERROR] Error creating master info: Multiple replication metadata repository instances found with data in them. Unable to decide which is the correct one to choose.
121015  9:38:58 [ERROR] Failed to create or recover replication info repository.
121015  9:38:58 [Note] Check error log for additional messages. You will not be able to start replication until the issue is resolved and the server restarted.

What’s interesting is that the data is still in the tables:

mysql> select * from mysql.slave_master_info\G
*************************** 1. row ***************************
       Number_of_lines: 23
       Master_log_name: mysql-bin.000003
        Master_log_pos: 2623
                  Host: 127.0.0.1
             User_name: rsandbox
         User_password: rsandbox
                  Port: 14701
         Connect_retry: 60
           Enabled_ssl: 0
                Ssl_ca: 
            Ssl_capath: 
              Ssl_cert: 
            Ssl_cipher: 
               Ssl_key: 
Ssl_verify_server_cert: 0
             Heartbeat: 1800
                  Bind: 
    Ignored_server_ids: 0
                  Uuid: 10fa73da-13ac-11e2-bdcd-0024e8cd3122
           Retry_count: 86400
               Ssl_crl: 
           Ssl_crlpath: 
 Enabled_auto_position: 1

I’ve tried restarting, setting variables in the config file, changing them dynamically. To no avail.

No, I haven’t filed a bug report yet.

These are still my first steps into 5.6 replication and my very first impressions.

sed, awk & grep have many overlapping features. Some simple tasks can be performed by either. For example, stripping empty lines can be performed by either:

grep '.'
awk '/./'
sed '/./!d'
grep -v '^$'
awk '!/^$/'
sed '/^$/d'

It’s a matter of taste & convention which tool and variation to use. So for any script I suggest, there may be many variations, possibly cleaner, shorter; feel free to comment.

mysqldump

The output of mysqldump is in particular useful when one wishes to make transformation on data or metadata.

• Convert MyISAM tables to InnoDB:

mysqldump | sed -e 's/^) ENGINE=MyISAM/) ENGINE=InnoDB/'

I’ve had several occasion when people said this type of conversion assumes no ‘ENGINE=MyISAM’ snippet exists within row data. This is not so. The ‘^) ENGINE=MyISAM/’ pattern strictly requires that this text is outside row data. No row data begins with a ‘)’. This is a safe conversion.

• Convert InnoDB to InnoDB plugin, compressed tables:

mysqldump | sed -e 's/^) ENGINE=InnoDB/) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8/'

• Slice out a specific database (assumes existence of the USE statement):

sed -n "/^USE \`employees\`/,/^USE \`/p"

• Slice out a specific table:

sed -n "/^-- Table structure for table \`departments\`/,/^-- Table structure for table/p"

• Combine the above two statements to slice a specific table from a specific database:

sed -n "/^USE \`employees\`/,/^USE \`/p" | sed -n "/^-- Table structure for table \`departments\`/,/^-- Table structure for table/p"

See also On restoring a single table from mysqldump.

my.cnf

Some my.cnf files are just a mess to read. Here’s some normalizing scripts:

• Strip a my.cnf file from comments, remove blank lines, normalize spaces:

cat my.sandbox.cnf | sed '/^#/d' | sed '/^$/d' | sed -e 's/[ \t]\+//g'

• Same, but only present [mysqld] section parameters:

cat my.sandbox.cnf | sed -n '/^\[mysqld\]/,/^\[/p' | sed '/^\[/d' | sed '/^#/d' | sed '/^$/d' | sed -e 's/[ \t]\+//g'

• Only present [mysqld] section parameters, tab delimited (this is useful in exporting and comparing instance parameters):

cat my.sandbox.cnf | sed -n '/^\[mysqld\]/,/^\[/p' | sed '/^\[/d' | sed '/^#/d' | sed '/^$/d' | sed -e 's/[ \t]\+//g' | sed -e 's/=/\t/'

• Multi-word parameters in my.cnf can be written with either hyphens or underscores. innodb_file_per_table is the same as innodb-file-per-table, as well as innodb_file-per_table. The following normalizes the parameter names to using underscores only, keeping from changing values (e.g. ‘mysql-bin’ parameter value should not change). It isn’t pretty!

cat my.sandbox.cnf | awk -F "=" 'NF < 2 {print} sub("=", "=~placeholder~=") {print}' | awk -F "=~placeholder~=" 'NF < 2 {gsub("-", "_", $0); print} NF==2 {gsub("-", "_", $1); print $1 "=" $2}'

I’ve written all about it before on Dangers of skip-grant-tables.

Darren’s 1st advice (look for password ini files, scripts, etc.) is a very good one. One password that can always be looked up in files is the replication’s password.

Replication’s password is easily forgotten: you only set it once and never use it again; never script it nor manually login with. When setting up new slaves, though, you suddenly need it.

Apparently not many realize that the replication password is written in plaintext in the master.info file. This file tells the slave all about it’s master connection: host, port, user & password are all there for you to read.

These files can easily win the “most outdated sample configuration file contest”.

Usually it’s no big deal: if some parameter isn’t right, you just go and change it. Some variables, though, have a long-lasting effect, and are not easily reversed.

What’s the deal with old_passwords?

No one should be using these anymore. This variable makes the password hashing algorithm compatible with that of MySQL 4.0. I’m pretty sure 4.0 was released 9 years ago. I don’t know of anyone still using it (or 4.0 client libraries).

The deal is this: with old_passwords you get a 16 hexadecimal digits (64 bit) hashing of your passwords. With so called “new passwords” you get 40 hexadecimal digits (plus extra “*“). So this is about better encryption of your password. Read more on the manual.

How do I upgrade to new password format?

You can’t just put a comment on the “old_passwords=1” entry in the configuration file. If you do so, the next client to connect will attempt to match a 41 characters hashed password to your existing 16 characters entry in the mysql.users table. So you need to make a simultaneous change: both remove the old_passwords entry and set a new password. You must know all accounts’ passwords before you begin.

Interestingly, old_passwords is both a global and a session variable. To work out an example, let’s assume the account ‘webuser’@’localhost’ enters with ‘123456’. Take a look at the following:

root@mysql-5.1.51> SET SESSION old_passwords=0;
Query OK, 0 rows affected (0.00 sec)

root@mysql-5.1.51> SELECT PASSWORD('123456');
+-------------------------------------------+
| PASSWORD('123456')                        |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set (0.00 sec)

root@mysql-5.1.51> SET SESSION old_passwords=1;
Query OK, 0 rows affected (0.00 sec)

root@mysql-5.1.51> SELECT PASSWORD('123456');
+--------------------+
| PASSWORD('123456') |
+--------------------+
| 565491d704013245   |
+--------------------+
1 row in set (0.00 sec

So, the PASSWORD() function consults the old_passwords session variable.

To upgrade ‘webuser’@’localhost’‘s password we do:

root@mysql-5.1.51> SET SESSION old_passwords=0;
Query OK, 0 rows affected (0.00 sec)

root@mysql-5.1.51> SET PASSWORD FOR 'webuser'@'localhost' = PASSWORD('123456')

Go ahead and see the password entry on the mysql.users table.

What we’ve just done is to set a 41 characters password hash for that account. Now, the next time the client wishes to connect, it must know in advance it is to expect a new password, otherwise it will encode a 16 characters hash, and try to match it with our new 41 characters hash. It is now time to perform:

root@mysql-5.1.51> SET GLOBAL old_passwords=0;
Query OK, 0 rows affected (0.00 sec

This will apply to all new connections made from that moment on (not affecting any existing connections). So, make sure you have updated passwords for all accounts.

To wrap it up, don’t forget to set old_passwords=0 in the my.cnf file, or, better yet, completely remove the entry.

So your ibdata1 file is some 150GB, and it won’t reduce. Really, it won’t reduce. You set innodb_file_per_table=1, do ALTER TABLE t ENGINE=InnoDB (optionally ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8), and you get all your tables in file-per-table .ibd files.

But the original ibdata1 file is still there. It has to be there, don’t delete it! It contains more than your old data.

InnoDB tablespace files never reduce in size, it’s an old-time annoyance. The only way to go round it, if you need the space, is to completely drop them and start afresh. That’s one of the things so nice about file-per-table: an ALTER TABLE actually creates a new tablespace file and drops the original one.

The procedure

The procedure is somewhat painful:

• Dump everything logically (either use mysqldump, mk-parallel-dump, or do it your own way)
• Erase your data (literally, delete everything under your datadir)
• Generate a new empty database
• Load your dumped data.

Using replication

Replication makes this less painful. Set up a slave, have it follow up on the master.

• Stop your slave.
• Make sure to backup the replication position (e.g. write SHOW SLAVE STATUS on a safe location, or copy master.info file).
• Work out the dump-erase-generate-load steps on the slave.
• Reattach the slave to the master using saved data.

For this to succeed you must keep enough binary logs on the master for the entire dump-load period, which could be lengthy.

Upgrading to barracuda

If you wish to upgrade your InnoDB tables to Barracuda format, my advice is this:

1. Follow the steps above to generate a file-per-table working slave
2. Stop the slave
3. Configure skip_slave_start
4. Restart MySQL
5. One by one do the ALTER TABLE into Barracuda format (ROW_FORMAT=COMPACT or ROW_FORMAT=COMPRESSED)

Note that if you’re about to do table compression, the ALTER statements become considerably slower the better the compression is.

If your dataset is very large, and you can’t keep so many binary logs, you may wish to break step 5 above into:

• ALTER a large table
• Restart MySQL
• Start slave, wait for it to catch up
• Restart MySQL again

and do the same for all large tables.

Why all these restarts?

I’ve been upgrading to Barracuda for a long time now. I have clearly noticed that ALTER into a COMPRESSED format works considerably slower after the slave has done some “real work”. This in particular relates to the last “renaming table” stage. There was a bug with earlier InnoDB plugin versions which made this stage hang. It was solved. But it still takes some time for this last, weird stage, where the new replacement table is complete, and it’s actually been renamed in place of the old table, and the old table renamed into something like “#sql-12345.ibd”, and all that needs to be done is have it dropped, and… Well, it takes time.

My observation is it works faster on a freshly started server. Which is why I take the bother to restart MySQL before each large table conversion.

Or maybe you want to check that some global variable has not been dynamically changed without an update to the configuration file. But the configuration file doesn’t make any sense — it looks like nothing is common between the file and the server.

Wait, which my.cnf file does MySQL read? Rather, which my.cnf files?

Ever happened to you? If you’re well organized, and only keep a single /etc/my.cnf file, you know exactly where everything is. But some systems are messier, with lots of configuration files hanging around. Which ones apply?

Let’s find out which configuration files apply.

No direct information

It would all be easier if we could just SHOW GLOBAL VARIABLES LIKE ‘configuration_files_that_this_server_has_read_list’. There isn’t such an option.

The MySQL documentation explains about the configuration files search path, and that’s one path you can follow. Also, you can detect another estimated search path by invoking:

root@myhost:~# mysqld --verbose --help | head -n 20
100927 19:53:06 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

mysqld  Ver 5.1.41 for unknown-linux-gnu on x86_64 (MySQL Community Server (GPL))
Copyright (C) 2000-2008 MySQL AB, by Monty and others
Copyright (C) 2008 Sun Microsystems, Inc.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Starts the MySQL database server

Usage: mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
The following groups are read: mysqld server mysqld-5.1
The following options may be given as the first argument:
...

Easy enough, right? Just walk through that search path and you’ve covered it all. Better yet, see which of these even exist!

root@myhost:~# ls -l /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
ls: cannot access /etc/my.cnf: No such file or directory
ls: cannot access /etc/mysql/my.cnf: No such file or directory
ls: cannot access /usr/local/mysql/etc/my.cnf: No such file or directory
-rw-r--r-- 1 shlomi shlomi 32 2010-03-03 15:21 /home/shlomi/.my.cnf

Seems like we got it. The mysqld process only reads /home/shlomi/.my.cnf. Right?

Wrong!

There are two running instances of MySQL running on my machine. Neither of the primary my.cnf files used by these instances is listed above.

root@myhost:~# ps aux | grep mysqld
shlomi   12092  0.0  0.0   4096   352 pts/1    S    Sep26   0:00 /bin/sh /home/shlomi/sandboxes/5.1/5.1.50/bin/mysqld_safe --defaults-file=/home/shlomi/sandboxes/msb_5_1_50/my.sandbox.cnf
shlomi   12167  0.0 14.5 765520 587924 pts/1   Sl   Sep26   1:12 /home/shlomi/sandboxes/5.1/5.1.50/bin/mysqld --defaults-file=/home/shlomi/sandboxes/msb_5_1_50/my.sandbox.cnf --basedir=/home/shlomi/sandboxes/5.1/5.1.50 --datadir=/home/shlomi/sandboxes/msb_5_1_50/data --log-error=/home/shlomi/sandboxes/msb_5_1_50/data/msandbox.err --pid-file=/home/shlomi/sandboxes/msb_5_1_50/data/mysql_sandbox5150.pid --socket=/tmp/mysql_sandbox5150.sock --port=5150
root     22827  0.0  0.0   4096   668 pts/3    S    16:50   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/eeyore.pid
mysql    22960  0.1  2.2 274584 90188 pts/3    Sl   16:50   0:18 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --log-error=/usr/local/mysql/data/eeyore.err --pid-file=/usr/local/mysql/data/eeyore.pid --socket=/tmp/mysql.sock --port=3306

Can you see the mess above?

The first two lines refer to a MySQL instance running under mysqlsandbox. The mysqld_safe script is passed the defaults-file parameter, and passes it on to the mysqld service.

However the next couple of lines refer to a MySQL server installed as a service; installed from a binary tarball, this instance reads configuration from the datadir. This time the mysqld_safe instance is passed nothing, but invokes mysqld with default-extra-file.

To be fair, I wasn’t expecting the “mysqld –verbose –help” invocation to find the mysqlsandbox configuration files. I did expect it to find the /usr/local/mysql/data/my.cnf file which it eventually used.

That’s nice & ugly. I can see the my.cnf file used by peeking at ps. A bit overkill.

Not quite there yet…

Because there’s still my private configuration file (resides on /home/shlomi/.my.cnf on my account). Now I do not expect this file to be read by my standard MySQL server, since it does not run as user “shlomi”. However my command line clients do actually read this file, and so I am affected by its settings.

I can verify whether such files have been used on a file system which is configured to support the atime option:

root@myhost:~# ls -lt --time=atime $(locate *my.cnf)

I usually keep the atime option enabled for my “/” and “/home” partitions, but disable it on data partitions.

• MySQL server is a single process application.
• It is multithreaded.
• It (usually) acts as a TCP/IP server, accepting connections.
• Each connection gets a dedicated thread.
• These threads are sometimes named processes, and sometimes they’re referred to as connections.

The last part is where confusion arises, so let me discuss again the use of threads and connections in MySQL.

MySQL truly is a single process server. It is multi threaded, in that there are many obvious and less obvious threads comprising the server. Such threads are the InnoDB I/O threads, the DELAYED INSERT thread, etc. Oh, and of course: the connection threads. More on this in a short while.

On older Linux versions or on glibc-static versions, one may view MySQL as a multi-process server. This is not so: it is merely because threads are mapped to OS processes. For the sake of this discussion this is irrelevant. mysqld is a single process.

So, every new connection gets its own thread. Assuming no thread pool is in use, every new connection makes for the creation of a new thread, and a disconnect causes for that thread’s destruction. Hence, there is a 1-1 mapping between connections and active threads. But then, there is a thread pool, which means there can be threads which are not associated with any connection. So, the number of threads is greater than or equal to the number of connections.

Here’s where terminology gets confusing. When you want to see what’s executing on the server, you issue SHOW PROCESSLIST:

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 4
   User: root
   Host: localhost
     db: mycheckpoint
Command: Query
   Time: 0
  State: NULL
   Info: SHOW PROCESSLIST
1 row in set (0.02 sec)

Perhaps this should have been called SHOW THREADLIST; the acting queries are not really processes.

OK, so there’s process #4 which is executing a query. What’s my process id? Turns out I don’t have a process id. I do get to have a CONNECTION_ID():

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|               4 |
+-----------------+

So how many processes or connections are now actually doing anything? We now must check for ‘Threads_running’.

mysql> SHOW GLOBAL STATUS LIKE 'Threads_running';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_running | 1     |
+-----------------+-------+

And so we have ‘Threads_cached’, ‘Threads_connected’ & ‘Max_used_connections’.

Confusing?

Most of the time one can simply think of processes, threads and connections as 1-1-1 mapped, and not bother with it.