My.cnf examples

[root@kiloccnp ~]# cat /etc/my.cnf
# On Linux you can copy this file to /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options
# (@localstatedir@ for this installation) or to
# ~/.my.cnf to set user-specific options.

[mysqld]
datadir=/usr/local/mysql/data
#tmpdir=/var/log/mysqld/
tmpdir=/db/mytmp
log-error=/var/log/mysqld/mysqld.err
# as of MySQL 5.1.29, log-slow-queries is deprecated, use the 2 options below
#log-slow-queries=/var/log/mysqld/mysqld-slow.log
slow-query-log=1
slow-query-log-file=/var/log/mysqld/mysqld-slow.log
performance_schema_consumer_events_statements_history_long = ON

bind-address=0.0.0.0

# query that are not using indexes
log-queries-not-using-indexes=off
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysql.sock
#port=3306
# Enable binary logging. The server logs all statements that change data to the binary log
#log-bin=/var/log/mysqld/mysqld-bin
master-info-file=/var/log/mysqld/master.info
relay-log=/var/log/mysqld/relay-bin
relay-log-index=/var/log/mysqld/relay-bin.index
relay-log-info-file=/var/log/mysqld/relay-log.info
log-warnings=1

default-storage-engine=InnoDB

# External locking is the use of file system locking to manage contention for database tables by multiple processes.
skip-external-locking
skip-name-resolve

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION"
# The number of open tables for all threads.
# max_connections*3 (3 tables in average: members and history and temp)
# For example, for 200 concurrent running connections, you
# should have a table cache size of at least 200*N, where N is the maximum number of
# tables per join in any of the queries which you execute.
table_open_cache=3000
table_open_cache_instances=1
# The open_files_limit should typically be set to at least 2x-3x
# that of table_open_cache.
open-files-limit=3000

# Disable query_cache
# query_cache_type 0 means OFF please set query_cache_size=0, 1 means ON, 2 means DEMAND
# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. Having the query
# cache enabled may result in significant speed improvements, if your
# have a lot of identical queries and rarely changing tables. See the
# "Qcache_lowmem_prunes" status variable to check if the current value
# is high enough for your load.
# Note: In case your tables change very often or if your queries are
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
# changed on 29/10
query_cache_type=0
query_cache_limit=256k
query_cache_min_res_unit = 2k
query_cache_size=120M

# (2 to 4) * Nb of CPUs
thread_concurrency=12

# The size of the buffer used for index blocks bufferized for MyISAM tables and are shared by all threads.
# Set up to 30-40% of available memory if you use MyISAM tables exclusively
# key_buffer_size is important for MyISAM temporary tables performance to avoid OS writes
key_buffer_size=200M

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
# WARNING : take care of the overall memory (check below section
max_connections=1000

##################################################
##################################################
# Allocated Per Thread/Connection

# Only for MyISAM : http://venublog.com/2007/11/07/load-data-infile-performance/
bulk_insert_buffer_size=16M
# A buffer used for the sort result set allocated for each request. This can speed up ORDER BY and GROUP BY queries.
# Check sort_merge_passes in SHOW STATUS
sort_buffer_size = 8M
# Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans.
read_buffer_size=10M
# Used after a sort, when reading rows in sorted order (allocated for each thread) - Default 256k
read_rnd_buffer_size = 8M
# A buffer used for full join. When there are large joins without indexes, increase this buffer size to improve the efficiency.
# If the query joins several tables this way, you'll get several join buffers allocated
# The buffer is allocated all at once
join_buffer_size=32M
# Temp tables too
myisam_sort_buffer_size=16M
# The maximum size of one packet or any generated/intermediate string (allocated only if needed)
# This value is only important for files_data SQL table where are stored longblog entries
max_allowed_packet=16M

##################################################
##################################################

# the 2 below are mandatory for compression (IBD files are created under each database directory)
# To avoid uncontrolled innodb main tablespace growth which you can't reclaim.
innodb_file_per_table=1
# The "Barracuda" file format improves efficiency for storing large variable-length columns, and enables table compression
innodb_file_format=Barracuda
# only necessary when using binary logs
innodb_support_xa=0
# keep using the system memory allocator (better for multi-table joins). As of MySQL 5.6.3, innodb_use_sys_malloc is deprecated and will be removed in a future MySQL release
#innodb_use_sys_malloc=1
# strongly recommended to use slow shutdown
#innodb_fast_shutdown=0

# The innodb_io_capacity parameter sets an upper limit, per buffer pool instance, on the I/O activity performed by the InnoDB
# background tasks, such as flushing pages from the buffer pool and merging data from the insert buffer. The default value is 200.
innodb_io_capacity=400

# The number of background I/O threads used for reads, keep default here
innodb_read_io_threads=4
# The number of background I/O threads used for writes, keep default here
innodb_write_io_threads=4

# The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables
# you may set this to up to 80% of the machine physical memory size.
innodb_buffer_pool_size=1024M
# The number of regions that the InnoDB buffer pool is divided into. As of MySQL 5.6.6, the default is 8
innodb_buffer_pool_instances=2
# Affect the recovery time
# Set .._log_file_size to 25 % of buffer pool size, but no bigger than 128M-256M
innodb_log_file_size=2048M
# The number of log files in the log group. InnoDB writes to the files in a circular fashion. The default (and recommended) value is 2.
innodb_log_files_in_group=2
# 2* (Nb of CPUs) + number of disks for innodb (to be compared with thread_concurrency param)
innodb_thread_concurrency=12
# Cache for the Innodb internal data dictionary. The default value is 8MB. Deprecated as of MySQL 5.6.3
innodb_additional_mem_pool_size = 16M
# Each update transaction commit (or each statement outside of transaction) will need to flush log to the disk
# This can be tuned for less write on disk but is insecure (is not ACID compliant).
# The database does all the buffering and each write system call will hit the disk immediately with no need for
# a flush system call ever.
innodb_flush_log_at_trx_commit=0
innodb_flush_method = O_DIRECT
innodb_doublewrite = 0
# The number of background threads devoted to the InnoDB purge operation. Running the purge operation in its own
# thread can reduce internal contention within InnoDB, improving scalability. Currently, the performance gain might
# be minimal because the background thread might encounter different kinds of contention than before.
innodb_purge_threads=1

# This variable is relevant only if you use multiple tablespaces in InnoDB. It specifies the maximum number of
# .ibd files that InnoDB can keep open at one time. The minimum value is 10. The default value is 300.
innodb_open_files=400

# A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at
# most this many seconds before issuing the following error:
# ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
innodb_lock_wait_timeout=120

# Set the amount of memory allocated to the buffer storing InnoDB write-ahead log entries.
# For large transactions, the log can be loaded into the log buffer instead of writing log
# to the log files on disk until the log buffer is flushed on each transaction commit.
innodb_log_buffer_size=8M

# we still need the shared tablespace
innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:100M:autoextend

# How many threads we should keep in a cache for reuse.
thread_cache_size=16

# When a tmp table exceeds that size mysql automatically converts it to a temp on disk table
# You can compare the number of internal on-disk temporary tables
# created to the total number of internal temporary tables created by
# comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.
tmp_table_size=4G
max_heap_table_size=4G

# The number of seconds the server waits for activity on a non-interactive connection before closing it.
# default: 28800 (8 hours)
wait_timeout = 14400

# Log queries that last for more than 45 seconds
long_query_time=45

# The number of days for automatic binary log file removal (see also PURGE BINARY LOGS)
expire_logs_days=15

# For the replication
# to accept NOT DETERMINISTIC functions
#log-bin-trust-function-creators=1
#server-id=1
#binlog-ignore-db=mysql
#slave-skip-errors=all
#master-host=10.10.1.4
#master-user=replicate
#master-password=
#replicate-ignore-db=mysql
#master-connect-retry=1
max-binlog-size=100M

# For text encoding
skip-character-set-client-handshake
character_set_server=utf8
collation_server=utf8_general_ci

# Set default week format to ensure compatibility between actrule application and mySQL
# value:3 -> c.setMinimalDaysInFirstWeek(4); c.setFirstDayOfWeek(Calendar.MONDAY); cf "first_day_of_week" parameter in actrule application
# value:6 -> c.setMinimalDaysInFirstWeek(4); c.setFirstDayOfWeek(Calendar.SUNDAY); cf "first_day_of_week" parameter in actrule application
default_week_format = 3

# MySQL Enterprise Monitor
performance_schema_consumer_events_statements_history_long = ON


[mysqld_safe]
datadir=/usr/local/mysql/data
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysql.sock
user=mysql

[client]
socket=/var/run/mysqld/mysql.sock
# For text encoding
default-character-set=utf8

[mysqladmin]
socket=/var/run/mysqld/mysql.sock
password = XXX
user = XXX
[root@kiloccnp ~]#