Admin Linux ... MySQL DBA

Allocating memory for caches in MySQL If the server is dedicated to MySQL, any memory you don’t reserve for the operating system or for query processing is available for caches. MySQL needs more memory for caches than anything else. It uses caches to avoid disk access, which is orders of magnitude slower than accessing data in memory. The operating system may cache some data on MySQL’s behalf (especially for MyISAM),but MySQL needs lots of memory for itself too. The following are the most important caches to consider for the majority of installations: • The operating system caches for MyISAM data • MyISAM key caches • The InnoDB buffer pool • The query cache There are other caches, but they generally don’t use much memory. It is much easier to tune a server if you’re using only one storage engine. If you’re using only MyISAM tables, you can disable InnoDB completely, and if you’re using only InnoDB, you need to allocate only minimal resources for MyISAM (MySQL uses MyISAM tables intern

If your database is MySQL, though, there's still hope. Over the course of this article, I will show you how to use MySQL's built-in crash recovery tools to check your database, and hopefully recover all the data you just lost. Built-in tools When it comes to repairing and checking tables, MySQL offers two options: myisamchk designed specifically to check and repair MyISAM tables (the default table type used by MySQL). This tool can scan your databases, identify problems, optimize tables for faster lookup, and optionally repair corrupted tables. The myisamchk tool is invoked from the command line. MySQL also allows you check and repair tables using SQL commands. The CHECK TABLE , REPAIR TABLE , and OPTIMIZE TABLE commands can be used on a running MySQL server and need to be entered through a MySQL client. Most of the time, it's preferable to use myisamchk because it is significantly faster than using SQL commands. Checking tables If you're having trouble

Here is a way to calculate Query Executed per second : time=10 begin=`mysql -e "show status" | awk '{if ($1 == "Questions") print $2}'` sleep $time end=`mysql -e "show status" | awk '{if ($1 == "Questions") print $2}'` diff=`expr $end - $begin` avg=`expr $diff / $time` echo "$avg"