MariaDB Config Help

[TDBF]

We upgraded our database to use MariaDB from MySQL on the recommendation of a few posters within this forum.

I'm not a DB expert and the other day we looked at the my.cnf and found that there was nothing in it. So, basically we are running on a box standard install for our server.

I would be pretty grateful if someone with a lot more knowledge on this subject would have a look at the MySQLTuner recommendations and give us a starting point to get us running a bit better.

This is the MySqlTuner recommendations

Quote

# chmod u+x mysqltuner.pl
# ./mysqltuner.pl
 >>  MySQLTuner 1.6.2 - 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.0.23-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM
[--] Data in MyISAM tables: 462M (Tables: 192)
[--] Data in InnoDB tables: 538M (Tables: 779)
[!!] Total fragmented tables: 102

-------- 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 5h 38m 21s (2M q [13.254 qps], 117K conn, TX: 84B, RX: 1B)
[--] Reads / Writes: 75% / 25%
[--] Binary logging is disabled
[--] Total buffers: 312.0M global + 2.8M per thread (151 max threads)
[OK] Maximum reached memory usage: 339.8M (11.98% of installed RAM)
[OK] Maximum possible memory usage: 732.0M (25.80% of installed RAM)
[OK] Slow queries: 0% (16/2M)
[OK] Highest usage of available connections: 6% (10/151)
[OK] Aborted connections: 0.01%  (7/117034)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 220K sorts)
[!!] Temporary tables created on disk: 49% (68K on disk / 139K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 2% (400 open / 16K opened)
[OK] Open file limit used: 1% (154/10K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 20.5% (27M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/100.4M
[OK] Read Key buffer hit rate: 99.6% (13M cached / 50K reads)
[!!] Write Key buffer hit rate: 42.4% (95K cached / 54K writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 24.0M/538.7M
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[!!] InnoDB Used buffer: 34.79% (534 used/ 1535 total)
[OK] InnoDB Read buffer efficiency: 97.02% (221355290 hits/ 228153287 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 411295 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

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

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Set thread_cache_size to 4 as a starting value
    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 (10000) variable
    should be greater than table_open_cache ( 400)

Variables to adjust:
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_open_cache (> 400)
    innodb_buffer_pool_size (>= 538M) if possible.
    innodb_buffer_pool_instances (=1)

And since we're using Innodb: I thought I would use the innodb.cnf as a starting point? (If I am wrong, please feel free to let me know as I am always willing to learn)

Quote

[client]
#password    = [your_password]
port        = 3306
socket        = /var/lib/mysql/mysql.sock

# *** Application-specific options follow here ***
#
# The MariaDB server
#
[mysqld]

# generic configuration options
port        = 3306
socket        = /var/lib/mysql/mysql.sock
back_log = 50
#skip-networking
max_connections = 500
max_connect_errors = 10
table_open_cache = 400
#external-locking
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 4
thread_concurrency = 8
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
#memlock
default-storage-engine = InnoDB
thread_stack = 240K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 16M
log-bin=mysql-bin
expire-logs-days  = 14
binlog_format=mixed

#log
#log_warnings
slow_query_log
long_query_time = 2
#tmpdir = /tmp

#*** MyISAM Specific options
key_buffer_size = 32M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover

# *** INNODB Specific options ***
#skip-innodb
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 538M
innodb_data_file_path = ibdata1:10M:autoextend
#innodb_data_home_dir = <directory>

innodb_write_io_threads = 8
innodb_read_io_threads = 8
#innodb_force_recovery=1
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
#innodb_fast_shutdown
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
#innodb_log_group_home_dir
innodb_max_dirty_pages_pct = 90
#innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 120
innodb_buffer_pool_instances = 1

[mysqldump]
# Do not buffer the whole result set in memory before writing it to
# file. Required for dumping very large tables
quick

max_allowed_packet = 16M

[mysql]
no-auto-rehash

# Only allow UPDATEs and DELETEs that use keys.
#safe-updates

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
# Increase the amount of open files allowed per process. Warning: Make
# sure you have set the global system limit high enough! The high value
# is required for a large number of opened tables
open-files-limit = 8192

Thanks in advance,

Cheers

 

 

 

[RevengeFNF]

@TDBF add this to your my.cnf, wait 24 hours and then post here the results of mysqltuner. Check also how your board speed is after it.

innodb_file_per_table
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
transaction-isolation = READ-COMMITTED
thread_handling = pool-of-threads
table_open_cache = 1000
join_buffer_size = 2M
read_buffer_size = 128K
sort_buffer_size = 256K
tmp_table_size = 128M
max_heap_table_size = 128M

 

[TDBF]

Thank you loads for your help. Will do this later today and post results back on Monday for you.

Thanks again and cheers,

 

 

 

[SeaTea]

On 7-2-2016 at 1:14 PM, RevengeFNF said:

Check also how your board speed is after it.

 

Did you also try using tmpfs instead of disk-based tmp files ?  I read some articles that this can speed-up mysql/mariadb operation.

# look at 'tmpdir' variable.

# change it to:
tmpdir = /tmpfs

[RevengeFNF]

2 hours ago, SeaTea said:

Did you also try using tmpfs instead of disk-based tmp files ?  I read some articles that this can speed-up mysql/mariadb operation.

# look at 'tmpdir' variable.

# change it to:
tmpdir = /tmpfs

I have tried it some time ago, but i did not see any improvements, probably because im using SSD. I also have very few % of Temp Tables to Disk.

[TDBF]

@RevengeFNF I'm sorry about the long delay in getting back to you on this subject. I was wondering if you could still have a look at this for me please?

I have included the pltuner and my.cnf as attachments.

Thanks, for any help you could provide

my.cnf.txt

tuner.txt

[TDBF]

On 19/02/2016 at 10:41 AM, SeaTea said:

Did you also try using tmpfs instead of disk-based tmp files ?  I read some articles that this can speed-up mysql/mariadb operation.

# look at 'tmpdir' variable.

# change it to:
tmpdir = /tmpfs

We have SDS, would this make any real difference?

[ABGenc]

@TDBF do you also use tuning-primer.sh ?  I have found out that this script give much better advices.

[RevengeFNF]

Just now, ABGenc said:

@TDBF do you also use tuning-primer.sh ?  I have found out that this script give much better advices.

Tuning-primer is very old and not prepared for the new versions of mysql. Mysqltuner has become good in the last versions.

1 hour ago, TDBF said:

@RevengeFNF I'm sorry about the long delay in getting back to you on this subject. I was wondering if you could still have a look at this for me please?

I have included the pltuner and my.cnf as attachments.

Thanks, for any help you could provide

my.cnf.txt

tuner.txt

Ok, later today when i have some time i'll give it a look to it.

[TDBF]

2 minutes ago, RevengeFNF said:

Tuning-primer is very old and not prepared for the new versions of mysql. Mysqltuner has become good in the last versions.

Ok, later today when i have some time i'll give it a look to it.

I really appreciate this, if I can return the favour I will

13 minutes ago, ABGenc said:

@TDBF do you also use tuning-primer.sh ?  I have found out that this script give much better advices.

Thanks, I already knew about this, but was recommend MySqlTuner.

How did you get on with installing Memcached and Memcache?

[ABGenc]

13 minutes ago, TDBF said:

How did you get on with installing Memcached and Memcache?

I have given up  there is no proper guide for WHM/Cpanel and Apache 2.4 configuration.

[ASTRAPI]

There are a lot more to optimize than the ones that the scripts recommends....

[TDBF]

2 hours ago, ASTRAPI said:

There are a lot more to optimize than the ones that the scripts recommends....

I assumed that would be the case. Unfortunately, my 25 years experience in development means squat when it comes to server management lol

3 hours ago, ABGenc said:

I have given up  there is no proper guide for WHM/Cpanel and Apache 2.4 configuration.

I'm quiet busy at the moment, but if you wish, I could help you install this during the weekend some time?

[ABGenc]

1 hour ago, TDBF said:

I'm quiet busy at the moment, but if you wish, I could help you install this during the weekend some time?

That would be great. I am just willing to see how much it will differ with memcache.. Actually I am a tech guy and I would love to do it myself but there are restrictions for memcache/Apache 2.4/WHM which is not well documented in the internet..

[RevengeFNF]

@TDBF you are using a default template from Centminmod. Too much confused for me lol. I have done a new one i think you should try and check how it goes, based on what i saw from your mysqltuner output(not recommendations).

[mysqld]
symbolic-links=0
max_connections=200
max_user_connections=200
wait_timeout=300
interactive_timeout=300
skip-name-resolve
max-connect-errors=1000000

#Slow Queries
slow_query_log = 0
long_query_time=5
log_slow_verbosity=Query_plan,explain,Innodb

#InnoDB
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size =2G
innodb_log_file_size=256M
innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_buffer_pool_instances=2
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
transaction-isolation = READ-COMMITTED
innodb-defragment=0
innodb_file_format=BARRACUDA
innodb_large_prefix=1

#MyISAM Aria
key_buffer_size=32M
aria_pagecache_buffer_size=256M
ft_min_word_len=3

#Thread Cache
thread_handling = pool-of-threads

#Table cache
table_open_cache=1000
open_files_limit=2000

#Buffers
join_buffer_size=2M
read_buffer_size=128K
sort_buffer_size=256K

#Query Cache
query_cache_type=1
query_cache_size=128M
query_cache_limit=6M
query_cache_strip_comments=1

#Temporary Tables
tmp_table_size=256M
max_heap_table_size=256M

 

[TDBF]

7 hours ago, RevengeFNF said:

@TDBF you are using a default template from Centminmod. Too much confused for me lol. I have done a new one i think you should try and check how it goes, based on what i saw from your mysqltuner output(not recommendations).

[mysqld]
symbolic-links=0
max_connections=200
max_user_connections=200
wait_timeout=300
interactive_timeout=300
skip-name-resolve
max-connect-errors=1000000

#Slow Queries
slow_query_log = 0
long_query_time=5
log_slow_verbosity=Query_plan,explain,Innodb

#InnoDB
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size =2G
innodb_log_file_size=256M
innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_buffer_pool_instances=2
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
transaction-isolation = READ-COMMITTED
innodb-defragment=0
innodb_file_format=BARRACUDA
innodb_large_prefix=1

#MyISAM Aria
key_buffer_size=32M
aria_pagecache_buffer_size=256M
ft_min_word_len=3

#Thread Cache
thread_handling = pool-of-threads

#Table cache
table_open_cache=1000
open_files_limit=2000

#Buffers
join_buffer_size=2M
read_buffer_size=128K
sort_buffer_size=256K

#Query Cache
query_cache_type=1
query_cache_size=128M
query_cache_limit=6M
query_cache_strip_comments=1

#Temporary Tables
tmp_table_size=256M
max_heap_table_size=256M

 

I believe you are correct about using a template from there. That's what happens when you don't have a clue about setting up Databases configs.

I really appreciate you taking your time to do this, thank-you

 

[TDBF]

@RevengeFNF

Hi m8,

Tried this config and MariaDB died on its face due to an issue.

This was the error message we got when we tried to restart MySQL with the above config.

Quote

160619 21:47:14 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2016-06-19 21:47:14 140260490676256 [Note] /usr/sbin/mysqld (mysqld 10.1.14-MariaDB) starting as process 22323 ...
2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2016-06-19 21:47:14 140260490676256 [Note] InnoDB: The InnoDB memory heap is disabled
2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Memory barrier is not used
2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Using Linux native AIO
2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Using SSE crc32 instructions
2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Initializing buffer pool, size = 2.0G
InnoDB: mmap(1107296256 bytes) failed; errno 12
InnoDB: Error: Block 0x7f90ecd2e1e8 incorrect state BUF_BLOCK_POOL_WATCH in buf_LRU_block_free_non_file_page()
160619 21:47:14 [ERROR] mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

I tried to do a search for a possible solution, but never came across anything that stood out.

Thanks,

[RevengeFNF]

If you use the previous config, starts to work again?

[TheDarkBlues]

5 minutes ago, RevengeFNF said:

If you use the previous config, starts to work again?

Yeah we had to revert back to that one. But we are having a ton of issues with memory at the moment with that config

Thanks,

Sorry, on another account

[ASTRAPI]

Your issue is related to physical ram (you need more ram) but you can try to resolve it by adding a swap file of 512MB on your system.....

Search on the net of how to do it as there is a ton of related info

 

[TDBF]

15 hours ago, RevengeFNF said:

If you use the previous config, starts to work again?

I'm sorry, this was our fault. I did a recheck and realised that something was added at the bottom, I removed the offending lines and it is working.

Sorry about that.

[RevengeFNF]

19 hours ago, TDBF said:

I'm sorry, this was our fault. I did a recheck and realised that something was added at the bottom, I removed the offending lines and it is working.

Sorry about that.

Can you post the output of mysqltuner with the new conf? 

[TDBF]

On 22/06/2016 at 3:55 AM, RevengeFNF said:

Can you post the output of mysqltuner with the new conf? 

Sorry about the delay,

We have had a lot of issues with our server using tons of memory and have since moved back to previous one in the hope that it helps the issue and so far we have had no problems. I will leave it until tomorrow to post another mysqltuner.