mediawiki mysql有时有很高的cpu负载。
我们有一个mediawiki，大约有50人在线
版本是1.31
以下是localesettings.php内容

<?php

# This file was automatically generated by the MediaWiki 1.31.0

# installer. If you make manual changes, please keep track in case you

# need to recreate them later.

# 

# See includes/DefaultSettings.php for all configurable settings

# and their default values, but don't forget to make changes in _this_

# file, not there.

# 

# Further documentation for configuration settings may be found at:

# https://www.mediawiki.org/wiki/Manual:Configuration_settings

# Protect against web entry

if ( !defined( 'MEDIAWIKI' ) ) {
        exit;
}

## Uncomment this to disable output compression

# $wgDisableOutputCompression = true;

$wgSitename = "MyWiki";

## The URL base path to the directory containing the wiki;

## defaults for all runtime URL paths are based off of this.

## For more information on customizing the URLs

## (like /w/index.php/Page_title to /wiki/Page_title) please see:

## https://www.mediawiki.org/wiki/Manual:Short_URL

$wgScriptPath = "";

## The protocol and server name to use in fully-qualified URLs

# $wgServer = "https://wikidecode.org";

$wgServer = "https://xxxxxxx.com";

## The URL path to static resources (images, scripts, etc.)

$wgResourceBasePath = $wgScriptPath;

## The URL path to the logo.  Make sure you change this from the default,

## or else you'll overwrite your logo when you upgrade!

$wgLogo = "$wgResourceBasePath/resources/assets/wiki.png";

## UPO means: this is also a user preference option

$wgEnableEmail = true;
$wgEnableUserEmail = true; # UPO

$wgEmergencyContact = "apache@xxxxxxx.com";
$wgPasswordSender = "apache@xxxxxxx.com";

$wgEnotifUserTalk = false; # UPO
$wgEnotifWatchlist = false; # UPO
$wgEmailAuthentication = true;

## Database settings

$wgDBtype = "mysql";
$wgDBserver = "127.0.0.1";
$wgDBname = "mediawikidb";
$wgDBuser = "mediawikidb";
$wgDBpassword = "xxxxxxxxxxxxxxxxx";

# MySQL specific settings

$wgDBprefix = "";
$wgSQLMode = null;

# MySQL table options to use during installation or update

$wgDBTableOptions = "ENGINE=InnoDB, DEFAULT CHARSET=binary";

## Shared memory settings

# $wgUseGzip = true;

$wgEnableSidebarCache = true;
$wgMainCacheType = CACHE_MEMCACHED;
$wgParserCacheType = CACHE_MEMCACHED; # optional
$wgMessageCacheType = CACHE_MEMCACHED; # optional
$wgMemCachedServers = array( "127.0.0.1:11211" );

# $wgUseLocalMessageCache = true;

$wgSessionsInObjectCache = true; # optional
$wgSessionCacheType = CACHE_MEMCACHED; # optional

# File cache

$wgUseFileCache = true;

# $wgFileCacheDirectory = "/var/www/mediawiki/file-cache";

$wgFileCacheDirectory = "$IP/cache";

# NO DB HITS!

# Refer to https://techwelkin.com/slow-mediawiki-optimize-to-enhance-performance-part-1

$wgDisableCounters = false;
$wgHitcounterUpdateFreq = 500;
$wgMiserMode = true;

# $wgJobRunRate = 0; // Set a cronjob via "crontab -e" with "0 0 * * * /usr/bin/php /var/www/mediawiki/maintenance/runJobs.php > /var/log/runJobs.log 2>&1"

## Set $wgCacheDirectory to a writable directory on the web server

## to make your wiki go slightly faster. The directory should not

## be publically accessible from the web.

$wgCacheDirectory = "$IP/cache";

## To enable image uploads, make sure the 'images' directory

## is writable, then set this to true:

$wgEnableUploads = true;

# $wgUseImageMagick = true;

# $wgImageMagickConvertCommand = "/usr/bin/convert";

# InstantCommons allows wiki to use images from https://commons.wikimedia.org

$wgUseInstantCommons = false;
$wgRunJobsAsync = true;

# Periodically send a pingback to https://www.mediawiki.org/ with basic data

# about this MediaWiki instance. The Wikimedia Foundation shares this data

# with MediaWiki developers to help guide future development efforts.

$wgPingback = false;

## If you use ImageMagick (or any other shell command) on a

## Linux server, this will need to be set to the name of an

## available UTF-8 locale

$wgShellLocale = "C.UTF-8";

# Site language code, should be one of the list in ./languages/data/Names.php

$wgLanguageCode = "zh";

$wgSecretKey = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";

# Changing this will log out all existing sessions.

$wgAuthenticationTokenVersion = "1";

# Site upgrade key. Must be set to a string (default provided) to turn on the

# web installer while LocalSettings.php is in place

$wgUpgradeKey = "xxxxxxxxxxxxxx";

## For attaching licensing metadata to pages, and displaying an

## appropriate copyright notice / icon. GNU Free Documentation

## License and Creative Commons licenses are supported so far.

$wgRightsPage = ""; # Set to the title of a wiki page that describes your license/copyright
$wgRightsUrl = "";
$wgRightsText = "";
$wgRightsIcon = "";

# Path to the GNU diff3 utility. Used for conflict resolution.

$wgDiff3 = "/usr/bin/diff3";

## Default skin: you can change the default skin. Use the internal symbolic

## names, ie 'vector', 'monobook':

# $wgDefaultSkin = "minerva";

$wgDefaultSkin = "Timeless";

# Enabled skins.

# The following skins were automatically enabled:

wfLoadSkin( 'MonoBook' );
wfLoadSkin( 'Timeless' );
wfLoadSkin( 'Vector' );

# Enabled extensions. Most of the extensions are enabled by adding

# wfLoadExtensions('ExtensionName');

# to LocalSettings.php. Check specific extension documentation for more details.

# The following extensions were automatically enabled:

wfLoadExtension( 'CategoryTree' );
wfLoadExtension( 'Cite' );
wfLoadExtension( 'CiteThisPage' );
wfLoadExtension( 'CodeEditor' );
wfLoadExtension( 'Gadgets' );

# wfLoadExtension( 'ImageMap' );

wfLoadExtension( 'InputBox' );
wfLoadExtension( 'Interwiki' );
wfLoadExtension( 'LocalisationUpdate' );
wfLoadExtension( 'MultimediaViewer' );
wfLoadExtension( 'OATHAuth' );
wfLoadExtension( 'ParserFunctions' );
wfLoadExtension( 'PdfHandler' );
wfLoadExtension( 'Poem' );
wfLoadExtension( 'Renameuser' );
wfLoadExtension( 'ReplaceText' );
wfLoadExtension( 'SpamBlacklist' );
wfLoadExtension( 'SyntaxHighlight_GeSHi' );
wfLoadExtension( 'TitleBlacklist' );
wfLoadExtension( 'Babel' );

wfLoadExtension( 'cldr' );

wfLoadExtension( 'CleanChanges' );
$wgCCTrailerFilter = true;
$wgCCUserFilter = false;
$wgDefaultUserOptions['usenewrc'] = 1;

wfLoadExtension( 'LocalisationUpdate' );
$wgLocalisationUpdateDirectory = "$IP/cache";

require_once "$IP/extensions/Translate/Translate.php";
$wgGroupPermissions['user']['translate'] = true;
$wgGroupPermissions['user']['translate-messagereview'] = true;
$wgGroupPermissions['user']['translate-groupreview'] = true;
$wgGroupPermissions['user']['translate-import'] = true;
$wgGroupPermissions['sysop']['pagetranslation'] = true;
$wgGroupPermissions['sysop']['translate-manage'] = true;
$wgTranslateDocumentationLanguageCode = 'qqq';
$wgExtraLanguageNames['qqq'] = 'Message documentation'; # No linguistic content. Used for documenting messages

wfLoadExtension( 'UniversalLanguageSelector' );
wfLoadExtension( 'WikiEditor' );

wfLoadExtension( 'Scribunto' );

# $wgScribuntoDefaultEngine = 'luastandalone';

# $wgScribuntoEngineConf['luastandalone']['cpuLimit'] = 'ulimit';

# $wgScribuntoEngineConf['luastandalone']['memoryLimit'] = 209715200; # bytes

$wgScribuntoDefaultEngine = 'luasandbox';
$wgScribuntoEngineConf['luasandbox']['cpuLimit'] = 'ulimit';
$wgScribuntoEngineConf['luasandbox']['memoryLimit'] = 209715200; # bytes

wfLoadExtension( 'Nuke' );

# wfLoadExtension( 'VisualEditor' );

// Enable by default for everybody

# $wgDefaultUserOptions['visualeditor-enable'] = 1;

// Optional: Set VisualEditor as the default for anonymous users
// otherwise they will have to switch to VE

# $wgDefaultUserOptions['visualeditor-editor'] = "visualeditor";

// Don't allow users to disable it

# $wgHiddenPrefs[] = 'visualeditor-enable';

# Parsoid fir Visual Editor

$wgVirtualRestConfig['modules']['parsoid'] = array(
    // URL to the Parsoid instance
    // Use port 8142 if you use the Debian package
    //'url' => 'http://wikidecode.org:8142',
    'url' => 'http://xxxxxxx.com:8142',
    // Parsoid "domain", see below (optional)
    //'domain' => 'localhost',
    // Parsoid "prefix", see below (optional)
    //'prefix' => 'localhost'
);

# Wikibase

$wgEnableWikibaseRepo = true;
$wgEnableWikibaseClient = true;
require_once "$IP/extensions/Wikibase/repo/Wikibase.php";
require_once "$IP/extensions/Wikibase/repo/ExampleSettings.php";
require_once "$IP/extensions/Wikibase/client/WikibaseClient.php";
require_once "$IP/extensions/Wikibase/client/ExampleSettings.php";

# Mobile responsive display

wfLoadExtension( 'MobileFrontend' );
$wgMFAutodetectMobileView = true;

wfLoadSkin( 'MinervaNeue' );
$wgMFDefaultSkinClass = 'SkinMinerva';

# End of automatically generated settings.

# Add more configuration options below.

# Auto confirm threshold

$wgAutoConfirmCount=20;

# Tidy # Fix the problem that infobox will show <td? </td>

$wgUseTidy=true;

# Permission (need to comment out to make Visual Editor work)

$wgGroupPermissions['*']['read'] = false;  # Disable reading by anonymous users
$wgGroupPermissions['*']['edit'] = false;
$wgGroupPermissions['*']['writeapi'] = false;
$wgGroupPermissions['*']['createpage'] = false;
$wgGroupPermissions['*']['createtalk'] = false;
$wgGroupPermissions['*']['createaccount'] = false;  # Prevent new user registrations except by sysops

# Debugging

# $wgShowExceptionDetails = true;

# $wgShowDBErrorBacktrace = true;

# $wgShowSQLErrors = true;

# $wgDebugDumpSql = true;

# $wgDebugComments = true;

$wgDebugLogFile = "/var/log/mediawiki/debug-{$wgDBname}.log";

$wgPFEnableStringFunctions = true;
$wgMaxShellMemory = 204800; # in KB

# TemplateStyle

wfLoadExtension( 'TemplateStyles' );

# Youtube

wfLoadExtension( 'YouTube' );

# Embedded Video

wfLoadExtension( 'EmbedVideo' );

# Permission to Hide history

$wgGroupPermissions['sysop']['deletelogentry'] = true;
$wgGroupPermissions['sysop']['deleterevision'] = true;

# Notice

# $wgSiteNotice = "Internal..."

# Upload picture option

# allow upload by URL

$wgAllowCopyUploads = true;
$wgCopyUploadsFromSpecialUpload = true;

# Increase Session timeout

$wgCookieExpiration = 86400;
$wgExtendedLoginCookieExpiration = null;

# Proxy WWW

# $wgUseSquid = true;

# $wgSquidServers = [ 'x.x.x.x' ];

# $wgSquidServersNoPurge = [ 'x.x.x.x' ];

# 貢獻得分

require_once "$IP/extensions/ContributionScores/ContributionScores.php";
$wgContribScoreIgnoreBots = true;          // Exclude Bots from the reporting - Can be omitted.
$wgContribScoreIgnoreBlockedUsers = true;  // Exclude Blocked Users from the reporting - Can be omitted.
$wgContribScoresUseRealName = true;        // Use real user names when available - Can be omitted. Only for MediaWiki 1.19 and later.
$wgContribScoreDisableCache = true;       // Set to true to disable cache for parser function and inclusion of table.

//Each array defines a report - 7,50 is "past 7 days" and "LIMIT 50" - Can be omitted.
$wgContribScoreReports = array(
    array(7,20),
    array(30,20),
    array(0,20));

# Change default user behavior

## Add pages the user edits to their watchlist by default

$wgDefaultUserOptions['watchdefault'] = 0;  // 不將更改的page加入監視列表

# 允許外部連結顯示圖片

$wgAllowExternalImages=true;

# 開啟濫用日誌

wfLoadExtension( 'AbuseFilter' );
$wgGroupPermissions['sysop']['abusefilter-modify'] = true;
$wgGroupPermissions['*']['abusefilter-log-detail'] = true;

## $wgGroupPermissions['*']['abusefilter-view'] = true;

## $wgGroupPermissions['*']['abusefilter-log'] = true;

$wgGroupPermissions['sysop']['abusefilter-private'] = true;
$wgGroupPermissions['sysop']['abusefilter-modify-restricted'] = true;
$wgGroupPermissions['sysop']['abusefilter-revert'] = true;

# 防止用戶名使用非格式化字串

# wfLoadExtension( 'AntiSpoof' );

# $wgSharedTables[] = 'spoofuser';

# 允許HTML img tag

$wgAllowImageTag=true;

# 必須email驗證

# $wgEmailConfirmToEdit=true;

# Lockdown Permission

wfLoadExtension( 'Lockdown' );
$wgNamespacePermissionLockdown[NS_TEMPLATE]['*'] = ['bureaucrat'];
$wgNamespacePermissionLockdown[NS_TEMPLATE]['read'] = ['*'];

# 網站維護....

# $wgReadOnly = 'Dumping Database, Access will be restored shortly';

# 打開舉報 (註冊用戶可以舉報，管理員處理)

wfLoadExtension( 'Report' );

# 可以看見誰正在監視那個page

wfLoadExtension( 'WhoIsWatching' );
$whoiswatching_nametype = "RealName";

# $wgGroupPermissions['sysop']['addpagetoanywatchlist'] = true;

# $wgGroupPermissions['sysop']['seepagewatchers'] = true;

# 追蹤某個使用者的進出狀況

# require_once "$IP/extensions/StalkerLog/StalkerLog.php";

# $wgGroupPermissions['*']['stalkerlog-view-log'] = false;

# $wgGroupPermissions['sysop']['stalkerlog-view-log'] = true;

# Who is online

wfLoadExtension( 'WhosOnline' );
$wgWhosOnlineShowAnons = true;

# 關閉page view counter

# $wgDisableCounters = true;

# wfLoadExtension( 'MatomoAnalytics' );

# $wgMatomoAnalyticsServerURL=false;

# $wgMatomoAnalyticsTokenAuth=false;

# Check User IP (blocking by IP range)

wfLoadExtension( 'CheckUser' );

# $wgGroupPermissions['sysop']['checkuser'] = true;

# $wgGroupPermissions['sysop']['checkuser-log'] = true;

$wgAddGroups['bureaucrat'][] = 'checkuser';
$wgRemoveGroups['bureaucrat'][] = 'checkuser';

# Local S3 for image

# https://github.com/edwardspec/mediawiki-aws-s3

# wfLoadExtension( 'AWS' );

// Configure AWS credentials.
// THIS IS NOT NEEDED if your EC2 instance has an IAM instance profile.

# $wgAWSCredentials = [

# 'key' => 'xxxxxxxxxxxxxx',

# 'secret' => 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',

# 'token' => false

# ];

# $wgAWSRegion = 'us-west-2'; # Oregon

// Replace <something> with the name of your S3 bucket, e.g. wonderfulbali234.

# $wgAWSBucketName = "mediawiki-uploads";

# $wgUploadDirectory = "$IP/s3mnt";

# $wgUploadPath = "$wgScriptPath/s3mnt";

# Anti-Robot Mechanism

# wfLoadExtension( 'ConfirmEdit' );

# $wgGroupPermissions['*'            ]['skipcaptcha'] = false; // Default

# $wgGroupPermissions['user'         ]['skipcaptcha'] = false; // Default

# $wgGroupPermissions['autoconfirmed']['skipcaptcha'] = false; // Default

# $wgGroupPermissions['bot'          ]['skipcaptcha'] = true;  // Default: registered bots

# $wgGroupPermissions['sysop'        ]['skipcaptcha'] = true;  // Default

# $wgCaptchaTriggers['edit'] = false;            // Trigger while editing a page

# $wgCaptchaTriggers['create'] = true;          // Default: Trigger while creating a page

# $wgCaptchaTriggers['addurl'] = true;          // Default: Trigger while ading a url

# $wgCaptchaTriggers['createaccount'] = true;   // Default: Trigger while registering

# $wgCaptchaTriggers['badlogin'] = true;        // Default: Trigger while bad login

# Echo to users

wfLoadExtension( 'Echo' );

# Delete a Page permanently

wfLoadExtension( 'DeletePagesForGood' );
$wgGroupPermissions['*']['deleteperm'] = false;
$wgGroupPermissions['user']['deleteperm'] = false;
$wgGroupPermissions['bureaucrat']['deleteperm'] = true;
$wgGroupPermissions['sysop']['deleteperm'] = false;

# Gadgets edit permission

# $wgGroupPermissions['sysop']['gadgets-edit'] = true;

# $wgGroupPermissions['sysop']['gadgets-definition-edit'] = true;

# Addthis

# require_once "$IP/extensions/AddThis/AddThis.php";

# $wgAddThisMain=false;

# $wgAddThisSidebar=false;

# Related Articles

# wfLoadExtension( 'RelatedArticles' );

# $wgRelatedArticlesFooterWhitelistedSkins = ['vector', 'timeless', 'minerva', 'monobook'];

$wgULSGeoService = 'https://freegeoip.app/json/8.8.8.8?callback=?';

# New namespace for private pages

// Define constants for my additional namespaces.
define("NS_FOO", 3000); // This MUST be even.
define("NS_FOO_TALK", 3001); // This MUST be the following odd integer.

// Add namespaces.
$wgExtraNamespaces[NS_FOO] = "Foo";
$wgExtraNamespaces[NS_FOO_TALK] = "Foo_talk"; // Note underscores in the namespace name.

$wgNamespaceProtection[NS_FOO] = array( 'editfoo' ); // permission "editfoo" required to edit the foo namespace
$wgNamespacesWithSubpages[NS_FOO] = true;            // subpages enabled for the foo namespace
$wgGroupPermissions['staff']['editfoo'] = true;      // permission "editfoo" granted to users in the "staff" group
$wgNamespacePermissionLockdown[NS_FOO]['read'] = ['staff'];

有时mysqld消耗所有cpu资源，nginx显示504网关超时。以下是mysql慢速查询日志的一部分：

# Time: 2018-12-04T17:13:53.679634Z

# User@Host: root[root] @ localhost [127.0.0.1]  Id: 82845

# Query_time: 3.868103  Lock_time: 0.000066 Rows_sent: 6  Rows_examined: 14

SET timestamp=1543943633;
SELECT /* SearchMySQL::searchInternal  */  page_id,page_namespace,page_title  FROM `page`,`searchindex`    WHERE (page_id=si_page) AND ( MATCH(si_text) AGAINST('+\"u8e7ae80 u8c2b7 u8e696b9 u8e8bebe\" ' IN BOOLEAN MODE) ) AND page_namespace IN ('0','120')   LIMIT 20;

# Time: 2018-12-04T17:13:57.542743Z

# User@Host: root[root] @ localhost [127.0.0.1]  Id: 82845

# Query_time: 3.861802  Lock_time: 0.000104 Rows_sent: 1  Rows_examined: 14

SET timestamp=1543943637;
SELECT /* SearchMySQL::searchInternal  */  COUNT(*) as c  FROM `page`,`searchindex`    WHERE (page_id=si_page) AND ( MATCH(si_text) AGAINST('+\"u8e7ae80 u8c2b7 u8e696b9 u8e8bebe\" ' IN BOOLEAN MODE) ) AND page_namespace IN ('0','120');

我不确定主要问题是什么，是mysql设置还是mediawiki，还是只需要升级硬件？
我已经尝试过多次更改mysql设置，但都没有修复。
这是我的mysqld.cnf：

# 

# The MySQL database server configuration file.

# 

# You can copy this to one of:

# - "/etc/mysql/my.cnf" to set global options,

# - "~/.my.cnf" to set user-specific options.

# 

# One can use all long options that the program supports.

# Run program with --help to get a list of available options and with

# --print-defaults to see which it would actually understand and use.

# 

# For explanations see

# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients

# It has been reported that passwords should be enclosed with ticks/quotes

# escpecially if they contain "#" chars...

# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs

# The following values assume you have at least 32M ram

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]

# 

# * Basic Settings

# 

user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

# 

# Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

bind-address            = 127.0.0.1

# 

# * Fine Tuning

# 

key_buffer_size         = 512M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 32

# This replaces the startup script and checks MyISAM tables if needed

# the first time they are touched

myisam-recover-options  = BACKUP

# max_connections        = 100

# table_cache            = 64

# thread_concurrency     = 10

# 

# * Query Cache Configuration

# 

query_cache_type        = 0
query_cache_limit       = 2M
query_cache_size        = 0

# 

# * Logging and Replication

# 

# Both location gets rotated by the cronjob.

# Be aware that this log type is a performance killer.

# As of 5.1 you can enable the log at runtime!

# general_log_file        = /var/log/mysql/mysql.log

# general_log             = 1

# 

# Error log - should be very few entries.

# 

log_error = /var/log/mysql/error.log

# 

# Here you can see queries with especially long duration

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log

# log_slow_queries = /var/log/mysql/mysql-slow.log

long_query_time = 1

# log-queries-not-using-indexes

# 

# The following can be used as easy to replay backup logs or for replication.

# note: if you are setting up a replication slave, see README.Debian about

# other settings you may need to change.

# server-id              = 1

# log_bin                        = /var/log/mysql/mysql-bin.log

expire_logs_days        = 10
max_binlog_size   = 100M

# binlog_do_db           = include_database_name

# binlog_ignore_db       = include_database_name

# 

# * InnoDB

# 

# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.

# Read the manual for more InnoDB related options. There are many!

# 

# * Security Features

# 

# Read the manual, too, if you want chroot!

# chroot = /var/lib/mysql/

# 

# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".

# 

# ssl-ca=/etc/mysql/cacert.pem

# ssl-cert=/etc/mysql/server-cert.pem

# ssl-key=/etc/mysql/server-key.pem

# add

table_open_cache = 432
innodb_buffer_pool_instances = 1
innodb_buffer_pool_size = 1G
innodb_log_file_size = 128M

硬件是amazon ec2 c5.large
操作系统版本：ubuntu 16.04.5
mysql版本：ver 14.14 distrib 5.7.24，适用于linux（x86\u 64），使用editline Package 器