fredag 20 februari 2015

Getting back the CREATE TABLE speed of MySQL 5.5 in MySQL 5.6!

I visited a customer some weeks back and saw some regression problem during an upgrade to MySQL 5.6. Problem was during initial setup of database, the CREATE TABLE statements was running much slower on MySQL 5.6 compared to MySQL 5.5.

I created a simple test case where I create one SQL file containing 1000 CREATE TABLE using the following statement syntax: CREATE TABLE TNNNN (i int, name VARCHAR(12))ENGINE=InnoDB;

Tested MySQL Versions:
  • MySQL 5.5.42
  • MySQL 5.6.22

OS: Ubuntu 14.04
HW: My Toshiba Portege laptop with 2 cores and SSD disk

MySQL 5.5.42 (Default settings)
Lets first get our baseline by running 10 runs: mysql test < /tmp/1000tables
Result: average execution time is 7.5 seconds

MySQL 5.6.22 (Default settings)
Lets first get our baseline by running 10 runs: mysql test < /tmp/1000tables
Result: average execution time is 23 seconds, more than 300% slower than MySQL 5.5

During this test we where mostly spending time working with disk-system, but why is MySQL 5.6 so much slower than MySQL 5.5 in creating tables?
One thing that changed in-between MySQL 5.5 and MySQL 5.6 that might impact performance of CREATE TABLE was InnoDB files per table, read more here.

Let's try going back to use one tablespace file for all tables and re-run test!

MySQL 5.6.22 (innodb_file_per_table=0)
Result: average execution time went down a bit, now at 16 seconds

This is stil far behind MySQL 5.5, something else is taking up resources during CREATE TABLE.
Another thing that was added into MySQL 5.6 was persistent optimizer statistics, read more here.

MySQL 5.6.22 (innodb_stats_persistent=0)
Result: average execution time went down a bit, now at 15.5 seconds

Lets try both options together!

MySQL 5.6.22 (innodb_file_per_table=0 and innodb_stats_persistent=0)
Result: average execution time is back at 7.5 seconds!

Conclusion
For most application I would not consider this as an huge problem, this is something done once and then you start working on the database. But for some applications where they CREATE/DROP tables as a part of normal work this might be important.
If you need to keep performance from MySQL 5.5 in your  CREATE TABLE statements and new features like InnoDB files per table and persistent optimizer statistics are not important disable these features and you have the performance from MySQL 5.5 back again!

torsdag 19 februari 2015

Sweden MySQL User Group meeting in Stockholm Thuesday 28th of April!

Hej alla SMUG:are!

Nu är det återigen dags för en ny träff, och detta är ju lagom i tiden för att prata lite om kommande 5.7-releasen.
Vi kommer att bjudas på två presentationer denna gången, en som hålls av Mattias Jonsson, utvecklare i InnoDB teamet, om partionering i 5.7. Här finns det många nyheter, ni kan kolla lite på server teamets blog, http://mysqlserverteam.com/category/partitioning/
Den andra presentationen hålls av Martin Hansson, utvecklare i MySQL server teamet. Ämnet är den nya kostnadsmodellen för planering av frågor i MySQL 5.7, detta är också en stor ändring som kommer i 5.7. Läs lite mer här: http://mysqlserverteam.com/optimizer-cost-model-improvements-in-mysql-5-7-5-dmr/

Denna gången är det King som bjuder på lokaler och lite käk, så om ni går runt med candy crush på telefonen så känns det säkert som hemma!, så stort tack till King!

Registrera er för eventet via facebook eller LinkedIn

Vi syns den 28:e!

torsdag 12 februari 2015

Creating a minimal MySQL installation for embedded system!

Over the last few MySQL releases the size of the MySQL package have increased in size and it looks like the trend is continuing.
  • 687M MySQL 5.5.42
  • 1,1G  MySQL 5.6.21
  • 1,3G  MySQL 5.7.4
  • 1,5G         MySQL 5.7.5
This guide is for a Linux installation. I have tested the instructions below to create a minimal installation on my Ubuntu 14.04 laptop. I know you can make it a bit smaller by running mysqld directly and only using one error message file but this would not affect total size much. I also added the mysql client to have some way of logging into MySQL.

MySQL configuration file used to start MySQL:
[mysqld_safe]
ledir = /home/ted/labb/mini-mysql/bin

[mysqld]
port = 63301
socket = /tmp/mysql63301.sock
basedir = /home/ted/labb/mini-mysql
datadir = /home/ted/labb/mini-mysql/data
tmpdir = /tmp
pid-file = /tmp/mysql66301.pid

Create MySQL basedir where you want to store all binaries:
mkdir /home/ted/labb/mini-mysql

Go to basedir:
cd /home/ted/labb/mini-mysql

Create folders for binaries and database (normally your datadir would not be located in your basedir):
mkdir bin
mkdir data

Download MySQL "Linux - Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive" from http://dev.mysql.com/downloads/mysql/
Copy files needed to start MySQL daemon and client program:
cp ~5.6.22/bin/mysql bin/
cp ~5.6.22/bin/mysqld bin/
cp ~5.6.22/bin/mysqld_safe bin/
cp -fr ~5.6.22/share .

We need some binaries and files to create mysql internal database, these can be removed later on:
cp ~5.6.22/scripts/mysql_install_db bin/
cp ~5.6.22/bin/resolveip bin/
cp ~5.6.22/support-files/my-default.cnf .

Create MySQL internal database:
./bin/mysql_install_db --datadir=./data/

# Now we can remove tools for creating internal mysql schema:
rm bin/mysql_install_db
rm bin/resolveip
rm my-default.cnf
rm rm my-new.cnf

Start server:
./bin/mysqld_safe --defaults-file=./my.cnf &

Log into server:
./bin/mysql -uroot -P63301 -h127.0.0.1

Total size of installation:
mysql$ du -sh *
88M     bin
3,2M    share

This is good news, with some minimal work it's still possible to get the binary package down to below 100M.

EDIT: Great news, with MySQL 8.0.16 we now have minimal packages via our normal downloads, see more here: https://mysqlrelease.com/2019/05/the-amazing-shrinking-tarball/