|< Day Day Up >|
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:
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:
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.
|< Day Day Up >|