The issue
I am currently using a 6CPU 16RAM VPS in a forum with 9k users , and I noticed heavy performance issue when users are quite active. What is the best configuration for Mysql?
Flarum information
Flarum core: 1.8.1
PHP version: 8.1.21
MySQL version: 8.0.32
Loaded extensions: Core, date, libxml, openssl, pcre, sqlite3, zlib, bcmath, ctype, curl, dom, filter, ftp, gd, gettext, hash, iconv, intl, json, mbstring, SPL, session, pcntl, standard, mysqlnd, PDO, pdo_mysql, pdo_sqlite, Phar, posix, Reflection, mysqli, shmop, SimpleXML, soap, sockets, sodium, sysvsem, tokenizer, xml, xmlreader, xmlwriter, zip, fileinfo, exif, redis, imagick, gmp, Zend OPcache
+------------------------------------------+------------+------------------------------------------+
| Flarum Extensions | | |
+------------------------------------------+------------+------------------------------------------+
| ID | Version | Commit |
+------------------------------------------+------------+------------------------------------------+
| flarum-flags | v1.8.0 | |
| flarum-approval | v1.8.0 | |
| flarum-likes | v1.8.0 | |
| flarum-subscriptions | v1.8.0 | |
| flarum-tags | v1.8.0 | |
| flarum-suspend | v1.8.0 | |
| flarum-lock | v1.8.0 | |
| antoinefr-money | v1.3.1 | |
| flarum-sticky | v1.8.0 | |
| flarum-markdown | v1.8.0 | |
| flarum-bbcode | v1.8.0 | |
| fof-follow-tags | 1.1.7 | |
| flamarkt-backoffice | 0.1.3 | |
| fof-byobu | dev-master | |
| v17development-blog | v0.6.5 | |
| afrux-forum-widgets-core | v0.1.7 | |
| ziiven-raffle-ticket | v1.1.3 | |
| ziiven-pay-to-see | v1.1.4 | |
| ziiven-dice-game | v1.0.6 | |
| ziiven-decoration-store | dev-master | |
| zerosonesfun-member | 1.2 | |
| zerosonesfun-bbcode-button | 1.1.3 | |
| zequeen-acgembed-remastered | v1.1.2 | |
| yannisme-oxotheme | 2.1.6 | |
| v17development-seo | v1.8.0 | |
| the-turk-stickiest | 3.0.1 | |
| the-turk-flamoji | 1.0.4 | |
| sycho-private-facade | v0.1.13 | |
| nearata-internal-links-noreload | v2.2.0 | |
| nearata-cakeday | v2.2.0 | |
| matteocontrini-imgur-upload | v3.9.1 | |
| littlecxm-reply-to-see | 1.0.2 | |
| kilowhat-rich-embeds | 1.2.5 | |
| justoverclock-feedback | 0.1.9 | |
| ianm-follow-users | 1.3.1 | |
| ganuonglachanh-sonic | 0.1.8 | |
| fof-user-bio | 1.2.2 | |
| fof-upload | 1.4.1 | |
| fof-sitemap | 2.2.0 | |
| fof-profile-image-crop | 1.1.6 | |
| fof-pretty-mail | 1.1.1 | |
| fof-polls | 2.1.1 | |
| fof-nightmode | 1.5.3 | |
| fof-masquerade | 2.1.3 | |
| fof-links | 1.2.0 | |
| fof-linguist | 1.1.1 | |
| fof-frontpage | 1.1.1 | |
| fof-formatting | 1.0.3 | |
| fof-drafts | 1.2.5 | |
| fof-doorman | 1.1.3 | |
| fof-default-user-preferences | 1.2.0 | |
| flarumite-simple-discussion-views | 1.2.1 | |
| flarum-statistics | v1.8.0 | |
| flarum-nicknames | v1.8.0 | |
| flarum-mentions | v1.8.2 | |
| flarum-lang-russian | 1.20.0 | |
| flarum-lang-english | v1.8.0 | |
| flarum-lang-chinese-simplified | v1.3.0 | |
| flamarkt-taxonomies | 0.1.9 | |
| datlechin-usercard-uid | v0.1.1 | |
| datlechin-scroll-buttons | v1.1.0 | |
| datlechin-copy-links | v0.1.1 | |
| datlechin-bbcode-hide-content | v0.1.5 | |
| club-1-cross-references | v1.1.1 | |
| clarkwinkelmann-see-past-first-post | 1.3.1 | |
| clarkwinkelmann-roll-die | 1.0.0 | |
| clarkwinkelmann-post-stream-search | 1.1.0 | |
| clarkwinkelmann-post-bookmarks | 1.0.0 | |
| clarkwinkelmann-money-rewards | 1.0.0 | d6ff6eb3255c2e65eea738c781510808da9d1eb3 |
| clarkwinkelmann-group-invitation | 1.0.2 | |
| clarkwinkelmann-formatted-banner | 1.0.0 | |
| clarkwinkelmann-discussion-open-position | 1.1.0 | |
| clarkwinkelmann-discussion-lists | 1.0.0 | |
| blomstra-no-email-notifications | 0.1.0 | |
| blomstra-cache-assets | 0.5 | |
| askvortsov-pwa | v3.3.3 | |
| askvortsov-moderator-warnings | v0.6.3 | |
| askvortsov-discussion-templates | v0.8.3 | |
| askvortsov-categories | v3.0.9 | |
| annonny-watermark | dev-master | |
| annonny-gpt-bot | dev-master | |
| annonny-date-filter | dev-master | |
| afrux-news-widget | v0.1.1 | |
| afrux-forum-stats-widget | v0.1.1 | |
| acpl-mobile-tab | 1.1.1 | |
+------------------------------------------+------------+------------------------------------------+
Base URL:
Installation path: /
Queue driver: redis
Session driver: redis (Code override. Configured to file)
Scheduler status: Inactive
Mail driver: smtp
Debug mode: off
My current Mysql Setting
![client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
[mysqld]
binlog_cache_size = 256K
thread_stack = 512K
join_buffer_size = 8192K
max_heap_table_size = 2048M
port = 3306
socket = /tmp/mysql.sock
datadir = /www/server/data
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 2048M
max_allowed_packet = 64M
table_open_cache = 6096
sort_buffer_size = 6024K
net_buffer_length = 4K
read_buffer_size = 4096K
read_rnd_buffer_size = 2048K
myisam_sort_buffer_size = 64M
thread_cache_size = 214
tmp_table_size = 2048M
default_authentication_plugin = mysql_native_password
lower_case_table_names = 1
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp = true
#skip-name-resolve
max_connections = 700
max_connect_errors = 100
open_files_limit = 65535
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
binlog_expire_logs_seconds = 600000
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
early-plugin-load = ""
innodb_data_home_dir = /www/server/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/server/data
innodb_buffer_pool_size = 7048M
innodb_log_file_size = 1024M
innodb_log_buffer_size = 1500M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 1800
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 6
innodb_write_io_threads = 6
[mysqldump]
quick
max_allowed_packet = 500M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
bt_mysql_set = 0
bt_mem_size =
bt_query_cache_size = 0
bt_mem_size = 27086.97!
