Copyright © tutorialspoint.com

MySQL Database Tuning

previous next


Careful index design is one technique that improves the speed of a DBMS and can reduce the resource requirements of a database. However, comprehensive database tuning is a complex topic that fills many books. We include in this section only a few additional practical ideas to begin to improve the performance of a database system.

Here are some ways to improve DBMS performance:

Another aspect of database tuning is optimizing the performance of the DBMS itself. Included with the MySQL installation is the mysqladmin tool for database administration. Details of the system setup can be found by running the following command from a Linux shell:

root@host# mysqladmin -p variables
Enter password:*******

Above command will display many variables. The important parameters are those that impact disk use. MySQL has several main-memory buffer parameters that control how much data is kept in memory for processing. These include:

In general, the larger these buffers, the more data from disk is cached or stored in memory and the fewer disk accesses are required. However, if the sum of these parameters is near to exceeding the size of the memory installed in the server, the underlying operating system will start to swap data between disk and memory, and the DBMS will be slow.

In any case, careful experimentation based on the application is likely to improve DBMS performance.

Section 10.2.3 of the MySQL manual suggests parameter settings when starting the MySQL server. First, for machines with at least 64 MB of memory, large tables in the DBMS, and a moderate number of users, use:

safe_mysqld -O key_buffer=16M -O table_cache=128 \
-O sort_buffer=4M -O record_buffer=1M &

Second, if there is less than 64 MB of memory available, and there are many users, try the following:

safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
-O record_buffer=100k &

The following setting might be appropriate for the winestore, because many users are expected, the queries are largely index-based, and the database is small:

safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
-O table_cache=32 -O record_buffer=8k
-O net_buffer=1K &

Even more conservative settings might also be acceptable.

There are two other parameters we have not discussed. The table_cache parameter manages the maximum number of open tables per user connection, while the net_buffer parameter sets the minimum size of the network query buffer in which incoming queries are kept before they are executed.

The mysqladmin utility can report the status of the DBMS:

root@host# mysqladmin -p status
Enter password:*******

The output has the following format:

Uptime: 5721024 Threads: 14 Questions: 7874982
Slow queries: 6 Opens: 115136 Flush tables: 1
Open tables: 62

This gives a brief point-in-time summary of the DBMS status and can help find more about the number of user connections, queries, and table use. Similar output can be generated by running the commands SHOW STATUS and SHOW VARIABLES through the MySQL command interpreter.

Information about query performance can be gained with the benchmark( ) function, which can be used iteratively for tuning when altering table design or DBMS system parameters. The following statement illustrates benchmarking:

SELECT benchmark(10000, COUNT(*))
FROM table_name;

This statement reports the time taken to evaluate 10,000 calls to COUNT() on the table_name table.

previous next

Copyright © tutorialspoint.com