facebook twitter youtube
Replication Query Optimization DB Tuning Server Load and Query Locking Linux Download Interview Questions Basic DB Commands Mysql
in DB Tuning - 04 Dec, 2013
by Zafar Malik - no comments
Server Configuration as per Mysql5.5 and DB Tuning related variables
  • port: port for connectivity default is 3306.
  • user: user for connection default is mysql.
  • datadir: Path where mysql data exists. Default path is /var/lib/mysql else you can set your own path in configuration file.
  • socket: where socket file will be stored for connection. Its default path is data directory else what you mention in config file. Mysql.sock is worked as connector at the time of connecting mysql from localhost (server).
  • log-error: where server logs will be stored. Default path is data directory else you can set in config file.
  • pid-file: mysql writes its process id in pid file and its default path is data directory else you can give your own path in configuration file.
  • default_storage_engine: By this we can set default storage engine for server. Default is innodb.
  • innodb_log_file_size: It will store dirty pages, which used at the time of crash recovery. Default is 5M and it is enough but it could not be more than 4 GB.
  • innodb_flush_log_at_trx_commit: In which way you want to flush uncommit logs from logfiles. default is 1 other options are as per following: 0:log files to be written and flushed to disk once per second., 1:causes the log buffer to be written and flushed to disk after every transaction commit. , 2: which causes the log buffer to be written after every transaction commit and flushes the log files to disk once per second. A value of 2 means that MySQL might think that some changes are written to the log file, but do not persist in the log file after an operating system crash, because the log file was not flushed to disk before a crash.
  • skip-name-resolve: Use IP addresses rather than host names when creating grant table entries. This option can be useful if your DNS does not work. It is to fast net speed as MySQL grants authentication on users based on IP address or hostnames. When there is a connection from a particular host, MySQL does a reverse DNS lookup and compares the hostname and IP address.
  • symbolic-links: Disabling symbolic-links is recommended to prevent assorted security risks
  • lower_case_table_names: If set to 0 (which is default), table names are stored as specified and comparisons are case sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive. If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases.
  • old_passwords: To enable old password format. Default is disable means 0 set 1 to enable it.
  • binlog_format: This variable sets the binary logging format on MASTER server, and can be any one of STATEMENT, ROW, or MIXED. Default option is STATEMENT.
  • innodb_file_per_table: To store tables in separate ibd file.
  • innodb_buffer_pool_size: The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The default value is 8MB. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. However, do not set it too large because competition for physical memory might cause paging in the operating system. Also, the time to initialize the buffer pool is roughly proportional to its size. On large installations, this initialization time may be significant. For example, on a modern Linux x86_64 server, initialization of a 10GB buffer pool takes approximately 6 seconds.
  • key_buffer_size: (for MyISAM it works like buffer_pool_size) Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.
  • sort_buffer_size: Default is 2MB. Each session that needs to do a sort allocates a buffer of this size. sort_buffer_size is not specific to any storage engine and applies in a general manner for optimization. If you see many Sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or improved indexing. The entire buffer is allocated even if it is not all needed, so setting it larger than required globally will slow down most queries that sort. It is best to increase it as a session setting, and only for the sessions that need a larger size. On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values. Experiment to find the best value for your workload. See Section C.5.4.4, “Where MySQL Stores Temporary Files”. The maximum permissible setting for sort_buffer_size is 4GB. Values larger than 4GB are permitted for 64-bit.
  • join_buffer_size: Default is 128K. 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. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary. There is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change to a larger setting only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it. The maximum permissible setting for join_buffer_size is 4GB. Values larger than 4GB are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB with a warning). Prior to MySQL 5.5.3, the minimum allowed value for this variable was 8200 for 32-bit platforms and 8228 for 64-bit platforms. In MySQL 5.5.3 and later, the minimum is 128 for all platforms.
  • max_allowed_packet: Default value is 1M. The maximum size of one packet or any generated/intermediate string. You must increase this value if you are using large BLOB columns or long strings.
  • thread_cache_size: How many threads the server should cache for reuse. When a client disconnects, the client’s threads are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. Normally, this does not provide a notable performance improvement if you have a good thread implementation. However, if your server sees hundreds of connections per second you should normally set thread_cache_size high enough so that most new connections use cached threads.
  • open_files_limit: how many maximum files should be open.
  • table_cache: The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable. See Section 5.1.6, “Server Status Variables”. If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_cache variable
  • max_connect_errors: Default is 10. 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. You can unblock blocked hosts by flushing the host cache. To do so, issue a FLUSH HOSTS statement or execute a mysqladmin flush-hosts command. If a connection is established successfully within fewer than max_connect_errors attempts after a previous connection was interrupted, the error count for the host is cleared to zero. However, once a host is blocked, flushing the host cache is the only way to unblock it.
  • wait_timeout: The number of seconds the server waits for activity on a non interactive connection before closing it. Default value is 28800 seconds.
  • connect_timeout: The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 seconds as of MySQL 5.0.52 and 5 seconds before that.
  • innodb_thread_concurrency: InnoDB tries to keep the number of operating system threads concurrently inside InnoDB less than or equal to the limit given by this variable. Once the number of threads reaches this limit, additional threads are placed into a wait state within a FIFO queue for execution. Threads waiting for locks are not counted in the number of concurrently executing threads. Formula to calculate it is 2*(num_cpus+num_disks)
  • innodb_read_io_threads The number of I/O threads for read operations in InnoDB. The default value is 4
  • innodb_write_io_threads The number of I/O threads for write operations in InnoDB. The default value is 4.
  • safe-show-database: With this option, the SHOW DATABASES command returns only those databases for which the user has some kind of privilege. From version 4.0.2 this option is deprecated and doesn’t do anything (the option is enabled by default)

 

  • Below 3 variables are linked with each other:

 

  1. query_cache_type: Default is 1 and other options are o (OFF) and 1 (ON) and 2 (Demand) where 0 means Do not cache results in or retrieve results from the query cache. Note that this does not deallocate the query cache buffer. To do that, you should set query_cache_size to 0, 1: Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE., 2: Cache results only for cacheable queries that begin with SELECT SQL_CACHE
  2. query_cache_size: The amount of memory allocated for caching query results. The default value is 0, which disables the query cache. To reduce overhead significantly, you should also start the server with query_cache_type=0 if you will not be using the query cache. The permissible values are multiples of 1024; other values are rounded down to the nearest multiple. Note that query_cache_size bytes of memory are allocated even if query_cache_type is set to 0. Special: When _any_ change (INSERT/UPDATE/…) occurs to a table _all_ entries in the QC that mention that table must be purged from the QC. The bigger the QC (query_cache_size), the longer this takes. It can take a long time to scan 2GB checking for entries involving a table, so can max. 100 MB if really required.
  3. query_cache_limit: Do not cache results that are larger than this number of bytes.

 

  • Below 2 variables are linked with each other:

 

  1. tmp_table_size: Default value is 16M. The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.) If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory. This variable does not apply to user-created MEMORY tables.
  2. max_heap_table_size: Default value is 16M. This variable sets the maximum size to which user-created MEMORY (HEAP) tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE, or altered with ALTER TABLE or TRUNCATE TABLE. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value.

 

 

 

  • Below 2 variables are linked with each other:

 

  1. max_connections: The maximum permitted number of simultaneous client connections. By default, this is 100
  2. max_user_connections: The maximum number of simultaneous connections permitted to any given MySQL user account. A value of 0 (the default) means no limit.

 

 

 

  • Below 3 variables related with slow query log are linked with each other:

 

  1. slow_query_log: By this we can store slow queries in a log file. Default value is 0 means disable we can enable it by setting it to 1.
  2. long_query_time: time in seconds after which query will be goes to slowlog. Default value is 10 seconds.
  3. log-slow-queries: Where slowlogs will be stored. Default path is /var/log/slowmysqld.log

 

 

 

  • Below 2 variables related with general log are linked with each other:

 

  1. general_log: By this we can store all queries in a log file. Default value is 0 means disable we can enable it by setting it to 1.
  2. general_log_file: Where general logs will be stored. Default path is /var/log/mysqldquery.log