We have seen number of issues with MySQL Server related to amount of memory you have in the system – these range from problems with large size Query Cache to bad drop table performance with large Innodb Buffer Pool size. As such I wonder how much memory do we really use to run MySQL Server these days ?

Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.

Feel free to select multiple sizes if you have multiple kinds of instances in production and for boundary numbers please select the highest range which applies, ie for 16GB system select 16-64GB range. Thank you for your feedback ! I will keep the pool open until the end of the year when it will be frozen so we do not get results from multiple years mixed.

MySQL command line utilities have an interesting property – if you only use the prefix of the option or command it will go over the list of available command and if there is only one command with matching prefix it will execute it with no warnings or any kind, otherwise it will report the error. For example mysqladmin e works as there is only one command “extended-status” which starts with “e” mysqladmin f however does not work because there are multiple commands which start with “f”.

This behavior can cause all kings of issues especially if you make a typo. I made one today (thankfully on the test box). MySQL init Scripts use “stop” and “start” commands to start and stop the server while mysqladmin uses “shutdown”. I mixed this while trying to stop Percona Server:

root@smt2:/var/lib/mysql# mysqladmin stop …

A few weeks back, I was working on a customer issue wherein they were observing database performance that dropped through the floor (to the point of an outage) roughly every 4 weeks or so. Nothing special about the environment, the hardware, or the queries; really, the majority of the database was a single table with an auto-incrementing integer PK and a secondary UNIQUE KEY.

The queries being run against this table were almost exclusively INSERT … ON DUPLICATE KEY UPDATE (INSERT ODKU), with the columns from the INSERT part of the statement corresponding to the columns in the secondary index, and they were coming in at a rate of approximately 1500 to 2000 per second, sustained, 24h per day. The mathematically-astute among you may already be able to see where this is going.

For purposes of discussion, we can use the following table to illustrate the situation:

CREATE TABLE update_test (
  id INT UNSIGNED NOT NULL …

If you are planning to upgrade or make any configuration change on your MySQL database the first advice usually is:

- Benchmark!

How should we do that benchmark? People usually run generic benchmark tools like sysbench, tpcc or mysqlslap that are good to know the number of transactions per seconds that a database can do but it doesn’t care about your workload, data set or queries. Those tools just run random queries against random generated data.

The best way to run a benchmark is replaying the load of your production server against a different database server and here is where Percona Playback can help us. Percona Playback is a new tool that can replay the data captured from the production server in another different server. It can replay queries from tcpdump or slow query logs. With this tool you can measure how a database upgrade, change on …

Did you just run an UPDATE against your 10 million row users table without a WHERE clause?  Did you know that in MySQL 5.5 that sometimes you can recover from a bad UPDATE statement?  This is possible if you are running in binlog_format=ROW !

Imagine this scenario:

CREATE TABLE `t1` (
 `c1` int(11) NOT NULL AUTO_INCREMENT,
 `c2` varchar(10) NOT NULL,
 PRIMARY KEY (`c1`)
) ENGINE=InnoDB;
INSERT INTO `t1` (`c2`) VALUES ('michael'), ('peter'), ('aamina');

We run an accidental UPDATE statement that changes a row:

UPDATE `t1` SET `c2` = 'tom' WHERE `c1` = 2;

If we examine this UPDATE using the Binary Logging format of STATEMENT the entry would look like:

# at 464
#121019 16:10:42 server id 1 end_log_pos 532 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1350677442/*!*/;
BEGIN
/*!*/;
# at 532
#121019 16:10:42 server id 1 end_log_pos 638 Query thread_id=1 exec_time=0 error_code=0
SET …

James Day just posted the great summary of defaults changes in MySQL 5.6 compared to MySQL 5.5
In general there are a lot of good changes and many defaults are now computed instead of hardcoded. Though some of changes are rather puzzling for me. Lets go over them:

back_log = 50 + ( max_connections / 5 ) capped at 900 – The large backlog is needed when there is a high rate (spikes) of connections which is rather unrelated to number of connections. You can get 1000s of connections a second even from as little as 100 connections, hence max_connections would be small. I think this is the case where somewhat higher fixed default (even 900) would be better than trying to be overly smart.

host_cache_size = 128 + 1 for each of the first 500 max_connections + 1 for every 20 max_connections over 500, …

Choosing a good InnoDB log file size is key to InnoDB write performance. This can be done by measuring the amount of writes in the redo logs. You can find a detailed explanation in this post.

To sum up, here are the main points:

• The redo logs should be large enough to store at most an hour of logs at peak-time
• You can either use the LSN in the SHOW ENGINE INNODB STATUS OUTPUT or the Innodb_os_log_written global status variable (if you are a Percona Server user, the LSN is also given by the Innodb_lsn_current status variable)

While reviewing the recommendation I made for a customer, one of my colleagues told me I was wrong in my redo log size calculations. After each one double checked the calculations, it turned out that we experienced something not expected:

…

I presented a webinar this week to give an overview of Tools and Techniques for Index Design. Even if you missed the webinar, you can register for it, and you’ll be emailed a link to the recording.

I’d like to invite folks who are interested in tools for query optimization to attend the new Percona online virtual training Analyzing SQL Queries with Percona Toolkit, October 29 – November 2, 2012. See http://store.percona.com/catalog.php?category=17 for details on this training class and how to register to attend.

During my webinar, I got a number of good questions. Here are the questions (paraphrased), with my answers.

Q: Can you link to the …

I recently worked through an issue with a client trying to detect data drift across some servers that were located in different timezones.  Unfortunately, several of the tables had timestamp fields and were set to a default value of CURRENT_TIMESTAMP.  From the manual, here is how MySQL handles timezone locality with timestamp fields:

Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.

This can result in every row being deemed out of sync when each server is using in it’s own timezone.  Fortunately, there is a workaround for this as a result of how pt-table-checksum/pt-table-sync calculate their …

Just yesterday I wrote about math of automatic failover today I’ll share my thoughts about what makes MySQL failover different from many other components and why asynchronous nature of standard replication solution is causing problems with it.

Lets first think about properties of simple components we fail over – web servers, application servers etc. We can put these behind some simple load balancer which can provide both high availability and fault tolerance. Technology can be made very robust because any node can handle request at any time and they all work concurrently. This means I do not have to move traffic
between the nodes but just chose what nodes participate in traffic handling which can be done a lot more seamless. This also drastically reduces risk of exposure in case of problems with setup.
I’ve seen …