Optimization Series | Discuz Forum MySQL General optimization

Previously wrote two articles on Discuz optimization in 2006 and 2009: MySQL Optimization discuz forum optimization, MySQL optimization discuz Forum optimization-continued, did not expect all 6 years later, Discuz still in the strong use of MyISAM engine, Comparable to the sanctions committee ...
Today help friends to optimize the millions of PV, millions of UV forum, background db using R710 (16G Ram,perc 6/i 256MB bbu,4 block 15K RPM SAS disk to do RAID 1+0,ext3 file system, E5620 * 2), this configuration seems good, However, the pressure is still large, and a large number of requests are in: sending data and statistics state.
After analysis, it is confirmed that the bottleneck is mainly:

1. IO read, io write down OK, not high, because the data table is MyISAM, need to produce a higher physical reading, can not be effectively buffered by memory; 2. The use of MySQL is the official 5.1 version, InnoDB queue request queuing more serious (some of the table has been turned into InnoDB); 3. Table MyISAM Table-level locks are more serious than those that are not converted to InnoDB;

In conclusion, the following improvement work is recommended:

1. Refer to Previous post: [MySQL FAQ] Series--Novice must see: One step of the InnoDB, the MySQL database default engine is modified to innodb;2. In addition to converting all data table engines to InnoDB (except Forum_postposition and common_session two tables, which are explained later); 3. In principle, all tables should create a self-Increment ID column as the primary key, which is completely unrelated to the business and avoids frequent updates causing reordering.

The following is the Forum_postposition and common_session table of the transformation plan.

1. First, the Forum_postposition table.
This table is used to store the sort of forum posts (the order in which posts are ranked) and stores the contents similar to the following: (1 1), (1 2), (2 1), (2 2), (2 3).
The official claims that because of this special business reason, unchanged changes into the InnoDB table, in fact, you can try to use the following scenario:
(1 1 1), (2 1 2), (3 2 1), (4 2 2), (5 2 3).
The difference between this and the previous one is that there is a new list of self-increment ID keys, which have nothing to do with the business and are only used as self-increment primary keys.
The original table uses (TID, Position) two fields to do the self-increment primary key, in the high concurrency situation, the efficiency is naturally not high.

2. Say the Common_session table again.
This table, as the name implies, is used to store account login sessions, similar to Forum_post, which are high concurrent request tables.
The table does not define a self-Increment ID column primary key, only one CHAR (6) Type of SID is used to make a unique index. After turning into InnoDB, the table will be very inefficient in high concurrency.
Therefore, before the conversion, you should first confirm if a new self-increment ID column primary key will affect the forum normal logic.

To summarize:
For Discuz official and two developers, it is recommended that:

1. All data sheets are converted into InnoDB engines and optimized for innodb characteristics; 2. All data tables should be created with the self-Increment ID column as the primary key, if not, 3. Similar to the Common_session table, consider using NOSQL storage, of course, if you want to achieve a high availability of DB, or continue in MySQL; 4. The development of paging restrictions, prevent search engines to crawl N multi-page post list, this function will lead to a larger physical database reading.

For Discuz general users, it is recommended that:

1. Refer to my blog: [MySQL FAQ] Series--Novice must see: One step InnoDB, all the data table engine is modified to innodb;2. The memory for DB is slightly larger, at least 8gb;3. Using the XFS file system will be much better than the default ext3 or even EXT4: XFS design-reprint; 4. is not cron task, periodically delete the session table in the expiration record, keep the table enough "slimming"; 5. Have a problem can come to this site message exchange, or on Sina Weibo (@ Jin Rongyue) on the message to me.

Finally, perhaps a friend asked, how can you be so keen on optimizing discuz, is it a third party service in doing this? In fact, just because discuz internal many people and my university has a deeper source, in addition discuz in the domestic popularization range is also quite wide, feel the need to help you do some optimization, just that:)

--------------------------------------Split Line--------------------------------------

http://zhishuedu.comTraining is a professional quality training brand jointly launched by senior MySQL expert Ye Jinlong and Wu Bingxi, with MySQL dba combat optimization and Python devops Development course, which is the most conscientious and The most quality training courses.

This article is from the "Lao Ye teahouse" blog, please be sure to keep this source http://imysql.blog.51cto.com/1540006/1879744

Optimization Series | Discuz Forum MySQL General optimization