From another web page:

"I'm not sure that loading a 1GB dump file into MySQL is a particularly good idea - it's not the most stable piece of software and I've had issues with smaller dumps than that before." March 29, 2013


Something you need to monitor

There is a decent chance that I can guess one thing you are not monitoring on your server. And it is something that can stop insertions to a table. Guess what it is yet? 


A common problem

With the majority of the clients where I perform server audits I find an issue where both the host server and MySQL are not configured with anything other than the default values for the open files limit. This can cause a system stalling event where users are not able to access the server. What do we do to keep this from happening? I'm glad you asked! But first a little background...


Using pt-slave-delay and mysqlslap for bug hunting!

I recently had an issue with a client where they needed developers to have enforced lag on master/slave dev setup. There were issues where slave lag wasn't accounted for and with their previous development setup they never had lag. We needed to essentially always have some amount of slave lag so these bugs were exposed. My first thought was to use pt-slave-delay, which is part of the Percona toolkit. As an initial stab at solving the problem I configured pt-slave-delay for 30 seconds of replication lag and have it check every 15 seconds for the amount of current delay.

Ten Best MySQL Books

A bit over four years ago I collapsed just across the finish line of completing a book on MySQL. It was 700 pages and even I am not long-winded enough to write it all myself but fortunately Sheeri did a standup job for her half of the book. Writing this book was one of the most painful processes in my life. Even so, it was a good thing in some ways. Looking back, I am satisfied that others still think its a good book even in a field where literature gets out of date very rapidly. Just a few days ago I run across a link with a version of the "ten best" MySQL books.

Transition Almost Complete

Our transition to a new platform is almost complete. All the basic data is now in place. Things are being checked and verified, prodded and tweaked. It looks like all blog information is going to be intact. Since the blog goes back for something like four or five years now that is a bit of a relief. 


Once we get everythign in place then the real fun begins as we expand the capabilities of the site!



Tricks with mysqldump

More than once I have wished that mysqldump was never created. In many backup scenarios it is better to use other tools such as xtrabackup. However, there is still times when I end up using mysqldump. For one thing it is included with the mysql packages. It's "always there". Secondly, it sometimes is really a good fit for the tasks. And occassionally, with a bit of work, you can make it do neat tricks. 

I'm going to cover a few of the tricks I have used over the years. Hopefully you will find them helpful. In some cases one of these might even save your bacon.

Some new (and useful!!) MySQL 5.6 variables

Upgrading from MySQL 5.5 to MySQL 5.6 is a fairly straightforward process. However, the move to version 5.6 does bring with it some new varaibles with which to work.

Using tcpdump and pt-query-digest for Low Level Traffic Analysis

Recently we had an "interesting" situation arise that required a bit of work and a bit of thought. The issue was that we had some slave servers that began "hanging". We noticed that a table change didn't get propogated down one of the slaves and investigation showed that the master binary log execution position (Exec_Master_Log_Pos in the output of SHOW SLAVE STATUS\G) was not changing. There were no errors in the error log. Stopping slave on the server and restarting it brought replication back online but it only lasted for a few minutes.

Maximum Open Files

Recently I was discussing with some colleagues the possibility of consolidating some MySQL servers. While the servers are not heavily loading (averaging less than 1,000 queries a second) they are pretty large in terms of storage requirements. Each server has roughly 200 databases on each with approximatley 50 tables. Thats 10,000 tables per server.  Each server contains up to 1 terabyte of data so if you consolidated servers at a 10:1 ratio you would have 10 terabytes of data, 2,000 databases and 100,000 tables with 10,000 queries per second average load.



Subscribe to RSS - blogs