Several parameters required for MySQL database Optimization

Step 1:
1: high-speed hard drive (7200 RPM). In theory, 7200 channels are searched per second. there is no way to change this. The optimization method is to use multiple hard disks or store data separately.

2: The read/write speed of the hard disk, which is very fast and easier to solve-it can be read and written in parallel from multiple hard disks.

3: The CPU. The CPU processes data in the memory. This is the most common limiting factor when there is a table with a relatively small memory.

4: Memory limit. When the CPU needs to exceed the data suitable for the CPU cache, the cache bandwidth becomes a bottleneck in the memory. However, the memory size is surprisingly large, and this problem will not occur.

Step 2: (I am using the Linux platform of the school website (Linux advx.mandrakesoft.com 2.4.3-19mdk ))

1: Adjust server Parameters

Use the shell> mysqld-HELP command to output the following information:

Possible variables for option -- Set-variable (-O) are:

Back_log current value: 5 // the number of connections that MySQL can have. back_log indicates how many connection requests can be stored in the stack during the time when MySQL suspends the connection.

Connect_timeout current value: 5 // MySQL Server waits for a connection time before responding with bad handshake

Delayed_insert_timeout current value: 200 // The time for an insert delayed to wait for the Insert before termination

Delayed_insert_limit current value: 50 // The insert delayed processor checks whether any select statements are not executed. If yes, run these statements before resuming.

Delayed_queue_size current value: 1000 // the size of the queue allocated for insert delayed

Flush_time current value: 0 // if it is set to a non-0 value, all tables are closed for each flush_time.

Interactive_timeout current value: 28800 // time the server waits for a foreign interactive connection before closing it

Join_buffer_size current value: 131072 // the buffer size connected to all

Key_buffer_size current value: 1048540 // the buffer size of the clause index block. You can increase it to better process the index.

Lower_case_table_names current value: 0 //

Long_query_time current value: 10 // If a query takes more time than this time, the slow_queried count will increase

Max_allowed_packet current value: 1048576 // size of a package

Max_connections current value: 300 // number of concurrent connections allowed

Max_connect_errors current value: 10 // if there are more than this number of interrupted connections, further connections will be blocked. You can use flush hosts to solve this problem.

Max_delayed_threads current value: 15 // Number of insert delayed processes that can be started

Max_heap_table_size current value: 16777216 //

Max_join_size current value: 4294967295 // Number of connections that can be read

Max_sort_length current value: 1024 // number of bytes used for sorting blob or text

Max_tmp_tables current value: 32 // number of temporary tables simultaneously opened by a connection

Max_write_lock_count current value: 4294967295 // specify a value (usually very small) to start mysqld, so that the read lock occurs after a certain number of write locks.

Net_buffer_length current value: 16384 // communication buffer size -- reset to this size during Query

Query_buffer_size current value: 0 // buffer size during Query

Record_buffer current value: 131072 // size of the buffer allocated to each table scanned by connections for each Sequential Scan

Sort_buffer current value: 2097116 // size of the buffer allocated for each sort connection

Table_cache current value: 64 // number of tables opened for all connections

Thread_concurrency current value: 10 //

Tmp_table_size current value: 1048576 // temporary table size

Thread_stack current value: 131072 // the size of each thread

Wait_timeout current value: 28800 // time the server waits for a connection before closing it 3

Configuring the above information as needed will help you