定时统计数据,一次要读出几十万的数据,请问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
解决方案 »
- 怎样在查询某一个客户的时候,可以在两个界面中显示出客户的不同信息
- 将excel表的数据导入数据库格式错误为什么?
- 如果在WIN XP中定时执行PostgreSql任务
- mysql保存不了二进制数据
- MySQL 5.0不支持中文全文索引吗?我在5.0.77里面是测试,可以搜到结果,不知道很多人所指的mysql不支持中文全文索引是指哪回事?
- 请教 sql问题!
- XAMPP sql启动失败,求大侠协助
- mysql 截取问题
- mysql备份-mysqldump命令的选择项--where +条件的问题
- MySql如何定义变量?
- 请教一个sql问题,要查一分钟啊,压力太大了,求大神指点!
- mysql中如何查询最近24小时、昨天、最近7天、上周、最近30天、上月的数据
用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 ... 分析一下。