dapurhosting.com Blog for Tech

March 23, 2012

MySql Configuration

Filed under: MySql — dh @ 8:40 am

[mysqld]
safe-show-database
skip-locking

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
skip-networking

##used in replication to give each master and slave a unique identity
server-id=1

##amount of seconds during inactivity that MySQL will wait before it will close a connection on a non-interactive connection.
wait_timeout=5

##amount of seconds during inactivity that MySQL will wait before it will close a connection on interactive connection, interactive is mysql shell sessions
interactive_timeout=28800

##The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake
connect_timeout=5

##If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections
max_connect_errors=10

#The maximum number of simultaneous connections permitted to any given MySQL user account
max_user_connections=15

##This value indicates how many maximum concurrent connections mysql server can handle. If mysql reaches to it maximum (max) limit then you can see errors like "too many connections".
max_connections=200

##key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.
key_buffer_size=128M

#The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans.
join_buffer_size=1M

##Each session that needs to do a sort allocates a buffer of this size
sort_buffer_size=2M

##Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans.
read_buffer_size=131072

#The maximum size of one packet or any generated/intermediate string.
max_allowed_packet=2M

##The number of open tables for all threads
table_open_cache=2048

##which sets the number of threads to hold open in memory to service new connections
thread_cache_size=16

##enables applications to give the threads system a hint about the desired number of threads that should be run at the same time.
thread_concurrency=10

##Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE.
query_cache_type=1

##Do not cache results that are larger than this number of bytes
query_cache_limit=1M

##The amount of memory allocated for caching query results
query_cache_size=128M

##The number of open tables for all threads
table_cache=5120

tmp_table_size=32M
max_heap_table_size=64M

innodb_buffer_pool_size=64M
innodb_additional_mem_pool_size=8M
innodb_log_file_size=8M

##If a query takes longer than this many seconds, the server increments the Slow_queries status variable
long_query_time=1

##to enable or disable the slow query log
slow_query_log=1
slow-query-log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log

No Comments

No comments yet.

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress