m2014-architecture-webserver-> million record-level MySQL database and discuz! Forum optimization

Shunz, Source: http://shunz.net/2008/06/mysql_discuz_.html
Recently, to help a friend to optimize a 200,000 theme, 1 million posts, 30,000 members, the average number of 2000 people online discuz! forum, using LINUX2.6+APACHE2+MOD_PHP5+MYSQL5, the server configured for dual Xeon +4G memory, optimized before, The average system load (load average) is basically maintained at more than 10, MySQL CPU occupancy rate is basically above 90%, after optimization, the average system load drops below 0.5, MySQL CPU occupancy rate is very little more than 10% time. YSlow scored only 35 points before optimization, YSlow score 86 points after optimization. The process and experience of optimization is now a record:
First, optimize Apache, edit httpd.conf, set hostnamelookups, KeepAlive, Maxkeepaliverequests, and keepalivetimeout four parameters, Adjust the maxspareservers, Serverlimit, maxclients, and Maxrequestsperchild parameters, and consider using the worker MPM to discard prefork. Set the Mod_deflate and Mod_expires modules, but note that discuz! cannot open the expires to PHP files, otherwise there will be problems. You can also consider opening the Mod_cache and Mod_mem_cache modules. In addition, Cronolog logs are truncated by day, and can be truncated by hours if the log is particularly large. In addition, the log is analyzed by Awstats, and the log is automatically deleted 1 months ago with gzip compression.
Next, optimize PHP, edit php.ini, adjust output_buffering, zlib.output_compression and Max_execution_time, Max_input_time, Memory_ Parameters such as limit, and install XCache and Zend Optimizer.
MySQL is then optimized. First, re-statically compiling MySQL so that it only supports MyISAM and memory two engines, and press discuz! encoding select only support UTF-8 or GBK character set. Edit the MY.CNF, set skip-locking, Skip-external-locking, skip-networking, and skip-name-resolve, and adjust the key_buffer_size according to the memory and database state, Query_cache_size, Query_cache_limit, Max_allowed_packet, Table_cache, Thread_cache_size, Sort_buffer_size, Read_ Buffer_size, Read_rnd_buffer_size, Join_buffer_size, Tmp_table_size, Max_tmp_tables, Back_log, Max_connections, Wait_ The parameter for timeout.
The database is optimized to index the partially unindexed fields in the threads and posts tables, and the Supersite database tables are detached from the BBS database. Modify the discuz! configuration file and set the Pconnect to open.
To discuz! Settings for optimization. Enter the discuz! system settings, modify the cache expiration and cache factor in the page cache settings, modify the Disable browser buffering and page gzip compression in the server optimization, modify the anti-theft chain settings to download attachments, and use Jsmin to automatically reduce the JS file (discuz! 6.1 Common.js Original file 29.3k, after jsmin reduced to 24.1k, then after deflate 7.3k), modify the attachments.php file, will:
Dheader (' cache-control:max-age=31536000 ');//dheader (' Expires: '. Gmdate (' d, D M Y h:i:s ', $timestamp + 31536000). ' GMT '), before the comment is removed. Modify the template directory under Adv.htm, and remove the code related to Insenz.
By looking at the status of MySQL, you can see that after optimization, the long-running key_read_ratio basically stays below 0.05% and threads_cache_hitrate stays above 99.9%. Personal feeling, discuz! will save the session in the database, greatly reducing the hit rate of the query cache, if further optimization is needed, you can consider modifying the discuz! source, save the session to Memcache.
After optimization with siege to do the concurrency stress test, in 200 concurrent, basically no error. If you have more people in the future, consider migrating your platform to ngix+php fastcgi.
Here are the results of the test with siege under 300 concurrency:
#siege-C 300-b-R 35-f Bbs.url
* * SIEGE 2.67
* * Preparing concurrent users for battle.
The server is now under siege. Done.
transactions:10500 Hits
availability:100.00%
Elapsed time:52.68 secs
Data transferred:65.67 MB
Response time:1.27 secs
Transaction rate:199.32 trans/sec
throughput:1.25 mb/sec
concurrency:253.07
Successful transactions:10500
Failed transactions:0
Longest transaction:24.88
Shortest transaction:0.00
500 Concurrent Test Results:
#siege-C 500-b-R 20-f Bbs.url
* * SIEGE 2.67
* * Preparing concurrent users for battle.
The server is now under siege. Done.
transactions:9979 Hits
availability:99.79%
Elapsed time:86.52 secs
Data transferred:82.66 MB
Response time:3.30 secs
Transaction rate:115.34 trans/sec
throughput:0.96 mb/sec
concurrency:381.07
Successful transactions:9979
Failed transactions:21
Longest transaction:34.80
Shortest transaction:0.00
Yesterday, the server migrated to the NGINX+PHP-FPM, the following is the post-migration test, the difference is really obvious, back to write NGINX+PHP-FPM experience:
[Email protected] ~]# siege-c 300-r 50-f bbs.url
* * SIEGE 2.67
* * Preparing concurrent users for battle.
The server is now under siege. Done.
transactions:15000 Hits
availability:100.00%
Elapsed time:64.12 secs
Data transferred:67.79 MB
Response time:0.55 secs
Transaction rate:233.94 trans/sec
throughput:1.06 mb/sec
concurrency:128.95
Successful transactions:14976
Failed transactions:0
Longest transaction:3.96
Shortest transaction:0.00
[Email protected] ~]# siege-c 500-r 50-f bbs.url
* * SIEGE 2.67
* * Preparing concurrent users for battle.
The server is now under siege. Done.
transactions:25000 Hits
availability:100.00%
Elapsed time:65.52 secs
Data transferred:83.65 MB
Response time:0.57 secs
Transaction rate:381.56 trans/sec
throughput:1.28 mb/sec
concurrency:216.02
Successful transactions:21707
Failed transactions:0
Longest transaction:5.83
Shortest transaction:0.00
Author Shunz (http://shunz.net), original address: http://shunz.net/2008/06/mysql_discuz_.html