mysql - 导入大数据之前的注意( max_allowed_packet, net_read_timeout, innodb_buffer_pool_size )

参考:http://blog.itpub.net/26736162/viewspace-2687871/

注意: 最好 写在配置文件中. 否则每次启动mysql之后, 之前手动输入的配置项目都会失效

在导入大数据的时候, 今天发现mysql容易出问题.

mysql> insert into temp_orders select * from orders where market_id in ('cabcg');
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

对于这个问题, 需要修改 innodb_buffer_pool_size

show variables like "%_buffer%";
set global innodb_buffer_pool_size=6442450944; (调成6G吧) 注意: 不要超过本机内存的总大小

mysql> insert into temp_orders select * from orders where market_id in ('cabcg'); 

 ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> set global net_read_timeout=30000;
mysql> set global max_allowed_packet=1677721600;