公司数据中心计划将海量数据做一次迁移,同时增加某时间字段(原来是datatime类型,现在增加一个date类型),单表数据量达到6亿多条记录,数据是基于时间做的partition由于比较忙,一直没有总结,所以很细节的地方都记不清楚了,此处只是简单总结下当时的情形,备忘

# mkdir -p /home/mysql/3306/{data,binlog,logs}

乱打乱撞

 

最初接到任务,没有明确的入手点,直接就是select * from db limit
10000,动态修改翻页数量,通过控制台看耗时情况,慢

[client]
port = 3306
socket=/tmp/my3306.sock

复制代码 代码如下:SELECT
IR_SID,IR_HKEY,IR_GROUPNAME,IR_SITENAME,IR_CHANNEL,IR_MID,IR_URLNAME,IR_STATUS_CONTENT,IR_CREATED_AT,date_format(IR_CREATED_AT,’%Y.%m.%d’),IR_LASTTIME,IR_VIA,IR_THUMBNAIL_PIC,IR_RTTCOUNT,IR_COMMTCOUNT,IR_UID,IR_SCREEN_NAME,IR_RETWEETED_UID,IR_RETWEETED_SCREEN_NAME,IR_RETWEETED_MID,IR_RETWEETED_URL,IR_STATUS_BODY
FROM TB_SINA_STATUS WHERE IR_SID>40000 AND IR_SID<50001 INTO
OUTFILE ‘/home/mysql/data/data_outfile.txt’; LOAD DATA INFILE
‘/home/mysql/data/data_outfile.txt’ INTO TABLE NEW_TB_SINA_STATUS;

[mysql]
#这个配置段设置启动MySQL服务的条件;在这种情况下,no-auto-rehash确保这个服务启动得比较快。
no-auto-rehash

是否可以基于partition读数据呢

[mysqld]
#########base############
user = mysql
port = 3306
bind_address = *
socket=/tmp/my3306.sock
character_set_server = utf8
log-error = /home/mysql/3306/logs/my3306_error.log
pid-file=/home/mysql/3306/logs/my3306.pid
datadir=/home/mysql/3306/data
basedir=/opt/mysql
default-storage-engine=InnoDB #MyISAM
#文件打开数
open_files_limit = 10240
#自动提交
autocommit = 1
sql_mode =
“STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER”

既然数据库是按partition做分区,是否可以按partition读数据呢,如果可以改用怎样的语法读呢?时间上只要按月读数据,mysql会自动的基于partition读,具体可以用命令:explain
partition即可看到具有基于哪个partition
读大数据会十分耗时,对于数据进行到什么状态,我们可能十分想了解,可以用命令:show
status查看,我印象中主要是sending data,writting to net之类的。
innodb引擎的性能较myIsam引擎到底如何?
导库实验中导出并导入一个月的数据,在myisam引擎下需要不到4h,但是在innodb引擎下,却需要32小时,改善索引之类的,也需要28h,性能有8倍之差。

#表名不区分大小写
lower_case_table_names=1
#跳过密码验证
skip-grant
#关闭域名解析
skip-name-resolve =on

在网上找到了高人关于innodb与myisam区别,说需要修改innodb_buffer_pool_size、innodb_flush_log_at_trx_commit

########binlog配置####################
log-bin = /home/mysql/3306/binlog/binlog
binlog_cache_size = 32M
binlog_format = ROW #MIXED
max_binlog_cache_size = 2048M
#binlog文件大小
max_binlog_size = 512M
#binlog保留90天
expire_logs_days = 90

可保证没有太大差别,尝试了没有明显改善,在本机倒是可以,为什么呢???这个折腾了我好长时间innodb_flush_log_at_trx_commit

####调优配置###############
#buffer内存的配置参数,索引缓冲区(1G以下内存设定128M;2G/256M;
4G/384M;8G/1024M;16G/2048M)
key_buffer_size = 1024M
#设置在网络传输中一次消息传输量的最大值。系统默认值
为4MB,最大值是1GB,必须设置1024的倍数
max_allowed_packet = 1024M
#打开一个表的时候,会临时把表里面的数据放到这部分内存中,一般设置成1024就够了
table_open_cache = 1024
# Sort_Buffer_Size
是一个connection级参数,在每个connection(session)第一次需要使用这个buffer的时候,一次性分配设置的内存。
#Sort_Buffer_Size
并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。例如:500个连接将会消耗
500*sort_buffer_size(8M)=4G内存
#Sort_Buffer_Size 超过2KB的时候,就会使用mmap() 而不是 malloc()
来进行内存分配,导致效率降低。 系统默认2M,使用默认值即可
sort_buffer_size = 8M
#读数据的时候的缓冲区
read_buffer_size = 8M
#随机读的缓冲区
read_rnd_buffer_size = 8M
join_buffer_size = 8M #以上4项 4g内存给4-8M
#myisam配置的缓冲区 一般4G内存给64M即可
myisam_sort_buffer_size = 128M
#缓存可重用的线程数和内存有关系,1G内存设置为8,2G内存设置为16,4G以上设置为64。
thread_cache_size = 128
#查询的缓存大小,存放查询结果 一般情况下4G内存设置64M足够了
query_cache_size= 128M
#设置thread_concurrency的值的正确与否,
对mysql的性能影响很大。最大并发线程数,cpu核数的2倍
thread_concurrency = 16
#连接时间
interactive_timeout = 1800
#断开时间跟连接时间一起开才生效 跟interactive_timeout 一起配置
wait_timeout = 1800

是否为Innodb比MyISAM慢1000倍而头大?看来也许你忘了修改这个参数了。默认值是
1,这意味着每次提交的更新事务都会刷新到磁盘中,而这相当耗费资源,尤其是没有电池备用缓存时。很多应用程序,尤其是从
MyISAM转变过来的那些,把它的值设置为 2
就可以了,也就是不把日志刷新到磁盘上,而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘中去,因此通常不会丢失每秒1-2次更新的消耗。如果设置
为 0 就快很多了,不过也相对不安全了 —
MySQL服务器崩溃时就会丢失一些事务。设置为 2
只会丢失刷新到操作系统缓存的那部分事务。

######与性能并无太大关系。为了避免一些错误我们一般都设置比较大########
#最大的连接数,根据业务请求量适当调整,设置500足够
max_connections = 1000
#是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。通常我们设置为100足够
max_user_connections = 1000
#设置每个主机的连接请求异常中断的最大次数,当超过该次数,MYSQL服务器将禁止host的连接请求,直到mysql服务器重启或通过flush
hosts命令清空此host的相关信息。默认100
max_connect_errors = 6000
#在MYSQL暂时停止响应新请求之前,短时间内的多少个请求可以被存在堆栈中。如果系统在短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的监听队列的大小。默认值80。
back_log = 600

innodb_buffer_pool_size

#####慢查询##########
long_query_time=3
#开启慢查询
slow_query_log=1
slow_query_log_file=/home/mysql/3306/logs/my3306-slow-query.log

Innodb在默认的 innodb_buffer_pool_size
设置下跟蜗牛似的。由于Innodb把数据和索引都缓存起来,无需留给操作系统太多的内存,因此如果只需要用Innodb的话则可以设置它高达
70-80% 的可用内存。

#记录没有索引的查询
log_queries_not_using_indexes = 1

最后千辛万苦的、跋山涉水的,找到了另外两个参数

#使用skip_external_locking MySQL选项以避免外部锁定。该选项默认开启
skip_external_locking=1

innodb_log_file_size

######innodb配置================
#独立表空间 1=on 0=off
innodb_file_per_table = on
innodb_buffer_pool_size = 2048M
#
这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的
key_buffer_size 设置下运行的可以,然而Innodb在默认的
innodb_buffer_pool_size
设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来,无需留给操作系统太多的内存,因此如果只需要用Innodb的话则可以设置它高达
70-80% 的可用内存。一些应用于 key_buffer 的规则有 —
如果你的数据量不大,并且不会暴增,那么无需把 innodb_buffer_pool_size
设置的太大了
#表空间文件 重要数据
innodb_data_file_path = ibdata1:1024M:autoextend
设置过大导致报错,默认12M观察
#服务器有几个CPU就设置为几,建议用默认设置,一般为8
innodb_thread_concurrency = 8
#
如果将此参数设置为1,将在每次提交事务后将日志写入磁盘。为提供性能,可以设置为0或2,但要承担在发生故障时丢失数据的风险。设置为0表示事务日志写入日志文件,而日志文件每秒刷新到磁盘一次。设置为2表示事务日志将在提交时写入日志,但日志文件每次刷新到磁盘一次。(使用默认值1)
innodb_flush_log_at_trx_commit = 1

在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高,但是要注意到可能会增加恢复时间。我经常设置为
64-512MB,跟据服务器大小而异。

 

innodb_log_buffer_size
默认的设置在中等强度写入负载以及较短事务的情况下,服务器性能还可以。如果存在更新操作峰值或者负载较大,就应该考虑加大它的值了。如果它的值设置太高了,可能会浪费内存
— 它每秒都会刷新一次,因此无需设置超过1秒所需的内存空间。通常 8-16MB
就足够了。越小的系统它的值越小。

#innodb_force_recovery=1  #修复数据库的时候用

最终搞定,myisam与innodb的导数据的性能基本一致,2500的数据约需要3.5h,单库读数据需要2h,这个只是一个示意值仅供参考,正式服务的上的测试结果更加明显

性能调优语句参考复制代码 代码如下:set
profiling = 1;show profilesGSHOW profile CPU,BLOCK IO io FOR query
1;show statusShow Processlistexplain

并行读取是否会更快?

如果基于partition导数据,还是不能达到既定目标,我最终是通过编写shell脚步,多进程并行基于partition导数据,即启动多个mysql
-uroot -p db < exp201201.sql 、mysql -uroot -p db <
exp201202.sql,每个sql下按天做读写

复制代码 代码如下:SELECT
IR_SID,IR_HKEY,IR_GROUPNAME,IR_SITENAME,IR_CHANNEL,IR_MID,IR_URLNAME,IR_STATUS_CONTENT,IR_CREATED_AT,date_format(IR_CREATED_AT,’%Y.%m.%d’),IR_LASTTIME,IR_VIA,IR_THUMBNAIL_PIC,IR_RTTCOUNT,IR_COMMTCOUNT,IR_UID,IR_SCREEN_NAME,IR_RETWEETED_UID,IR_RETWEETED_SCREEN_NAME,IR_RETWEETED_MID,IR_RETWEETED_URL,IR_STATUS_BODY
INTO OUTFILE ‘/home/mysql/data/sinawb20120724/111101.txt’FIELDS
TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ ESCAPED BY ‘\’ LINES
TERMINATED BY ‘n’FROM TB_SINA_STATUS WHERE ir_created_at
>=’2011-11-01 00:00:00’ and ir_created_at

相关文章