MariaDB: the new MySQL? Interview with Michael Monty Widenius.
Sep 29, 2011
InnoDB at Oracle OpenWorld
Sep 28, 2011
Sunny and I will be presenting at the Oracle OpenWorld next week:
Introduction to InnoDB, MySQL’s Default Storage Engine,? 10/04/11 Tuesday 01:15 PM, ? Marriott Marquis – Golden Gate C3, ? ? Calvin Sun
InnoDB Performance Tuning,? 10/04/11 Tuesday? 03:30 PM, ? Marriott Marquis – Golden Gate C2, ? Sunny Bains
The first session is for beginners, who are new to InnoDB and MySQL. The second session will cover many new performance features in MySQL 5.5 and 5.6, and share some tuning tips to maximize MySQL performance.
What to learn more about MySQL? There will be something for everyone. Come to join us!
MySQL Connection Timeouts
Apr 20, 2011
If you time the connect you will often see connection times are being close to 3 and 9 seconds. These are “magic” numbers which I remember from years ago, which correspond to SYN packet being dropped during connection attempt and being resent. 3 seconds corresponds to 1 packet being dropped and 9 seconds correspond to two. If this is happening it is possible you have network issues or more likely you have listen queue overflow. You can check if it is the case by running netstat -s and finding something like:
38409 times the listen queue of a socket overflowed
38409 SYNs to LISTEN sockets dropped
This means some SYN packets have to be dropped because kernel buffer of connection requests on LISTEN socket is overflow – MySQL is not accepting connections as quickly as it needs.
There are 2 tuning places you need to consider if this is what is happening.
First – Linux kernel net.ipv4.tcp_max_syn_backlog This is size of kernel buffer for all sockets.
Default I have on my kernel is 2048 though it might vary for different versions, you might need to increase it to 8192 or so if you have intense connection. I’ll explain the math below.
Second – is MySQL parameter back_log which has default value of just 50. You may want to set it to 1000 or even higher. You may also need to increase
net.core.somaxconn kernel setting which contains the maximum depth of listen queue allowed. The kernel I’m running has it set to just 128 which would be too low for many
conditions.
Now lets look more into the problem and do some Math. First lets look into how MySQL accepts connection. There is single main thread which is accepting connections coming to LISTEN
sockets. Once there is connection coming it it needs to create a new socket for incoming connection and create a new thread or take one out of the thread cache. From this point on MySQL processes network communication in multiple threads and can benefit from multiple cores but this work done by main thread does not.
Usually main thread is able to accept connections pretty quickly, however if it stalls waiting on mutex or doing any other work such as launching new thread takes a lot of time you can have the listen queue to overflow. Lets look at the database which accepts 1000 of connects/sec in average. This is a high number but you can see ones even higher. In most cases because of “random arrivals” nature of traffic you will see some seconds where as much as 3000 connections come in. Under such conditions the default back_log of 50 is enough just for 17 milliseconds, and if main thread stalls somewhere longer than, some SYN packets may be lost.
I would suggest sizing your tcp_max_syn_backlog and back_log value to be enough for at least 2 seconds worth of connection attempts. For example If I have 100 connects/sec which means I should plan for 300 connections using 3x for “peak multiplier”. This means they should be set to at least 600.
Setting it to cover much more than 2 seconds does not make much sense because if client does not get a response within 3 seconds it will consider SYN packet is lost and will send the new one anyway.
There is something else. If you’re creating 1000 of connections a second to MySQL Server you might be pushing your luck and at very least you’re using a lot of resources setting up and tearing down connections. Consider using persistent connections or connection pool at least for applications which are responsible for most of connections being created.
Setting up replication with XtraBackup
Apr 19, 2011
innobackupex –databases=test –stream=tar /tmp/ –slave-info | ssh root@slave "tar xfi – -C /root"
When it finishes, you should see something like this:
110419 18:54:21 innobackupex: completed OK!
tar: Read 6656 bytes from -
Take note of 3 lines immediately above it, where it states the binlog file and log position, like this:
innobackupex: MySQL binlog position: filename 'mysql-bin.000002', position 2515
6. On slave, run this command:
innobackupex –apply-log /locationWhereBackupIs
then, assuming the database name is test, run the 2 commands below to change the group and owner to mysql:
chgrp -R mysql test
chown -R mysql test
move the directory under mysqld’s datadir:
mv test/ /mysql/datadir
If test database has innodb tables in it, stop mysql on slave, then copy ibdata1 to datadir, restart mysql.
7. On master, open up port 3306 if it is not already open, then create the replication account:
grant replication slave, replication client on *.* to repl@'50.56.121.%' identified by 'p@ssw0rd';
8. On slave, run:
change master to master_host='50.56.121.96', master_user='repl', master_password='p@ssw0rd', master_log_file='see output from innobackupex backup command on master', master_log_pos=numFrominnobackupexOutputOnMaster;
start slave;
show slave status\G
MySQL & NoSQL Survey
Mar 13, 2011
Could you please take the time and fill in this short survey about using MySQL and NoSQL in companies.
I will publish the results in a week.
Thank you for your time.
<p>Loading…</p>
A cool terminal tip for Mac users
Mar 13, 2011
MySQL Workbench 5.2.32 GA Available
Mar 05, 2011
Data Modeling
Query (replaces the old MySQL Query Browser)
Administration (replaces the old MySQL Administrator)
Please get your copy from our Download site. Sources and binary packages are available for several platforms, including Windows, Mac OS X and Linux.
http://dev.mysql.com/downloads/workbench/
To get started quickly, please take a look at this short tutorial.
MySQL Workbench 5.2 RC Tutorial
http://wb.mysql.com/?p=406
Workbench Documentation can be found here.
http://dev.mysql.com/doc/workbench/en/index.html
In addition to the new Query/SQL Development and Administration modules, version 5.2 features improved stability and performance ? especially in Windows, where OpenGL support has been enhanced and the UI was optimized to offer better responsiveness.
This release also includes improvements to the scripting capabilities of the SQL Editor. You can read more about it in
http://wb.mysql.com/workbench/doc/
For a detailed list of resolved issues, see the change log.
http://dev.mysql.com/doc/workbench/en/wb-change-history.html
If you need any additional info or help please get in touch with us.
Post in our forums, leave comments on our blog pages or if you want to talk to us directly you can visit us on our IRC channel #workbench on irc.freenode.net.
- The MySQL Workbench Team
Xtrabackup for MySQL, and issues with streaming mode
Mar 05, 2011
MySQL 5.5.9
Feb 11, 2011
Fixing MySQL with a comment in the config file
Feb 04, 2011
It turns out that this server’s /etc/init.d/mysql_multi wouldn’t start unless it found the text “mysqld_multi” in the /etc/my.cnf file. Not a [mysqld_multi] config file section, but the text string “mysqld_multi”. It was using this text as a proxy for “I found a [mysqld_multi] configuration section.” This was a rather brittle test, as you can imagine.
After reading the source, I determined that the my.cnf file was fine and the configuration should not be changed, and I could not understand what had changed since it was previously working. Perhaps an automated upgrade or a similar change to the system had broken it.
The fix was to place the following comment into the file.
# This comment is only necessary to make /etc/init.d/mysql_multi work OK, it greps for mysqld_multi in an ‘if’ statement
That’s the only time I can recall fixing software by putting a comment into its configuration file. Unfortunately I don’t recall what Linux distribution this was on; I just checked a recent download, and the official MySQL distribution contains a file called mysqld_multi (note the different name) that doesn’t contain this error-prone test.
