So, you have a binlog. You want to find out something specific that happened inside of it. What to do? mysqlbinlog has some neat features, which I thought we would look at here.

I should first explain what mysqlbinlog really is. It is a tool that lets you analyze and view the binlogs/relaylogs from mysql, which are stored in binary format. This tool converts them to plaintext, so that they’re human-readable.

For the first tip, let’s start with the --read-from-remote-server option, which allows you to examine a binlog on a master server in order, perhaps, to dump it onto your slave and compare master/slave logs for potential problems*.

$ mysqlbinlog --read-from-remote-server -uwesterlund -p mysql-bin.000001 -h 127.0.0.1 -P 3306 | head -5
Enter password:
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#080815 19:25:23 server id 101  end_log_pos 107 	Start: binlog v 4, server v 6.0.5-alpha-log created 080815 19:25:23 at startup

Pretty useful!

Now, let’s assume we have a binlog that is 94 lines long*:

(more…)

There are quite a few “tuning primers” and “my.cnf generators” and “sample my.cnf files” online. The ultimate tool for generating an optimal my.cnf is not a tool. It’s a human with many years of experience, deep knowledge of MySQL and the full application stack, and familiarity with your application and your data.

I don’t know exactly the percentage, but quite a few of the servers I take a look at have been “optimized” with some tuning primer or question-and-answer script that spits out “optimal” parameters for my.cnf.

Most of the time these servers are far from optimal. Sometimes the my.cnf parameters are extremely wrong, to the point of causing a severe performance penalty.

If it were as easy as writing a tool to do this, don’t you think Maatkit would have mk-optimal-mycnf already? In my opinion — as someone who knows very well the complexity of creating a good my.cnf — it’s practically impossible. Much harder than syncing data, or manipulating a replication hierarchy, or any of the other things Maatkit can do already. And I doubt I’ll ever even feel motivated to try creating such a tool.

Don’t bother with scripts. Don’t waste your time with most of the advice you see on the web in forums — much of it is fundamentally wrong, even when it seems to come from an informed source. Don’t put too much faith in the my.cnf samples that come with your operating system; many of them have very bad advice in the comments, such as instructing you on how to set up replication in ways that guarantee breakage.

If you want solid advice, ask someone who knows what they’re doing (and can prove it). Or buy our book.

But even more fundamentally, you should not focus so much on my.cnf. It is not the be-all and end-all of performance. Tuning your server settings has far less impact on performance than tuning your schema, indexing, queries and — you guessed it — thinking deeply about your application architecture. Server settings are a distraction and a waste of time for most people.

Most my.cnf files I see only need minor tweaks, which give only so-so performance improvements. Tuning my.cnf only helps a lot when my.cnf has extremely bad parameters. The kind you’ll get from tuning primers and automated my.cnf optimization scripts.

Entry posted by Baron Schwartz | 7 comments

Add to: | | | |

How do you design a reliable distributed file system when the expected availability of the individual nodes are only ~1/5? That is the case for P2P systems. Dominik Grolimund, the founder of a Swiss startup Caleido will show you how! They have launched Wuala, the social online storage service which scales as new nodes join the P2P network.

The goal of Wua.la is to provide distributed online storage that is:

• large
• scalable
• reliable
• secure

by harnessing the idle resources of participating computers.

This challenge is an old dream of computer science. In fact as Andrew Tanenbaum wrote in 1995:
"The design of a world-wide, fully transparent distributed filesystem fot simultaneous use by millions of mobile and frequently disconnected users is left as an exercise for the reader"

After three years of research and development at at ETH Zurich, the Swiss Federal Institute of Technology on a distributed storage system, Caleido is ready to unveil the result: Wuala. Wuala is a new way of storing, sharing, and publishing files on the internet. It enables its users to trade parts of their local storage for online storage and it allows us to provide a better service for free. In this Google Tech Talk, Dominik will explain what Wuala is and how it works, and he will also show a demo.

read more

Update: Dormando asks Should you use memcached? Should you just shard mysql more?. The idea of caching is the most important part of caching as it transports you beyond a simple CRUD worldview. Plan for caching and sharding by properly abstracting data access methods. Brace for change. Be ready to shard, be ready to cache. React and change to what you push out which is actually popular, vs over planning and wasting valuable time.

Feedster's François Schiettecatte wonders if Fotolog's 21 memcached servers wouldn't be better used to further shard data by adding more MySQL servers? He mentions Feedster was able to drop memcached once they partitioned their data across more servers. The algorithm: partition until all data resides in memory and then you may not need an additional memcached layer.

Parvesh Garg goes a step further and asks why people think they should be using MySQL at all?

Related Articles

Pre-generating static files is an oldy but a goody, and as Thomas Brox Røst says, it's probably an underused strategy today. At one time this was the dominate technique for structuring a web site. Then the age of dynamic web sites arrived and we spent all our time worrying how to make the database faster and add more caching to recover the speed we had lost in the transition from static to dynamic.

Static files have the advantage of being very fast to serve. Read from disk and display. Simple and fast. Especially when caching proxies are used. The issue is how do you bulk generate the initial files, how do you serve the files, and how do you keep the changed files up to date? This is the process Thomas covers in his excellent article Serving static files with Django and AWS - going fast on a budget", where he explains how he converted 600K thousand previously dynamic pages to static pages for his site Eventseer.net, a service for tracking academic events.

Eventseer.net was experiencing performance problems as search engines crawled their 600K dynamic pages. As a solution you could imagine scaling up, adding more servers, adding sharding, etc etc, all somewhat complicated approaches. Their solution was to convert the dynamic pages to static pages in order to keep search engines from killing the site. As an added bonus non logged-in users experienced a much faster site and were more likely to sign up for the service.

The article does a good job explaining what they did, so I won't regurgitate it all here, but I will cover the highlights and comment on some additional potential features and alternate implementations...

read more