Command Center

Here we will discuss one situation that to our experience happens very frequently for people working across multiple servers. The situation is that if a client sends a SQL-statement longer than the server max_allowed_packet setting, the server will simply disconnect the client. Next query from the same client instance will find that the ‘MySQL server has gone away’.  At least it is like that with recent server versions.

Actually I have not seen the ‘got packet bigger ..’ error myself for many years. Not since MySQL 3.23 or 4.0. I am uncertain if a recent server will sometimes still return ‘got packet bigger’ or not or if also this error message itself has ‘gone away’. If the ‘got packet bigger’ message is still relevant with recent servers it would be nice to have it specified under what conditions it occurs and when only ‘gone away’ will. If this error mesage is now ‘historical’ it should at least be removed from documentation or it should be mentioned that the error no. is reserved for this message – but not used anymore. But it would of course be much preferable to have the ‘got packet bigger’ error returned if that is the problem. It tells what the problem is – “MySQL server has gone away” does not tell anything specific. So ‘got packet bigger’ is a *much* better message than ‘gone away’. Also ‘got packet bigger’ is listed among client errors and not server errors what I would expect.  So maybe some problem with my understanding of things here?
Does anybody have any idea about if and why ‘got packet bigger’ now effectively seems to have ‘gone away’ too?
And most important: why disconnect the client? There are reconnect options of course, but it does not really help here. After a reconnect and executing the same query things just repeat themselves.
2)
Basically I never understood why MySQL stick with the default 1M setting for [mysqld] when it is 16M for [mysqldump] in configuration ‘templates’ shipped with the MySQL server (I have tried to ‘hint’ them several times over the last 3-4 years). Obviously they realize that 1M is often too little for backup/restore since they use a larger setting for mysqldump. However users use all other sorts of tools for backup: other script-based tools running on the server, third-party (and MySQL) GUI clients, web-based tools (hosting control panels, phpMyAdmin), backup/restore routines shipping with or built-in applications etc. Often users do not have access to run mysqldump at all on hosted servers (at least not if they are shared servers). Further often Sysadmins are unwilling to change configuration settings and users are left with the option to generate SINGLE INSERTS – with horrible restore performance as a consequence – to ensure cross-server exports/imports (and still it fails with a well-grown MEDIUMBLOB). I deliberately use the term ‘exports/imports’ and not ‘backup/restore’ because it also applies to various tools that can connect to two or more servers at a time and copy data using various techniques without actually generating a file.

The max_allowed_packet problem as described here has been a big problem for us over time. I do not think MySQL fully realises the importance of the problem – mostly because our tools and the tools/clients shipped with the server respectively are used primarily by different segments of users (with some significant overlapping of course). We handle this problem now 100% in SQLyog (we generate the largest BULK INSERTS possible up to 16M everywhere when transferring data from one server to another with all the methods available) but we cannot prevent user  - if he wants to use BULK INSERTS -  to generate a SQL-DUMP on one server that will not import another because BULK INSERTS are too large. We will of course only be able to handle it if we are connected to both servers.

3)
One solution would be to allow for max_allowed_packet as a SESSION variable. After a long time of unclarity about this – refer to http://bugs.mysql.com/bug.php?id=22891 andhttp://bugs.mysql.com/bug.php?id=32223
- it is now clear that it is not and will not be possible to override the GLOBAL setting for the SESSION. I regret this! It would be very nice to be able to “SET max_allowed_packet ..” on top of a SQL-script for instance.

4)
And actually – and most basically – I also do not really understand why a max_allowed_packet setting is required at all – except that it makes sense of course that a server admin should be able to restrict not-well-behaving users in bombing the server with statements containing 1 GB large WHERE-clauses! But then we are not talking about 1M but rather something like 16-64-100M as a critical threshold, I think.

max_allowed_packet= 64M
wait_timeout= 6000

solution worked for me as well – thanks a lot

Here is the general plan:

Tracking down a performance issue to the actual PHP app can be hard enough by itself, but what do you do once you’re sure that the app itself is the bottleneck? The excellent Xdebug extension has many useful features for assisting application developers, but today we’ll be looking at one specific feature that can help us see exactly what is slow in the application: profiling.

Profiling a PHP application can explain how much time the server spent in each function, each file, and each code path. It can also show you how many times a function or method was called, which is useful for diagnosing programming errors involving pointless looping. Xdebug generates cachegrind-compatible files (part of the Valgrind tool suite) which can also be used to create easy-to-understand graphs usingKCachegrind.

Let’s start off with some very simple PHP code that just loops 10 times so we can get some output out of KCachegrind. I won’t cover the installation of Xdebug here, but here is the configuration that I’m using for it:

Notice that I’m storing the cachegrind output on /dev/shm/trace. This is so I don’t kill performance on a production system when using Xdebug profiling with autotrace if I’m trying to get a large sampling of data. Not really necessary here, but it doesn’t hurt anything either, so we’ll just stick with it.

Here is the PHP code that we’ll be executing:

This will just print ’12345678910′ in our browser window when we load the page up. Because of the way we have Xdebug configured, it will only profile PHP execution when we either GET or POST the special string ‘XDEBUG_PROFILE’ . So, assuming our small test PHP script is hosted at http://example.com/test.php , we would want to hit http://example.com/test.php?XDEBUG_PROFILE . This will generate a file called something like this: ‘_test_php_XDEBUG_PROFILE-1296249911_052628.trace’ . Notice how it has the name of the PHP file that was executed, the query string, and the UNIX timestamp (including sub-second timing info). If we open up the file in a text editor, we can see the contents, which aren’t really meant to be human-readable in this mode (although you can change that, refer to the Xdebug documentation for more info).

Let’s see what happens when we run this cachegrind output file through KCachegrind. Because I really don’t like wasting all the space installing the KDE libraries on my Ubuntu system which runs Gnome, I just set up a Ubuntu Server virtual machine and installed KCachegrind and all the KDE libraries on that. I then connect to it using ‘ssh -X user@vmhostname kcachegrind’ which opens up KCachegrind in a new window, similar to if I were running it locally.

As you can see, test.php took up 100% of the time and there were no callers or calls to external scripts / functions / methods.

Now, let’s see what happens when we test this with some simple PHP software that we love: DokuWiki (http://www.dokuwiki.org/dokuwiki ):

Ahh, now we’re on to something. We can see on the chart that the ‘langsel’ function took ~63% of the processing time when executing the script, so if we wanted to optimise, we would want to look there first.

Here is another example: The main doku.php index page once DokuWiki is installed:

We can see the structure of the program from the graph, and while most of it looks normal, I’d be a bit suspicious as to why php::date_default_timezone_get took so long and consider just making that a constant to speed things up (by almost 13% !). This is, of course, with zero real wiki content on the page, so things would change as the content changes, but it’s still an very valuable tool for going deep into why an application is slow.

Next time, I’ll cover the other side of application performance tuning: SQL query profiling with Maatkit.