1

my platform:

8 cores, CPU benchmark > 10k (Intel(R) Xeon(R) CPU E5-1620 v2 @ 3.70GHz)
32GB of RAM
2x 7200 SATA enterprise HDD, 128MB cache, each of them have physical 512b block size

Actual file system (/var on md3) is XFS (128GB), RAID level 0 (Striped), Chunk size 64 kB:

xfs_info:

    meta-data=/dev/md3               isize=256    agcount=16, agsize=2097024 blks
             =                       sectsz=512   attr=2, projid32bit=1
             =                       crc=0        finobt=0 spinodes=0
    data     =                       bsize=4096   blocks=33552384, imaxpct=25
             =                       sunit=128    swidth=256 blks
    naming   =version 2              bsize=4096   ascii-ci=0 ftype=0
    log      =internal               bsize=4096   blocks=16384, version=2
             =                       sectsz=512   sunit=8 blks, lazy-count=1
    realtime =none                   extsz=4096   blocks=0, rtextents=0

Server is Apache+nginx+redis+Mariadb mixture

/var partition is dedicated for the database, close to the disk center:

            Filesystem      Size  Used Avail Use% Mounted on
    /dev/md2         16G  6.1G   10G  38% /
    /dev/md3        128G   14G  115G  11% /var
    /dev/md0         32G   34M   32G   1% /tmp
    /dev/md5        3.5T  3.1T  372G  90% /home

    /dev/md2 on / type xfs (rw,noatime,nodiratime,nobarrier,attr2,inode64,usrquota,grpquota) [/]
    /dev/md3 on /var type xfs (rw,noatime,nodiratime,nobarrier,attr2,inode64,sunit=1024,swidth=2048,usrquota,grpquota) [/var]
    /dev/md0 on /tmp type xfs (rw,noexec,noatime,nobarrier,nodiratime,attr2,inode64,sunit=1024,swidth=2048,usrquota,grpquota)
    /dev/md5 on /home type xfs (rw,noatime,nodiratime,nobarrier,attr2,inode64,sunit=1024,swidth=2048,usrquota,grpquota) [/home]

Currently the InnoDB data size is jumping from 3-4GB now.

I'm expecting to add extra ~8GB InnoDB data, MyISAM 2-3GB, Aria ~<2GB soon. Seems that after Mariadb 10.2 to 10.3 upgrade, db starts eating more and more cpu, some DB responses comes up to 4-5 seconds. DB slow log is not catching anything at the moment.

I want to prepare my system for the future without adding SSD or extra memory. There is Redis service, but I cannot cache 100% of db queries. Redis is already handling ~550 requests/sec.

I'm thinking to change innodb_page_size value (the innodb_page_size have 16k default option now), is it possible to do it without exporting+importing data back? worth doing it? Is it any better option for my system? Any recommendations for system resetup without HW changes? Is the slowing down because of 90% of the /home partition? disk head must go to the end disk for web files and return to the beginning? raid disk chunk size increase/decrease from 64k?

iostat:

    avg-cpu:  %user   %nice %system %iowait  %steal   %idle
              53.68   17.72    4.94    0.84    0.00   22.81

    Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
    sda              44.93       954.33       359.22  131137332   49362235
    sdb              42.35       948.70       352.07  130364493   48378490
    md2               0.64         9.86         2.63    1354830     361356
    md3              51.90       118.53       668.17   16287740   91814945
    md0               0.41         3.04        17.59     418198    2416612
    md5              22.25      1743.19        19.57  239537550    2689559

My current db setup looks like:

    [mysql]
    port                           = 3306
    socket                         = /var/lib/mysql/mysql.sock
    [mysqld]
    server_id                      = 1
    default_time_zone              = America/Toronto
    user                           = mysql
    default-storage-engine         = InnoDB
    socket                         = /var/lib/mysql/mysql.sock
    pid-file                       = /var/lib/mysql/mysql.pid
    character-set-server           = utf8
    collation-server               = utf8_bin
    tmpdir                         = /tmp
    event-scheduler                = ON
    performance_schema             = ON
    query_response_time_stats      = ON
    slave-net-timeout              = 360
    net_write_timeout              = 120
    # MyISAM #
    key-buffer-size                = 1024M
    myisam-recover-options         = FORCE,BACKUP
    myisam_sort_buffer_size        = 8M #128M is default on Mariadb 10.3; range 4096-18446744073709547520;

    aria_pagecache_buffer_size     = 512M
    max-allowed-packet             = 16M
    max-connect-errors             = 1000000
    skip-name-resolve
    bind-address                   = 127.0.0.1
    sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
    sysdate-is-now                 = 1
    innodb                         = FORCE
    innodb-strict-mode             = 1

    # DATA STORAGE #
    datadir                        = /var/lib/mysql/

    # BINARY LOGGING #
    log-bin                        = /var/log/mariadb/server-id-1-mysql-bin
    expire-logs-days               = 14 #0 is default for Mariadb 10.3; range 0-99;
    sync-binlog                    = 600 #0 is default on Mariadb 10.3; 0 = OS depend; 1 = one write per commit/statement;

    # CACHES AND LIMITS #
    tmp-table-size                 = 1G
    max-heap-table-size            = 1G
    query-cache-type               = 2 # 0=disabled, 1=enabled, 2=on demand
    query-cache-size               = 64M #1M is default on Mariadb 10.3
    query_cache_limit              = 1M #1m is default on Mariadb 10.3
    query_cache_min_res_unit       = 512 #512 is default on Mariadb 10.3
    max-connections                = 128
    thread-cache-size              = 128 #128 is default on Mariadb 10.3
    open-files-limit               = 32768 #16k is default on Mariadb 10.3
    table-definition-cache         = 512 #400 is default on Mariadb 10.3
    table-open-cache               = 4096 #2000 is default on Mariadb 10.3
    join_buffer_size               = 1M #256K is default on Mariadb 10.3
    sort_buffer_size               = 2M #2M is default on Mariadb 10.3
    thread_cache_size              = 128 # 128 is default on Mariadb 10.3; recommended 100 - ((show status like Threads_created / show status like Connections) * 100)
    thread_concurrency             = 10 # 10 is default on Mariadb 10.3

    # INNODB #
    ###
    #performance increase, but very dangerous
    skip-innodb-doublewrite         #default if off on Mariadb 10.3
    innodb-checksum-algorithm      = none #crc32 is default on Mariadb 10.3
    innodb-compression-level       = 0 #6 is default on Mariadb 10.3
    ###

    #innodb-log-block-size          = 4096 #512 is default on Mariadb 10.3; recommended by disk aligment (blockdev --getbsz /dev/sdXX)
    #innodb_page_size               = 4096 #16K is default on Mariadb 10.3; range (4k or 4096, 8k or 8192, 16k or 16384; Mariadb > 10.1 can be up also 32k/64k); ATTENTION - InnoDB DATA EXPORTl+DROP+IMPORT REQUIRED
    innodb-defragment              = 1
    innodb-flush-method            = O_DIRECT
    innodb-log-files-in-group      = 2
    innodb-log-file-size           = 512M # recommended = innodb-buffer-poolsize / 4
    innodb_log_buffer_size         = 64M #16M is default on Mariadb 10.3
    innodb-flush-log-at-trx-commit = 0 #0 = best performance, 1 = best durability, 2 = better performance
    innodb_flush_log_at_timeout    = 1 #1 is default on Mariadb 10.3
    innodb-file-per-table          = 1 #1 is default for Mariadb 10.3
    innodb-buffer-pool-size        = 4G # recommended = >total size of InnoDB data
    innodb_buffer_pool_instances   = 4 # recommended = RAM chips count;
    innodb_thread_concurrency      = 0 #0 is default on Mariadb 10.3
    innodb_flush_log_at_timeout    = 1 #1 is default on Mariadb 10.3
    innodb_sort_buffer_size        = 1M #1M is default on 10.3 Mariadb
    innodb_read_io_threads         = 4 #4 is default on Mariadb 10.3
    innodb_write_io_threads        = 4 #4 is default om Mariadb 10.3


    # LOGGING #
    long_query_time                = 2
    log-error                      = /var/log/mariadb/mariadb-error.log
    log-queries-not-using-indexes
    slow-query-log
    ####filter options: admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
    log-slow-filter                = admin,full_join,filesort_on_disk,tmp_table,tmp_table_on_disk
    log_slow_verbosity             = innodb,query_plan,explain
    log_slow_rate_limit            = 100 #1 is default on Mariadb 10.3
    slow-query-log-file            = /var/log/mariadb/mariadb-slow.log

top:

    top - 11:00:37 up 1 day, 14:08,  2 users,  load average: 6.69, 6.50, 6.98
    Tasks: 372 total,  11 running, 358 sleeping,   0 stopped,   3 zombie
    %Cpu0  : 57.0 us,  9.7 sy, 21.0 ni,  9.3 id,  0.3 wa,  0.0 hi,  2.7 si,  0.0 st
    %Cpu1  : 41.9 us,  7.0 sy, 40.5 ni, 10.3 id,  0.3 wa,  0.0 hi,  0.0 si,  0.0 st
    %Cpu2  : 60.1 us,  4.3 sy, 24.9 ni, 10.6 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
    %Cpu3  : 67.4 us,  8.0 sy, 17.6 ni,  7.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
    %Cpu4  : 96.0 us,  0.7 sy,  3.0 ni,  0.3 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
    %Cpu5  : 77.4 us,  4.0 sy, 18.6 ni,  0.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
    %Cpu6  : 77.7 us,  2.3 sy, 19.9 ni,  0.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
    %Cpu7  : 96.7 us,  1.3 sy,  1.7 ni,  0.3 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
    KiB Mem : 32746416 total,   755700 free,  8179264 used, 23811452 buff/cache
    KiB Swap:        0 total,        0 free,        0 used. 22176368 avail Mem

      PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
      653 mysql     20   0 9890372   2.8g  15288 S 574.1  9.0 756:18.11 mysqld
    29788 user2    25   5  447196  68176  48376 S  21.6  0.2   0:00.65 php70-cgi
    29790 user2    25   5  447196  68280  48404 S  21.6  0.2   0:00.65 php70-cgi
    29807 user2    25   5  442972  63084  45284 R  17.3  0.2   0:00.52 php70-cgi
    29811 user2    25   5  442904  56644  40128 R  12.3  0.2   0:00.37 php70-cgi
     1137 user1     20   0  876800  83452   5900 S   8.0  0.3   1:56.75 php-fpm
    29815 user1     25   5  442536  31244  11192 R   8.0  0.1   0:00.24 php72-cgi
    29813 user1     25   5  437760  26280  11008 R   5.0  0.1   0:00.15 php72-cgi
    29817 user2    25   5  436752  41688  30368 R   4.0  0.1   0:00.12 php70-cgi
    18803 netdata   20   0   15648   4856    912 R   1.0  0.0   3:23.39 apps.plugin
    29795 user1     25   5  430876  19464  11052 S   1.0  0.1   0:00.03 php72-cgi
    29799 user1     25   5  430876  19288  10880 S   1.0  0.1   0:00.03 php72-cgi
    29818 user1     25   5  430876  19464  11052 R   1.0  0.1   0:00.03 php72-cgi
    29819 user1     25   5  430876  19344  10912 R   1.0  0.1   0:00.03 php72-cgi
      842 nginx     20   0   47744   4468   2376 S   0.7  0.0   0:08.39 nginx
     5511 root      20   0  728880  49836  48040 S   0.7  0.2   0:23.29 rsyslogd
     5804 netdata   20   0  270380  28012   5560 S   0.7  0.1  21:09.58 python
    27760 netdata   20   0    9688   1536   1220 S   0.7  0.0   0:00.14 bash
      490 root      20   0  122064  67104  66668 S   0.3  0.2   6:54.47 systemd-journal
      843 nginx     20   0   47988   4700   2388 S   0.3  0.0   0:13.04 nginx
      848 nginx     20   0   48692   5420   2380 S   0.3  0.0   0:40.13 nginx
      936 user1     20   0  751612  14992   2824 S   0.3  0.0   0:02.90 php-fpm
     1006 user1     20   0  863532  71616   6964 S   0.3  0.2   1:59.43 php-fpm
     1205 redis     10 -10 1806528   1.5g   1184 S   0.3  4.9  15:36.72 redis
     3819 user1     20   0  751480  14840   2752 S   0.3  0.0   0:00.60 php-fpm
     5569 netdata   20   0  234140  66072   1688 S   0.3  0.2   8:13.37 netdata
    18784 user1     20   0  893804 101080   6264 S   0.3  0.3   1:47.93 php-fpm
    24478 apache    25   5  824688  19604   4072 S   0.3  0.1   0:00.21 httpd
    24938 apache    25   5  824596  19408   3952 S   0.3  0.1   0:00.17 httpd
    29529 root      20   0  162248   2616   1600 R   0.3  0.0   0:00.09 top
    29821 user2    25   5  302320  12804   8356 R   0.3  0.0   0:00.01 php70-cgi

    ...

mytop:

    MySQL on localhost (10.3.9-MariaDB-log)  up 0+04:30:25 [10:59:02]
     Queries: 893.2k  qps:   56 Slow:     3.0         Se/In/Up/De(%):    63/00/05/00
                 qps now:  112 Slow qps: 0.0  Threads:    3 (   8/  22) 73/00/04/00
     Key Efficiency: 100.0%  Bps in/out:  8.8k/114.7k   Now in/out: 11.5k/70.2k


          Id      User         Host/IP         DB      Time    Cmd Query or State
           --      ----         -------         --      ----    --- ----------
            1 system us                                    0 Daemon InnoDB purge coordinator
            2 system us                                    0 Daemon InnoDB purge worker
            3 system us                                    0 Daemon InnoDB purge worker
            4 system us                                    0 Daemon InnoDB purge worker
            5 system us                                    0 Daemon InnoDB shutdown handler
           64   netdata       localhost                    0  Sleep
        49426      root       localhost      mysql         0  Query show full processlist
            7 event_sch       localhost                 7000 Daemon Waiting on empty queue

mysqltuner:

     >>  MySQLTuner 1.7.10 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering

    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 10.3.9-MariaDB-log
    [OK] Operating on 64-bit architecture

    -------- Log file Recommendations ------------------------------------------------------------------
    [--] Log file: /var/log/mariadb/mariadb-error.log(9K)
    [OK] Log file /var/log/mariadb/mariadb-error.log exists
    [OK] Log file /var/log/mariadb/mariadb-error.log is readable.
    [OK] Log file /var/log/mariadb/mariadb-error.log is not empty
    [OK] Log file /var/log/mariadb/mariadb-error.log is smaller than 32 Mb
    [OK] /var/log/mariadb/mariadb-error.log doesn't contain any error.
    -------- Storage Engine Statistics -----------------------------------------------------------------
    [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
    [--] Data in Aria tables: 11.3M (Tables: 6)
    [--] Data in MyISAM tables: 334.7M (Tables: 593)
    [--] Data in InnoDB tables: 3.1G (Tables: 663)
    [--] Data in MEMORY tables: 124.1K (Tables: 6)
    [OK] Total fragmented tables: 0

    -------- Security Recommendations ------------------------------------------------------------------
    [OK] There are no anonymous accounts for any database users
    [OK] All database users have passwords assigned
    -------- CVE Security Recommendations --------------------------------------------------------------
    [--] Skipped due to --cvefile option undefined

    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 4h 25m 12s (893K q [56.160 qps], 113K conn, TX: 1G, RX: 136M)
    [--] Reads / Writes: 91% / 9%
    [--] Binary logging is enabled (GTID MODE: OFF)
    [--] Physical Memory     : 31.2G
    [--] Max MySQL memory    : 7.1G
    [--] Other process memory: 3.9G
    [--] Total buffers: 6.6G global + 3.7M per thread (128 max threads)
    [--] P_S Max memory usage: 100M
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 6.8G (21.66% of installed RAM)
    [OK] Maximum possible memory usage: 7.1G (22.84% of installed RAM)
    [OK] Overall possible memory usage with other process is compatible with memory available
    [OK] Slow queries: 0% (3/893K)
    [OK] Highest usage of available connections: 19% (25/128)
    [OK] Aborted connections: 0.00%  (2/113504)
    [!!] Query cache may be disabled by default due to mutex contention.
    [!!] Query cache efficiency: 0.0% (0 cached / 562K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 115K sorts)
    [!!] Joins performed without indexes: 194
    [OK] Temporary tables created on disk: 15% (12K on disk / 80K total)
    [OK] Thread cache hit rate: 99% (25 created / 113K connections)
    [OK] Table cache hit rate: 98% (1K open / 1K opened)
    [OK] Open file limit used: 7% (1K/16K)
    [OK] Table locks acquired immediately: 99% (330K immediate / 330K locks)
    [OK] Binlog cache memory access: 100.00% (42935 Memory / 42935 Total)

    -------- Performance schema ------------------------------------------------------------------------
    [--] Memory used by P_S: 100.3M
    [--] Sys schema isn't installed.

    -------- ThreadPool Metrics ------------------------------------------------------------------------
    [--] ThreadPool stat is enabled.
    [--] Thread Pool Size: 8 thread(s).
    [--] Using default value is good enough for your version (10.3.9-MariaDB-log)

    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 18.9% (202M used / 1B cache)
    [OK] Key buffer size / total MyISAM indexes: 1.0G/81.9M
    [OK] Read Key buffer hit rate: 100.0% (115M cached / 6K reads)
    [!!] Write Key buffer hit rate: 77.2% (5K cached / 4K writes)

    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [--] InnoDB is enabled.
    [--] InnoDB Thread Concurrency: 0
    [OK] InnoDB File per table is activated
    [OK] InnoDB buffer pool / data size: 4.0G/3.1G
    [OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 512.0M * 2/4.0G should be equal 25%
    [OK] InnoDB buffer pool instances: 4
    [--] Number of InnoDB Buffer Pool Chunk : 32 for 4 Buffer Pool Instance(s)
    [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
    [OK] InnoDB Read buffer efficiency: 100.00% (39286543837 hits/ 39286642011 total)
    [!!] InnoDB Write Log efficiency: 76.58% (77174 hits/ 100771 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 23597 writes)

    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is enabled.
    [OK] Aria pagecache size / total Aria indexes: 512.0M/808.0K
    [OK] Aria pagecache hit rate: 99.7% (3M cached / 11K reads)


    -------- Recommendations ---------------------------------------------------------------------------
    General recommendations:
        Control warning line(s) into /var/log/mariadb/mariadb-error.log file
        Restrict Host for user@% to user@SpecificDNSorIp
        MySQL was started within the last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
        Consider installing Sys schema from https://github.com/mysql/mysql-sys
    Variables to adjust:
        query_cache_size (=0)
        query_cache_type (=0)
        query_cache_limit (> 1M, or use smaller result sets)
        join_buffer_size (> 1.0M, or always use indexes with JOINs)

innodb:

    =====================================
    2018-08-30 10:56:41 0x7fccb833c700 INNODB MONITOR OUTPUT
    =====================================
    Per second averages calculated from the last 5 seconds
    -----------------
    BACKGROUND THREAD
    -----------------
    srv_master_thread loops: 14238 srv_active, 0 srv_shutdown, 1184 srv_idle
    srv_master_thread log flush and writes: 15422
    ----------
    SEMAPHORES
    ----------
    OS WAIT ARRAY INFO: reservation count 186890
    OS WAIT ARRAY INFO: signal count 9143467
    RW-shared spins 0, rounds 31440402, OS waits 142956
    RW-excl spins 0, rounds 13095374, OS waits 61503
    RW-sx spins 2037, rounds 40312, OS waits 453
    Spin rounds per wait: 31440402.00 RW-shared, 13095374.00 RW-excl, 19.79 RW-sx
    ------------
    TRANSACTIONS
    ------------
    Trx id counter 95899652
    Purge done for trx's n:o < 95899643 undo n:o < 0 state: running but idle
    History list length 20
    LIST OF TRANSACTIONS FOR EACH SESSION:
    ---TRANSACTION 421993893611928, not started
    0 lock struct(s), heap size 1136, 0 row lock(s)
    ---TRANSACTION 421993893606216, not started
    0 lock struct(s), heap size 1136, 0 row lock(s)
    --------
    FILE I/O
    --------
    I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
    I/O thread 1 state: waiting for completed aio requests (log thread)
    I/O thread 2 state: waiting for completed aio requests (read thread)
    I/O thread 3 state: waiting for completed aio requests (read thread)
    I/O thread 4 state: waiting for completed aio requests (read thread)
    I/O thread 5 state: waiting for completed aio requests (read thread)
    I/O thread 6 state: waiting for completed aio requests (write thread)
    I/O thread 7 state: waiting for completed aio requests (write thread)
    I/O thread 8 state: waiting for completed aio requests (write thread)
    I/O thread 9 state: waiting for completed aio requests (write thread)
    Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
    ibuf aio reads:, log i/o's:, sync i/o's:
    Pending flushes (fsync) log: 0; buffer pool: 0
    99820 OS file reads, 433239 OS file writes, 130428 OS fsyncs
    0.20 reads/s, 16384 avg bytes/read, 17.40 writes/s, 5.80 fsyncs/s
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 1, free list len 270, seg size 272, 344 merges
    merged operations:
    insert 87, delete mark 267, delete 0
    discarded operations:
    insert 0, delete mark 0, delete 0
    Hash table size 1106407, node heap has 919 buffer(s)
    Hash table size 1106407, node heap has 1995 buffer(s)
    Hash table size 1106407, node heap has 4 buffer(s)
    Hash table size 1106407, node heap has 983 buffer(s)
    Hash table size 1106407, node heap has 919 buffer(s)
    Hash table size 1106407, node heap has 2357 buffer(s)
    Hash table size 1106407, node heap has 1394 buffer(s)
    Hash table size 1106407, node heap has 735 buffer(s)
    378057.19 hash searches/s, 663309.94 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number 58746155240
    Log flushed up to   58746154730
    Pages flushed up to 58746153668
    Last checkpoint at  58746143988
    0 pending log flushes, 0 pending chkp writes
    27080 log i/o's done, 1.20 log i/o's/second
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total large memory allocated 4429185024
    Dictionary memory allocated 4705071
    Buffer pool size   262144
    Free buffers       135207
    Database pages     116025
    Old database pages 42906
    Modified db pages  16
    Percent of dirty pages(LRU & free pages): 0.006
    Max dirty pages percent: 75.000
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 98, not young 0
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 99142, created 16883, written 406172
    0.20 reads/s, 0.00 creates/s, 16.20 writes/s
    Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 116025, unzip_LRU len: 3216
    I/O sum[0]:cur[0], unzip sum[0]:cur[0]
    ----------------------
    INDIVIDUAL BUFFER POOL INFO
    ----------------------
    ---BUFFER POOL 0
    Buffer pool size   65536
    Free buffers       33684
    Database pages     29131
    Old database pages 10773
    Modified db pages  4
    Percent of dirty pages(LRU & free pages): 0.006
    Max dirty pages percent: 75.000
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 31, not young 0
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 24938, created 4193, written 123109
    0.00 reads/s, 0.00 creates/s, 4.60 writes/s
    Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 29131, unzip_LRU len: 741
    I/O sum[0]:cur[0], unzip sum[0]:cur[0]
    ---BUFFER POOL 1
    Buffer pool size   65536
    Free buffers       33601
    Database pages     29203
    Old database pages 10799
    Modified db pages  3
    Percent of dirty pages(LRU & free pages): 0.005
    Max dirty pages percent: 75.000
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 43, not young 0
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 25078, created 4125, written 91633
    0.00 reads/s, 0.00 creates/s, 1.80 writes/s
    Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 29203, unzip_LRU len: 815
    I/O sum[0]:cur[0], unzip sum[0]:cur[0]
    ---BUFFER POOL 2
    Buffer pool size   65536
    Free buffers       33966
    Database pages     28826
    Old database pages 10660
    Modified db pages  4
    Percent of dirty pages(LRU & free pages): 0.006
    Max dirty pages percent: 75.000
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 20, not young 0
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 24528, created 4298, written 74115
    0.20 reads/s, 0.00 creates/s, 2.60 writes/s
    Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 28826, unzip_LRU len: 807
    I/O sum[0]:cur[0], unzip sum[0]:cur[0]
    ---BUFFER POOL 3
    Buffer pool size   65536
    Free buffers       33956
    Database pages     28865
    Old database pages 10674
    Modified db pages  5
    Percent of dirty pages(LRU & free pages): 0.008
    Max dirty pages percent: 75.000
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 4, not young 0
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 24598, created 4267, written 117315
    0.00 reads/s, 0.00 creates/s, 7.20 writes/s
    Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 28865, unzip_LRU len: 853
    I/O sum[0]:cur[0], unzip sum[0]:cur[0]
    --------------
    ROW OPERATIONS
    --------------
    0 queries inside InnoDB, 0 queries in queue
    6 read views open inside InnoDB
    Process ID=653, Main thread ID=140512678807296, state: sleeping
    Number of rows inserted 816, updated 48716, deleted 818, read 17466709916
    0.00 inserts/s, 1.60 updates/s, 0.00 deletes/s, 1058084.38 reads/s
    Number of system rows inserted 0, updated 0, deleted 0, read 0
    0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
    ----------------------------
    END OF INNODB MONITOR OUTPUT
    ============================
  • 1
    ps. there is a lot of full scans, when user tries to search something by keyword: EXPLAIN: #Query_time: 5.861146 Lock_time: 0.000094 Rows_sent: 4 ,Rows_examined: 1299024, Rows_affected: 0 Bytes_sent: 356, Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No Filesort: No Filesort_on_disk: No Merge_passes: 0 Priority_queue: No QUERY: SELECT d.id AS id, d.title AS title FROM keywords AS k INNER JOIN data_keywords AS dk ON k.id = dk.keyword_id INNER JOIN data AS d ON dk.data_id = d.id WHERE k.keyword = 'unbelievably_beautiful' LIMIT 30; – vagiz Aug 30 '18 at 15:16
  • Additional information request, please. Post on pastebin.com or here. A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; Optional very helpful information, if available includes - ulimit -a for a linux/unix list of limits, df -h for a linux/unix free space list by device, for server tuning analysis. – Wilson Hauck Aug 30 '18 at 18:13
  • 1
    @WilsonHauck here is the link: paste.ubuntu.com/p/H2k52k7y3b let me know if something extra needed – vagiz Aug 31 '18 at 21:09
  • 1
    additional, i have missed to explain top 3 query: paste.ubuntu.com/p/wQBgvd9XqV – vagiz Aug 31 '18 at 21:23
  • 1
    also I was sure 100% that my raid chunk 64K, but as I can see is not. I think is reasonable to run: mdadm --grow --chunk=32 /dev/md3 (/var, for database dir) – vagiz Aug 31 '18 at 21:35
0

Suggestions to consider for your my.cnf OR my.ini [mysqld] section (RPS = Rate Per Second)

# 20180901 1346 from mysqlservertuning.com
# log_error=/var/lib/mysql/2cd908c24352-error.log  # from default of stderr, for log
# max_connect_errors=10  # from 1000000 - why give a cracker/hacker more than 10 shots?
# tmp_table_size=320M  # from 1G to stay under 1% RAM
# max_heap_table_size=320M  # from 1G should be same as TTS
# thread_cache_size=100  # from 128 for CAP suggested in V8 refman to avoid OOM
# innodb_io_capacity=15000  # from 200 to allow more IOPS for your SSD
# read_buffer_size=256K  # from 128K to reduce handler_read_next RPS
# read_rnd_buffer_size=128K  # from 256K to reduce handler_read_rnd_next RPS
# key_cache_age_threshold=3600  # from 300 seconds to delay AGE OUT and reduce key_reads RPS
# innodb_buffer_pool_instances=1  # from 8 to minimize impact of innodb_lru_scan_depth on CPU every second
# thread_concurrency=14  # from 10 to encourage multi processing when possible with your 8 cores
# min_examined_row_limit=1  # from 0 to reduce slow query log clutter

Each DAY, save your current my.cnf OR my.ini in \history with DATED timed filename such as 20180827hhmm-my.cnf to allow going back to last working my.cnf OR my.ini quickly.

Copy this BLOCK (including the leading date and our web site name)to END of your [mysqld] section and ENABLE ONE change per DAY by removing leading # and spacecharacter, monitor before moving on to next change.

Disable EARLIER same NAMED variable with leading # and space bar, to avoid confusion. In 5 years you will still have history of my.cnf OR my.ini changes with approximate dates.

Normally only ONE change per day, monitor before moving to next change. If a change seems detrimental, go back to last working my.cnf OR my.ini and let us know, please.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.