MySQL, tips - Revision history

Jimbo: moved MySQL: tips to MySQL, tips

2010-01-16T14:34:54Z

moved MySQL: tips to MySQL, tips

DrModiford: Added tip about moving MySQL databases from the /var volume

2008-09-24T11:41:23Z

Added tip about moving MySQL databases from the /var volume

199.249.176.251: /* Optimize MySQL tables */

2005-09-22T15:22:24Z

Optimize MySQL tables

Dave at 21:42, 23 February 2005

2005-02-23T21:42:41Z

Jimbo at 04:24, 24 December 2004

2004-12-24T04:24:13Z

Dave: MySQL tips and tricks

2004-12-23T19:23:35Z

MySQL tips and tricks

New page

This article will be devoted to tips that will either enhance or tune MySQL for better performance.

==Optimize MySQL tables==

MySQL tables can grow large and cumbersome, and similar to defragging a hard drive to get optimal space, you can run
mysql> mysqlcheck -o <database>
to achieve a similar effect on your database. This is especially important and useful when you've changed a large database (e.g., deleted a lot of stuff from it.)

==Play nice with others==

If you're running a server that does a lot of heavy MySQL lifting, it makes sense that MySQL should run as fast as possible. An easy way to give MySQL priority is to re-[[nice]] it to a lower value. Go through the ''safe_mysqld'' script in your MySQL directory and add a line like
renice -10 $$
to it. You may find it necessary to comment out the section that starts with NOHUP_NICENESS and any [[if]]/[[fi]] loops in it. Note that if you set the [[nice]] value too low, the rest of the system's performance can be affected. For this reason, it's usually best not to go below -10 or -15; between -5 and -10 should be a pretty safe spot.

← Older revision		Revision as of 04:24, 24 December 2004
Line 12:		Line 12:
	renice -10 $$		renice -10 $$
	to it. You may find it necessary to comment out the section that starts with NOHUP_NICENESS and any [[if]]/[[fi]] loops in it. Note that if you set the [[nice]] value too low, the rest of the system's performance can be affected. For this reason, it's usually best not to go below -10 or -15; between -5 and -10 should be a pretty safe spot.		to it. You may find it necessary to comment out the section that starts with NOHUP_NICENESS and any [[if]]/[[fi]] loops in it. Note that if you set the [[nice]] value too low, the rest of the system's performance can be affected. For this reason, it's usually best not to go below -10 or -15; between -5 and -10 should be a pretty safe spot.
		+	[[Category:Common Tasks]]

@@ Line 7: / Line 7: @@
   (enter password)
 to achieve a similar effect on your database. This is especially important and useful when you've changed a large database (e.g., deleted a lot of stuff from it.)
 ==Play nice with others==

@@ Line 4: / Line 4: @@
 MySQL tables can grow large and cumbersome, and similar to defragging a hard drive to get optimal space, you can run
-  mysql> mysqlcheck -o <database>
+  # mysqlcheck -u <username> -p -o <database>
 to achieve a similar effect on your database. This is especially important and useful when you've changed a large database (e.g., deleted a lot of stuff from it.)

@@ Line 12: / Line 12: @@
   renice -10 $$
 to it. You may find it necessary to comment out the section that starts with NOHUP_NICENESS and any [[if]]/[[fi]] loops in it. Note that if you set the [[nice]] value too low, the rest of the system's performance can be affected. For this reason, it's usually best not to go below -10 or -15; between -5 and -10 should be a pretty safe spot.
 [[Category:Common Tasks]]