MYSQL optimization problem is this is to limit what can optimize

To say that my configuration
Machine configuration
8 core I7 processor, 8G of memory

I'm MY.INI table type I use 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=512

tmp_table_size=18M
myisam_max_sort_file_size=64G

key_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


At present, the query index status
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>


I tried.
Key_buffer_size=4095M 4095M seems to be the limit of MYSQL.

According to the online posts
key_cache_miss_rate = Key_reads / Key_read_requests * 100%
I'm shooting
0.3680212169897083

There is no way to improve how to improve it

Started by Neil at November 20, 2016 - 4:29 AM

Improvement of the method is the SQL statement in your where condition and index

Posted by Burnell at November 29, 2016 - 4:45 AM

The configuration is optimized on the one hand, an index is optimized in another.

Posted by Gerald at December 13, 2016 - 5:22 AM

Improvement of the method is the SQL statement in your where condition and index for me
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'

No SQL import

Posted by Neil at December 15, 2016 - 6:10 AM

LOAD / The insert itself has no hit rate at all.

Posted by Mamie at December 30, 2016 - 6:45 AM

This optimization would buy more powerful hardware to increase speed, such as stronger CPU faster solid state disk SSD

Posted by Burnell at January 09, 2017 - 7:01 AM

At present this set of MY.INI is it right? Please enlighten

Posted by Neil at January 13, 2017 - 7:45 AM