Mysql Basic Optimization Tutorials for discuz forum programs _mysql

After so long, the question of Discuz forum still puzzles a lot of netizens, in fact, from the various forums to see the problem summed up, the key point is because there is no data table engine into the innodb caused, discuz in a slightly higher environment of the performance is very bad, resulting in a large number of lock waiting, I'm sure it would be much better if I changed the data-sheet engine to InnoDB. This time, write a literacy bar.

1. Enable the InnoDB engine and configure related parameters

#skip-innodb

Innodb_additional_mem_pool_size = 16M #一般16M也够了, you can adjust the
innodb_buffer_pool_size = 6G #如果是专用db的话, generally the total amount of memory 80%
Innodb_data_file_path = ibdata1:1024m:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 20
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
Innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct =
innodb_lock_wait_timeout =
innodb_ File_per_table

Modify table engine to InnoDB:

Mysql> ALTER TABLE cdb_access engine = InnoDB;

Other table similar to the above, the table name can be changed ...
After changing the table storage engine to InnoDB, not only can avoid a large number of lock waiting, but also can improve the efficiency of the query, because InnoDB will put both data and index in the buffer pool, more efficient.

2. Cache optimization
Add/Modify the following options in MY.CNF:

 #取消文件系统的外部锁
skip-locking
#不进行域名反解析, note the resulting permissions/authorization issues
skip-name-resolve
#索引缓存, depending on the size of the memory, if it is a stand-alone DB server , you can set up to 80% total memory
key_buffer = 512M
#连接排队列表总数
back_log =
max_allowed_packet = 2M
#打开表缓存总数, You can avoid the overhead of opening a datasheet frequently
Table_cache =
#每个线程排序所需的缓冲
sort_buffer_size = 4M
#每个线程读取索引所需的缓冲
Read_buffer_size = 4M
#MyISAM表发生变化时重新排序所需的缓冲
myisam_sort_buffer_size = 64M
#缓存可重用的线程数
thread_ cache = 128
#查询结果缓存
query_cache_size = 128M
#设置超时时间, can avoid long connection
set-variable = wait_timeout=60
# The maximum number of concurrent threads, the number of CPUs *2
thread_concurrency = 4
#记录慢查询, and then the slow query one by one optimizes
log-slow-queries = Slow.log
long_ Query_time = 1
#关闭不需要的表类型, don't add this skip-bdb if you need to

The above parameters are adjusted according to the configuration differences of the respective servers, only as a reference.

3. Index optimization
As mentioned above, the slow query has been turned on, and then the slow query is optimized one by one.

The query SQL for the search is roughly as follows:

 SELECT t.* from Cdb_posts p, cdb_threads t WHERE
T.fid in (' 37 ', ' 45 ', ' 4 ', ' 6 ', ' 17 ', ' 41 ', ' 28 ', ' 32 ', ' 31 ', ' 1 ', ' 4 ') 2 ') and
P.tid=t.tid and p.author like ' Joanswin '
GROUP by T.tid ORDER by Lastpost DESC LIMIT 0, 80;

The results of the analysis with EXPLAIN are as follows:

 Mysql>explain SELECT t.* from cdb_posts p, cdb_threads t WHERE
T.fid in (' 37 ', ' 45 ', ' 4 ', ' 6 ', ' 17 ', ' 41 ', ' 28 ', ' 3 2 ', ', ' 1 ', ' P.tid=t.tid ' and p.author like
' Joanswin '
GROUP by T.tid ORDER by Lastpost DESC LIMIT 0, 8 0; 

+-----------+------------+----------+--------------+-------------+-----------+-------------+
| id | select_ Type | Table | Type | Possible_keys | Key | Key_len | Ref   | rows | Extra
+-----------+------------+----------+--------------+-------------+-----------+-------------+
| 1 | Simple  | t  | range | Primary,fid | FID | 2  | NULL  | 66160 | The Using where; 
Using temporary; Using Filesort |
| 1 | Simple  | p  | ref | tid   | tid | 3  | Forum.t.tid | 10 | Using where
| +----+-------------+-------+-------+---------------+------+---------+-------------+-------+
---------

Only T.fid and P.tid are used, and p.author is not indexed, a total need to scan
66160*10 = 661,600 times index, that's enough exaggeration:(
Re-analyze indexing of cdb_threads and cdb_posts:

 Mysql>show index from Cdb_posts; 

+-----------+------------+----------+--------------+-------------+-----------+----------
---+----------+--- -----+------+--+
| Table  | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | 
Packed | Null | Index_type | Comment | +-----------+------------+----------+--------------+----
---------+-----------+-------------+----------+--- -----+------+--+
| cdb_posts   | 0 | PRIMARY |   1 | PID   | A   |  680114 |  NULL | NULL |
| Btree | | |
| cdb_posts   | 1 | FID  |   1 | FID   | A   |   Ten |  NULL | NULL |
| Btree | | |
| cdb_posts   | 1 | Tid  |   1 | Tid   | A   |  68011 |  NULL | NULL |
| Btree | | |
| cdb_posts   | 1 | Tid  |   2 | Dateline | A   |  680114 |  NULL | NULL |
| Btree | | |
| cdb_posts   | 1 | Dateline |   1 | Dateline | A   |  680114 |  NULL | NULL |
| Btree  |   | 
+-----------+------------+----------+--------------+-------------+-----------+---

And

 Mysql>show index from Cdb_threads; 

+-----------+------------+----------+--------------+-------------+-----------+-------------+
----------+-------- +------+-----+
| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality |
Sub_part | Packed | Null | Index_type | Comment | +-----------+------------+----------+--------------+-----
--------+-----------+-------------+----------+-------- +------+-----+
|   Cdb_threads | 0 |   PRIMARY | 1 | Tid |  A |  68480 | NULL | NULL | |   Btree | |
|   Cdb_threads | 1 |   Lastpost | 1 | Topped |   A |  4 | NULL | NULL | |   Btree | |
|   Cdb_threads | 1 |   Lastpost | 2 | Lastpost |  A |  68480 | NULL | NULL | |   Btree | |
|   Cdb_threads | 1 |   Lastpost | 3 | FID |  A |  68480 | NULL | NULL | |   Btree | |
|   Cdb_threads | 1 |   Replies | 1 | Replies |   A |  233 | NULL | NULL | |   Btree | |
|   Cdb_threads | 1 |   Dateline | 1 | Dateline |  A |  68480 | NULL | NULL | |   Btree | |
|   Cdb_threads | 1 |   FID | 1 | FID | A |   10 | NULL | NULL | |   Btree | |
|   Cdb_threads | 1 |   Enablehot | 1 | Enablehot |   A |  2 | NULL | NULL | |   Btree | |
 +-------------+------------+-----------+--------------+-------------+------

Seeing that the index FID and enablehot cardinality is too small, it seems that the index is completely unnecessary, but for a larger FID base, you may need to preserve the > index.
The changes are as follows:

 ALTER TABLE ' cdb_threads ' drop index ' enablehot ', DROP index ' FID ', ADD index (' fid ', ' lastpost ');
ALTER TABLE ' cdb_posts ' DROP index ' FID ', ADD index (' author ');
OPTIMIZE TABLE ' cdb_posts ';
OPTIMIZE TABLE ' cdb_threads ';

Here, the P.author field I set a part of the index length is 10, is the result of my analysis, different systems, the length here is different, it is best to first take the average, and then appropriate adjustments.
Now, to perform the slow query above again, the discovery time has changed from 6s to 0.19s and has increased 30 times times.