You are here: Home / 2010 / Archives for November 2010

Unexpected mysqld crashing in 5.5

by

An update of MySQL from 5.0 to 5.5 on CentOS 5.5 64bit has not resulted in a good experience. The mysqld process would then crash every few minutes with the following message.

101120 8:29:27 InnoDB: Operating system error number 22 in a file operation.
InnoDB: Error number 22 means ‘Invalid argument’.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/operating-system-error-codes.html
InnoDB: File name /tmpfs/#sql6cf3_5c_0.ibd
InnoDB: File operation call: ‘aio write’.
InnoDB: Cannot continue operation.

The work around was to change the tmpdir=/tmpfs (which was a 16G tmpfs filesystem) to a physical disk.

The referenced URL didn’t provide any more information of help. Unlike Bug #26662 O_DIRECT is not specified as the flush method.

Damm you Peformance Schema

by

One significant new feature of MySQL 5.5 is the Performance Schema. I recently performed an upgrade from 5.0 to 5.5, however my check of differences in the MySQL variables via mysqladmin variables failed because we now have a new record long variable name “performance_schema_events_waits_history_long_size”.

The full list of new performance_schema variables are:

> | performance_schema_events_waits_history_long_size | 10000                                                                                                                  |
> | performance_schema_events_waits_history_size      | 10                                                                                                                     |
> | performance_schema_max_cond_classes               | 80                                                                                                                     |
> | performance_schema_max_cond_instances             | 1000                                                                                                                   |
> | performance_schema_max_file_classes               | 50                                                                                                                     |
> | performance_schema_max_file_handles               | 32768                                                                                                                  |
> | performance_schema_max_file_instances             | 10000                                                                                                                  |
> | performance_schema_max_mutex_classes              | 200                                                                                                                    |
> | performance_schema_max_mutex_instances            | 1000000                                                                                                                |
> | performance_schema_max_rwlock_classes             | 30

Not really a big problem, and not a complaint, just no longer a simple diff.

The Casual MySQL DBA – Operational Basics

by

So your not a MySQL DBA, but you have to perform like one. If you have a production environment that’s running now, what are the first things you do when it’s not running or reported as not running?

  1. Are the MySQL processes running? (i.e. mysqld and mysqld_safe)
  2. Can you connect locally via cli?
  3. What’s in the MySQL error log?
  4. What are current MySQL threads doing? Locked? long running? how many? idle sources?
  5. Can you connect remotely via cli?
  6. Verify free diskspace?
  7. Verify system physical resources?
  8. If this is a slave, is MySQL replication running? Is it up to date?
  9. What is the current MySQL load, e.g. reads/writes/throughput/network/disk etc?
  10. What is the current InnoDB state and load? (based on if your using InnoDB)

After you do this manually more then once you should be scripting these commands to be productive for future analysis and proactive monitoring?

Is a problem obvious? Does the output look different to what a normal environment looks like? (HINT: This list is not just for when there is a problem)

So moving forward?

  1. Is disk/memory/cpu/network bottleneck an issue you can resolve?
  2. Can you improving locking statements (if applicable)?
  3. Can you identify, analyse and tune long running statements?
  4. Do you know how to restart MySQL?
  5. Do you know who to call when you have a non working environment?
  6. When did your backup last run?
  7. Does your last backup work?

In order to support any level of production MySQL environment you need to know the answers to these questions? If you don’t, then this is your homework checklist for MySQL DBA operations 101. There a number of resources where you can find the answers, and this help can be available online, however never assume the timeliness of responses, especially if your expecting if for FREE! Open source software can be free, open source support rarely is.

MySQL HandlerSocket under Ubuntu

by

Starting with the great work of Yoshinori-san Using MySQL as a NoSQL – A story for exceeding 750,000 qps on a commodity server and Golan Zakai who posted Installing Dena’s HandlerSocket NoSQL plugin for MySQL on Centos I configured and tested HandlerSocket under Ubuntu 10.04 64bit.

NOTE: This machine already compiles MySQL and Drizzle. You should refer to appropriate source compile instructions for necessary dependencies.

# Get Software
cd /some/path
export DIR=`pwd`
wget http://download.github.com/ahiguti-HandlerSocket-Plugin-for-MySQL-1.0.6-10-gd032ec0.tar.gz
wget http://mysql.mirror.iweb.ca/Downloads/MySQL-5.1/mysql-5.1.52.tar.gz
wget  http://mysql.mirror.iweb.ca/Downloads/MySQL-5.1/mysql-5.1.52-linux-x86_64-glibc23.tar.gz

# Install MySQL Binaries
tar xvfz mysql-5.1.52-linux-x86_64-glibc23.tar.gz
ln -s mysql-5.1.52-linux-x86_64-glibc23 mysql5152
rm -rf mysql5152/mysql-test
rm -rf mysql5152/sql-bench
cd mysql5152
scripts/mysql_install_db
bin/mysqld_safe &

# Install MySQL Source
tar xvfz mysql-5.1.52.tar.gz

# Install HandlerSocket
tar xvfz ahiguti-HandlerSocket-Plugin-for-MySQL-1.0.6-10-gd032ec0.tar.gz
cd ahiguti-HandlerSocket-Plugin-for-MySQL-d032ec0/
./autogen.sh
./configure --with-mysql-source=${DIR}/mysql-5.1.51 --with-mysql-bindir=${DIR}/mysql5152/bin
make
sudo make install

# libtool: install: /usr/bin/install -c .libs/handlersocket.lai /home/rbradfor/projects/handlersocket/mysql5152/lib/plugin/handlersocket.la

# Install the Perl dependency
cd perl-Net-HandlerSocket
perl Makefile.PL
Checking if your kit is complete...
Warning: the following files are missing in your kit:
	lib/HandlerSocket.pm
Please inform the author.
Writing Makefile for Net::HandlerSocket
# For some reason I had to run this twice, after error I check, saw nothing wrong, ran a second time and it worked ???
perl Makefile.PL
Writing Makefile for Net::HandlerSocket
make
sudo make install

Files found in blib/arch: installing files in blib/lib into architecture dependent library tree
Installing /usr/local/lib/perl/5.10.1/auto/Net/HandlerSocket/HandlerSocket.so
Installing /usr/local/lib/perl/5.10.1/auto/Net/HandlerSocket/HandlerSocket.bs
Installing /usr/local/lib/perl/5.10.1/Net/HandlerSocket.pm
Installing /usr/local/lib/perl/5.10.1/Net/HandlerSocket/Pool.pm
Installing /usr/local/man/man3/Net::HandlerSocket.3pm
Appending installation info to /usr/local/lib/perl/5.10.1/perllocal.pod

cp lib/Net/HandlerSocket.pm blib/lib/Net/HandlerSocket.pm
cp lib/Net/HandlerSocket/Pool.pm blib/lib/Net/HandlerSocket/Pool.pm
/usr/bin/perl /usr/share/perl/5.10/ExtUtils/xsubpp  -typemap /usr/share/perl/5.10/ExtUtils/typemap  HandlerSocket.xs > HandlerSocket.xsc && mv HandlerSocket.xsc HandlerSocket.c
Please specify prototyping behavior for HandlerSocket.xs (see perlxs manual)
g++ -fPIC -c  -I. -I../libhsclient -D_REENTRANT -D_GNU_SOURCE -DDEBIAN -fno-strict-aliasing -pipe -fstack-protector -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -g -O3 -Wall -Wno-unused   -DVERSION="0.01" -DXS_VERSION="0.01" -fPIC "-I/usr/lib/perl/5.10/CORE"   HandlerSocket.c
Running Mkbootstrap for Net::HandlerSocket ()
chmod 644 HandlerSocket.bs
rm -f blib/arch/auto/Net/HandlerSocket/HandlerSocket.so
LD_RUN_PATH="/usr/local/lib" g++ -fPIC  -shared -O2 -g -L/usr/local/lib -fstack-protector HandlerSocket.o  -o blib/arch/auto/Net/HandlerSocket/HandlerSocket.so 	
	   -lhsclient  	

chmod 755 blib/arch/auto/Net/HandlerSocket/HandlerSocket.so
cp HandlerSocket.bs blib/arch/auto/Net/HandlerSocket/HandlerSocket.bs
chmod 644 blib/arch/auto/Net/HandlerSocket/HandlerSocket.bs
Manifying blib/man3/Net::HandlerSocket.3pm

Now all built, it’s as simple as the following to install:

${DIR}/mysql5152/bin/mysql -uroot
mysql> INSTALL PLUGIN HandlerSocket SONAME 'handlersocket.so';
mysql> SHOW PLUGINS;
+---------------+----------+----------------+------------------+---------+
| Name          | Status   | Type           | Library          | License |
+---------------+----------+----------------+------------------+---------+
| binlog        | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| partition     | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| ARCHIVE       | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| BLACKHOLE     | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| CSV           | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| FEDERATED     | DISABLED | STORAGE ENGINE | NULL             | GPL     |
| MEMORY        | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| InnoDB        | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| MyISAM        | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| MRG_MYISAM    | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |
| handlersocket | ACTIVE   | DAEMON         | handlersocket.so | BSD     |
+---------------+----------+----------------+------------------+---------+
11 rows in set (0.00 sec)

However, I found it not actually running even after restarting MySQL. You need the following configuration

cd ${DIR}/mysql5152
echo "[mysqld]
plugin-load=handlersocket.so
loose_handlersocket_port = 9998 # the port number to bind to (for read requests)
loose_handlersocket_port_wr = 9999 # the port number to bind to (for write requests)
loose_handlersocket_threads = 16 # the number of worker threads (for read requests)
loose_handlersocket_threads_wr = 1 # the number of worker threads (for write requests)" >> my.cnf

After restarting you get the following confirmation.

mysql>SHOW PROCESSLIST;
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+
| Id | User        | Host            | db            | Command | Time | State                                     | Info             |
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+
|  1 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  2 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  3 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  4 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  5 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  6 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  7 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  8 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
|  9 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 10 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 11 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 12 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 13 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 14 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 15 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 16 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             |
| 17 | system user | connecting host | handlersocket | Connect | NULL | handlersocket: mode=wr, 0 conns, 0 active | NULL             |
| 18 | root        | localhost       | NULL          | Query   |    0 | NULL                                      | SHOW PROCESSLIST |
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+
18 rows in set (0.00 sec)

My Test Data

USE test
CREATE TABLE user (
  user_id INT UNSIGNED NOT NULL,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(255) NOT NULL,
  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(user_id)
) ENGINE=InnoDB;

INSERT INTO user (user_id,name, email) VALUES
(100,'Mickey Mouse','[email protected]'),
(101,'Marvin the Martian','[email protected]'),
(102,'Spiderman','[email protected]');

SELECT user_id,name,email FROM user WHERE user_id=101;
+---------+--------------------+---------------------+
| user_id | name               | email               |
+---------+--------------------+---------------------+
|     101 | Marvin the Martian | [email protected] |
+---------+--------------------+---------------------+

My test retrieval program is basically unchanged from the original post.

$ cat retrieve.pl
#!/usr/bin/perl

use strict;
use warnings;
use Net::HandlerSocket;

#1. establishing a connection
my $args = { host => '10.0.0.6', port => 9998 };
my $hs = new Net::HandlerSocket($args);

#2. initializing an index so that we can use in main logics.
 # MySQL tables will be opened here (if not opened)
my $res = $hs->open_index(0, 'test', 'user', 'PRIMARY',
    'name,email,created');
die $hs->get_error() if $res != 0;

#3. main logic
 #fetching rows by id
 #execute_single (index id, cond, cond value, max rows, offset)
$res = $hs->execute_single(0, '=', [ '101' ], 1, 0);
die $hs->get_error() if $res->[0] != 0;
shift(@$res);
for (my $row = 0; $row < 1; ++$row) {
  my $user_name= $res->[$row + 0];
  my $user_email= $res->[$row + 1];
  my $created= $res->[$row + 2];
  print "$user_namet$user_emailt$createdn";
}

#4. closing the connection
$hs->close();

If your HandlerSocket is not running you will see:

$ perl retrieve.pl
FATAL_EXIT: getaddrinfo failed: ip_to_remote_host:9998
$ telnet 10.0.0.6 9998
Trying 10.0.0.6...
telnet: Unable to connect to remote host: Connection refused

When working

$ telnet 10.0.0.6 9998
Trying 10.0.0.6...
Connected to 10.0.0.6.
Escape character is '^]'.
P	0	test	user	PRIMARY	name,email,created
0	1
0	=	1	101
0	3	Marvin the Martian	[email protected]	2010-11-05 13:11:34


$ perl retrieve.pl
Marvin the Martian	[email protected]	2010-11-05 13:11:34

Again, great work by Yoshinori-san to have this code as production deployable. Now to run some real benchmarks.

Improving MySQL Insert thoughput

by

There are three simple practices that can improve general INSERT throughput. Each requires consideration on how the data is collected and what is acceptable data loss in a disaster.

General inserting of rows can be performed as single INSERT’s for example.

INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?);
INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?);
INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?);

While this works, there are two scalability limitations. First is the network overhead of the back and forth of each SQL statement, the second is the synchronous nature, that is your code can not continue until your INSERT is successfully completed.

The first improvement is to use MySQL’s multi values capability with INSERT. That is you can insert multiple rows with a single INSERT statement. For example:

INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?),  (?, ?, ?), (?, ?, ?);

Depending on how you collect the information to be inserted, you may be able to easily implement this. The benefit, as previously mentioned in The value of multi insert values shows an 88% improvement in performance.

One system variable to be aware of is max_allowed_packet. You may have to adjust this for larger INSERT statements.

Next is the ability to INSERT data based on information already in another table. You can also leverage for example another storage engine like MEMORY to batch up data to be inserted via this approach.

INSERT INTO table (col1, col2, col3) SELECT col1, col2, col3 FROM anothertable

The third option is to batch load your data from a flat file. Depending on how you source the information you are inserting, that may also be a significant improvement in throughput in bulk inserting data.

LOAD DATA [CONCURRENT] INFILE 'file'
INTO TABLE (col1, col2, col3)

On a closing note, the choice of storage engine can also have a significant effect on INSERT throughput. MySQL also offers other non ANSI options including DELAYED, IGNORE and LOW_PRIORITY. These should definitely be avoided.

OTN MySQL conference slides

by

2010 has been the first year I have re-presented any of my developed MySQL presentations. Historically I have always created new presentations, however Paul Vallee gave me some valuable advice at UC 2010. In the past two weeks I’ve traveled to seven countries in South America on the OTN LA tour where I have been speaking about and promoting MySQL.

My three current presentations have been improved and even simplified, more future improvements are planned. There is definitely a benefit in repeating a good presentation multiple times.

My SQL Idiosyncrasies That Bite OTN
MySQL Best Practices – OTN
MySQL Scalability Mistakes – OTN