Performance tuning for MySQL database

MySQL Performance tuning:

The speed of the insert query:

The time to insert a record consists of the following:

Connection: (3)

Send query to server: (2)

Analysis of the query: (2)

Insert Record: (1 x record size)

Insert Index: (1 x index)

Off: (1)

The numbers here are a bit proportional to the overall time. This does not take into account the initial cost of opening a table (it is done once for each query that runs concurrently).

The size of the table slows the insertion of the index with the speed of n log n (B-tree).

Some ways to speed up insertion:

If you insert many rows from the same client at the same time, insert statements that use multiple value tables. This is faster than using separate INSERT statements (several times in some cases).

If you insert many rows from different clients, you can get a higher speed by using the Insert delayed statement.

Note that with MyISAM, if there are no deleted rows in the table, the rows can be inserted while the select:s is running.

When loading a table from a text file, use the load DATA INFILE. This is usually 20 times times faster than using many INSERT statements. When a table has many indexes, it is possible to do more work to make the load DATA infile faster. Use the following procedure:

Create a table selectively with the CREATE table. For example, use MySQL or perl-dbi.

Perform flush TABLES, or shell commands mysqladmin flush-tables.

Use Myisamchk--keys-used=0-rq/path/to/db/tbl_name. This deletes the use of all indexes from the table.

Inserting data into a table with the load data infile will not update any indexes, so it is very quick.

If you have myisampack and want to compress the table, run Myisampack on it.

Use Myisamchk-r-q/path/to/db/tbl_name to create the index again. This creates an index tree in memory before it is written to disk, and it is faster because it avoids a large number of disk seek. The result index tree is also perfectly balanced.

Perform flush TABLES, or shell commands mysqladmin flush-tables.

This process will be constructed into a future version of MySQL's Load DATA INFILE.

You can lock your table to speed the insertion.

Mysql> LOCK TABLES A write;mysql> insert into a values (1,23), (2,34), (4,33);mysql> inserts into a values (8,26), (6 ;mysql> UNLOCK TABLES; The main speed difference is that the index buffer is purged only once to the disk, after all INSERT statements have been completed. There is generally an index buffer flush that takes the same as a different insert statement. If you can insert all the rows with a single statement, the lock is not required. Locking also reduces the overall time of a multiple-connection test, but the maximum wait time for some threads will rise (because they wait for the lock). For example:

Thread 1 does 1000 Insertsthread 2, 3, and 4 does 1 insert

Thread 5 Does 1000 inserts if you do not use locks, 2, 3, and 4 will be completed before 1 and 5. If you use locks, 2, 3, and 4 will probably not be completed before 1 or 5, but the overall time should be about 40% faster. Because the INSERT, update, and delete operations are quick in MySQL, you will get better overall performance by locking things for more than about 5 consecutive inserts or updates. If you do a lot of inserts, you can do a lock table and occasionally then do a unlock tables (about every 1000 lines) to allow additional threads to access the tables. This will still result in good performance. Of course, the load data infile is still faster on the load.