mysql 性能调优，写入，将数据库放入内存中

Question 1

您说“感觉有些写入操作耗时长得令人无法接受”。是什么让您有这种感觉？您是否尝试过措施它有什么？

如果你使用的是 OS X，跟踪是衡量这类事情的绝佳工具。有些人已经做了一些这为你工作。至少，使用vmstat、iostat或iotop来确定您是否看到磁盘计时明显减慢。

您的query_cache_size缓存可能太大了。写入操作必须使查询缓存中该表的所有条目失效。查询缓存越大，花费的时间越长这可能是导致写入速度变慢的原因。手册建议“几十兆字节”。您应该逐步将其调低，并在每次更改后测量性能，以确定您的大小应该为多大。

该Table locks acquired immediately: 100% (3K immediate / 3K locks)值表明 MyISAM 表锁定对您来说不是问题。对于写入与读取比率较高的 MyISAM 表来说，这可能会成为一个严重问题。

写入磁盘的查询包含在慢查询跟踪中，因此如果您看到 INSERT、UPDATE、REPLACE、DELETE、TRUNCATE 等查询很慢，则表明您遇到了问题。

慢查询日志可以是文件或表，也可以是两者。由于您似乎没有为其指定位置，因此应该使用默认值。来自手册中关于慢查询日志的部分和日志位置部分：

如果您未指定慢查询日志文件的名称，则默认名称为 host_name-slow.log。除非给出绝对路径名来指定其他目录，否则服务器将在数据目录中创建该文件。

Answer

您说“感觉有些写入操作耗时长得令人无法接受”。是什么让您有这种感觉？您是否尝试过措施它有什么？

如果你使用的是 OS X，跟踪是衡量这类事情的绝佳工具。有些人已经做了一些这为你工作。至少，使用vmstat、iostat或iotop来确定您是否看到磁盘计时明显减慢。

您的query_cache_size缓存可能太大了。写入操作必须使查询缓存中该表的所有条目失效。查询缓存越大，花费的时间越长这可能是导致写入速度变慢的原因。手册建议“几十兆字节”。您应该逐步将其调低，并在每次更改后测量性能，以确定您的大小应该为多大。

该Table locks acquired immediately: 100% (3K immediate / 3K locks)值表明 MyISAM 表锁定对您来说不是问题。对于写入与读取比率较高的 MyISAM 表来说，这可能会成为一个严重问题。

写入磁盘的查询包含在慢查询跟踪中，因此如果您看到 INSERT、UPDATE、REPLACE、DELETE、TRUNCATE 等查询很慢，则表明您遇到了问题。

慢查询日志可以是文件或表，也可以是两者。由于您似乎没有为其指定位置，因此应该使用默认值。来自手册中关于慢查询日志的部分和日志位置部分：

如果您未指定慢查询日志文件的名称，则默认名称为 host_name-slow.log。除非给出绝对路径名来指定其他目录，否则服务器将在数据目录中创建该文件。

Question 2

鉴于您提到的这些因素

MySQL 5.0.92
32位操作系统

这很快排除了使用 InnoDB 的可能性，因为 MySQL 5.0 没有可以使用多核的 InnoDB（具有可以使用多核的 InnoDB 的 MySQL 最旧版本是 5.1.38 InnoDB 插件）。

您无法将整个数据库放在 RAM 中，因为 MyISAM 仅缓存索引。但是，您可以将所有 MyISAM 索引加载到键缓冲区中。

你需要做的第一件事是计算正确大小密钥缓冲区大小。这里有一个查询来帮你计算：

SELECT CONCAT(ROUND(KBS/POWER(1024, 
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999), 
SUBSTR(' KMG',IF(PowerOf1024<0,0, 
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) 
recommended_key_buffer_size FROM 
(SELECT LEAST(POWER(2,31),KBS1) KBS 
FROM (SELECT SUM(index_length) KBS1 
FROM information_schema.tables 
WHERE engine='MyISAM' AND 
table_schema NOT IN ('information_schema','mysql')) AA ) A, 
(SELECT 2 PowerOf1024) B;

请注意，如果所有 MyISAM 索引的总和超过 2G，则建议上限将为 2G。另请注意：SQL 语句包含子句(SELECT 2 PowerOf1024) B。这将以 MB 为单位输出建议。使用(SELECT 1 PowerOf1024) B以 KB 为单位的输出。使用(SELECT 3 PowerOf1024) B以 GB 为单位的输出。

好的，您现在知道 MyISAM 键缓冲区的大小了。如何加载它？

运行这个：

SELECT DISTINCT CONCAT('SELECT ',ndxcollist,' FROM ',
db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (SELECT engine,table_schema db,table_name tb,index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
FROM (SELECT B.engine,A.table_schema,A.table_name,A.index_name,
A.column_name,A.seq_in_index
FROM information_schema.statistics A INNER JOIN
(SELECT engine,table_schema,table_name FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) B
USING (table_schema,table_name)
WHERE A.index_type <> 'FULLTEXT'
ORDER BY table_schema,table_name,index_name,seq_in_index) A
GROUP BY table_schema,table_name,index_name) AA
ORDER BY db,tb;

此查询将显示强制所有 MYI 索引页进入键缓冲区所需运行的每个查询。将此查询输出到脚本并运行该输出：

SQLSTMT="SELECT DISTINCT CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache FROM (SELECT engine,table_schema db,table_name tb,index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist FROM (SELECT B.engine,A.table_schema,A.table_name,A.index_name,A.column_name,A.seq_in_index FROM information_schema.statistics A INNER JOIN (SELECT engine,table_schema,table_name FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) B USING (table_schema,table_name)  WHERE A.index_type <> 'FULLTEXT' ORDER BY table_schema,table_name,index_name,seq_in_index) A GROUP BY table_schema,table_name,index_name) AA ORDER BY db,tb;"
mysql -u... -p.... -AN -e"${SQLSTMT}" > MyISAMIndexPreload.sql
mysql -u... -p.... -A < MyISAMIndexPreload.sql

试一试！！！

Answer