Welcome to Centmin Mod Community
Become a Member

MariaDB CPU Intensive with XenForo

Discussion in 'Install & Upgrades or Pre-Install Questions' started by Oxide, Jun 16, 2015.

  1. rdan

    rdan Well-Known Member

    5,452
    1,418
    113
    May 25, 2014
    Ratings:
    +2,212
    Local Time:
    2:52 PM
    Mainline
    10.2
    If you have only one XenForo forum, you can lower it to 500 or 200 I think.

     
  2. Sunka

    Sunka Well-Known Member

    1,150
    325
    83
    Oct 31, 2015
    Pula, Croatia
    Ratings:
    +525
    Local Time:
    8:52 AM
    Nginx 1.17.9
    MariaDB 10.3.22
    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?
     
  3. rdan

    rdan Well-Known Member

    5,452
    1,418
    113
    May 25, 2014
    Ratings:
    +2,212
    Local Time:
    2:52 PM
    Mainline
    10.2
    Just set it to 512MB, is very enough.
    Just comment it out.
     
  4. Sunka

    Sunka Well-Known Member

    1,150
    325
    83
    Oct 31, 2015
    Pula, Croatia
    Ratings:
    +525
    Local Time:
    8:52 AM
    Nginx 1.17.9
    MariaDB 10.3.22
    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
    
     
  5. eva2000

    eva2000 Administrator Staff Member

    58,907
    12,490
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +19,122
    Local Time:
    4:52 PM
    Nginx 1.31.x
    MariaDB 10.x/11.4+/12.3+
    trial and error + monitoring tests is key ;)
     
  6. Sunka

    Sunka Well-Known Member

    1,150
    325
    83
    Oct 31, 2015
    Pula, Croatia
    Ratings:
    +525
    Local Time:
    8:52 AM
    Nginx 1.17.9
    MariaDB 10.3.22
    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?
     
  7. eva2000

    eva2000 Administrator Staff Member

    58,907
    12,490
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +19,122
    Local Time:
    4:52 PM
    Nginx 1.31.x
    MariaDB 10.x/11.4+/12.3+
    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 ;)