MySQL Database Design and Tuning

Some users have a natural talent for bringing the most efficient database servers to their knees, followed quickly by the responsible administrators. These users devour vast amounts of computing resources, but don't be too harsh on them: In many cases, they don't even know the damage they cause.

Luckily, MySQL administrators have several process restrictions options available when invoking the GRANT command to give users access to the database. These restrictions include the following:

• Connections per hour, via the MAX_CONNECTIONS_PER_HOUR setting

• Queries per hour, via the MAX_QUERIES_PER_HOUR setting

• Updates per hour, via the MAX_UPDATES_PER_HOUR setting

In the event that users are constructing their own queries or are using automated tools of dubious quality, set the max_join_size variable (either on mysqld startup or within your application code via the SET SQL_MAX_JOIN_SIZE statement) to prevent MySQL from completing this many disk operations or joins, such as those that might occur when unrestricted and/or nonindexed queries are sent to the server.

For example, look at how MySQL rejects this poorly constructed query:

mysql> set SQL_MAX_JOIN_SIZE = 1000; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM customer_master, customer_address; ERROR 1104: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

This is when the query cache might help: If the results are already in resident in the cache, MySQL returns them to the user without triggering a resource consumption error. Another way to enable this bad query-blocking behavior is to set SQL_BIG_SELECTS to zero.

To get an idea of your environment's temporary table situation, monitor these three system status variables:

• created_tmp_disk_tables

• created_tmp_files

• created_tmp_tables

You can restrict the number of temporary tables that a client can keep open at one time by setting the max_tmp_tables variable. This will be fully enabled soon.