PlanetMysql.ru — информация о СУБД MySQL

Recently I had to turn a few EC2 instances into MySQL database servers. The third time I had to do it, I grabbed the list of steps from my previous sessions and just replayed it. Later I thought maybe polishing information a little bit and publishing a step-by-step walkthrough on the blog may help a few people. So here it is.

Before you begin.

For my MySQL instances I used the following:

• Extra Large, High-Memory, and High-CPU instances. Although the instruction should work on any type of instance.
• RedHat Enterprise Linux 6.2 64-bit AMI
• For MySQL data storage, multiple identical EBS devices attached to each instance

The configuration template provided in this post assumes the new MySQL instance only needs InnoDB storage engine.

Grab the packages.

Download the appropriate packages from MySQL web page. You will need client, shared-compat, and server. For example:

MySQL-client-5.5.24-1.el6.x86_64.rpm
MySQL-server-5.5.24-1.el6.x86_64.rpm
MySQL-shared-compat-5.5.24-1.el6.x86_64.rpm

Make sure the packages you downloaded are for a 64-bit architecture. You could of course choose packages from other vendors as the change should not affect the process in any relevant way.

Install MySQL

Remove the MySQL libraries that have been shipped with the system and install the new client and library packages:

[root@ip-10-238-234-26 ~]# rpm -e --nodeps mysql-libs
[root@ip-10-238-234-26 ~]# rpm -ihv MySQL-client-5.5.24-1.el6.x86_64.rpm MySQL-shared-compat-5.5.24-1.el6.x86_64.rpm
Preparing...                ########################################### [100%]
   1:MySQL-shared-compat    ########################################### [ 50%]
   2:MySQL-client           ########################################### [100%]

Then install the server package:

[root@ip-10-238-234-26 ~]# rpm -ihv MySQL-server-5.5.24-1.el6.x86_64.rpm
Preparing...                ########################################### [100%]
   1:MySQL-server           ########################################### [100%]

Search for EBS volumes.

Examine system to find the EBS volumes you will use for MySQL storage.

[root@ip-10-32-2-145 ~]# dmesg | grep 'unknown partition'
 xvds: unknown partition table
 xvdt: unknown partition table
 xvds: unknown partition table
 xvdt: unknown partition table

Please keep in mind some of the disks reported may be EC2 ephemeral storage, which you typically do not want to use for MySQL data, so make sure to only use EBS volumes in the following steps. If you are uncertain what is what, you can call fdisk -l and check the disk sizes:

[root@ip-10-32-2-145 ~]# fdisk -l

Disk /dev/xvde1: 6442 MB, 6442450944 bytes
255 heads, 63 sectors/track, 783 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
[..]

Create partitions.

Create a new partition on the first disk.

[root@ip-10-32-2-145 ~]# fdisk /dev/xvds
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x3774dc60.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-54823, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-54823, default 54823):
Using default value 54823

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): fd
Changed system type of partition 1 to fd (Linux raid autodetect)

Command (m for help): wq
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

Assuming you created several identical EBS volumes, duplicate the partition table onto each.

[root@ip-10-32-2-145 ~]# sfdisk -d /dev/xvds |  sfdisk /dev/xvdt
Checking that no-one is using this disk right now ...
OK

Disk /dev/xvdt: 54823 cylinders, 255 heads, 63 sectors/track

sfdisk: ERROR: sector 0 does not have an msdos signature
 /dev/xvdt: unrecognized partition table type
Old situation:
No partitions found
New situation:
Units = sectors of 512 bytes, counting from 0

   Device Boot    Start       End   #sectors  Id  System
/dev/xvdt1            63 880731494  880731432  fd  Linux raid autodetect
/dev/xvdt2             0         -          0   0  Empty
/dev/xvdt3             0         -          0   0  Empty
/dev/xvdt4             0         -          0   0  Empty
Warning: no primary partition is marked bootable (active)
This does not matter for LILO, but the DOS MBR will not boot this disk.
Successfully wrote the new partition table

Re-reading the partition table ...

If you created or changed a DOS partition, /dev/foo7, say, then use dd(1)
to zero the first 512 bytes:  dd if=/dev/zero of=/dev/foo7 bs=512 count=1
(See fdisk(8).)

Verify partitions.

[root@ip-10-32-2-145 ~]# fdisk -l | grep -E '^/dev/xvd'
[..]
/dev/xvds1               1       54823   440365716   fd  Linux raid autodetect
/dev/xvdt1               1       54823   440365716   fd  Linux raid autodetect

Create RAID volume.

Create a new RAID level 0 volume over all EBS partitions. Use relatively large chunk size – 128KB or 256KB should be good choices.

[root@ip-10-32-2-145 ~]# mdadm -C /dev/md0 --chunk=256 -n 2 -l 0 /dev/xvds1 /dev/xvdt1
mdadm: Defaulting to version 1.2 metadata
mdadm: array /dev/md0 started.

Save the md array configuration.

[root@ip-10-32-2-145 ~]# echo 'DEVICE /dev/xvds1 /dev/xvdt1' >> /etc/mdadm.conf
[root@ip-10-32-2-145 ~]# mdadm --examine --scan >> /etc/mdadm.conf

Here is what the file should look like

[root@ip-10-32-2-145 ~]# cat /etc/mdadm.conf
DEVICE /dev/xvds1 /dev/xvdt1
ARRAY /dev/md/0 metadata=1.2 UUID=f7d5b2e7:495efeb3:26297d34:7be87159 name=ip-10-32-2-145:0

Rebuild the initramfs image.

If this is not done, system won’t see the new /etc/mdadm.conf contents during boot and md device may be renamed to something else.

[root@ip-10-32-2-145 ~]# mkinitrd --force /boot/initramfs-2.6.32-220.el6.x86_64.img 2.6.32-220.el6.x86_64

You have to use the initramfs file and kernel version that are appropriate for your system. You can list /boot directory contents to figure this out:

[root@ip-10-226-155-162 ~]# ls -1 /boot/
config-2.6.32-220.el6.x86_64
efi
grub
initramfs-2.6.32-220.el6.x86_64.img
symvers-2.6.32-220.el6.x86_64.gz
System.map-2.6.32-220.el6.x86_64
vmlinuz-2.6.32-220.el6.x86_64

Create filesystem.

This part may take a while depending on the volume size.

[root@ip-10-32-2-145 ~]# mkfs.ext4 /dev/md0
mke2fs 1.41.12 (17-May-2010)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=64 blocks, Stripe width=128 blocks
55050240 inodes, 220182272 blocks
11009113 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
6720 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
        4096000, 7962624, 11239424, 20480000, 23887872, 71663616, 78675968,
        102400000, 214990848

Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 36 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.

Create a mount point.

I do not like mounting to /var/lib/mysql directly. Typically I create a new directory inside /vol, or /mnt, or /data.

[root@ip-10-32-2-145 ~]# mkdir -p /vol/mysql

Store the filesystem information.

Always use noauto option during installation. It skips automatic filesystem mounting during system init, which can save you from a lot of trouble if something does not work for any reason and when the boot process hangs on this step. EC2 does not come with a system console that would allow rescuing a system from such state.

[root@ip-10-32-2-145 ~]# echo '/dev/md0   /vol/mysql   ext4   rw,nobarrier,noatime,nodiratime,noauto   0 0' >> /etc/fstab

It may actually be very reasonable to keep noauto there forever and either mount the database volume manually each time the server reboots (it implies you also have to start MySQL manually) or implement volume mounting in a way that does not block the instance in case of some errors.

Mount the new volume.

This command can now use the configuration from /etc/fstab.

[root@ip-10-32-2-145 ~]# mount /vol/mysql

The command should not return anything, but to make sure it worked, just run a quick check whether the volume was acutally mounted or not:

[root@ip-10-32-2-145 ~]# df -h /vol/mysql
Filesystem            Size  Used Avail Use% Mounted on
/dev/md0              827G  201M  785G   1% /vol/mysql

Mounted on should point the new mount point and not to / for example.

Move the default data directory.

Move into the new location the default MySQL data directory created by the MySQL-server package installtion. Also create a directory for MySQL logs.

[root@ip-10-32-2-145 ~]# mv /var/lib/mysql /vol/mysql/
[root@ip-10-32-2-145 ~]# ln -s /vol/mysql/mysql /var/lib/mysql
[root@ip-10-32-2-145 ~]# mkdir /vol/mysql/log
[root@ip-10-32-2-145 ~]# chown mysql:mysql /vol/mysql/log

Set swappiness to prevent unnecessary swapping.

You can refer to this article for more details on swappiness.

[root@ip-10-32-2-145 ~]# echo 'vm.swappiness = 0' >> /etc/sysctl.conf
[root@ip-10-32-2-145 ~]# sysctl -p /etc/sysctl.conf

Configure time synchronization.

Create a new file called ntpdate in /etc/cron.daily with the following contents:

#!/bin/sh

/usr/sbin/ntpdate pool.ntp.org 1> /dev/null 2>&1

Then set the file permissions:

[root@ip-10-32-2-145 ~]# chmod +x /etc/cron.daily/ntpdate

Disable or configure SELinux.

RedHat comes with SELinux enabled, but often SELinux will be disabled during a database server installation process. To disable, perform the following:

[root@ip-10-32-2-145 ~]# cd /etc/sysconfig/
[root@ip-10-32-2-145 sysconfig]# replace 'SELINUX=enforcing' 'SELINUX=disabled' -- selinux
selinux converted

Restart the system.

It is time to restart the system. You should do this for two reasons:

• You want to verify if the RAID volume loads correctly after a restart and that MySQL data volume can be mounted again without any problems.
• You disabled SELinux and it is also the only way to make the change effective.

Verify md array stats and SELinux status.

Once the system boots again, verify the md array status.

[root@ip-10-32-2-145 ~]# cat /proc/mdstat
Personalities : [raid0]
md0 : active raid0 xvds1[0] xvdt1[1]
      880729088 blocks super 1.2 256k chunks

unused devices: 

It should still say md0 if you regenerated the initramfs image.

If you disabled SELinux, verify that as well.

[root@ip-10-32-2-145 ~]# selinuxenabled
[root@ip-10-32-2-145 ~]# echo $?
1

selinuxenabled indicates whether SELinux is enabled or disabled. It returns 0 if SELinux is enabled and 1 if it is not enabled.

Mount data volume.

[root@ip-10-32-2-145 ~]# mount /vol/mysql
[root@ip-10-32-2-145 ~]# df -h /vol/mysql
Filesystem            Size  Used Avail Use% Mounted on
/dev/md0              827G  201M  785G   1% /vol/mysql

If you want, now you can remove noauto from /etc/fstab.

Create MySQL configuration file.

I sometimes use this simple script that writes a good default configuration into /etc/my.cnf. It auto-tunes a few parameters that commonly need customization. You could just copy-paste it into the command line and run.

(cat <<EOF
[mysqld_safe]
log-error = /vol/mysql/log/mysql-error.log

[mysqld]
user = mysql
port = 3306
socket = /vol/mysql/mysql/mysql.sock

datadir = /vol/mysql/mysql
tmpdir = /tmp
pid-file = /vol/mysql/mysql/mysql.pid

character-set-server = utf8

slow-query-log
slow_query_log_file = /vol/mysql/log/mysql-slow.log
long_query_time = 10

log-bin = /vol/mysql/log/mysql-bin
relay-log = /vol/mysql/log/mysql-relay
server-id = $(/bin/hostname | /usr/bin/md5sum | /usr/bin/awk --non-decimal-data '{ hex=substr($1, 24, 8); printf ("0x"hex)+0 }')

back_log = 50
max_connections = 500
max_connect_errors = 100
skip-name-resolve

key_buffer_size = 8M
sort_buffer_size = 2M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 8M
max_allowed_packet = 16M
max_heap_table_size = 64M
tmp_table_size = 64M

table_open_cache = 2048
thread_cache_size = 16

innodb_file_per_table
innodb_data_file_path = ibdata1:128M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_buffer_pool_size = $(/usr/bin/awk '/MemTotal:/ { printf "%.0fM\n", ($2*0.70)/1024 }' /proc/meminfo)
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_read_io_threads = $(($(/bin/grep -E 'md[0-9]+ : active raid0' /proc/mdstat | wc -w) - 2))
innodb_write_io_threads = $(($(/bin/grep -E 'md[0-9]+ : active raid0' /proc/mdstat | wc -w) - 2))
innodb_io_capacity = $((($(/bin/grep -E 'md[0-9]+ : active raid0' /proc/mdstat | wc -w) - 4 ) * 130))
innodb_thread_concurrency = 0

[mysql]
no-auto-rehash
socket=/vol/mysql/mysql/mysql.sock

[client]
socket=/vol/mysql/mysql/mysql.sock

EOF
) > /etc/my.cnf

Remember this is just a template. Each server may need individual adjustments to database configuration.

It’s done.

You can now start MySQL.

[root@ip-10-32-2-145 ~]# /etc/init.d/mysql start
Starting MySQL (Percona Server)........................     [OK]

PlanetMySQL Voting: Vote UP / Vote DOWN

Have you ever tried to kill a query, but rather than just go away, it remained among the running ones for an extended period of time? Or perhaps you have noticed some threads makred with Killed showing up from time to time and not actually dying. What are these zombies? Why does MySQL sometimes seem to fail to terminate queries quickly? Is there any way to force the kill command to actually work instantaneously? This article sheds some light on it.

Threads and connections

MySQL uses a separate thread for each client connection. A query sent to MySQL is handled by a thread that was previously associated with the connection over which the query arrived. Anyone with sufficient privileges can see the list of currently active threads, along with some additional details, by running SHOW PROCESSLIST command, which returns a table-like view where each connection becomes a separate row:

+-----------+-------------+-------------------+--------+-------------+----------+-------------+---------+
| Id        | User        | Host              | db     | Command     | Time     | State       | Info    |
+-----------+-------------+-------------------+--------+-------------+----------+-------------+---------+
| 827044892 | production  | 10.0.1.100:48596  | proddb | Sleep       |        1 |             |    NULL |
| 827044893 | production  | 10.0.1.100:39181  | proddb | Sleep       |        1 |             |    NULL |
| 827044894 | production  | 10.0.1.100:48598  | proddb | Sleep       |        1 |             |    NULL |
| 827044895 | production  | 10.0.1.100:39183  | proddb | Sleep       |        1 |             |    NULL |

More advanced techniques of dealing with this information are described in the posts titled “Anohter way to work with MySQL process list” and “How to selectively kill queries in MySQL?”.

Any running query or any existing connection from the list can be terminated by using KILL command.

What actually happens when you run KILL?

The command sytnax is KILL [QUERY | CONNECTION] <thread_id>, where thread_id is the value from the first column of the process list output. The optional argument determines whether only running query or should the entire session be terminated. It defaults to the latter, so specifying CONNECTION is not required.

Running the command doesn’t actually do anything except for setting a special flag inside the selected thread. Therefore the kill operation doesn’t happen synchronously with the corresponding request. In many cases it takes some time for a thread or a query to stop. The flag is checked at various stages of statement execution. When it happens exactly, or how frequently, depends on the work a thread is actually doing.

For example:

• During ALTER TABLE it is checked before each block of rows is read from the original table while MySQL is rewriting data into a new temporary table. In the process, the temporary table is deleted and the original structure remains unchanged.
• For UPDATE and DELETE there is also an additional check after each updated or deleted row. If a query managed to make any changes prior to noticing the request to terminate, they have to be rolled back. Note: if a table’s storage engine does not support transactions (e.g. MyISAM), the changes cannot be rolled back, so the operation will result in partial update!
• SELECT also checks it after reading a block of rows.

Of course, these were just basic examples and the behavior can be different in different situations. It is even possible that some queries will be unkillable in certain circumstances. A case of this happened once while reading from INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES_INDEX table. The database was having performance problems at the time and the query execution basically stopped on a lock somewhere deep inside InnoDB and it never got back to the point where the flag value could be checked again.

Threads stay Killed for a long time. What does it mean?

Actually, there can be two different cases. If KILL <thread_id> was issued, there would be Killed in the process list. KILL QUERY <thread_id> doesn’t kill a connection, but rather it only stops a running query within a connection, so in that case query end text may appear instead.

What if you see something like this?

+----+------+-----------+------+---------+------+----------+------------------------------------+
| Id | User | Host      | db   | Command | Time | State    | Info                               |
+----+------+-----------+------+---------+------+----------+------------------------------------+
| 10 | root | localhost | NULL | Query   |    0 | NULL     | show processlist                   |
| 14 | root | localhost | NULL | Killed  |   27 | Updating | update testdb.sometable set cc=sqrt(id) |
+----+------+-----------+------+---------+------+----------+------------------------------------+

It can either be an effect of a bug (e.g. Bug #52528), or more likely it means the database is performing some work internally to clean up after a task that was terminated.

There is of course no easy way to confirm if this is a bug. So in order to figure it out, you should rather look for evidence that it is not a bug. That what you see is just the effect of a standard operation, which MySQL has to perform to clean up after a query or a transaction.

Probably the most common reason for a thread to stay with either Killed or query end for a longer period of time is waiting for a transaction rollback on InnoDB tables. This sometimes can take a lot of time to complete, especially when hundreds of thousands or millions of changes have to be removed.

How to verify that?

Check is the output of SHOW ENGINE INNODB STATUS\G. It can simply print the information if there is a rollback currently in progress:

---TRANSACTION 0 10411, ACTIVE 28 sec, process no 15506, OS thread id 140732309711184 rollback
mysql tables in use 1, locked 1
ROLLING BACK 7585 lock struct(s), heap size 751600, undo log entries 798854
MySQL thread id 14, query id 206 localhost root end
update testdb.sometable set cc=sqrt(id)

The example shows how easily the information can be found. If a thread is marked with Killed, or with query end, and the InnoDB engine status reports a rollback for the same thread, just wait until it ends.

What if it isn’t that?

It can be related to removing some temporary table from disk. An ALTER TABLE may need to discard a very large temporary table, while large file removal on some filesystems (e.g. ext3 or ext4) can be rather slow, so it may need a few seconds or sometimes even longer than that. A temporary table can also be created by any DML statement, but usually not nearly as big in size.

It should not usually be necessary as even in the most extreme cases deleting a file should not take more than ten or twenty seconds, but under heavy I/O load it could be much longer, so is there any way to see whether any temporary table were created or not?

In Percona Server or MariaDB you check the contents of INFORMATION_SCHEMA.GLOBAL_TEMPORARY_TABLE and INFORMATION_SCHEMA.TEMPORARY_TABLE. It will only work for manually established temporary tables with CREATE TEMPORARY TABLE statement, not for those created implicitly by MySQL to execute complex queries.

mysql> select * from INFORMATION_SCHEMA.GLOBAL_TEMPORARY_TABLES;
+------------+--------------+------------+--------+-------------------+------------+----------------+-------------+--------------+-------------+-------------+
| SESSION_ID | TABLE_SCHEMA | TABLE_NAME | ENGINE | NAME              | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | INDEX_LENGTH | CREATE_TIME | UPDATE_TIME |
+------------+--------------+------------+--------+-------------------+------------+----------------+-------------+--------------+-------------+-------------+
|   28051622 | test         | tbl        | InnoDB | #sql8c3_1ac08a6_0 |       1265 |             51 |       65536 |            0 | NULL        | NULL        |
+------------+--------------+------------+--------+-------------------+------------+----------------+-------------+--------------+-------------+-------------+

SESSION_ID is the same as the thread identifier in the process list, so you can connect any killed threads to their temporary tables.

The other type of temporary tables cannot be easily traced. Sometimes it’s possible to spot the information in the SHOW PROCESSLIST output:

mysql> show processlist;
+----------+------+-----------+------+---------+------+----------------------+---------------------------------------------------+
| Id       | User | Host      | db   | Command | Time | State                | Info                                              |
+----------+------+-----------+------+---------+------+----------------------+---------------------------------------------------+
| 1934     | root | localhost | test | Killed  |    4 | Copying to tmp table | INSERT INTO test.tbl SELECT * FROM test.testtable |
..

Sometimes you can check what temporary files the instance keeps open:

server ~ # lsof -c mysqld | grep \#sql
mysqld  5626 mysql  138u   REG              253,1       1024 43843585  /vol/vol1/mysql/#sql_95fa_0.MYI
mysqld  5626 mysql  139u   REG              253,1  227262885 43843605  /vol/vol1/mysql/#sql_95fa_0.MYD

The colored value is the file’s size. These may help you to make an assumption that database could be removing a temporary table.

Conclusions

From time to time it may be normal to see a database thread that was killed, but didn’t die immediately, or a query that can’t seem to end. The real operation is not be performed synchronously with the kill command and afterwards MySQL may still need some time to clean things up properly.

No matter what the real cause is, it is impossible to get rid of such hanging threads without a database restart. They should, however, simply be allowed to go away on their own.

There is also no way of forcing a kill that would execute instantly like kill -9 <pid> in Unix systems.

PlanetMySQL Voting: Vote UP / Vote DOWN

Last month, Ronald Bradford, Giuseppe Maxia and Mark Leith spoke about how to simulate a mysqldump –ignore-database.
This mysqldump option doesn’t exist and these three guys have given us various helpful solutions.

But for those of us who use ZRM community to make backup with mysqldump, the –exclude-pattern seems to do the job :

--exclude-pattern "pattern" 
All databases or tables that match the pattern are not backed up. 
If --all-databases or --databases are specified, the exclude pattern applies
to database names. If --database is specified, the exclude pattern applies
to table names. Wildcard characters * (match one or more characters),
? (match one character), [] (match one of the characters specified
within parenthesis) and | (match one of the patterns) are supported.
For example: Exclude pattern "*_db|dbase[123]|abcd" will match names
tom_db, dbase1, dbase2, abcd. All tables or databases that match this pattern
will be excluded. Character # is not permitted in the exclude pattern.

But this option seems to not work properly and I remember that I’ve used a hack to force ZRM to exclude a database from the database list since I used this tools.
I’m not talented enough in perl to really hack the code of this tool, so, I made it simple.

I made ​​a simple change to this file : /usr/bin/mysql-zrm-backup
By adding this new line after the row number 1597 :  $params = “–databases @pdbs”

To obtain that :

} else {
 $params = "--all-databases ";
 @pdbs = &enumAllDatabases();
 if( defined $inputs{"exclude-pattern"} ){
 @pdbs = &filterPattern( @pdbs );
 my $l = @pdbs;
 if( $l == 0 ){
 &printAndDie( "Nothing to backup after exclude-pattern is applied\n" );
 }
 $params = "--databases @pdbs"
 }
 if( $verbose ){
 &printLog( "backup of the following databases will be done @pdbs\n" );
 }

I can now exclude a database from my dump (but not a table).

But I’m sure a talented person could try to review the code to improve it in a right way.
If you are this person, you know what needs to be done…

If you are a ZRM Community edition practicer, share your thoughts about that.

PlanetMySQL Voting: Vote UP / Vote DOWN

I would like to share this story based on a true event about the temporary files behavior in MySQL.

MONyog reports this error to my already full mailbox several times a day :

 

1 – Catch the query (if you can) !

 

I don’t have access to the client logs but I would like to know which query is involved in this error.

Let me explain how I can retrieve informations about this query with MONyog and a very simple shell loop :

•  Enable the query sniffer in MONyog (based on processlist) : Edit server -> Advanced settings -> Sniffer settings

 

while [ 1 = 1 ]; do { date;ls -artlh /database/tmp ; df -h /database/tmp;
  lsof | grep mysql | grep /database/tmp;sleep 1; }; done

 

4 – What next ?

 

The killer question : why did you have only 6.5 GB for your temporary space ?
Answer : Why not :-)

You have to consider this behavior to set your temporary space properly, specially if this space is a ramdisk filesystem.

Tschüs !

PS : I let the experts explains why these two additional files are created (hint : look at the Exta column)

PlanetMySQL Voting: Vote UP / Vote DOWN

I just want to share about a strange behavior of one of our MySQL server yesterday.
This server is a 5.1.50 MySQL server on debian 4.0 (Yes, I know…)

When a “mysqld got signal 6” error occurred yesterday, the MySQL server crashed and didn’t want to restart.
Then, I found these informations in the error log file :

/usr/local/mysql/bin/mysqld: File '*** glibc detected ***
malloc():memory corruption: 0x00002aac2d5ab460 ***' not found (Errcode: 2)
120306 17:19:47 [ERROR] Failed to open log (file '*** glibc detected ***
malloc():memory corruption: 0x00002aac2d5ab460 ***', errno 2)
120306 17:19:47 [ERROR] Could not open log file
120306 17:19:47 [ERROR] Can't init tc log
120306 17:19:47 [ERROR] Aborting

120306 17:19:47 InnoDB: Starting shutdown...
120306 17:19:53 InnoDB: Shutdown completed; log sequence number 55 1061584593
120306 17:19:53 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

Great, let see what is the Errcode 2 through the perror utility :

# perror 2
OS error code 2: No such file or directory

But where is this missing file ? And have you ever heard about a file called “*** glibc detected *** malloc():memory corruption: 0x00002aac2d5ab460 ***” ?!
No, of course not. But this error message  was more meaningful : Could not open log file

The InnoDB log files and binary log files seemed to be ok (file permissions and other checks showed no failure)
But when I did a cat on the binary log index file… surprise ! :

# cat mysql-bin.index
/var/database/bin-log/mysql-bin.126242
/var/database/bin-log/mysql-bin.126243
/var/database/bin-log/mysql-bin.126244
/var/database/bin-log/mysql-bin.126245
*** glibc detected *** malloc(): memory corruption: 0x00002aac2d5ab460 ***

The index file was corrupted during the last crash and an error message was inserted inside this file because of a memory corruption.
Deleting files helped to restart the MySQL server but I found very strange that MySQL wasn’t able to deliver a more accurate message about that.

PlanetMySQL Voting: Vote UP / Vote DOWN

Today I’m working on integrate the Percona toolkit (instead of maatkit) in my own tools and I’m playing with pt-query-digest and pt-query-advisor.
These tools can be very interesting to identify some queries from established rules.
The  –review option is available for two of them and helps me to store a sample of each class of query and match them with an advice.

The rules (or advices) are available in the pt-query-advisor documentation and let you identify various problems such as queries with an argument with leading wildcard or with a table joined twice, for example.
There are 3 types of rules : note, warning and  critical.

The goal here is to quickly identify the queries corresponding to a rule, from the slow query log.
So, let me explain what was my method to do that, and, please, give me yours.

First, I need to create a table to store the queries (you can also use –create-review-table option) :

CREATE TABLE query_review (
   checksum     BIGINT UNSIGNED NOT NULL PRIMARY KEY,
   fingerprint  TEXT NOT NULL,
   sample       TEXT NOT NULL,
   first_seen   DATETIME,
   last_seen    DATETIME,
   reviewed_by  VARCHAR(20),
   reviewed_on  DATETIME,
   comments     TEXT
)

It’s interesting to note that the 3 last columns are used to annotate the queries. A simple PHP interface or script can be used to fill these fields (sorry, not enough time to write this script now).

Second easy step, put the queries from the slow query log to the review table with pt-query-digest :
(Note that my review table is in the mysql database and my slow query log is /var/log/slowq.log)

pt-query-digest /var/log/slowq.log --review <DSN OPTIONS>
DSN OPTIONS : u=<user>,p=<pass>,D=mysql,t=query_review,S=/tmp/mysql.sock

And obtain this result in database :
(fingerprint, sample and number of queries truncated for readability)

 
Now, generate an analyze file with pt-query-advisor from the review table :

pt-query-advisor --review <DSN OPTIONS> >/tmp/analyze_queries.log
DSN OPTIONS : u=<user>,p=<pass>,D=mysql,t=query_review,S=/tmp/mysql.sock

In this file, you can find a list of rules with associated queries (but you don’t need to edit the file, go to next step) :

CLA.007 0x4D97479E1774609A 0x6040F6055F330176 0x6BF38A80507513C8 0x7A4ACE71E642E539 0xCE21735A6006AB3D 0xEE13BCBC394FCD83

Finaly, to retrieve all the queries for a particular rule, use the command below :

cat /tmp/analyze_queries.log | grep <RULE ID>
| awk '{for ( i = 2 ; i <= NF ; i++ ) print substr($i,3)}'
| xargs -i mysql -u<user> -p<password> -E
-e"SELECT * FROM mysql.query_review WHERE CHECKSUM=CONV('{}',16,10)"

Replace the RULE ID, user and password with your informations.
The rules ID can be find in the pt-query-advisor documentation.

Hope that can help.

PlanetMySQL Voting: Vote UP / Vote DOWN

We still have a few seats left for our MySQL Cluster training in Washington DC January 24 - 26. If you're interested in learning about MySQL Cluster, the architecture, how to install it, administer it and troubleshoot it this is the course for you. The course will also cover replication and optimization and we will also discuss the exciting new features coming in version 7.2 of MySQL Cluster. For more information about the course contents visit http://www.skysql.com/services/training/courses/administering-mysql-cluster and to sign up to the course go to http://www.skysql.com/services/training/schedule/administering-mysql-cluster-1.

See you there!

 

PlanetMySQL Voting: Vote UP / Vote DOWN

[UPDATE 2011/01/11] : New poll added, vote for the best GUI client tool ! (And continue to vote for other polls)
And thanks again for your involvement. It’s time to vote again… 

First of all, I wish you a happy new year.
Many things happened last year, it was really exciting to be involved in the MySQL ecosystem.
I hope this enthusiasm will be increased this year, up to you !

To start the year, I propose the MySQL+ Community Awards 2011
It will only take 5 minutes to fill out these polls.
Answer with your heart first and then with your experience with some of these tools or services.

Polls will be closed January 31, so, vote now !
For “other” answers, please,  let me a comment with details.

Don’t hesitate to submit proposal for tools or services in the comments.
And, please, share these polls !

 

Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.

Happy 2012 !
Cédric

This article is obviously not sponsored !
(MySQL is a trademark of Oracle Corporation and/or its affiliates)

Sources :

• Oracle products, tools and services : http://www.mysql.com
• SkySQL products, tools and services : http://www.skysql.com
• Percona products, tools and services : http://www.percona.com
• FromDual products, tools and services : http://www.fromdual.com
• PalominoDB products, tools and services : http://palominodb.com/
• Pythian products, tools and services : http://www.pythian.com/
• Blue Gecko products, tools and services : http://www.bluegecko.net/
• Zabbix : http://www.zabbix.com/
• MySQL Cacti templates : http://code.google.com/p/mysql-cacti-templates/
• Nagios : http://www.nagios.org/
• r1soft CDP for MySQL : http://www.r1soft.com/linux-cdp/cdp-enterprise-edition/mysql/ 
• mylvmbackup tool : http://www.lenzg.net/mylvmbackup/
• mydumper tool : http://www.mydumper.org/
• ZRM tools : http://www.zmanda.com
• Galera replication : http://codership.com/products/galera_replication
• Tungsten replicator : http://code.google.com/p/tungsten-replicator/
• DRBD : http://www.drbd.org/
• ShoonerSQL : http://www.schoonerinfotech.com/products/schoonersql
• ScaleBase : http://www.scalebase.com/
• common-schema : http://code.google.com/p/common-schema/
• innotop : http://code.google.com/p/innotop/
• MySQL Tuner : https://launchpad.net/mysqltuner/2.0/mysqltuner-2.1-rev17
• mycheckpoint : http://code.openark.org/forge/mycheckpoint
• MariaDB : http://mariadb.org/
• TokuDB : http://www.tokutek.com/
• InfiniDB : http://infinidb.org/
• Drizzle : https://launchpad.net/drizzle
• Amazon SimpleDB : http://aws.amazon.com/fr/simpledb//180-2635771-0721067/
• mongoDB : http://www.mongodb.org/
• Cassandra : http://cassandra.apache.org/
• CouchDB : http://couchdb.apache.org/
• Tokyo Cabinet : http://fallabs.com/
• Riak : http://basho.com/
• Hadoop : http://hadoop.apache.org/
• Tarantool : http://tarantool.org/
• memcached : http://memcached.org/
• MySQL Sandbox : http://mysqlsandbox.net/
• openark kit : http://code.openark.org/forge/openark-kit
• Redis : http://redis.io/
• Ganglia : http://ganglia.sourceforge.net/
• HandlerSocket : https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL

PlanetMySQL Voting: Vote UP / Vote DOWN

Regardless of the monitoring tool you use to monitor your databases, it can be better to monitor this tool.
No, it’s not a joke ! Do you think you can have a benefit with a monitoring tool not connected to your servers ? ( without being alerted )

I choose to talk about MONyog here but this can apply to all existing monitoring tools.
I just want to share the message, the tool does not matter, so, do it !

So, let me explain how to control if you have fresh data with MONyog.
With MONyog it’s easy because it’s an agentless monitoring tool.

There are two ways to check that :

Per server general info :

 

 

 

 

 

 

For each server, you can find the last data updated date in the Monitors/Advisors tab.
The MySQL Availability in the dashboard is also usefull.

But it’s a per server information, so, that can be boring if you have a lot of databases servers.

General log file :

 

The default location for this file is MONyog_PATH/MONyog.log on your MONyog server.
You can find usefull informations there about MySQL, SFTP or SSH connexion failures for all your databases servers, in one place.

This log can also be used to detect client side errors if you don’t have access to the application servers.

Hope that can help, tell us what is the best method to do that with others monitoring tools like MySQL Enterprise Monitor.

Have a nice week-end.

PlanetMySQL Voting: Vote UP / Vote DOWN

I was glad to present how to schedule and monitor mysqldump with ZRM community last week in Paris as part of the MySQL Meetup Viadeo / LeMUG

You can find my slides below, enjoy ! :

PlanetMySQL Voting: Vote UP / Vote DOWN