1

I stuck a bit with setting the best performance of MariaDB on my VPS. I have: 6 cores; 16GB RAM; 300GB SSD; CloudLinux; LiteSpeed

 >>  MySQLTuner 1.7.0 - 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.2.13-MariaDB-log
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/dev.whitespot.co.err(611K)
[OK] Log file /var/lib/mysql/dev.whitespot.co.err exists
[OK] Log file /var/lib/mysql/dev.whitespot.co.err is readable.
[OK] Log file /var/lib/mysql/dev.whitespot.co.err is not empty
[OK] Log file /var/lib/mysql/dev.whitespot.co.err is smaller than 32 Mb
[!!] /var/lib/mysql/sqlhost.err contains 4467 warning(s).
[!!] /var/lib/mysql/sqlhost.err contains 164 error(s).
[--] 8 start(s) detected in /var/lib/mysql/dev.whitespot.co.err
[--] 1) 2018-02-14  3:17:43 140627065792640 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2018-02-14  3:08:56 140369082124416 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2018-02-14  3:04:17 140342812088448 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2018-02-14  2:27:29 139698717120640 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2018-02-14  2:27:05 140353362925696 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2018-02-14  0:59:46 140394864134272 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2018-02-14  0:58:53 140612241397888 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2018-02-14  0:40:07 140392565569664 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7 shutdown(s) detected in /var/lib/mysql/sqlhostname.err
[--] 1) 2018-02-14  3:17:39 140368255764224 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2018-02-14  3:08:52 140342016706304 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2018-02-14  3:04:13 139688655320832 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2018-02-14  2:27:26 140353156269824 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2018-02-14  2:26:59 140384804599552 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2018-02-14  0:59:42 140611259844352 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2018-02-14  0:58:49 140391611623168 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in MyISAM tables: 2G (Tables: 4460)
[--] Data in InnoDB tables: 1G (Tables: 1872)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] User 'netdata@localhost' has no password set.
[!!] User 'jb9363_user@%' hasn't specific host restriction.
[!!] User 'robak@%' hasn't specific host restriction.
[!!] User 'robak_alexadam@%' hasn't specific host restriction.
[!!] User 'robak_francesco@%' hasn't specific host restriction.
[!!] User 'robak_g@%' hasn't specific host restriction.
[!!] User 'robak_pro@%' hasn't specific host restriction.
[!!] User 'robak_tom@%' hasn't specific host restriction.
[--] There are 612 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 8h 12m 49s (4M q [162.139 qps], 51K conn, TX: 41G, RX: 1G)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Physical Memory     : 15.7G
[--] Max MySQL memory    : 9.8G
[--] Other process memory: 1.5G
[--] Total buffers: 8.4G global + 4.7M per thread (300 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 8.5G (54.58% of installed RAM)
[OK] Maximum possible memory usage: 9.8G (62.60% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[!!] Slow queries: 36% (1M/4M)
[OK] Highest usage of available connections: 8% (24/300)
[OK] Aborted connections: 0.13%  (66/51527)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (24 temp sorts / 563K sorts)
[!!] Joins performed without indexes: 10333
[!!] Temporary tables created on disk: 69% (195K on disk / 282K total)
[OK] Thread cache hit rate: 99% (24 created / 51K connections)
[OK] Table cache hit rate: 98% (7K open / 7K opened)
[OK] Open file limit used: 38% (9K/25K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.

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

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 31.3% (84M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/295.8M
[OK] Read Key buffer hit rate: 99.9% (34M cached / 34K reads)
[!!] Write Key buffer hit rate: 12.9% (112K cached / 97K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 8.0G/1.6G
[OK] InnoDB log file size / InnoDB Buffer pool size: 1.0G * 2/8.0G should be equal 25%
[OK] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 64 for 8 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: 99.99% (205255768 hits/ 205283702 total)
[!!] InnoDB Write Log efficiency: 77.83% (185463 hits/ 238296 total)
[OK] InnoDB log waits: 0.00% (0 waits / 52833 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[!!] Aria pagecache hit rate: 93.9% (2M cached / 181K reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/lib/mysql/sqlhostname.err file
    Control error line(s) into /var/lib/mysql/sqlhostname.err file
    Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
    query_cache_size (>= 8M)
    join_buffer_size (> 2.0M, or always use indexes with joins)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)

I've installed some monitoring tool for SQL. This software emails me every hour about slow queries. Log file is now 500mb... log created in 12h :/

I run on this VPS only WordPress websites via LiteSpeed + LS Cache. I'm not sure hot to fix memory issues - at the moment on my VPS 0.4GB is available. And those two Temporary tables created on disk and Joins performed without indexes - any ideas how to fix that?

  • See stackoverflow for suggestions posted a couple days ago, please. – Wilson Hauck Feb 17 '18 at 9:54
  • Question 48786386 at stackoverflow.com has an Answer with suggestions. – Wilson Hauck Feb 20 '18 at 19:18
  • Made any progress? – Wilson Hauck May 13 '18 at 23:19
0

You "can't tune your way out of a performance problem".

My tips:

| improve this answer | |

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