Tuning performance Mariadb/ MySQL

Add more config (maybe at /etc/my.cnf.d/server.conf):

[mariadb]
# Increase data & index buffer on RAM:
innodb_buffer_pool_size = 768M
# Enable query cache
query_cache_type = 1
query_cache_limit = 2M
query_cache_size = 512M
# Timeout idle connetions
wait_timeout=60

max_connections = 200 # 150-200 a good value
thread_cache_size = 200 # 150-200 a good value. 4x->8x of max_connections is good

With a powerful server, can adjust I/O:

innodb_io_capacity = 2000 # default = 200
innodb_read_io_threads = 64 # default = 4
innodb_thread_concurrency = 0
innodb_write_io_threads = 64 # default = 4

How to find the good innodb_buffer_pool_size number? Here is Recommended InnoDB Buffer Pool Size:

SELECT CEILING(Total_InnoDB_Bytes/POWER(1024,2)) AS RIBPS_MB 
FROM (
  SELECT SUM(data_length+index_length) AS Total_InnoDB_Bytes
  FROM information_schema.tables 
  WHERE engine='InnoDB'
) A;

Or adjust more 10%:

SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
    SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    FROM
    (
        SELECT SUM(data_length+index_length)*1.1*growth RIBPS
        FROM information_schema.tables AAA,
        (SELECT 1 growth) BBB
        WHERE ENGINE='InnoDB'
    ) AA
) A;

After your DB run with new config in a period time (daily, weekly,…), check how many memory in used. This Data_MB number can be scale more 25% for adjusting your new config:

SELECT (PagesData*PageSize)/POWER(1024,2) Data_MB 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;

Write file to show global status:

SELECT * FROM information_schema.GLOBAL_STATUS INTO OUTFILE '/var/lib/mysql/global_status.txt';
SELECT * FROM information_schema.GLOBAL_VARIABLES INTO OUTFILE '/var/lib/mysql/global_variables.txt';