Lingceng's Blog

Upgrade MySQL With Docker

The search problem

Recently a friend and I made a comic App. I crawled about 70,000 comics and saved them into MySQL. Search for comic name started to slow down. It takes about 2000ms.

SELECT `comics`.* FROM `comics` WHERE `comics`.`name` LIKE '%text%' LIMIT 21 OFFSET 0

I had added an index for name field. But an index won’t help text matching with a leading wildcard, an index can be used for:

LIKE 'text%'

Query with LIKE 'text%' is much faster. But comic name such as “sometext” won’t match. It does not fit my need.

I found out, that the same query was much faster on my MacBook, which only took about 200ms. Here was the difference:

EVN        | CPU                   | Memory | OS           | MySQL Version
---        | ---                   | ---    | ---          | ---
MacBook    | 2.7 GHz Intel Core i5 | 8G     | MacOS Sierra | 5.7.17
Production | 2.6 GHZ 1 Core        | 2G     | CentOS 6.5   | 5.1.73

After some tests, I found out MySQL Version was the key point. Why 5.7 is so much faster than 5.1? I don’t know.

Try to upgrade MySQL

So I needed to upgrade MySQL from 5.1 to 5.7. I had 3 choices:

  • Using mysql_upgrade
  • Installing MySQL5.7 manually on the same machine
  • Installing MySQL5.7 in docker

Installing MySQL.7 in docker seemed much safer and simpler for me. And docker is an useful tool to help deploying. I tried to install the latest stable docker but failed, as the latest Docker CE is only supported on CentOS 7.3 64-bit. I installed the docker 1.7 which is supported on CentOS 6.5.

Run MySQL in docker and publish to host port 6603:

sudo docker run --detach --name=comic-mysql --env="MYSQL_ROOT_PASSWORD=mypassword" --publish 6603:3306 mysql

Connect MySQL in docker with host MySQL client:

mysql -uroot -p -h 127.0.0.1 -P 6603

Create database:

CREATE DATABASE comic_production CHARACTER SET utf8 COLLATE utf8_general_ci;

Import sql.gz file:

zcat ~/backup/comic_production.20170606112459.sql.gz  | sudo docker exec -i comic-mysql mysql -uroot -pmypassword comic_production

Configure my rails database.yml:

default: &default
  adapter: mysql2
  pool: 5
  username: root
  password: mypassword
  host: 127.0.0.1
  port: 6603

It worked! I restarted the rails server, the database changed. The users got much faster response for searching.

References

Comments