Tweet

If your MySQL server has hundreds of clients (applications) and tens of thousands of queries per second,  MySQL default network settings may NOT be for you.  Network performance is not often a significant factor in the performance of MySQL.  That said, there are things to consider.

If you are building new applications make these changes now.  Developer expectations are hard to change.  My example below will break your application if  developers open a database connections and then spend ten minutes playing with their play doe before making a query.  If you have working applications …

MySQL 5.1 introduces row based binary logging.  In fact, the default binary logging format in GA versions of MySQL 5.1 is 'MIXED' STATEMENT*;   The binlog_format  variable can still be changed per sessions which means it is possible that some of your binary log entries will be written in a row-based fashion instead of the actual statement which changed data, even when the global setting on the master is to write binary logs in statement mode.   The row-based format does offer advantages particularly if triggers or stored procedures are used, or if non deterministic functions like RAND() are used in DML statements.

A statement based replication slave can get out of sync with the master fairly easily, especially if data is changed on the slave.   It is possible for a statement to execute successfully on a slave even if the data is not 100% in sync, so MySQL doesn't know anything is wrong.  This isn't the case …

I don't usually post these simple tricks, but it came to my attention today and it's very simple and have seen issues when trying to get around it. This one tries to solve the question: How do I restore my production backup to a different schema? It looks obvious, but I haven't seen many people thinking about it.

Most of the time backups using mysqldump will include the following line:

USE `schema`;
This is OK when you're trying to either (re)build a slave or restore a production database. But what about restoring it to a test server in a different schema?

The actual trick
Using vi (or similar) editors to edit the line will most likely result in the editor trying to load the whole backup file into memory, which might cause paging or even crash the server if the backup is big enough (I've seen it happen). Using sed (or similar) might take some time with a big …

(Re-posted from Florian’s blog.)

MySQL replication support for the Pacemaker cluster manager (the stuff that we explained in this webinar) has made it into the Linux-HA resource agents default branch. If you are interested in testing — and you should! — please read the extended announcement. Feedback is extremely welcome on the linux-ha-dev mailing …

I get ask, by application developers,  “how do you optimize MySQL”.  I do lots of things that don’t really relate to a developer. I analyze the percent of queries are being pulled from cache for instance.  What a developer can do to optimize the SQL they develop is a different questions.   So here is a quick list of things applications developers should know about MySQL.

Explain will analyze your query.

This example shows the possible indexes (keys) that could be used and the index that was selected.  2,262 rows where selected and then sorted (Using file sorts) and one record was returned (limit 1).

mysql> explain SELECT 5/9*(temp_F-32) as t, 5/9*(dewpt_F-32) as td, speed_mps as spd, dir
 > where stn='KLDM' and date_time<'2010-02-12 18:15' and …

I often hear suggestion to use fadvise system call to avoid caching in OS cache.
We recently made patch for tar, which supposes to create archive without polluting OS cache, as like in case with backup, you do not really expect any benefits from caching.

However working on the patch, I noticed, that fadvise with FADV_DONTNEED, does not really do what I expected (I used this call as it is often suggested for this purpose). In fact it does not prevent caching, it only releases already cached data.

And if we do man fadvise, it says exactly:
FADV_DONTNEED
Do not expect access in the near future. Subsequent access of pages in this range will succeed, but will
result either in reloading of the memory contents from the underlying mapped file or zero-fill-in-demand
pages for mappings without an underlying file.

So it is …

Did that ever happen to you in production?

PLAIN TEXT CODE:

1. [percona@sandbox msb_5_0_87]$ ./use
2. ERROR 1040 (00000): Too many connections

Just happened to one of our customers. Want to know what we did?

For demo purposes I'll use sandbox here (so the ./use is actually executing mysql cli). Oh and mind it is not a general-purpose best-practice, but rather a break-and-enter hack when the server is flooded. So, when this happens in production, the problem is - how do you quickly regain access to mysql server to see what are all the sessions doing and how do you do that without restarting the application? Here's the trick:

PLAIN TEXT CODE:

1. [percona@sandbox msb_5_0_87]$ gdb -p $(cat data/mysql_sandbox5087.pid) \
2.     …

A while back Friendfeed posted a blog post explaining how they changed from storing data in MySQL columns to serializing data and just storing it inside TEXT/BLOB columns. It seems that since then, the technique has gotten more popular with Ruby gems now around to do this for you automatically.

So when is it a good idea to use this technique?

If the application really is schema-less and has a lot of optional parameters that do not appear in every record, serializing the data in one column can be a better idea than having many extra columns that are NULL. The restriction on this, would be that searching on these columns now becomes more difficult[1]. A good example of this optional nature of data is user preferences - you only really need to store the settings that differ from …

This is going to be a really short post, but for someone it could save an hour of life.

So, you’ve nothing to do and you’ve decided to play around with IPv6 or maybe you’re happened to be an administrator of a web service that needs to support IPv6 connectivity and you need to make your nginx server work nicely with this protocol.

First thing you need to do is to enable IPv6 in nginx by recompiling it with --with-ipv6 configure option and reinstalling it. If you use some pre-built package, check if your nginx already has this key enabled by running nginx -V.

The results should have --with-ipv6 option in configure arguments:

There was a discussion on LinkedIn one month ago that caught my eye:

Database search by "within x number of miles" radius?

Anyone out there created a zipcode database and created a "search within x numer of miles" function ?
Thankful for any tips you can throw my way..

J

A few people commented that some solutions wouldn't scale. To understand why these sorts of geographic search queries are problematic in MySQL, it's best to show some execution plans on dummy data:

PLAIN TEXT SQL:

1. EXPLAIN …