Here is the original /etc/my.cnf file for a default mariadb installation:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Location of other .cnf files you can use:

/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf

Do you wonder which databases are actually taking up how much space but only have one huge ibdata1 in your /var/lib/mysql and the directories inside your mysql data directory don’t represent the actual database sizes?

Run from a mysql root console:
[bash]

mysql> SELECT table_schema AS “Database name”, SUM(data_length + index_length) / 1024 / 1024 AS “Size (MB)” FROM information_schema.TABLES GROUP BY table_schema;
+——————–+————-+
| Database name | Size (MB) |
+——————–+————-+
| information_schema | 0.00878906 |
| mysql | 0.62860394 |
| performance_schema | 0.00000000 |
| roundcube | 0.46875000 |
+——————–+————-+
8 rows in set (1.21 sec)
[bash]

Please follow the under mentioned instructions to turn off the MySQL strict mode. Make the following changes in the “my.ini/my.cnf”:

Look for the following line:

sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Change it to:

sql_mode=""

Restart the MySQL service.

or

Look for the following line , this line will set MySQL strict mode

sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

To disable MySQL strict mode, you can change the above line as follow:

sql_mode="TRADITIONAL,NO_AUTO_CREATE_USER,,NO_ENGINE_SUBSTITUTION"

Restart the MySQL service.

You may be able to run an SQL query within your database management tool such as phpMyAdmin which can normally be found from your web hosting control panel:

SET @@global.sql_mode= '';

Test

Run the following:

#mysql -uroot -p -e 'select @@GLOBAL.sql_mode;'

+------------------------+
| @@GLOBAL.sql_mode      |
+------------------------+
| TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------+

First – run mysqltuner:

]# ./mysqltuner.pl
 >>  MySQLTuner 1.6.4 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.29-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 445K (Tables: 9)
[--] Data in InnoDB tables: 204M (Tables: 13)
[!!] Total fragmented tables: 3

-------- Security Recommendations  -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'sysadmin@%' hasn't specific host restriction.
[!!] There is no basic password file list!

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 17d 19h 48m 54s (1M q [0.865 qps], 87K conn, TX: 39B, RX: 258M)
[--] Reads / Writes: 94% / 6%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 432.0M global + 6.5M per thread (151 max threads)
[OK] Maximum reached memory usage: 913.0M (49.61% of installed RAM)
[OK] Maximum possible memory usage: 1.4G (76.81% of installed RAM)
[OK] Slow queries: 0% (1/1M)
[OK] Highest usage of available connections: 49% (74/151)
[OK] Aborted connections: 2.80%  (2442/87276)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 271K sorts)
[!!] Temporary tables created on disk: 71% (80K on disk / 114K total)
[OK] Thread cache hit rate: 94% (4K created / 87K connections)
[!!] Table cache hit rate: 2% (115 open / 5K opened)
[OK] Open file limit used: 9% (99/1K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)
[OK] Binlog cache memory access: 99.95% ( 52384 Memory / 52412 Total)

-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 18.3% (49M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/291.0K
[OK] Read Key buffer hit rate: 100.0% (9M cached / 510 reads)
[!!] Write Key buffer hit rate: 1.4% (1K cached / 1K writes)

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 128.0M/204.8M
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
&#91;OK&#93; InnoDB Used buffer: 87.50% (7167 used/ 8191 total)
&#91;OK&#93; InnoDB Read buffer efficiency: 99.97% (88883891 hits/ 88912014 total)
&#91;OK&#93; InnoDB Write log efficiency: 96.95% (869671 hits/ 897014 total)
&#91;OK&#93; InnoDB log waits: 0.00% (0 waits / 27343 writes)

-------- ThreadPool Metrics --------------------------------------------------
&#91;--&#93; ThreadPool stat is disabled.

-------- AriaDB Metrics ------------------------------------------------------
&#91;--&#93; AriaDB is disabled.

-------- TokuDB Metrics ------------------------------------------------------
&#91;--&#93; TokuDB is disabled.

-------- Galera Metrics ------------------------------------------------------
&#91;--&#93; Galera is disabled.

-------- Replication Metrics -------------------------------------------------
&#91;--&#93; No replication slave(s) for this server.
&#91;--&#93; This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    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 ( 256)
Variables to adjust:
    query_cache_type (=1)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 256)
    innodb_buffer_pool_size (>= 204M) if possible.
    innodb_buffer_pool_instances (=1)

Here is what you should do. First run this query

mysql> SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
    -> (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
    -> FROM information_schema.tables WHERE engine='InnoDB') A;
+-------+
| RIBPS |
+-------+
|     1 |
+-------+
1 row in set (0.08 sec)

This will give you the RIBPS, Recommended InnoDB Buffer Pool Size (in GB)based on all InnoDB Data and Indexes with an additional 60%.

After the restart, run mysql for a week or two. Then, run this query:

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;

This will give you how many actual pages of InnoDB data reside in the InnoDB Buffer Pool.

# mysql -u root -p 

MariaDB [(none)]> wp_database -e "EXPLAIN SELECT * FROM posts"

 EXPLAIN SELECT * FROM 2qGr3sjm_posts;
+------+-------------+----------------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+----------------+------+---------------+------+---------+------+------+-------+
|    1 | SIMPLE      |          posts | ALL  | NULL          | NULL | NULL    | NULL | 2519 |       |
+------+-------------+----------------+------+---------------+------+---------+------+------+-------+

Or from command line

#  mysql -u root -p wp_database -e "EXPLAIN SELECT * FROM posts"

https://www.digitalocean.com/community/tutorials/how-to-measure-mysql-query-performance-with-mysqlslap