my.cnf config MariaDB10
Hi,
I am trying to get the best result from mysql server, i have edited my.cnf 100 times but still i am not 100% happy with it.
I am running a 64 GB ram server with Intel" Xeon" Processor E5-1620 v2 (10M Cache, 3.70 GHz)
Mysq is MariaDB 10 with defaul storage engine ToKuDB.
I use ToKuDb because i have large databases some tables are more then 100 mill. rows.
i need fast select and insert :)
can anyone help me with a good my.cnf configuration.
any help is welcome.
my.cnf now looks like.
Thank you
# The MySQL server
[mysqld]
plugin-load=ha_tokudb.so
default_storage_engine=TokuDB
tokudb_load_save_space = 1
tokudb_commit_sync = OFF
#tokudb_fsync_log_period = 1000
#tokudb_directio = 1
tokudb_debug=0
init_connect = 'SET collation_connection = utf8_general_ci,NAMES utf8'
collation_server = utf8_general_ci
character_set_client = utf8
character_set_server = utf8
skip-character-set-client-handshake
concurrent_insert = 2
skip-external-locking
skip-name-resolve
key_buffer_size = 4G
max_allowed_packet = 512M
table_open_cache = 8192
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 32M
# Try number of CPU's*2 for thread_concurrency
innodb_buffer_pool_size = 300M
join_buffer_size = 32M
sort_buffer_size = 32M
read_buffer_size = 32M
read_rnd_buffer_size = 32M
myisam_sort_buffer_size = 32M
tmp_table_size = 1G
max_heap_table_size = 1G
bulk_insert_buffer_size = 1G
#log-slow-queries
local-infile=0
innodb_file_per_table=1
max_prepared_stmt_count=500000
slow_query_log = ON
slow_query_log_file =/var/lib/mysql/server1.host-web4u-slow.log
tokudb_tmp_dir = /var/mysqltmp/
#large-pages
#large_pages = true
#innodb_use_sys_malloc = 0
# Point the following paths to different dedicated disks
tmpdir = /var/mysqltmp/
max_connections = 2000
open_files_limit= 9000
[mysqldump]
quick
max_allowed_packet = 512M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
Thank you
-
As your using MariaDB + query_cache you might benifit from adding: query_cache_strip_comments=1 Note to those using Oracle MySQL, adding the above will [COLOR="#FF0000">stop MySQL from starting!! 0 -
Thank you kernow i will try that. Can you tell me if the other values look ok? 0 -
Can you tell me if the other values look ok?
Impossible to tell without running tests and more tests. There is no one size fits all configuration, and even when you get it as best you can you will have to make further changes in future months. Have you run any tuning script that has shown you errors? What is it that you think isn't running perfect? For more tips on the query cache check this page: [url=http://www.techinfobest.com/optimize-mysql-query_cache_size/]Optimize MySQL query_cache_size - Place of all Tech Info0 -
Hello :) I've moved this thread to our "Optimization" forum. Thank you. 0
Please sign in to leave a comment.
Comments
4 comments