分类： MySQL

gh-ost does not support foreign key

gh-ost was released in August, it might be the best tool to upgrade MySQL table structure online so far. But haven’t gotten a chance to try.

This week, I was preparing release of one of our big sites, which has three big tables need to be upgraded (structure change), they have more than 70 million rows in total, data and index occupy more than 30G space on disk. I tried the normal structure change in MySQL, it took me around 5 hours to finish all changes on all of the three tables. That means we have to close the production site for 5 hours, sounds crazy, but what we did always.

Then, I thought maybe I can try this new tool, gh-ost, which created and tested by GitHub. But, finally, I found it does not support foreign key!

user1@db1:~/gh-ost$ ./gh-ost --max-load=Threads_running=25 --critical-load=Threads_running=1000 --chunk-size=1000 --max-lag-millis=1500 --user="user" --password="******" --host="127.0.0.1" --allow-on-master --database="database1" --table="TRANSACTION2" --verbose --alter="ALTER TABLE TRANSACTION2 MODIFY COLUMN TEMP_GH_OST_TEST_FIELD_1 INT NULL" --switch-to-rbr --cut-over=default --exact-rowcount --concurrent-rowcount --default-retries=60 --nice-ratio=0.5 --serve-socket-file=/home/user1/gh-ost/game_tx2/sock.gh-ost.database1.TRANSACTION2 --throttle-flag-file=/home/user1/gh-ost/game_tx2/flag.gh-ost.database1.TRANSACTION2.throttle --panic-flag-file=/home/user1/gh-ost/game_tx2/flag.gh-ost.database1.TRANSACTION2.panic.flag --postpone-cut-over-flag-file=/home/user1/gh-ost/game_tx2/flag.gh-ost.database1.TRANSACTION2.postpone.flag
2016-10-06 03:54:25 INFO starting gh-ost 1.0.20
2016-10-06 03:54:25 INFO Migrating `database1`.`TRANSACTION2`
2016-10-06 03:54:25 INFO connection validated on 127.0.0.1:3306
2016-10-06 03:54:25 INFO User has ALL privileges
2016-10-06 03:54:25 INFO binary logs validated on 127.0.0.1:3306
2016-10-06 03:54:25 INFO Restarting replication on 127.0.0.1:3306 to make sure binlog settings apply to replication thread
2016-10-06 03:54:26 INFO Table found. Engine=InnoDB
2016-10-06 03:54:54 INFO Found foreign key on `database1`.`ALARM_LOG` related to `database1`.`TRANSACTION2`
2016-10-06 03:54:54 INFO Found foreign key on `database1`.`TRANSACTION2` related to `database1`.`TRANSACTION2`
2016-10-06 03:54:54 INFO Found foreign key on `database1`.`TRANSACTION2` related to `database1`.`TRANSACTION2`
2016-10-06 03:54:54 INFO Found foreign key on `database1`.`TRANSACTION2` related to `database1`.`TRANSACTION2`
2016-10-06 03:54:54 INFO Found foreign key on `database1`.`UNKNOWN_WIN` related to `database1`.`TRANSACTION2`
2016-10-06 03:54:54 ERROR Found 5 foreign keys related to `database1`.`TRANSACTION2`. Foreign keys are not supported. Bailing out
2016-10-06 03:54:54 FATAL 2016-10-06 03:54:54 ERROR Found 5 foreign keys related to `database1`.`TRANSACTION2`. Foreign keys are not supported. Bailing out

How to get rows, size of data and index from information_schema.TABLES

SELECT TABLE_NAME AS "Tables",
TABLE_ROWS AS "Rows",
round(((data_length) / 1024 / 1024), 2) AS "Data (MB)",
round(((index_length) / 1024 / 1024), 2) AS "Index (MB)"
FROM information_schema.TABLES
WHERE table_schema = "database_name"
ORDER BY (data_length + index_length) DESC;

Output:
information_schema.TABLES

Performance issue when updating/inserting massive data into InnoDB

innodb_flush_log_at_trx_commit － 在MySQL 5.5中，该值默认为1，此时在每次事务提交时，MySQL都会将log buffer中的内容写到日志文件，并将日志文件刷新到磁盘上去。所以如果磁盘不是SSD，在进行批量更新、插入的时候性能会非常差。

今天在Stage上生成所有的报表数据，其中一张源表大概有4千万行，结果运行速度非常慢，生成一天的报表数据要花上好几个小时。生成报表的存储过程需要操作（插入、更新）十来张表，但在存储过程中，我们却没有使用事务，也就是说，每一次insert/update后，MySQL都会将改动写入磁盘，而stage仅仅是一般的SATA硬盘，自然性能就会非常差。

解决的办法是改进存储过程，在其中加入事务控制，大概每1000条报表记录提交一次。测试后速度大大提高，生成一天的数据仅需要1到2分钟。

MySQL 查询优化

1. 索引按顺序排列存储

2. 选择索引
　　搜索的索引列，不一定是所要选择的列。出现在ON，WHERE和GROUP BY后的列适合作索引。
　　使用惟一索引
　　　　对于惟一值的列，索引的效果最好。例如：存放年龄的列具有不同值，所以很容易区分各行；而用来记录性别的列，只有’M’和’F’，不论搜索哪个值，都会得出大约一半的行，所以对其进行索引没有多大用途。
　　使用短索引
　　　　如果对字符串类型的列进行索引，应该指定一个前辍长度。比如一个varchar(200)的列，最好指定索引为前10个或20个字符内。（短的索引节省存储空间，并可能使查询更快）
　　利用最左前辍
　　　　在创建一个包含n列的索引时，实际上是创建了MySQL可以使用的n个索引。
多列索引可起几个索引的作用，因为可利用索引中最左边的列来匹配行。这样的列的集称为最左前辍。

　　比如在一个表中的state, city和zip三个列上创建索引，那么索引中的行是按state/city/zip的次序存放的。因此，索引中的行也会自动按state/city的顺序和state的顺序来存放。所以，该索引可以用来搜索下列的列的组合：
　　　　state/city/zip
　　　　state/city
　　　　state

继续阅读“MySQL 查询优化”

MySQL 5.0 字符集(2)－－数据库连接中的字符集

1. 客户端以什么字符集来发送SQL命令？
character_set_client

2. 当服务器接收到SQL命令时，它会将其转换成什么字符集？
character_set_connection and collation_connection
服务器将客户端发送的SQL命令从character_set_client转换到character_set_connection
在进行字符串比较时，collation_connection将起作用。
但是在进行column中字符串比较时，collation_connection将不起作用，因为column有自己的collation。

3. 服务器将以什么字符集发送 给果/错误信息 给客户端？
character_set_results

继续阅读“MySQL 5.0 字符集(2)－－数据库连接中的字符集”

MySQL 5.0 字符集(1)－－服务器端的字符集

自4.1以后，MySQL增强了对字符集的支持。

名词解释：
—————————————————–
字符集(character set)是一套符号和编码。
而校对(collation)是在该字符集下，用于比较字符的一个规则。
—————————————————–

对于数据库，有四种级别的编码和整理设定：
1. server
2. database
3. table
4. column

继续阅读“MySQL 5.0 字符集(1)－－服务器端的字符集”

MySQL 存储引擎及比较

查看当前数据库中安装的存储引擎：

mysql> SHOW ENGINES;
+------------+---------+----------------------------------------------------------------+
| Engine     | Support | Comment                                                        |
+------------+---------+----------------------------------------------------------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     |
| BerkeleyDB | NO      | Supports transactions and page-level locking                   |
| BLACKHOLE  | NO      | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE    | NO      | Example storage engine                                         |
| ARCHIVE    | YES     | Archive storage engine                                         |
| CSV        | NO      | CSV storage engine                                             |
| ndbcluster | NO      | Clustered, fault-tolerant, memory-based tables                 |
| FEDERATED  | NO      | Federated MySQL storage engine                                 |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          |
| ISAM       | NO      | Obsolete storage engine                                        |
+------------+---------+----------------------------------------------------------------+
12 rows in set (0.00 sec)

1. MyISAM存储引擎
MyISAM是默认存储引擎。它基于更老的ISAM代码，但有很多有用的扩展。

继续阅读“MySQL 存储引擎及比较”