by Peter Zaitsev | May 31, 2009 | Insight for DBAs
This is mainly a cheat sheet for me to remember. Nothing rocket science. It often makes sense to use netcat/nc to copy MySQL database between hosts in trusted networks. It bypasses encryption overhead of SSH and depending on configuration can be significantly faster....
by Peter Zaitsev | May 21, 2009 | Insight for DBAs
Every so often I run into a situation when I need to terminate connections on MySQL server – for example, hundreds of instances of some bad query is running, making the server unusable. Many people have special scripts which can take the user, source host, or...
by Vadim Tkachenko | May 20, 2009 | Percona Software
Using xtrabackup for copying files can really saturate your disks, and that why we made special option --throttle=rate 1 --throttle=rate to limit rate of IO per second. But it really works when you do local copy. What about stream backup ? Even you copy just to remote...
by Vadim Tkachenko | May 17, 2009 | Insight for DBAs
Sometimes when you do profiling you can see number like this in timestamps. Periodically in our patches there was Query_time: 18446744073709550.000 in slow.log file (well, it was fixed recently, but still appears in other places). I faced this problem several years...
by Baron Schwartz | May 14, 2009 | Insight for DBAs
I see a lot of people filtering replication with binlog-do-db, binlog-ignore-db, replicate-do-db, and replicate-ignore-db. Although there are uses for these, they are dangerous and in my opinion, they are overused. For many cases, there’s a safer alternative....
by Vadim Tkachenko | May 7, 2009 | Percona Software
It is obvious thing for me, but I just figured out it may be not common understanding. Xtrabackup is also can be used (not only can, but we actually use it this way) to clone one slave to another, or just setup new slave from the master. And it is done in almost...
by Baron Schwartz | Apr 15, 2009 | Insight for DBAs
Sometimes a MySQL server running InnoDB takes a long time to shut down. The usual culprit is flushing dirty pages from the buffer pool. These are pages that have been modified in memory, but not on disk. If you kill the server before it finishes this process, it will...
by Peter Zaitsev | Mar 23, 2009 | Insight for DBAs
Quite common task during schema review is to find the optimal data type for the column value – for example column is defined as INT but is it really needed or may be SMALLINT or even TINYINT will do instead. Does it contain any NULLs or it can be defined NOT...
by Baron Schwartz | Mar 19, 2009 | Insight for DBAs
I feel like I’ve been seeing this a lot lately. occasionally, seemingly innocuous selects take unacceptably long. Or Over the past few weeks, we’ve been having bizarre outages during which everything seems to grind to a halt… and then fixes itself...
by Ryan Lowe | Mar 17, 2009 | Insight for Developers
The web is going the way of utf8. Drizzle has chosen it as the default character set, most back-ends to websites use it to store text data, and those who are still using latin1 have begun to migrate their databases to utf8. Googling for “mysql convert charset to...
by Baron Schwartz | Jan 28, 2009 | Insight for DBAs
Are you running MySQL on Debian or Ubuntu with InnoDB? You might want to disable /etc/mysql/debian-start. When you run /etc/init.d/mysql start it runs this script, which runs mysqlcheck, which can destroy performance. It can happen on a server with MyISAM tables, if...
by Baron Schwartz | Jan 23, 2009 | Insight for Developers
How smart is the MySQL optimizer? If it sees an expression repeated many times, does it realize they’re all the same and not calculate the result for each of them? I had a specific case where I needed to find out for sure, so I made a little benchmark. The query...
by Aurimas Mikalauskas | Jan 19, 2009 | Insight for Developers
These days I’m working with a customer who has an application based entirely on stored routines on MySQL side. Even though I haven’t worked much with stored procedures, I though it’s going to be a piece of cake. In the end – it was, but...
by Peter Zaitsev | Jan 15, 2009 | Insight for DBAs
Vadim wrote some time ago about how to find unused indexes with single query. I was working on the system today and found hundreds of unused indexes on dozens of tables so just dropping indexes manually did not look fun. So I extended Vadim’s query to generate...
by Baron Schwartz | Dec 22, 2008 | Insight for Developers
We have a lot of customers who do click analysis, site analytics, search engine marketing, online advertising, user behavior analysis, and many similar types of work. The first thing these have in common is that they’re generally some kind of loggable event....
by Baron Schwartz | Nov 26, 2008 | Insight for Developers
There are all sorts of different interfaces to memcached, but you don’t need any of them to make requests from the command line, because its protocol is so simple. Try this, assuming it’s running on the usual port on the local machine: echo stats | nc...
by Maciej Dobrzanski | Nov 7, 2008 | Insight for DBAs
Occasionally there is a need to see what queries reach MySQL. The database provides several ways to share that information with you. One is called general log activated with --log 1 --log (or --general-log 1 --general-log in MySQL 5.1+) start-up parameter. The log...
by Maciej Dobrzanski | Nov 6, 2008 | Insight for DBAs
Everyone does backups. Usually it’s some nightly batch job that just dumps all MySQL tables into a text file or ordinarily copies the binary files from the data directory to a safe location. Obviously both ways involve much more complex operations than it would seem...
by Piotr Biel | Nov 3, 2008 | Insight for DBAs
Some time ago I get brand new IBM POWER6 server as the replacement for “old” P5 used to host Oracle database. Because we planed to use advanced virtualization with VIOS + LPAR/DLPAR I conceived the idea to use one spare partition for MySQL tests. Because I...
by Maciej Dobrzanski | Oct 31, 2008 | Insight for DBAs
When running InnoDB you are able to dig into the engine internals, look at various gauges and counters, see past deadlocks and the list of all open transactions. This is in your reach with one simple command — SHOW ENGINE InnoDB STATUS 1 SHOW ENGINE InnoDB...
