MySQL configuration recommendation

[Jirinex]

Hi all, 

I am looking for some guidance regarding the mysql (mariadb) config.

I am on a cloud server with 4vCPUs and 16GB of ram.

My IPS4 db is about 7gb and my members love to use the new content button  

Can someone please help with configuration sample for this type of server?

Thank you

[ASTRAPI]

There is no "one configuration" for all.

Database performance has to do with many factors like, ssd disk, use of Innodb tables, Optimize my.cnf according to your traffic and server specifications and how and when your users interact with it and many more........

 

You may post some mysqltuner script output so we will get a basic idea and recommend some basic configuration......

But again if you need maximum performance you will need a full system optimization

[Jirinex]

Hello, 

Thanks for coming back to me. I never assumed there was one-size fits all, I am just looking for general recommendations around a setup with IPS. 4vcpus, 16gb of ram, SSD SAN storage. At the moment I am with myisam tables and i am soon hoping to convert them to innodb.

Here is mysqltuner output:

 >>  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 5.5.52-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mariadb/mariadb.log(8K)
[OK] Log file /var/log/mariadb/mariadb.log exists
[OK] Log file /var/log/mariadb/mariadb.log is readable.
[OK] Log file /var/log/mariadb/mariadb.log is not empty
[OK] Log file /var/log/mariadb/mariadb.log is smaller than 32 Mb
[!!] /var/log/mariadb/mariadb.log contains 1 warning(s).
[OK] /var/log/mariadb/mariadb.log doesn't contain any error.
[--] 6 start(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 170114 19:56:26 [Note] /usr/libexec/mysqld: ready for connections.
[--] 2) 170114 19:38:47 [Note] /usr/libexec/mysqld: ready for connections.
[--] 3) 170114 19:28:34 [Note] /usr/libexec/mysqld: ready for connections.
[--] 4) 170114 19:27:30 [Note] /usr/libexec/mysqld: ready for connections.
[--] 5) 170112 20:20:10 [Note] /usr/libexec/mysqld: ready for connections.
[--] 6) 170112 16:35:47 [Note] /usr/libexec/mysqld: ready for connections.
[--] 5 shutdown(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 170114 19:56:24 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 2) 170114 19:38:45 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 3) 170114 19:28:33 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 4) 170114 19:27:29 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 5) 170112 20:14:04 [Note] /usr/libexec/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 6G (Tables: 269)
[--] Data in InnoDB tables: 13M (Tables: 369)
[!!] Total fragmented tables: 1

-------- Database Metrics --------------------------------------------------------------------------
[--] There is 9 Database(s).
[--] All Databases:
[--]  +-- TABLE : 717
[--]  +-- ROWS  : 13587229
[--]  +-- DATA  : 4.8G(71.94%)
[--]  +-- INDEX : 1.9G(28.06%)
[--]  +-- SIZE  : 6.7G
[--]  +-- COLLA : 5 (utf8_general_ci, utf8_bin, latin1_swedish_ci, utf8mb4_unicode_ci, utf8_unicode_ci)
[--]  +-- ENGIN : 5 (MEMORY, Aria, InnoDB, MyISAM, CSV, PERFORMANCE_SCHEMA)

[--] Database: ips4db
[--]  +-- TABLE: 269
[--]  +-- COLL : 2 (utf8mb4_unicode_ci, utf8_unicode_ci)
[--]  +-- ROWS : 13560802
[--]  +-- DATA : 4.8G(71.99%)
[--]  +-- INDEX: 1.9G(28.01%)
[--]  +-- TOTAL: 6.7G
[--]  +-- ENGIN : 1 (MyISAM)
[!!] 2 different collations for database ips4db
[OK] 1 engine for ips4db database.
[--] Charsets for ips4db database table column: utf8mb4, utf8
[!!] ips4db table column(s) has several charsets defined for all text like column(s).
[--] Collations for ips4db database table column: utf8mb4_unicode_ci, utf8_unicode_ci
[!!] ips4db table column(s) has several collations defined for all text like column(s).

-------- Indexes Metrics ---------------------------------------------------------------------------
[--] Worst selectivity indexes:
[--] Index: archive_restored(archive_restored)
[--]  +-- COLUNM      : ips4db.ipb_forums_archive_posts
[--]  +-- NB SEQS     : 1 sequence(s)
[--]  +-- NB COLS     : 1 column(s)
[--]  +-- CARDINALITY : 1 distinct values
[--]  +-- NB ROWS     : 231564 rows
[--]  +-- TYPE        : BTREE
[--]  +-- SELECTIVITY : 0.00%
[!!] archive_restored(archive_restored) has a low selectivity
[--] Index: app(type)
[--]  +-- COLUNM      : ips4db.ipb_core_reputation_index
[--]  +-- NB SEQS     : 2 sequence(s)
[--]  +-- NB COLS     : 5 column(s)
[--]  +-- CARDINALITY : 11 distinct values
[--]  +-- NB ROWS     : 478517 rows
[--]  +-- TYPE        : BTREE
[--]  +-- SELECTIVITY : 0.00%
[!!] app(type) has a low selectivity
[--] Index: find_rel_follows(follow_visible)
[--]  +-- COLUNM      : ips4db.ipb_core_follow
[--]  +-- NB SEQS     : 1 sequence(s)
[--]  +-- NB COLS     : 3 column(s)
[--]  +-- CARDINALITY : 2 distinct values
[--]  +-- NB ROWS     : 40268 rows
[--]  +-- TYPE        : BTREE
[--]  +-- SELECTIVITY : 0.00%
[!!] find_rel_follows(follow_visible) has a low selectivity
[--] Index: container(index_class)
[--]  +-- COLUNM      : ips4db.ipb_core_search_index
[--]  +-- NB SEQS     : 1 sequence(s)
[--]  +-- NB COLS     : 2 column(s)
[--]  +-- CARDINALITY : 11 distinct values
[--]  +-- NB ROWS     : 2372470 rows
[--]  +-- TYPE        : BTREE
[--]  +-- SELECTIVITY : 0.00%
[!!] container(index_class) has a low selectivity
[--] Index: log_action(log_action)
[--]  +-- COLUNM      : ips4db.ipb_core_file_logs
[--]  +-- NB SEQS     : 1 sequence(s)
[--]  +-- NB COLS     : 1 column(s)
[--]  +-- CARDINALITY : 2 distinct values
[--]  +-- NB ROWS     : 75889 rows
[--]  +-- TYPE        : BTREE
[--]  +-- SELECTIVITY : 0.00%
[!!] log_action(log_action) has a low selectivity
[--] Index: ipsconnect_id(ipsconnect_id)
[--]  +-- COLUNM      : ips4db.ipb_core_members
[--]  +-- NB SEQS     : 1 sequence(s)
[--]  +-- NB COLS     : 1 column(s)
[--]  +-- CARDINALITY : 1 distinct values
[--]  +-- NB ROWS     : 49041 rows
[--]  +-- TYPE        : BTREE
[--]  +-- SELECTIVITY : 0.00%
[!!] ipsconnect_id(ipsconnect_id) has a low selectivity
[--] Index: type(type)
[--]  +-- COLUNM      : ips4db.ipb_conv_link_topics
[--]  +-- NB SEQS     : 1 sequence(s)
[--]  +-- NB COLS     : 1 column(s)
[--]  +-- CARDINALITY : 1 distinct values
[--]  +-- NB ROWS     : 285724 rows
[--]  +-- TYPE        : BTREE
[--]  +-- SELECTIVITY : 0.00%
[!!] type(type) has a low selectivity
[--] Index: moved_redirects(moved_on)
[--]  +-- COLUNM      : ips4db.ipb_forums_topics
[--]  +-- NB SEQS     : 1 sequence(s)
[--]  +-- NB COLS     : 3 column(s)
[--]  +-- CARDINALITY : 1 distinct values
[--]  +-- NB ROWS     : 173796 rows
[--]  +-- TYPE        : BTREE
[--]  +-- SELECTIVITY : 0.00%
[!!] moved_redirects(moved_on) has a low selectivity
[--] Index: index_class_and_object_id(index_class)
[--]  +-- COLUNM      : ips4db.ipb_core_search_index
[--]  +-- NB SEQS     : 1 sequence(s)
[--]  +-- NB COLS     : 2 column(s)
[--]  +-- CARDINALITY : 11 distinct values
[--]  +-- NB ROWS     : 2372470 rows
[--]  +-- TYPE        : BTREE
[--]  +-- SELECTIVITY : 0.00%
[!!] index_class_and_object_id(index_class) has a low selectivity
[--] Index: item(index_class)
[--]  +-- COLUNM      : ips4db.ipb_core_search_index
[--]  +-- NB SEQS     : 1 sequence(s)
[--]  +-- NB COLS     : 2 column(s)
[--]  +-- CARDINALITY : 11 distinct values
[--]  +-- NB ROWS     : 2372470 rows
[--]  +-- TYPE        : BTREE
[--]  +-- SELECTIVITY : 0.00%
[!!] item(index_class) has a low selectivity

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2d 13h 53m 14s (10M q [47.596 qps], 391K conn, TX: 103G, RX: 33G)
[--] Reads / Writes: 93% / 7%
[--] Binary logging is disabled
[--] Physical Memory     : 15.5G
[--] Max MySQL memory    : 2.7G
[--] Other process memory: 2.7G
[--] Total buffers: 800.0M global + 13.2M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[--] Global Buffers
[--]  +-- Key Buffer: 512.0M
[--]  +-- Max Tmp Table: 16.0M
[--] Query Cache Buffers
[--]  +-- Query Cache: ON - ON DEMAND
[--]  +-- Query Cache Size: 0B
[--] Per Thread Buffers
[--]  +-- Read Buffer: 2.0M
[--]  +-- Read RND Buffer: 1.0M
[--]  +-- Sort Buffer: 2.0M
[--]  +-- Thread stack: 256.0K
[--]  +-- Join Buffer: 8.0M
[OK] Maximum reached memory usage: 972.2M (6.12% of installed RAM)
[OK] Maximum possible memory usage: 2.7G (17.63% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (16/10M)
[OK] Highest usage of available connections: 8% (13/151)
[OK] Aborted connections: 0.00%  (2/391202)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (4K temp sorts / 867K sorts)
[!!] Joins performed without indexes: 14174
[!!] Temporary tables created on disk: 76% (146K on disk / 193K total)
[OK] Thread cache hit rate: 99% (13 created / 391K connections)
[!!] Table cache hit rate: 4% (431 open / 9K opened)
[OK] Open file limit used: 54% (563/1K)
[OK] Table locks acquired immediately: 99% (11M immediate / 11M locks)

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

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

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 33.3% (178M used / 536M cache)
[OK] Key buffer size / total MyISAM indexes: 512.0M/1.9G
[OK] Read Key buffer hit rate: 100.0% (2B cached / 747K reads)
[OK] Write Key buffer hit rate: 96.8% (56M cached / 1M writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Buffers
[--]  +-- InnoDB Buffer Pool: 128.0M
[--]  +-- InnoDB Buffer Pool Instances: 1
[--]  +-- InnoDB Additional Mem Pool: 8.0M
[--]  +-- InnoDB Log File Size: 5.0M(7.8125 % of buffer pool)
[--]  +-- InnoDB Log File In Group: 2
[--]  +-- InnoDB Total Log File Size: 10.0M
[--]  +-- InnoDB Log Buffer: 8.0M
[--]  +-- InnoDB Log Buffer Free: 6.7K
[--]  +-- InnoDB Log Buffer Used: 8.0K
[--] InnoDB Thread Concurrency: 0
[!!] InnoDB File per table is not activated
[OK] InnoDB buffer pool / data size: 128.0M/13.9M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (7.8125 %): 5.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.94% (2305256 hits/ 2306582 total)
[!!] InnoDB Write Log efficiency: 76.7% (14431 hits/ 18814 total)
[OK] InnoDB log waits: 0.00% (0 waits / 4383 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.7% (30M cached / 102K 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/log/mariadb/mariadb.log file
    Run OPTIMIZE TABLE to defragment tables for better performance
      OPTIMIZE TABLE ips4db.ipb_core_cache; -- can free 122.209255218506 MB
    Total freed space after theses OPTIMIZE TABLE : 122.209255218506 Mb
    Check all table collations are identical for all tables in ips4db database.
    Limit charset for column to one charset if possible for ips4db database.
    Limit collations for column to one collation if possible for ips4db database.
    Enable the slow query log to troubleshoot bad queries
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    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
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (1024) variable
    should be greater than table_open_cache (431)
Variables to adjust:
    query_cache_size (>= 8M)
    join_buffer_size (> 8.0M, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 431)
    innodb_file_per_table=ON
    innodb_log_file_size should be equals to 1/4 of buffer pool size (=32M) if possible.

 

[ASTRAPI]

Almost all settings seems to be wrong :-(

In my opinion you will need a full optimization of system and your database....

I usually recommend some basic adjustments but in your case it seems that you need a full restructure of your my.cnf and as i offer it as a service i can't help more.Sorry.

If you need me to give a closer look just send me a PM.

But you can wait from other users for any recommendations that may help .

 

[Jirinex]

Well, thanks I guess,

I can't afford at the moment such service and it's not overly critical as the site still works just fine. Obviously it can work much better so I am hoping for any other recommendations.

[ASTRAPI]

Check your PM :-)