MariaDB CPU Intensive with XenForo

rdan · Nov 6, 2015

Sunka said: ↑

what about pm.max_requests = 1000
Click to expand...

If you have only one XenForo forum, you can lower it to 500 or 200 I think.

Sunka · Nov 7, 2015

I can not find that two settings. Will it work if I add them and set to enable into my.conf, and if that settings allready are somwhere with option disabled, what will be overridden - will be on the end anabled or disabled?
Code:
innodb_save_on_shutdown
innodb_load_on_startup
Also, regarding my server configuration, set innodb_buffer_pool_size to 2 GB (now is 512mb) and innodb_buffer_pool_instances to 2 will be Ok and that 2 GB will not ruined other software on server (I have only 4GB ram), or to set to 1 GB and innodb_buffer_pool_instances leave commented?

rdan · Nov 7, 2015

Sunka said: ↑

set innodb_buffer_pool_size to 2 GB (now is 512mb)
Click to expand...

Just set it to 512MB, is very enough.

Sunka said: ↑

innodb_buffer_pool_instances to 2
Click to expand...

Just comment it out.

Sunka · Sep 1, 2016

In mysqlmymonlite I see that:

Code:

InnoDB Buffer Pool __________________________________________________
Usage         851.06M of    1.00G  %Usage:  83.11
Read hit       99.99%
Pages
  Free         11.07k              %Total:  16.89
  Data         53.20k                       81.17  %Drty:   0.00
  Misc           1271                        1.94
  Latched           0                        0.00
Reads           1.00G     10.4k/s
  From disk    51.91k       0.5/s   %Disk:   0.01
  Ahead Rnd         0         0/s
Writes          4.27M      44.3/s
Flushes       698.22k       7.2/s
Wait Free           0         0/s   %Wait:   0.00

Would it be time to raise innodb_buffer_pool_size in my.cnf to some bigger value? If yes, how much? Maybe1/2 GB so it will be 1024M + 512M = 1536M?

This is setting right now:

Code:

# innodb settings
innodb_large_prefix=1
innodb_purge_threads = 1
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 1024M

mysqlmonlite:

Code:

-------------------------------------------------------------
System MySQL monitoring stats
mysqlmymonlite.sh - 0.5.4 mysqlmymon.com
compiled by George Liu (eva2000) vbtechsupport.com
-------------------------------------------------------------

Report Generated:
Thu Sep 1 02:14:04 CEST 2016

----------------------------
MySQL Uptime:
----------------------------
MySQL server was started
Uptime: 1 day 2 hours 46 min 9 sec
Uptime 96369
Uptime_since_flush_status 96369

Max Used Connections:      6/500 (1.20%)
Key Buffer Used:           1.00k/256.00M (0.00%)
Key Buffer Used:           1.71M/64.00M (2.67%)
Current Key Buffer Usage:  46.69M (18.24%)
Current Key Buffer Usage:  2.66M (4.15%)
Table Locks Waited:        44 Rate: 0.0/s (0.00%)
--------------------
Query Cache Limit (bytes): 0
Query Cache Min Unit (bytes): 2048
Query Cache Size (bytes): 0
Query Cache Free Mem (bytes): 0
Query Cache Used Mem (bytes): 0
Queries in Query Cache: 1
Query Cache Avg Size (bytes): 0

----------------------------
Hardware Info:
----------------------------
Processors physical = 2, cores = 2, virtual = 2, hyperthreading = no
      2  1797.917
      2  Intel(R) Xeon(R) CPU E5-2650L v3 @ 1.80GHz
      2  30720 KB


----------------------------
TOP stats
----------------------------

Linux 3.10.0-327.28.3.el7.x86_64 ((yourserverhostname))     09/01/2016     _x86_64_    (2 CPU)

02:14:05 AM  CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest  %gnice   %idle
02:14:07 AM  all    2.27    0.00    0.76    0.00    0.00    0.00    0.25    0.00    0.00   96.72
02:14:07 AM    0    2.02    0.00    1.01    0.00    0.00    0.00    0.00    0.00    0.00   96.97
02:14:07 AM    1    3.02    0.00    1.01    0.50    0.00    0.00    0.00    0.00    0.00   95.48

02:14:07 AM  CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest  %gnice   %idle
02:14:09 AM  all    4.53    0.00    0.76    0.00    0.00    0.00    0.25    0.00    0.00   94.46
02:14:09 AM    0    3.02    0.00    0.50    0.00    0.00    0.00    0.50    0.00    0.00   95.98
02:14:09 AM    1    6.09    0.00    0.51    0.00    0.00    0.00    0.00    0.00    0.00   93.40

Average:     CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest  %gnice   %idle
Average:     all    3.40    0.00    0.76    0.00    0.00    0.00    0.25    0.00    0.00   95.59
Average:       0    2.52    0.00    0.76    0.00    0.00    0.00    0.25    0.00    0.00   96.47
Average:       1    4.55    0.00    0.76    0.25    0.00    0.00    0.00    0.00    0.00   94.44

top - 02:14:09 up 7 days, 21:58,  1 user,  load average: 0.43, 0.31, 0.22
Tasks: 113 total,   1 running, 112 sleeping,   0 stopped,   0 zombie
%Cpu(s):  7.4 us,  1.4 sy,  0.0 ni, 90.1 id,  0.3 wa,  0.0 hi,  0.1 si,  0.7 st
KiB Mem :  3882428 total,   172828 free,  1844768 used,  1864832 buff/cache
KiB Swap:  2097148 total,  2060440 free,    36708 used.  1520356 avail Mem

Code:

-------------------------------------------------------------
System MySQL monitoring stats
mysqlmymonlite.sh - 0.5.4 mysqlmymon.com
compiled by George Liu (eva2000) vbtechsupport.com
-------------------------------------------------------------

Report Generated:
Thu Sep 1 02:14:34 CEST 2016

-------------------------------------------------
mysqltuner output
-------------------------------------------------
mysqltuner.pl [found]
 >>  MySQLTuner 1.6.4 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Modified by George Liu (eva2000) at http://vbtechsupport.com/
 >>  Run with '--help' for additional options and output filtering
[[0;34m--[0m] Skipped version check for MySQLTuner script
[[0;32mOK[0m] Currently running supported MySQL version 10.1.17-MariaDB
[[0;32mOK[0m] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[[0;34m--[0m] Status: [0;32m+Aria [0m[0;32m+CSV [0m[0;32m+InnoDB [0m[0;32m+MRG_MyISAM [0m[0;32m+SEQUENCE [0m
[[0;34m--[0m] Data in MyISAM tables: 8K (Tables: 4)
[[0;34m--[0m] Data in InnoDB tables: 659M (Tables: 337)
[[0;34m--[0m] Data in MEMORY tables: 1M (Tables: 6)
[[0;31m!![0m] Total fragmented tables: 57


-------- CVE Security Recommendations  ---------------------------------------
[[0;34m--[0m] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[[0;34m--[0m] Up for: 1d 2h 46m 39s (3M q [31.454 qps], 249K conn, TX: 24B, RX: 1B)
[[0;34m--[0m] Reads / Writes: 75% / 25%
[[0;34m--[0m] Binary logging is disabled
[[0;34m--[0m] Total buffers: 1.6G global + 1.5M per thread (500 max threads)
[[0;32mOK[0m] Maximum reached memory usage: 1.6G (42.86% of installed RAM)
[[0;32mOK[0m] Maximum possible memory usage: 2.3G (62.83% of installed RAM)
[[0;32mOK[0m] Slow queries: 0% (0/3M)
[[0;32mOK[0m] Highest usage of available connections: 1% (6/500)
[[0;32mOK[0m] Aborted connections: 0.00%  (0/249218)
[[0;31m!![0m] Query cache is disabled
[[0;32mOK[0m] Sorts requiring temporary tables: 0% (675 temp sorts / 315K sorts)
[[0;31m!![0m] Joins performed without indexes: 1607
[[0;32mOK[0m] Temporary tables created on disk: 11% (16K on disk / 146K total)
[[0;32mOK[0m] Thread cache hit rate: 99% (6 created / 249K connections)
[[0;32mOK[0m] Table cache hit rate: 89% (789 open / 878 opened)
[[0;32mOK[0m] Open file limit used: 0% (71/8K)
[[0;32mOK[0m] Table locks acquired immediately: 99% (4M immediate / 4M locks)

-------- MyISAM Metrics ------------------------------------------------------
[[0;31m!![0m] Key buffer used: 18.2% (48M used / 268M cache)
[[0;32mOK[0m] Key buffer size / total MyISAM indexes: 256.0M/148.0K
[[0;32mOK[0m] Read Key buffer hit rate: 100.0% (1 cached / 0 reads)
[[0;31m!![0m] Write Key buffer hit rate: 0.0% (2 cached / 2 writes)

-------- InnoDB Metrics ------------------------------------------------------
[[0;34m--[0m] InnoDB is enabled.
[[0;32mOK[0m] InnoDB buffer pool / data size: 1.0G/659.5M
[[0;32mOK[0m] InnoDB buffer pool instances: 1
[[0;32mOK[0m] InnoDB Used buffer: 83.11% (54468 used/ 65535 total)
[[0;32mOK[0m] InnoDB Read buffer efficiency: 99.99% (1001506845 hits/ 1001558758 total)
[[0;31m!![0m] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[[0;32mOK[0m] InnoDB log waits: 0.00% (0 waits / 283411 writes)

-------- ThreadPool Metrics --------------------------------------------------
[[0;34m--[0m] ThreadPool stat is disabled.

-------- AriaDB Metrics ------------------------------------------------------
[[0;34m--[0m] AriaDB is disabled.

-------- TokuDB Metrics ------------------------------------------------------
[[0;34m--[0m] TokuDB is disabled.

-------- Galera Metrics ------------------------------------------------------
[[0;34m--[0m] Galera is disabled.

-------- Replication Metrics -------------------------------------------------
[[0;34m--[0m] No replication slave(s) for this server.
[[0;34m--[0m] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
Adjust your join queries to always utilize indexes
Variables to adjust:
    query_cache_size (>= 8M)
    join_buffer_size (> 256.0K, or always use indexes with joins)

Report Complete:
Thu Sep 1 02:14:35 CEST 2016
----------------------------

my.cnf:

Code:

[mysqld]
ignore-db-dir=cmsetiofiotest
local-infile=0
ignore_db_dirs=lost+found
character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#bind-address=127.0.0.1
# optimized my.cnf for MariaDB 5.5.x
# by eva2000
# vbtechsupport.com

tmpdir=/home/mysqltmp

innodb=ON
#skip-federated
#skip-pbxt
#skip-pbxt_statistics
#skip-archive
#skip-name-resolve
#old_passwords
back_log = 512
max_connections = 500
key_buffer_size = 256M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 2048M
join_buffer_size = 256K
read_buffer_size = 256K
sort_buffer_size = 256K
table_definition_cache = 8192
table_open_cache = 4096
thread_cache_size = 256
wait_timeout = 1800
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 64M
max_seeks_for_key = 1000
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 512K
bulk_insert_buffer_size = 8M
query_cache_limit = 0
query_cache_size = 0
query_cache_type = 0
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

log_warnings=1
slow_query_log=0
long_query_time=1
slow_query_log_file=/var/lib/mysql/slowq.log
log-error=/var/log/mysqld.log

# innodb settings
innodb_large_prefix=1
innodb_purge_threads = 1
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 1024M

## https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances
innodb_buffer_pool_instances = 1

innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
#innodb_thread_concurrency = 8
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1

# 200 * # DISKS
innodb_io_capacity = 1100
innodb_io_capacity_max = 2200
innodb_read_io_threads = 2
innodb_write_io_threads = 2

# mariadb settings
[mariadb]
#thread-handling = pool-of-threads
#thread-pool-size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1

userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 128M
aria_log_purge_type = immediate
aria_pagecache_buffer_size = 64M
aria_sort_buffer_size = 32M

[mariadb-5.5]
#ignore_db_dirs=
query_cache_strip_comments=0

innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512

log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mysqld_safe]
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
#nice = -5
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 64M

[myisamchk]
tmpdir=/home/mysqltmp
key_buffer = 256M
sort_buffer = 32M
read_buffer = 32M
write_buffer = 32M

[mysqlhotcopy]
interactive-timeout

[mariadb-10.0]
# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb

## MariaDB 10 only save and restore buffer pool pages
## warm up InnoDB buffer pool on server restarts
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_populate=0
## Disabled settings
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk


[mariadb-10.1]
## wsrep specific
# wsrep_on=OFF
# wsrep_provider
# wsrep_cluster_address
# binlog_format=ROW
 default_storage_engine=InnoDB
# innodb_autoinc_lock_mode=2
# innodb_doublewrite=1
# query_cache_size=0

# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb

## MariaDB 10 only save and restore buffer pool pages
## warm up InnoDB buffer pool on server restarts
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_populate=0
## Disabled settings
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

# Defragmenting unused space on InnoDB tablespace
innodb_defragment=1
innodb_defragment_n_pages=7
innodb_defragment_stats_accuracy=0
innodb_defragment_fill_factor_n_recs=20
innodb_defragment_fill_factor=0.9
innodb_defragment_frequency=40

eva2000 · Sep 2, 2016

Sunka said: ↑

Would it be time to raise innodb_buffer_pool_size in my.cnf to some bigger value? If yes, how much? Maybe1/2 GB so it will be 1024M + 512M = 1536M?
Click to expand...

trial and error + monitoring tests is key

Sunka · Sep 2, 2016

eva2000 said: ↑

trial and error + monitoring tests is key
Click to expand...

I will raise for 256M

So, to do that, only raise innodb_buffer_pool_size in my.cnf from 1024M to 1280M
And then restart mysql and php-fpm?

Also, @eva2000 how to see from all memory (ram) I have, how much is alocated to what?

eva2000 · Sep 2, 2016

restart mysql server

mysqlmymonlite.sh has some output

excerpt from mysqlmymonlite run output command

Code (Text):

./mysqlmymonlite.sh run

Code (Text):

 Private  +   Shared  =  RAM used       Program 

156.0 KiB +  43.5 KiB = 199.5 KiB       agetty
292.0 KiB +  89.0 KiB = 381.0 KiB       irqbalance
548.0 KiB + 102.0 KiB = 650.0 KiB       lvmetad
548.0 KiB + 111.0 KiB = 659.0 KiB       auditd
704.0 KiB +  71.0 KiB = 775.0 KiB       crond
716.0 KiB + 110.5 KiB = 826.5 KiB       systemd-udevd
496.0 KiB + 350.0 KiB = 846.0 KiB       avahi-daemon (2)
844.0 KiB + 122.0 KiB = 966.0 KiB       dbus-daemon
644.0 KiB + 343.5 KiB = 987.5 KiB       bash
756.0 KiB + 329.0 KiB =   1.1 MiB       mysqlmymonlite.
  1.0 MiB +  70.5 KiB =   1.1 MiB       systemd-logind
888.0 KiB + 243.5 KiB =   1.1 MiB       wpa_supplicant
  1.2 MiB + 143.0 KiB =   1.4 MiB       master
  1.4 MiB + 273.5 KiB =   1.6 MiB       pure-ftpd
  1.2 MiB + 510.0 KiB =   1.7 MiB       pickup
  1.3 MiB + 510.0 KiB =   1.8 MiB       qmgr
  1.2 MiB +   1.4 MiB =   2.7 MiB       systemd-journald
  2.7 MiB + 143.5 KiB =   2.8 MiB       systemd
  2.8 MiB +  57.5 KiB =   2.9 MiB       memcached
  1.4 MiB +   1.5 MiB =   2.9 MiB       rsyslogd
  3.6 MiB +  39.5 KiB =   3.6 MiB       haveged
  3.2 MiB +   1.1 MiB =   4.3 MiB       sshd (2)
  4.9 MiB +   1.1 MiB =   6.0 MiB       NetworkManager
  8.3 MiB + 251.5 KiB =   8.5 MiB       php-fpm
 10.3 MiB +   1.0 MiB =  11.3 MiB       polkitd
 13.1 MiB + 400.5 KiB =  13.5 MiB       dhclient
 12.8 MiB + 700.0 KiB =  13.5 MiB       tuned
 22.6 MiB +  96.0 KiB =  22.7 MiB       lfd - sleeping
  6.5 MiB +  23.2 MiB =  29.7 MiB       nginx (2)
 95.1 MiB + 407.0 KiB =  95.5 MiB       mysqld
---------------------------------
                        235.7 MiB
=================================

 Private  +   Shared  =  RAM used       Program

and

Use ps command

Might want to start a separate thread in System Administration forums

Log in / Register

Forums

Welcome to Centmin Mod Community

MariaDB CPU Intensive with XenForo

rdan Well-Known Member

Sunka Well-Known Member

rdan Well-Known Member

Sunka Well-Known Member

eva2000 Administrator Staff Member

Sunka Well-Known Member

eva2000 Administrator Staff Member

.

Log in / Register

Useful Searches

Welcome to Centmin Mod Community

MariaDB CPU Intensive with XenForo

rdan Well-Known Member

Sunka Well-Known Member

rdan Well-Known Member

Sunka Well-Known Member

eva2000 Administrator Staff Member

Sunka Well-Known Member

eva2000 Administrator Staff Member

.