mysql and disk space anomoloy

Every hour, we saw the diskspace on our root partition go up from 45 %  to 90 % and come back to 45 %. Initially thought mysql was processing some temp tables and in the next hour we kept watching the temp location. In terms of files / diskspace usage using “du / ls” nothing [...]

moving from mysql 5.0 to 5.1

Problem Statement : 386 GB of MySQL 5.0 Compressed MyISAM tables (Merge engine used)  need to be migrated to MySQL 5.1 Compressed/Partitioned tables. Also we had to add a few new columns and change the datatype of few existing columns.
Initial plan : Setup the MySQL 5.1 database with the schema with partitioning to which the [...]

Data inconsistency issues – binlog to rescue

One of our peer group developers contacted me today stating that they were inserting a value of 10 in one of the columns in their tables. But after the insert, when they query the table they were getting the value as 0.
Logged into the box to check what was happening. Luckily binlog was turned on [...]

Relay binlog corrupt

The slave failed with the error that the relay binlog is corrupt. It had copied close to 12 binlogs from the master and they were yet to be applied. Unfortunately those binlogs have been purged on the master. Now to sync up cleanly we might have to refresh data from the master which can be [...]

Adding dynamic swap file

All production servers are normally installed using kickstart files. Unfortunately, the ks file had a bug that it didnt add a swap partition if there was only one logical or physical disk.
Once the database was setup without swap, the configuration was sized such that all processes fit into memory. But we started seeing OOM killing [...]

Tweaks for loading data into MySQL

Whenever we load data into a MySQL database the following settings can help speed up the import.

innodb_flush_log_at_trx_commit = 0

innodb_support_xa = 0

skip-innodb_doublewrite

If the host doesn’t have slaves, and if log-bin is turned on, turning it off using set SQL_LOG_BIN=0 or commenting out log-bin in my.cnf will also help. Similarly disabling other logs like update [...]

Skip duplicate entries in a slave

The following one liner helps to sync a slave that is facing duplicate entry errors.

while [ 1 ]; do if [ `mysql -u root -e "show slave status \G;" | grep "Duplicate entry" | wc -l` -eq 1 ] ; then mysql -u root -e “stop slave; set global sql_slave_skip_counter=1; start slave;”; fi; sleep [...]

building mysql proxy ! not my cup of tea

On a RHEL 4u4 host run the following wget commands to get the necessary source code.
wget http://gd.tuwien.ac.at/graphics/gimp/gtk/v2.6/glib-2.6.4.tar.bz2
wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.6.1.tar.gz/from/http://mysql.easynet.be/
wget http://luaforge.net/frs/download.php/3132/lua5_1_3_Sources.tar.gz
wget http://monkey.org/~provos/libevent-1.3d.tar.gz
Untar all the files and run configure , make and make install.
To tackle few of the hiccups that you might encounter, you can install glib on to a location that is different from your root.
For Glib [...]

mysql timezone patching

Generate the timezone tables using the following command :
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
This needs to be done only after the OS level timezone patch is applied. Incase you dont have the timezone info, it can be downloaded @ http://dev.mysql.com/downloads/timezones.html
To check if your timezone patching is good, run the following sql and both the [...]

reading off index is slow

Today the development team posted an interesting problem in staging. The table in question had 2 million records. There was an index on the column referred in the where clause. It was a range lookup. Now here is the problem ,
Query using index took 15 secs.
Query without the index took 3 secs.
Now given the background [...]