People often ask “what’s the best hardware to run a database on?” And the answer, of course, is “it depends”. With MySQL, though, you can get good performance out of almost any hardware. If you need *great* performance, and you have active databases with a large data set, here are some statistics on real life databases — feel free to add your own.
What Was Going On Around 2:30pm? This is a question a customer asked us. To answer it we ran MySAR for a few days and queried the results for analysis. Looking at the data, we determined that the number of INSERT operations was significantly higher than any other, so we queried for the Com_insert status values. Com_insert is a counter that accumulates the number of INSERTs issued since the last server start (or since the last FLUSH STATUS command). For details on the variables available check Chapter 1. mysqld Options/Variables Reference.
This presentation was done by Sheeri Cabral of The Pythian Group and went into how to use SHOW ENGINE INNODB STATUS to get more information about your Innodb tables, foreign keys and transactions. This is a great presentation to learn how InnoDB works.
Welcome to the 165th edition of Log Buffer, the weekly review of database blogs.
This is a hack, but it is a good one. I was looking at some machines on a new client, and they had the general log turned on. I was surprised, because it was a fairly busy server, and they had had many problems with the server a few months ago. I thought perhaps they had turned on the general log to diagnose a problem and forgotten to turn it off, or something similar.
One day, while looking into slave lag problem, the idea of MySAR popped into my head and a few hours later I was using its first incarnation. I was able to relate the server’s I/O activity peaks with these lags and in turn, discovered that it was caused by a great number of INSERT statements coming in in waves. It was an encouraging outcome for what was nothing more than a proof of concept.
Welcome to the 164th edition of Log Buffer, the weekly review of database blogs.
After running both InnoDB Hot Backup and Xtrabackup, we have found that there is a measurable but not large difference between the resources that Xtrabackup and InnoDB Hot Backup consume. Here they are.
I will be giving a presentation explaining SHOW ENGINE INNODB STATUS for the Boston MySQL User Group. There is information about foreign keys, transactions, deadlocks and mutexes just waiting to be discovered, and I will show how to decipher the information.
We are often asked to “do a schema diff and create a script that will ‘patch’ one server.”. We like to use the best tool for the job, and while diff is good, I like to use MySQL workbench. The OSS (Community) edition provides all the functionality we need for creating a schema diff and patch. MySQL workbench does both reverse engineering and “forward engineer” alter script for free in the OSS/Community version. Step by step, here’s how to do it: