Profile cover photo
Profile photo
Mark Leith
MySQL database monitoring and management expert
MySQL database monitoring and management expert

Mark's interests
View all
Mark's posts

Post has shared content
After 5.7, there will be 8.0. 5.5 gave us performance_schema, which all of you are by now using instead of the slow log and things on a daily basis. But of course you are not using it pure, because that hurts - P_S is for machines - you are using, which 5.7 made part of the default install.

Now, sys is full of P_S joins, which used to be slow. Until now.

Post has shared content
"MySQL Logs": There are quite a few annotations that I have on this article. In fact, I would rewrite this, if I could.  Anyway:

Error Log: In Oracle MySQL 5.7, the error logging has been refactored. mysqld is now logging natively to syslog, and that is the recommended configuration on Unix and Linux. Previously, mysqld always has been logging to stderr and the mysqld_safe wrapper captured that and pushed it into syslog through "logger".

Be sure to read for the new and improved behavior.

The General Log produces a lot of output, has no filtering facility and does produce the output in server timeline order, so that queries belonging to a single connection are interspersed with a lot of noise from other connections. Also, the output volume on a server that already has problems is so large that the server will have even more problems with the general log enabled. 

That is, the General Log no longer has any useful use case. Never enable it.

The article suggests that the General Log can be used to audit a server. That is untrue. An audit mechanism must the able to attribute operations to a natural person, and it must be inescapeable. The general log does not fulfill that property. It also is way to noisy. Use something like for that or use that API with a tailor made property.

Never write any General Log or Slow Log to a MyISAM format tabele, even if that option exists. MyISAM has table level locks, so essentially logging to a MyISAM table generates a global lock on your server, killing all parallelism in your database. This particular option should never have been created in the first place, it is deadly.

Slow Log: The article refers to the Slow Query Log and mysqldumpslow for tracking performance problems. This is outdated and a lot less useful that PERFORMANCE_SCHEMA and the sys schema, both of which come installed and enabled by default in 5.7.

P_S is optimized for the machine and hard to use. The main goal of P_S is to capture and store performance data. sys is a set of views on top of P_S that format P_S data in a way that makes it easy to use for humans or monitoring programs.

Look at sys and learn to use it. Never use the slow log any more: Unlike slowlog, sys has current data, and does not only tell you that you have a problem, but also what the actual problem is.

+Mark Leith  has tons of slides on slideshare and maintains sys on github. Check out his work and stop the using or recommending slowlog.

Binlog: The binlog is not controversial, it's essential. It is needed for replication and for point in time recovery, as well as for server promotion in HA scenarios. A lot of tools, such as Galera, Orchestrator and many others depend on it.

The also depend on using InnoDB and every table having a properly defined primary key, so make sure that your server works like this: All tables InnoDB, every table with a primary key, binlog enabled and configured to Row Based Replication. Also, use the binlog and relay log indexes in InnoDB table format, not in file format (this is a different use case than the MyISAM General Log above, so this is not a contradiction).

It's the recommended setup. Other config options exist, but should not.

It has been hard in the past to make binlog work reliably in failure scenarios, and to make it efficient, but with group commit and with the index file being moved into InnoDB tables in the mysql.* schema, you can have both, a fast and reliable binlog.

Use the mysqlbinlog command (with the -vv option where necessary) to read the content of the binlog.

With ROW format binlog, you can also write tools that asynchronously monitor stuff that happens on your tables, which is nice for monitoring and for certain trigger-like update operations in master-slave scenarios

DDL Log: If you are discussing the DDL log in an article, you should probably also be discussing the Redo and Undo Logs, which are also part of internal operations, and unlike the DDL Log actually frequently have performance implications.

Refer to the appropriate manual sections for details.

Redo and Undo Logs: Basically: The Redo Log should probably for a normal mix of statements on a write-heavy server about 1/5 the size of your InnoDB buffer pool size. If you server is not write heavy, it can be smaller. If you are not monitoring redo and undo log utilization you won't notice if you are limiting your performance by maxing out available redo log space.

Putting these logs on SSD can be an extremely good idea.

The DDL log exists when necessary, and has a manual section, but is otherwise uninteresting.

Post has shared content
Shutting down for 40 minutes…

+Mark Leith says at "There was a bug in an early 5.6 or 5.5 version where [ a performance schema statment ] did replicate, but that was a pre-GA version."

It was 5.6.5, and I killed for 40 minutes with it, with Mark in the room…

At that point in time, had a large cluster of SSD based databases running to hold read-optimized, flattened hotel room availability data.

This data is being calculated from write-optimized availability data by a big materialized view engine, and at that point the original cluster design that had a bunch of sharded masters with two slaves each had been replaced with a new design that featured only standalone servers and multiple replicated writes by the materialization engine.

The new clusters already were running a GA version of MySQL, and were not replicating, but for some reason the productive cluster had not yet been upgraded and was still busy running 5.6.5 and the replicating cluster design.

The reason it had not been upgraded was that the old cluster-switch script did not work with the new cluster design. The new cluster-switch script had not been finished, because it wasn't a priority - we had a cluster-switch script already, so why another?

At that time I had a meeting with Mark as the PO for P_S and with the PO for replication, and Mark was showing of P_S to me. I wanted to turn it on on something productive for it to produce actually meaningful data, and because the flat availability database was very busy, I chose that one.

I verified that it should be safe and turned it on on a production box. I verified that the production box still did work, and that it returned actually useful P_S information.

What I did not notice, violating Booking rule #1 ("If you break it, will you even notice?") what that I lost that boxes two slaves. These slaves due to the bug got the replicated statement via row based replication and nicely executed the row handler for that, which was a NULL pointer, crashing the server process, badly.

Well, without hotel room availability data the site was down, and Ops got busy handling the problem. It shouldn't be a problem, because there were multiple replacement clusters up and running - just choose any of them and point production there to be back again. Only the scripts…

In the end, production was switched manually, and in the post-mortem Booking took away a lot of learnings from this particular incident. I got a nice keynote anecdote for the internal developer meeting at the Booking Annual Meeting out of it.

Or as an IBM manager supposedly phrased it in a similar case "We just invested half a million dollars into your education, why should we fire you?" No matter how much you try, no matter how careful you are, no matter how much you know, eventually you will fuck up, and then you need a good team to save your ass, so that you survive to tell the story for others so they don't need to fuck up themselves.

Post has attachment

Post has shared content
Great comments by Kris, not least the plug for p_s and ps_helper, but my thoughts exactly on the log tables as well..

Personally, I think the logging interfaces need an overhaul (and be made pluggable) so that you could create your own asynchronous logging to some UDP interface or something..
+Morgan Tocker on collecting MySQL slow query data

Two remarks:

1. Use PERFORMANCE_SCHEMA wherever you can. It completely rocks, you can have it turned on at all time with no performance impact whatsoever and it returns not only slow queries, but also a lot of information about why queries are slow.

PERFORMANCE_SCHEMA can be hard to learn. Find the PS_HELPER package by +Mark Leith and install it. That will make it easier and more bearable.

2. Morgan talks about the log tables in mysql.slow_log and mysql.general_log as well.

By default they are nontables, actually CSV files. They are being written to as OS-level "append only" files (file mode "a"), which is heavily optimized for efficient concurrent access. The manual talks about the option of turning them optionally into MyISAM files.

Do not ever do this!

MyISAM has table level locks. Turning log files of any kind into MyISAM files will serialize all log writes, and in the case of mysql.general_log, there is a log write for every query, including simple SET and SELECT statements.

If you have not had performance problems before this one change guarantees that you will have some afterwards. It is the single most stupid idea ever mentioned in the manual besides altering all tables to the BLACKHOLE storage engine.

Also, somebody still has to explain to me why having log files that potentially grow without bounds inside a relatively small config schema that needs quick backup and restore was deemed to be a good idea by somebody and why nobody else vetoed that.

In fact, in my book mysql.general_log and mysql.slow_log both need to die, or being thrown out forcibly from this schema.

Post has attachment

Post has shared content
Take a look at "We’re +NASA and we know it" by @Satire via +Washington Post  #MSL 

Post has attachment

Post has attachment

Post has attachment
Wait while more posts are being loaded