定时统计数据,一次要读出几十万的数据,请问my.cnf该如何配置?服务器500G硬盘,8G内存,1颗4核CPU下面是我的配置 11 local-infile=0
12 #set-variable = local-infile=0 (exactly the same as the prior line but the old syntax way of doing it)
13 #skip-locking (default is skip-external-locking as the name is no longer skip-locking and skip-external-locking is the default)
14 max_connections = 120
15 key_buffer_size = 1024M
16 long_query_time = 1
17 log-slow-queries = /home/log/mysql_slow_log.txt
18 open_files_limit= 500000
19 max_allowed_packet = 4M
20 #table_cache = 512
21 table_cache = 20M
22 #sort_buffer_size = 2M
23 #read_buffer_size = 2M
24 sort_buffer_size = 50M
25 read_buffer_size = 50M
26 read_rnd_buffer_size = 50M
27 myisam_sort_buffer_size = 256M
28 thread_cache_size = 64
29 #query_cache_size = 32M
30 query_cache_size = 128M
31 query_cache_limit = 128M
32 #thread_concurrency = 8 (this doesn't exist on Linux, it's only Solaris, do "mysqladmin var | grep concurrency to see that this variable doesn't exist on Linux, you 'll only see innodb_thread_concurrency return which is a different variable)
33 join_buffer_size = 10M
34 max_heap_table_size = 64M
35 tmp_table_size = 64M
12 #set-variable = local-infile=0 (exactly the same as the prior line but the old syntax way of doing it)
13 #skip-locking (default is skip-external-locking as the name is no longer skip-locking and skip-external-locking is the default)
14 max_connections = 120
15 key_buffer_size = 1024M
16 long_query_time = 1
17 log-slow-queries = /home/log/mysql_slow_log.txt
18 open_files_limit= 500000
19 max_allowed_packet = 4M
20 #table_cache = 512
21 table_cache = 20M
22 #sort_buffer_size = 2M
23 #read_buffer_size = 2M
24 sort_buffer_size = 50M
25 read_buffer_size = 50M
26 read_rnd_buffer_size = 50M
27 myisam_sort_buffer_size = 256M
28 thread_cache_size = 64
29 #query_cache_size = 32M
30 query_cache_size = 128M
31 query_cache_limit = 128M
32 #thread_concurrency = 8 (this doesn't exist on Linux, it's only Solaris, do "mysqladmin var | grep concurrency to see that this variable doesn't exist on Linux, you 'll only see innodb_thread_concurrency return which is a different variable)
33 join_buffer_size = 10M
34 max_heap_table_size = 64M
35 tmp_table_size = 64M
用infobright引擎更好
我看了一下,大部分很慢的查询在sending data状态,都是类似这样的查询语句:
select open from zhishu where heyue = 'sr1305' and time >= unix_timestamp('2012-07-01 09:00:00') and time < unix_timestamp('2012-07-31 15:00:00');heyue和time都是联合索引,slow_query是这么写的:
# Query_time: 2 Lock_time: 0 Rows_sent: 156656 Rows_examined: 156656
select open from zhishu where heyue = 'sr1305' and time >= unix_timestamp('2012-07-01 09:00:00') and time < unix_timestamp('2012-07-31 15:00:00');请问该如何优化呢?
现在的情况是MYSQL的内存占用非常低,但是CPU占用率很高
用变量
OR
字符串累加生成SQL语句,再执行试试
听一楼的 把数据放到infobright里面了在弄,只是查询的话 效率高很多。
cpu过高 大量的慢查询是主要的罪魁祸首、
贴出你的 explain select ... 分析一下。