先说下我的配置
机器配置
8核心I7处理器 8G内存我现在的MY.INI我用的表类型是 MyISAM
[client]
port=3306
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
port=3306
basedir="H:/phpStudy2013/MySQL/"
datadir="H:/phpStudy2013/MySQL/data/"
character-set-server=utf8
default-storage-engine=MyISAM
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=512tmp_table_size=18M
myisam_max_sort_file_size=64Gkey_buffer_size=4095M
max_allowed_packet = 16M
table_cache=1024
sort_buffer_size=32M
read_buffer_size=32M
read_rnd_buffer_size=32M
myisam_sort_buffer_size=256M
thread_cache_size=32
query_cache_size= 256M
thread_concurrency=8
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=47M
innodb_log_file_size=24M
innodb_thread_concurrency=8
目前查询索引状态
mysql> show full processlist;
+-----+------+-----------------+--------+---------+-------+-------+----------------------------------------------------
| Id | User | Host | db | Command | Time | State | Info
+-----+------+-----------------+--------+---------+-------+-------+----------------------------------------------------
| 57 | root | localhost:51165 | data11 | Query | 21354 | NULL | load data local infile 'h:/0000001/houdao/guest.txt'
ignore into table `guest`
character set utf8 fields
terminated by ','
enclosed by '"'
lines terminated by '\r\n'
(`id`,`MAX`,`SALE`,`IP`,`WORD`) |
| 220 | root | localhost:62724 | NULL | Query | 0 | NULL | show full processlist
| 221 | root | localhost:62888 | NULL | Sleep | 2434 | | NULL
| 222 | root | localhost:62889 | temp | Sleep | 2705 | | NULL
| 223 | root | localhost:62890 | data | Sleep | 2389 | | NULL
| 224 | root | localhost:49184 | 111 | Sleep | 965 | | NULL
+-----+------+-----------------+--------+---------+-------+-------+----------------------------------------------------
6 rows in set (0.00 sec)mysql> show global status like 'key_read%';
+-------------------+------------+
| Variable_name | Value |
+-------------------+------------+
| Key_read_requests | 3009661261 |
| Key_reads | 11076192 |
+-------------------+------------+
2 rows in set (0.00 sec)mysql>
我试过调大
key_buffer_size=4095M 4095M貌似是MYSQL的极限吧根据网上的帖子
key_cache_miss_rate = Key_reads / Key_read_requests * 100%
我现在命中率是
0.3680212169897083有没有办法改善 要如何改善呢
机器配置
8核心I7处理器 8G内存我现在的MY.INI我用的表类型是 MyISAM
[client]
port=3306
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
port=3306
basedir="H:/phpStudy2013/MySQL/"
datadir="H:/phpStudy2013/MySQL/data/"
character-set-server=utf8
default-storage-engine=MyISAM
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=512tmp_table_size=18M
myisam_max_sort_file_size=64Gkey_buffer_size=4095M
max_allowed_packet = 16M
table_cache=1024
sort_buffer_size=32M
read_buffer_size=32M
read_rnd_buffer_size=32M
myisam_sort_buffer_size=256M
thread_cache_size=32
query_cache_size= 256M
thread_concurrency=8
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=47M
innodb_log_file_size=24M
innodb_thread_concurrency=8
目前查询索引状态
mysql> show full processlist;
+-----+------+-----------------+--------+---------+-------+-------+----------------------------------------------------
| Id | User | Host | db | Command | Time | State | Info
+-----+------+-----------------+--------+---------+-------+-------+----------------------------------------------------
| 57 | root | localhost:51165 | data11 | Query | 21354 | NULL | load data local infile 'h:/0000001/houdao/guest.txt'
ignore into table `guest`
character set utf8 fields
terminated by ','
enclosed by '"'
lines terminated by '\r\n'
(`id`,`MAX`,`SALE`,`IP`,`WORD`) |
| 220 | root | localhost:62724 | NULL | Query | 0 | NULL | show full processlist
| 221 | root | localhost:62888 | NULL | Sleep | 2434 | | NULL
| 222 | root | localhost:62889 | temp | Sleep | 2705 | | NULL
| 223 | root | localhost:62890 | data | Sleep | 2389 | | NULL
| 224 | root | localhost:49184 | 111 | Sleep | 965 | | NULL
+-----+------+-----------------+--------+---------+-------+-------+----------------------------------------------------
6 rows in set (0.00 sec)mysql> show global status like 'key_read%';
+-------------------+------------+
| Variable_name | Value |
+-------------------+------------+
| Key_read_requests | 3009661261 |
| Key_reads | 11076192 |
+-------------------+------------+
2 rows in set (0.00 sec)mysql>
我试过调大
key_buffer_size=4095M 4095M貌似是MYSQL的极限吧根据网上的帖子
key_cache_miss_rate = Key_reads / Key_read_requests * 100%
我现在命中率是
0.3680212169897083有没有办法改善 要如何改善呢
load data local infile 'h:/0000001/houdao/guest.txt'
ignore into table `guest`
character set utf8 fields
terminated by ','
enclosed by '"'
lines terminated by '\r\n' 根本没有SQL导入