MySQL优化

1、先来看看多次删除插入操作后的表索引情况

mysql> SHOW INDEX FROM `tbl_name`;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tbl_name |          0 | PRIMARY    |            1 | StepID      | A         |           1 |     NULL | NULL   |      | BTREE      |         |

1. 先看当前的 QCACHE, hits 和 in_cache 均为 0

mysql>SHOW GLOBAL STATUS LIKE 'QCACHE%';

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_hits             | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 0        |
+-------------------------+----------+

mysql>SELECT * FROM `Weather` WHERE `AddTime` = 1185897600;

2. 第一次查询完之后，再来看下，not_cached 增加了 1， in_cache 变成 1，因为 SHOW GLOBAL STATUS LIKE 'QCACHE%' 本身不会放在 QCACHE 里的

mysql>SHOW GLOBAL STATUS LIKE 'QCACHE%';
+-------------------------+----------+

mysql>explain select * from tbl_name where date_format(EndTime, '%Y-%m-%d') >= '2007-08-08' AND
 date_format(EndTime, '%Y-%m-%d') set @dt='2007-08-08' ; set @dt1 = '2007-08-09';

mysql>explain select * from tbl_name where EndTime >= @dt AND EndTime 

错误现象如下：

mysql> DELETE FROM JHF_CP_SPOT_RATE WHERE FRONT_DATE = '20070801';
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

把 delete 改成 select，再explain一下，发现返回结果高达300多万，于是检查 max_write_lock_count 的值，是 8446744073709551615，肯定够用了。
再看看 innodb_buffer_pool_size，我的天，才设置默认的 8M，该死的。加大到合适的值，重启 mysql，一切ok。

还有一招，就是delete时分成多次，每次用 LIMIT 限定记录数，这样也ok :)

sysbench是一个模块化的、跨平台、多线程基准测试工具，主要用于评估测试各种不同系统参数下的数据库负载情况。关于这个项目的详细介绍请看：http://sysbench.sourceforge.net。
它主要包括以下几种方式的测试：
1、cpu性能
2、磁盘io性能
3、调度程序性能
4、内存分配及传输速度
5、POSIX线程性能
6、数据库性能(OLTP基准测试)
目前sysbench主要支持 MySQL,pgsql,oracle 这3种数据库。

一、安装
首先，在 http://sourceforge.net/projects/sysbench 下载源码包。
接下来，按照以下步骤安装：

tar zxf sysbench-0.4.8.tar.gz
cd sysbench-0.4.8
./configure && make && make install

2007-01-27，应bjchenxu之邀，和linuxmine一起，与网友们一起学习了MySQL优化，提纲见附件。

附件：MySQL优化讲义(2007-01-27)

一. 前言
近日由于需要,对discuz论坛(简称dz)进行优化,当然了,只是涉及到数据库的优化.
先说一下服务器及dz的数据量,2 * Intel(R) Xeon(TM) CPU 2.40GHz, 4GB mem, SCISC硬盘.
MySQL 版本为 4.0.23. 数据表情况:
cdb_attachments 2万
cdb_members 10万
cdb_posts 68万
cdb_threads 7万
二. 缓存优化
在 my.cnf 中添加/修改以下选项:

 #取消文件系统的外部锁
skip-locking
#不进行域名反解析,注意由此带来的权限/授权问题
skip-name-resolve
#索引缓存,根据内存大小而定,如果是独立的db服务器,可以设置高达80%的内存总量
key_buffer = 512M
#连接排队列表总数
back_log = 200

一. 启动参数优化
修改 my.cnf (或者my.ini)，加入/修改以下几行

#设定缓存的连接数,节省连接时的开销
back_log	= 64

#禁用文件系统外部锁
external-locking	= 0

#禁用BDB,如果你确实不需要的话,innodb也是如此
skip-bdb

#索引缓冲,如果是专用的数据库服务器,可以设置高达服务器内存的一半,如果不是专用的,
#还是设置得低一点
key_buffer	= 512M

#缓存数据表数量,如果内存较大,可以设置稍微高一点,否则还是设置低一点
#设置这个参数可以参见系统状态中的 open_tables(表示当前打开的数据表总数) 
#和 opened_tables(表示所有打开的数据表总数)

系统性能相关的MySQL变量

原文摘录自：http://forge.mysql.com/wiki/ServerVariables

Memory-Related Variables
内存相关变量

These server variables control the amount of memory allocated to the various buffers and caches within MySQL.
以下这些服务器变量控制这MySQL分配给各种缓冲或者换缓存的内存总数。

join_buffer_size
(PER SESSION) Controls the amount of memory allocated to perform joins on tables that have no keys which can be used to perform a condition filter. Allocated for each table joined without necessary filter conditions