My SQL Optimization

My SQL Optimization


You can edit this in the my.cnf file

A typical configuration that can be used by a shared server is given below.


[mysqld]

max_connections = 800

The number of simultaneous client connections allowed.

key_buffer = 36M

Index blocks for MyISAM and ISAM tables are buffered and are shared by all threads.

myisam_sort_buffer_size = 64M

The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.

join_buffer_size = 2M
The 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

Increase the value ofjoin_buffer_size to get a faster full join when adding indexes is not possible.

read_buffer_size = 2M

Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value.

sort_buffer_size = 3M

Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY orGROUP BY operations.

table_cache = 1024

The number of open tables for all threads. Increasing this value increases the number of file descriptors thatmysqld requires. You can check whether you need to increase the table cache by checking the Opened_tablesstatus variable

thread_cache_size = 286

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


interactive_timeout = 25

The number of seconds the server waits for activity on an interactive connection before closing it

wait_timeout = 1800

The number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made via named pipes, or shared memory.

connect_timeout = 5

The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 5 seconds

max_allowed_packet = 16M

The maximum size of one packet or any generated/intermediate string.

max_connect_errors = 10

If there are more than this number of interrupted connections from a host, that host is blocked from further connections. You can unblock blocked hosts with the FLUSH HOSTS statement

query_cache_limit = 1M

Don't cache results that are larger than this number of bytes. The default value is 1MB.

query_cache_size = 16M

The amount of memory allocated for caching query results. The default value is 0, which disables the query cache. The allowable values are multiples of 1024;

query_cache_type = 1

Set the query cache type. Setting the GLOBAL value sets the type for all clients that connect thereafter. Individual clients can set the SESSION value to affect their own use of the query cache.

tmp_table_size = 16M

The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum oftmp_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.
skip-innodb

[mysqld_safe]
open_files_limit = 8192

Changes the number of file descriptors available to mysqld. You should try increasing the value of this option ifmysqld gives you the error Too many open files