A better way to re-set the Auto Increment value within MySQL

I work for a real estate website and we are running out of property numbers for some of our listings. Many moons ago logic in the code stated that listing types. For example a Commercial listing or Residential listing ID start with a different digit. Residential listings start with a '1' and Commercial listings start with a '5' for example. 


Putting aside that logic for a second, we are in a situation whereby our Commercial listings are now in the range of 599000. Meaning we only have a few new listings before, according to the 'business' here, the world ends. (As the numbers will rollover to 600000 and the hard coded numeric logic in the code will blow up and cause a fire in the datacenters we run).


So the thoughts are to re-set the Auto Increment value appending a few zeros to keep the logic of the listing starting with a '5'. The change SQL is something like this (and we're InnoDB everywhere):

 ALTER TABLE Commercial AUTO_INCREMENT = 500000000;

Now, that works, but its really bad. Why? Well MySQL will actually re-create the entire table to just re-set the Auto Increment value. The table is around 10G and on our infrastructure with our MySQL and configured values takes around 5 minutes. When you demand 100% uptime, having something blocking for 5 minutes is unacceptable.  We use MySQL Master / Master Active Standby replication to perform online schema changes though this is really unnecessary.


Keep in mind we're InnoDB here and we have foreign keys set up and unique indexes on this table. 




Here is the better way to perform the change that is not impacting and very quick is the following SQL on the master server only. (Note PropertyNumber is the Auto Increment primary key column)









/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
begin;
insert into Commercial (PropertyNumber) values(500000000);
delete from Commercial where PropertyNumber=500000000;                                          
commit;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;



For those watching will have noticed that I affectively appended the mysqldump output to relax to sql mode and disable foreign key and unique checks.

ruby mysql2 gem and MySQL 5.5 client library failure

My team was provisioning a ruby on rails application on a new Operating System with different ruby and client libraries and we came across a strange bug.

Intermittently we were seeing the failures in the application with the following messages in the passenger log:

NoMethodError: undefined method `each' for nil:NilClass

and

(NoMethodError) "undefined method `each' for nil:NilClass"

/usr/local/rvm/gems/ruby-1.9.2-p0/ruby/1.9.1/gems/mysql2-0.2.4/lib/active_record/connection_adapters/mysql2_adapter.rb:628:in `select'
/usr/local/rvm/gems/ruby-1.9.2-p0/ruby/1.9.1/gems/activerecord-3.0.0/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all'
/usr/local/rvm/gems/ruby-1.9.2-p0/ruby/1.9.1/gems/activerecord-3.0.0/lib/active_record/connection_adapters/abstract/query_cache.rb:54:in `block in select_all'
/usr/local/rvm/gems/ruby-1.9.2-p0/ruby/1.9.1/gems/activerecord-3.0.0/lib/active_record/connection_adapters/abstract/query_cache.rb:68:in `cache_sql'
/usr/local/rvm/gems/ruby-1.9.2-p0/ruby/1.9.1/gems/activerecord-3.0.0/lib/active_record/connection_adapters/abstract/query_cache.rb:54:in `select_all'
/usr/local/rvm/gems/ruby-1.9.2-p0/ruby/1.9.1/gems/activerecord-3.0.0/lib/active_record/base.rb:467:in `find_by_sql'
/usr/local/rvm/gems/ruby-1.9.2-p0/ruby/1.9.1/gems/activerecord-3.0.0/lib/active_record/relation.rb:64:in `to_a'
/usr/local/rvm/gems/ruby-1.9.2-p0/ruby/1.9.1/gems/activerecord-3.0.0/lib/active_record/relation/finder_methods.rb:143:in `all'

We found similar people experiencing the same problem here though the issues page on github didnt state a resolution.

We discovered we were running the MySQL 5.5 client libraries - namely the libmysqlclient 18. mysql2.so was built against  libmysqlclient 18:


[root@host]# ldd ./vendor/bundle/ruby/1.8/gems/mysql2-0.2.13/ext/mysql2/mysql2.so
linux-vdso.so.1 =>  (0x00007fff51788000)
libruby.so.1.8 => /usr/lib64/libruby.so.1.8 (0x00007f47f68e4000)
libmysqlclient.so.18 => /usr/lib64/libmysqlclient.so.18 (0x00007f47f6389000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f47f6165000)
libm.so.6 => /lib64/libm.so.6 (0x00007f47f5ee1000)
librt.so.1 => /lib64/librt.so.1 (0x00007f47f5cd9000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f47f5ad4000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f47f589d000)
libc.so.6 => /lib64/libc.so.6 (0x00007f47f551e000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f47f5307000)
/lib64/ld-linux-x86-64.so.2 (0x0000003eccc00000)
libfreebl3.so => /usr/lib64/libfreebl3.so (0x00007f47f50aa000)



We are running Centos6 and use the Percona MySQL client libraries, so we downgraded from Percona-Server-shared-55 to Percona-Server-shared-51 to provide the earlier client library.

We gem bundled against an earlier version of MySQL client libraries (libmysqlclient 16) and we no longer experienced that intermit failure:


[root@host]# ldd vendor/bundle/ruby/1.8/gems/mysql2-0.2.13/lib/mysql2/mysql2.so
linux-vdso.so.1 =>  (0x00007fff9e7d5000)
libruby.so.1.8 => /usr/lib64/libruby.so.1.8 (0x00007ff304784000)
libmysqlclient_r.so.16 => /usr/lib64/mysql/libmysqlclient_r.so.16 (0x00007ff30436e000)
libz.so.1 => /lib64/libz.so.1 (0x00007ff304159000)
librt.so.1 => /lib64/librt.so.1 (0x00007ff303f51000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007ff303d33000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007ff303afc000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00007ff3038e3000)
libm.so.6 => /lib64/libm.so.6 (0x00007ff30365e000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007ff30345a000)
libc.so.6 => /lib64/libc.so.6 (0x00007ff3030db000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007ff302ec4000)
/lib64/ld-linux-x86-64.so.2 (0x0000003a52200000)
libfreebl3.so => /lib64/libfreebl3.so (0x00007ff302c67000)



I'm yet to discover the specific issue with what I think is asynchronous caching race conditions and libmysqlclient18 with the mysql2 gem, but wanted to share my experience with the wider community to avoid others from going down the same rabbit hole I went down today.

NetApp and cifs - where is the traffic going

Usually my posts are MySQL related though sometimes I will add something that I find that may not be related to MySQL....  like this. Sorry guys at http://planet.mysql.com :)

Here is a cool little NetApp feature to see where all the lovely windows CIFS traffic is going.

The command is 'cifs top' and it needs to have stats capture enabled on the NetApp for it to work. Here it is in action.


nas01b> options cifs.per_client_stats.enable true
nas01b> cifs top
ops/s reads(n, KB/s) writes(n, KB/s) suspect/s IP Name
44 | 6 206 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
44 | 6 192 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
44 | 6 182 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
39 | 5 133 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
nas01> cifs top
ops/s reads(n, KB/s) writes(n, KB/s) suspect/s IP Name
100 | 15 415 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
96 | 13 343 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
92 | 13 375 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
84 | 12 361 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
nas01> cifs top
ops/s reads(n, KB/s) writes(n, KB/s) suspect/s IP Name
140 | 21 592 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
138 | 21 571 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
137 | 20 533 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
115 | 17 499 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
nas01> cifs top
ops/s reads(n, KB/s) writes(n, KB/s) suspect/s IP Name
196 | 28 800 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
191 | 28 781 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
190 | 29 791 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
190 | 30 784 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
nas01> cifs top
ops/s reads(n, KB/s) writes(n, KB/s) suspect/s IP Name
186 | 27 774 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
184 | 28 762 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
184 | 28 764 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
181 | 27 748 | 0 0 | 0 | 127.0.0.1 IMG01\notreal
nas01> options cifs.per_client_stats.enable false







I hope this is useful in debugging where the lovely NetApp cifs traffic is going :)

Database metics - thoughts and measurements

If I were to ask you 'what are the (database) metrics?', what do you think I'm asking? Thats an interesting question and something that requires some thought so lets handle this by actually looking at the following two alternative questions. 'What am I trying to archive [with database metrics]?' and 'how will I know when I have achieved it [by having database metrics]'?

Define the problem

What are we really doing
What are we trying to achieve?

• Know how much capacity we have
• Know that our databases are fast

How do we know when we have achieved it?

• When we can forward plan upgrades when we are near a capacity limit
• To be alerted of when things are not fast we can we investigate

So, what is capacity

Capacity is knowing how much extra we can do without making things 'slow'. Its a nice statement but it doesnt really mean much as the words are not quantified.

What is fast?

If I were to say that in a database, query (a) took 1 second to run and another query (b) took 10 seconds to run, what does it mean? If a website takes 10 seconds to render a search page and 5 seconds to render a home page, is it fast? Its all relative and very subjective and we really need to define it.

So lets redefine what we are measuring as 'response time'. The time it takes from start to finish. But going back to the statement, is it 'fast', we'll we dont actually know. We need to come up with a definition of fast. Lets call this definition our 'goal'.


What are our goals?

A 'goal' is what we want to archive and being 'fast' or 'slow' is relative to the goal. A database makes up part of the stack - so should we be setting the goals for the application and not the database? Maybe, but lets assume no.


Examples of goals

• How fast should the home page be?
• How fast should a search page be?
• How fast should a details page be?

We need these goals as we cannot set metrics without knowing what is acceptable behaviour.  We need these goals as time and optimisation is finite. If you were to fix one thing at the expense of another, what would it be and what loss would you be willing to accept?

Goals and optimisation
Optimise your infrastructure or solution to meet your goals. For example adding an index in a database will make some things fast (eg reads against that specific column) though it will make some things slow (writes to that table as the new index will need to be updated). To extend that simple example,  if its important to have really quick write access and we dont care about read access then we shouldnt add an index.


So what to measure

Measure capacity

In databases, the universal scaling law can be used to measure capacity. The law states at specific concurrency throughputs, we can measure the overall throughput and state what our peak concurrency (eg capacity) is.


Measure how fast (eg measure the response time)

Set goals for what you want to achieve and drill down the response time from the database to ensure that the database component is able to meet the response time SLA. Different pages (eg home page, search results,details page) should have different goals therefore the response time for the database should set for each.

ESXi DELL servers and Intel-VT

Have you ever tried to power on a new ESXi guest host and come across an error message saying "The host is VT-capable, but VT is disabled. VT might be disabled if it has been disabled in the BIOS settings or the host has not been power-cycled since changing this setting". An example screen shot of this error:

To correct this issue, reboot the server and pull up the bios settings (usually F2)

You will need to pull up the CPU information and adjust the 'Virtualization Technology' state from Disabled to Enabled

slow queries are not logged if they're replicated slow queries

I was debugging a performance problem and was shocked that while overnight we had the same slow query run on a replica, it wasnt logged to the slow log that I set up the day prior.

It seems slow queries that are executed from the replication SQL thread are not logged. I did not know this. This is how I confirmed this is the case:

*****SLAVE SERVER*****

enable slow log and confirm its working

mysql> set @@global.slow_query_log=ON;

Query OK, 0 rows affected (0.00 sec)

mysql> set @@global.long_query_time=1;

Query OK, 0 rows affected (0.00 sec)

mysql> flush logs;

Query OK, 0 rows affected (0.12 sec)

mysql> select sleep(10);

+-----------+

| sleep(10) |

+-----------+

| 0 |

+-----------+

1 row in set (10.00 sec)

mysql> Ctrl-C -- exit!

Aborted

slave:~# cd /databases/logs

slave:/databases/logs# tail mysqld-slow.log

/usr/sbin/mysqld, Version: 5.1.45-89.lenny.52-log ((Percona SQL Server (GPL), XtraDB 10.2)). started with:

Tcp port: 3306 Unix socket: /databases/logs/mysqld.sock

Time Id Command Argument

# Time: 110428 15:25:07

# User@Host: root[root] @ localhost []

# Thread_id: 332059 Schema: Last_errno: 0 Killed: 0

# Query_time: 10.000293 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 Rows_affected: 0 Rows_read: 1

# Bytes_sent: 64 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

SET timestamp=1303968307;

select sleep(10);

The slave is a slave (I'm not just telling stories here!)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

Now on the master, lets confirm this is the case and generate a slow write query:

mysql> create database replicationslow;

Query OK, 1 row affected (0.00 sec)

mysql> use replicationslow;

Database changed

mysql> create table foo ( a int) engine innodb;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo select sleep(10);

Query OK, 1 row affected, 1 warning (10.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> show warnings;

+-------+------+-------------------------------------------------------+

| Level | Code | Message |

+-------+------+-------------------------------------------------------+

| Note | 1592 | Statement may not be safe to log in statement format. |

+-------+------+-------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select * from foo;

+------+

| a |

+------+

| 0 |

+------+

1 row in set (0.00 sec)

Now lets see if its replicated and logged on the slave:

**SLAVE SERVER***

slave:/databases/binlog# ls

master.000010 master.000011 master.index master.info relay.000382 relay.000383 relay.index relay.info

slave:/databases/binlog# mysqlbinlog master.000011 |grep -i "insert into foo"

insert into foo select sleep(10)

slave:/databases/binlog#

mysql> show databases like 'replicationslow';

+----------------------------+

| Database (replicationslow) |

+----------------------------+

| replicationslow |

+----------------------------+

1 row in set (0.00 sec)

mysql> use replicationslow;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from foo;

+------+

| a |

+------+

| 0 |

+------+

1 row in set (0.00 sec)

slave:/databases/logs# tail mysqld-slow.log

/usr/sbin/mysqld, Version: 5.1.45-89.lenny.52-log ((Percona SQL Server (GPL), XtraDB 10.2)). started with:

Tcp port: 3306 Unix socket: /databases/logs/mysqld.sock

Time Id Command Argument

# Time: 110428 15:25:07

# User@Host: root[root] @ localhost []

# Thread_id: 332059 Schema: Last_errno: 0 Killed: 0

# Query_time: 10.000293 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 Rows_affected: 0 Rows_read: 1

# Bytes_sent: 64 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

SET timestamp=1303968307;

select sleep(10);

Wow I did not know that. This is a shame as a slave may have different specs so it would be nice to know if a slow replicated query occurs....

Problems turning on the MySQL slow log under 5.1+

I started debugging a performance problem and noticed the following when trying to enable the MySQL slow log:

mysql> set @@global.slow_query_log=ON;

ERROR 13 (HY000): Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)

What is going on?

First off within MySQL5.1 MySQL has the ability to put slow logs into a CSV table to query against. It appears even though the slow query logging is set to file, MySQL server confirms that the slow_log table within the mysql schema is there and working.

As a background, this database was hotbackup restored from another server. As Pythian mention, CVS tables are not synced over by default.

To recover, lets put an empty CVS file structure on the filesystem and turn the slow query on:

box01:/databases/data/mysql# touch slow_log.CSV

box01:/databases/data/mysql# touch slow_log.CSM

box01:/databases/data/mysql# chown mysql:mysql slow_log*

box01:/databases/data/mysql#

mysql> set @@global.slow_query_log=ON;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;

+-------+------+--------------------------------------------------------------+

| Level | Code | Message |

+-------+------+--------------------------------------------------------------+

| Error | 1194 | Table 'slow_log' is marked as crashed and should be repaired |

+-------+------+--------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> repair table mysql.slow_log;

+----------------+--------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+----------------+--------+----------+----------+

| mysql.slow_log | repair | status | OK |

+----------------+--------+----------+----------+

1 row in set (0.00 sec)

mysql> set @@global.slow_query_log=OFF;

Query OK, 0 rows affected (0.00 sec)

mysql> set @@global.slow_query_log=ON;

Query OK, 0 rows affected (0.00 sec)

Now lets start debugging the performance problems....