MySQL : Altering Huge Tables

You have a huge mysql table - maybe 100 GB. And you need to run alter on it - to either add an index, drop an index, add a column or drop a column. If you run the simple mysql "alter table" command, you will end up spending ages to bring the table back into production.

Here is a simple hack to get the thing done. The benefit of the hack is that the alter runs quite fast. But since this is a hack, you will need to take care of the backups - in case anything goes wrong. I have seen this hack work effectively with both MyISAM and InnoDB tables.

Here I have created a simple table to show this hack process. You can assume that this table has billions of rows and is more than 100GB in size.

CREATE TABLE `testhack` (
`id` int(11) NOT NULL DEFAULT '0',
`unq` varchar(100) DEFAULT NULL,
`keyword` varchar(250) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unq` (`unq`)
) ENGINE=MyISAM

I need to drop the unique key. So, i have create a new table 'testhack_new' with the following schema

CREATE TABLE `testhack_new` (
`id` int(11) NOT NULL DEFAULT '0',
`unq` varchar(100) DEFAULT NULL,
`keyword` varchar(250) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM

Flush both tables with read lock

mysql> Flush tables with read lock;

Open another terminal. And go to the mysql/data/ directory. Do the following:

mysql/data/test $ mv testhack.frm testhack_old.frm; mv testhack_new.frm testhack.frm; mv testhack_old.frm testhack_new.frm; mv testhack.MYI testhack_old.MYI; mv testhack_new.MYI testhack.MYI; mv testhack_old.MYI testhack_new.MYI;

So, what is happening here is that the index, table definitions are being switched. After this process, the table definition of testhack will not contain the unique key. Now unlock the tables in the main window. And run repair tables to remove any issues.

mysql> unlock tables;
mysql> repair tables testhack;

+---------------+--------+----------+-------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------+--------+----------+-------------------------------------------+
| test.testhack | repair | warning | Number of rows changed from 0 to 20000000 |
| test.testhack | repair | status | OK |
+---------------+--------+----------+-------------------------------------------+

The repair table rebuilds the indexes. It is faster since it skips the use of key_cache for rebuilding the index (which is used in a normal alter table scenario).

Source :
http://www.mysqlperformanceblog.com/2007/10/29/hacking-to-make-alter-table-online-for-certain-changes/

mysql-packages

The RPM packages usages and details:

Ø MySQL-server-VERSION.glibc23.i386.rpm - The MySQL server. You need this unless you only want to connect to a MySQL server running on another machine.

Ø MySQL-client-VERSION.glibc23.i386.rpm - The standard MySQL client programs. You probably always want to install this package.

Ø MySQL-devel-VERSION.glibc23.i386.rpm - The libraries and include files that are needed if you want to compile other MySQL clients, such as the Perl modules.

Ø MySQL-debuginfo-VERSION.glibc23.i386.rpm - This package contains debugging information. debuginfo RPMs are never needed to use MySQL software; this is true both for the server and for client programs. However, they contain additional information that might be needed by a debugger to analyze a crash.

Ø MySQL-shared-VERSION.glibc23.i386.rpm - This package contains the shared libraries (libmysqlclient.so*) that certain languages and applications need to dynamically load and use MySQL. It contains single-threaded and thread-safe libraries. If you install this package, do not install the MySQL-shared-compat package.

Ø MySQL-shared-compat-VERSION.glibc23.i386.rpm - This package includes the shared libraries for MySQL 3.23, 4.0, and so on, up to the current release. It contains single-threaded and thread-safe libraries. Install this package instead of MySQL-shared if you have applications installed that are dynamically linked against older versions of MySQL but you want to upgrade to the current version without breaking the library dependencies.

Ø MySQL-shared-compat-advanced-gpl-VERSION.glibc23.i386.rpm, MySQL-shared-compat-advanced-VERSION.glibc23.i386.rpm - These are like the MySQL-shared-compat package, but are for the “MySQL Enterprise Server – Advanced Edition” products. Install these packages rather than the normal MySQL-shared-compat package if you want to included shared client libraries for older MySQL versions.

Ø MySQL-embedded-VERSION.glibc23.i386.rpm - The embedded MySQL server library.
Ø MySQL-ndb-management-VERSION.glibc23.i386.rpm, MySQL-ndb-storage-VERSION.glibc23.i386.rpm, MySQL-ndb-tools-VERSION.glibc23.i386.rpm, MySQL-ndb-extra-VERSION.glibc23.i386.rpm - Packages that contain additional files for MySQL Cluster installations.
o Note : The MySQL-ndb-tools RPM requires a working installation of perl. Prior to MySQL 5.1.18, the DBI and HTML::Template packages were also required. See Section 2.15, “Perl Installation Notes”, and Section 17.4.21, “ndb_size.pl — NDBCLUSTER Size Requirement Estimator”, for more information.

Ø MySQL-test-VERSION.glibc23.i386.rpm - This package includes the MySQL test suite.
Ø MySQL-VERSION.src.rpm - This contains the source code for all of the previous packages. It can also be used to rebuild the RPMs on other architectures (for example, Alpha or SPARC).

Default Installation Directory :

Directory -> Contents of Directory
/usr/bin -> Client programs and scripts
/usr/sbin -> The mysqld server
/var/lib/mysql -> Log files, databases
/usr/share/info -> Manual in Info format
/usr/share/man -> Unix manual pages
/usr/include/mysql -> Include (header) files
/usr/lib/mysql -> Libraries
/usr/share/mysql -> Miscellaneous support files, including error messages, character set files, sample configuration files, SQL for database installation
/usr/share/sql-bench -> Benchmarks

Security Issues with MySQL ROOT Access

Security Issues with MySQL ROOT Access

MySQL offers simple but very effective security mechanisms. Unfortunately, the default installation of MySQL, and in particular the empty root password and the potential vulnerability to buffer overflow attacks, makes the database an easy target for attacks.
In order to achieve the highest possible level of security, the installation and configuration of MySQL should be performed in accordance with the following security requirements:

* MySQL processes must run under a unique UID/GID that is not used by any other system process.
* Only local access to MySQL need to be allowed.(some exceptions for jobs/backups)
* MySQL root's account must be protected by a complex/hard to guess password.
* The administrator's account (root) need to be renamed.
* Anonymous access to the database (by using the nobody account) must be disabled.

MySQL Security risks can be categorized into the following.

* Filesystem security risks. MySQL Installation (basedir) and database information (datadir) and other log (querylog/slowlog) files that contain information about queries that clients execute. These files/directories need to be protected so that other users who have login accounts on the server host cannot access them directly.
* Network security risks. The MySQL server provides access to databases by allowing clients to connect and make requests. Information about client accounts is stored in the mysql database. Each account should be set up with privileges that provide access only to the data the accounts needs to see or modify.

MySQL root account has full privileges to perform any database operation, so it's important to assign the account a password that is not easily guessed. Note that usernames and passwords for MySQL accounts are unrelated to those for system login accounts. OS login and MySQL login both should not be the same.

Restrict anonymous remote access

Grant access to specific users from specific hosts only. Do not grant access from all hosts.
Do not grant the PROCESS or SUPER privilege to non-administrative users. The output of mysqladmin processlist and SHOW PROCESSLIST shows the statements currently being executed, so any user who is allowed to see the server process list might be able to see statements issued by other users such as UPDATE user SET password=PASSWORD(pwd).

mysqld reserves an extra connection for users who have the SUPER privilege, so that a MySQL root user can log in and check server activity even if all normal connections are in use. This is very useful when MySQL reaches the max_connections threshold. This cannot be beneficial; if users root (which ever has SUPER privilege) is used for all client connections.
Do not grant the FILE privilege to non-administrative users. Any user that has this privilege can write a file anywhere in the file system with the privileges of the mysqld daemon. To make this a bit safer, files generated with SELECT ... INTO OUTFILE do not overwrite existing files and are writable by everyone.
The FILE privilege may also be used to read any file that is accessible to the Unix user that the server runs as. With this privilege, user can read any file into a database table. This could be abused, for example, by using LOAD DATA to load /etc/passwd into a table, which then can be displayed with SELECT.

Improve local security

Use different socket file for both client and server connections. The following parameter should be changed in the [client] section of /etc/my.cnf:
[client] socket = /tmp/mysql.sock

Change admin password

One of the most important steps in securing MySQL is changing the database administrator's password, which is empty by default. In order to change the administrator's password, follow the steps:
mysql -u root mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');
It is good practice not to change passwords from the command line,(instead change at database level). This is especially important when other users working on the server. In that case the password could be easily revealed, e.g. by using the "ps aux" command or reviewing history files, when improper access rights are set to them.

Change admin name

It is also recommended to change the default name of administrator's account (root), to a different, harder to guess one. Such a change will make it difficult to perform brute-force and dictionary attacks on the administrator's password.
mysql> update user set user="mysqluser" where user="root"; mysql> flush privileges;

Remove history

We should also remove the content of the MySQL history file (~/.mysql_history, ~/.history, ~/.bash_history,~/.mysql_history) in which all executed SQL commands are being stored (especially passwords, which are stored as plain text).
User Access Privileges
We can create accounts for specific databases which will be used by specific applications. These accounts should have access rights only to the databases which are used by the specific applications. In particular, they should not have any access rights to the mysql database, nor any system or administrative privileges (FILE, GRANT, ALTER, SHOW DATABASE, RELOAD, SHUTDOWN, PROCESS, SUPER etc.). Application users should not granted all privileges to database with Grant option from any host.


mysql> select user,host,password from user; GRANT USAGE ON *.* TO 'user1'@'%' IDENTIFIED BY PASSWORD 'xxxxxx' | GRANT ALL PRIVILEGES ON `user1`.* TO 'glist'@'%' WITH GRANT OPTION

MySQL Development History

MySQL Development History

- MySQL was first released internally on 23 May 1995
- Windows version was released on January 8, 1998 for Windows 95 and NT
- Version 3.23: beta from June 2000, production release January 2001
- Version 4.0: beta from August 2002, production release March 2003
- Version 4.1: beta from June 2004, production release October 2004
- Version 5.0: beta from March 2005, production release October 2005
- Version 5.1: currently pre-production (since November 2005)
- Sun Microsystems acquires MySQL AB on 26 February 2008

MySQL Features History

- Version 3.23.23 Full-Text Search
- Version 4.0 Full-Text Search (IN BOOLEAN MODE), UNIONS
- Version 4.1 R-Tree and B-Tree, Sub-Queries, Prepared Statements
- Version 5.0 Cursors, Stored Procedures, Triggers, Views, XA Transactions
- Version 5.1 Event Scheduler, Partitioning, Plugin API, Row-Based Replication, Server Log Tables

Display Information About File - Stat

In Unix/Linux world everything is treated as files. whether it is a devices, directories and sockets — all of these are files.

Stat command displays file or file system status.

[Prabhat@Server1 Archive]$ stat 1_16470_587807474.arc
File: `1_16470_587807474.arc'
Size: 208514560 Blocks: 407664 IO Block: 4096 regular file
Device: fd02h/64770d Inode: 17006596 Links: 1
Access: (0640/-rw-r-----) Uid: ( 500/ oracle) Gid: ( 500/ dba)
Access: 2010-06-12 23:28:58.000000000 -0700
Modify: 2010-06-12 23:31:22.000000000 -0700
Change: 2010-06-12 23:31:22.000000000 -0700

Details of Linux Stat Command Output

* File: `1_16470_587807474.arc’ – Name of the file.
* Size: 208514560 – File size in bytes.
* Blocks: 407664 – Total number of blocks used by this file.
* IO Block: 4096 – IO block size for this file.
* regular file – Indicates the file type. This indicates that this is a regular file. Following are available file types.

o regular file. ( ex: all normal files ).
o directory. ( ex: directories ).
o socket. ( ex: sockets ).
o symbolic link. ( ex: symbolic links. )
o block special file ( ex: hard disk ).
o character special file. ( ex: terminal device file ).

* Device: fd02h/64770d – Device number in hex and device number in decimal
* Inode: 17006596 – Inode number is a unique number for each file which is used for the internal maintenance by the file system.
* Links: 1 – Number of links to the file
* Access: (0640/-rw-r-----): Access specifier displayed in both octal and character format. Let us see explanation about both the format.
* Uid: ( 500/oracle) – File owner’s user id and user name are displayed.
* Gid: ( 500/dba) – File owner’s group id and group name are displayed.
* Access: 2010-06-12 23:28:58.000000000 -0700 – Last access time of the file.
* Modify: 2010-06-12 23:31:22.000000000 -0700 – Last modification time of the file.
* Change: 2010-06-12 23:31:22.000000000 -0700 – Last change time of the inode data of that file.

Stat – Display Information About Directory

You can use the same command to display the information about a directory as shown below.

[Prabhat@Server1 oradata]$ stat Archive
File: `Archive'
Size: 12288 Blocks: 24 IO Block: 4096 directory
Device: fd02h/64770d Inode: 16990209 Links: 2
Access: (0755/drwxr-xr-x) Uid: ( 500/ oracle) Gid: ( 500/ dba)
Access: 2009-11-02 23:43:22.000000000 -0800
Modify: 2010-06-13 05:26:54.000000000 -0700
Change: 2010-06-13 05:26:54.000000000 -0700

MySQL : About LIMIT 0 , 30

Limit is used to limit your MySQL query results to those that fall within a specified range. You can use it to show the first X number of results, or to show a range from X - Y results. It is phrased as Limit X, Y and included at the end of your query.
X is the starting point (remember the first record is 0) and Y is the duration (how many records to display).

Table 'example' have following data.
SELECT * FROM `example` ORDER BY `example`.`age` ASC LIMIT 0 , 30

id name age
3 Amit 26
5 pani 27
2 Sanjay 28
4 Lucky 29
6 atul 30
1 Kumar 31

SELECT * FROM `example` ORDER BY `example`.`age` ASC LIMIT 3 , 1

id name age
4 Lucky 29

This will show records 3rd only (remember the first record is 0)

MySQL : Allocating memory for caches

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 internally for some operations). But if you’re using a mixture of storage engines, it can be very hard to figure out the right balance between them.

The MyISAM Key Cache

The MyISAM key caches are also referred to as key buffers; there is one by default, but you can create more. Unlike InnoDB and some other storage engines, MyISAM itself caches only indexes, not data (it lets the operating system cache the data). If you use mostly MyISAM, you should allocate a lot of memory to the key caches.

The most important option is the key_buffer_size, which you should try setting to between 25% and 50% of the amount of memory you reserved for caches. The remainder will be available for the operating system caches, which the operating system will usually fill with data from MyISAM’s .MYD files.

MySQL 5.0 has a hard upper limit of 4 GB for this variable, no matter what architecture you’re running. (MySQL 5.1 allows larger sizes. Check the current documentation for your version of the server.)

By default MyISAM caches all indexes in the default key buffer, but you can create multiple named key buffers. This lets you keep more than 4 GB of indexes in memory at once. To create key buffers named key_buffer_1 and key_buffer_2, each sized at 1 GB, place the following in the configuration file:
key_buffer_1.key_buffer_size = 1G
key_buffer_2.key_buffer_size = 1G
Now there are three key buffers: the two explicitly created by those lines and the default buffer. You can use the CACHE INDEX command to map tables to caches. You can also tell MySQL to use key_buffer_1 for the indexes from tables t1 and t2 with the following SQL statement:
mysql> CACHE INDEX t1, t2 IN key_buffer_1;
Now when MySQL reads blocks from the indexes on these tables, it will cache the blocks in the specified buffer. You can also preload the tables’ indexes into the cache with the LOAD INDEX command:
mysql> LOAD INDEX INTO CACHE t1, t2;

You can place this SQL into a file that’s executed when MySQL starts up. The filename must be specified in the init_file option, and the file can include multiple SQL commands, each on a single line (no comments are allowed). Any indexes you don’t explicitly map to a key buffer will be assigned to the default buffer the first time MySQL needs to access the .MYI file.

You can monitor the performance and usage of the key buffers with information from SHOW STATUS and SHOW VARIABLES. You can calculate the hit ratio and the percentage of the buffer in use with these equations:
Cache hit ratio
100 - ( (Key_reads * 100) / Key_read_requests )
Percentage of buffer in use
100 - ( (Key_blocks_unused * key_cache_block_size) * 100 / key_buffer_size )

It’s good to know the cache hit rate, but this number can be misleading. For example,the difference between 99% and 99.9% looks small, but it really represents a tenfold increase. The cache hit rate is also application-dependent: some applications might work fine at 95%, whereas others might be I/O-bound at 99.9%. You might even be able to get a 99.99% hit rate with properly sized caches.

The number of cache misses per second is generally much more empirically useful. Suppose you have a single hard drive that can do 100 random reads per second. Five misses per second will not cause your workload to be I/O-bound, but 80 per second
will likely cause problems.
You can use the following equation to calculate this value cache misses:
Key_reads / Uptime
Calculate the number of misses incrementally over intervals of 10 to 100 seconds, so you can get an idea of the current performance. The following command will show the incremental values every 10 seconds:
$ mysqladmin extended-status -r -i 10 | grep Key_reads
When you’re deciding how much memory to allocate to the key caches, it might help to know how much space your MyISAM indexes are actually using on disk. You don’t need to make the key buffers larger than the data they will cache.
Space your MyISAM indexes are actually using on disk
$ du -sch `find /path/to/mysql/data/directory/ -name "*.MYI"`

Remember that MyISAM uses the operating system cache for the data files, which are often larger than the indexes. Therefore, it often makes sense to leave more memory for the operating system cache than for the key caches. Finally, even if you don’t have any MyISAM tables, bear in mind that you still need to set key_buffer_size to a small amount of memory, such as 32M. The MySQL server sometimes uses MyISAM tables for internal purposes, such as temporary tables for GROUP BY queries.

The MyISAM key block size The key block size is important (especially for write-intensive workloads) because of the way it causes MyISAM, the operating system cache, and the filesystem to interact. If the key block size is too small, you may encounter read-around writes, which
are writes that the operating system cannot perform without first reading some data from the disk. Here’s how a read-around write happens, assuming the operating system’s page size is 4 KB (typically true on the x86 architecture) and the key block size is 1 KB:
1. MyISAM requests a 1 KB key block from disk.
2. The operating system reads 4 KB of data from the disk and caches it, then passes the desired 1 KB of data to MyISAM.
3. The operating system discards the cached data in favor of some other data.
4. MyISAM modifies the 1 KB key block and asks the operating system to write it back to disk.
5. The operating system reads the same 4 KB of data from the disk into the operating system cache, modifies the 1 KB that MyISAM changed, and writes the entire
4 KB back to disk.

The read-around write happened in step 5, when MyISAM asked the operating system to write only part of a 4 KB page. If MyISAM’s block size had matched the operating system’s, the disk read in step 5 could have been avoided.*Unfortunately, in MySQL 5.0 and earlier, there’s no way to configure the key block size. However, in MySQL 5.1 and later, you can avoid read-around writes by making MyISAM’s key block size the same as the operating system’s. The myisam_block_size variable controls the key block size. You can also specify the size for each key with the KEY_BLOCK_SIZE option in a CREATE TABLE or CREATE INDEX statement, but because all keys are stored in the same file, you really need all of them to have blocks as large as or larger than the operating system’s to avoid alignment issues that could still cause read-around writes. (For example, if one key has 1 KB blocks and another has 4 KB blocks, the 4 KB block boundaries might not match the operating system’s page boundaries.)

The InnoDB Buffer Pool

If you use mostly InnoDB tables, the InnoDB buffer pool probably needs more memory than anything else. Unlike the MyISAM key cache, the InnoDB buffer pool doesn’t just cache indexes: it also holds row data, the adaptive hash index, the insert buffer, locks, and other internal structures. InnoDB also uses the buffer pool to help it delay writes, so it can merge many writes together and perform them sequentially. In short, InnoDB relies heavily on the buffer pool, and you should be sure to allocate enough memory to it.

The MySQL manual suggests using up to 80% of the machine’s physical memory for the buffer pool on a dedicated server; in reality, you can use more than that if the machine has a lot of memory.

As with the MyISAM key buffers, you can use variables from SHOW commands or tools such as innotop to monitor your InnoDB buffer pool’s memory usage and performance. There’s no equivalent of LOAD INDEX INTO CACHE for InnoDB tables. However, if you’re trying to warm up a server and get it ready to handle a heavy load, you can issue queries that perform full table scans or full index scans.

In most cases, you should make the InnoDB buffer pool as large as your available memory allows. However, in rare circumstances, very large buffer pools (say, 50 GB) can cause long stalls. For example, a large buffer pool may become slow during checkpoints or insert buffer merge operations, and concurrency can drop as a result of locking. If you experience these problems, you may have to reduce the buffer pool size.

You can change the innodb_max_dirty_pages_pct variable to instruct InnoDB to keep more or fewer dirty (modified) pages in the buffer pool. If you allow a lot of dirty pages, InnoDB can take a long time to shut down, because it writes the dirty pages to the data files upon shutdown.
You can monitor the number of dirty pages by watching the Innodb_buffer_pool_pages_dirty server status variable or using innotop to monitor SHOW INNODB STATUS.
Lowering the value of the innodb_max_dirty_pages_pct variable doesn’t actually guarantee that InnoDB will keep fewer dirty pages in the buffer pool. Instead, it controls the threshold at which InnoDB stops being “lazy.” InnoDB’s default behavior is to flush dirty pages with a background thread, merging writes together and performing them sequentially for efficiency. This behavior is called “lazy” because it lets InnoDB delay flushing dirty pages in the buffer pool, unless it needs to use the space for some other data. When the percentage of dirty pages exceeds the threshold, InnoDB will flush pages as quickly as it can to try to keep the dirty page count lower. The variable’s default value is 90, so by default InnoDB will flush lazily until the buffer pool is 90% full of dirty pages. You can tweak the threshold for your workload if you wish to spread out the writes a bit more. For example, lowering it to 50 will generally cause InnoDB to do more write operations, because it will flush pages sooner and therefore be unable to batch the writes as well. However, if your workload has a lot of write spikes, using a lower value may help InnoDB absorb the spikes better: it will have more “spare” memory to hold dirty pages, so it won’t have to wait for other dirty pages to be flushed to disk.

The Thread Cache

The thread cache holds threads that aren’t currently associated with a connection but are ready to serve new connections. When there’s a thread in the cache and a new connection is created, MySQL removes the thread from the cache and gives it to the new connection. When the connection is closed, MySQL places the thread back into the cache, if there’s room. If isn’t room, MySQL destroys the thread. As long as MySQL has a free thread in the cache, it can respond very rapidly to connect requests, because it doesn’t have to create a new thread for each connection.
The thread_cache_size variable specifies the number of threads MySQL can keep in the cache. You probably won’t need to tune this value, unless your server gets many connection requests. To check whether the thread cache is large enough, watch the Threads_created status variable. We generally try to keep the thread cache large enough that we see fewer than 10 new threads created each second, but it’s often pretty easy to get this number lower than 1 per second.

A good approach is to watch the Threads_connected variable and try to set thread_cache_size large enough to handle the typical fluctuation in your workload. For example, if Threads_connected usually stays between 100 and 200, you can set the cache size to 100. If it stays between 500 and 700, a thread cache of 200 should be large enough. Think of it this way: at 700 connections, there are probably no threads in the cache; at 500 connections, there are 200 cached threads ready to be used if the load increases to 700 again. Making the thread cache very large is probably not necessary for most uses, but keeping it small doesn’t save much memory, so there’s little benefit in doing so. Each thread that’s in the thread cache or sleeping typically uses around 256 KB of memory. This is very little compared to the amount of memory a thread can use when a connection is actively processing a query. In general, you should keep your thread cache large enough that Threads_created doesn’t increase very often. If this is a very large number, however (e.g., many thousand threads), you might want to set it lower because some operating systems don’t handle very large numbers of threads well, even when most of them are sleeping.

The Table Cache

The table cache is similar in concept to the thread cache, but it stores objects that represent tables. Each object in the cache contains the associated table’s parsed .frm file, plus other data. Exactly what else is in the object depends on the table’s storage engine. For example, for MyISAM, it holds the table data and/or index file descriptors. For merge tables it may hold many file descriptors, because merge tables can have many underlying tables. The table cache can help you reuse resources. For instance, when a query requests access to a MyISAM table, MySQL might be able to give it a file descriptor from the cached object instead of opening the file. The table cache can also help avoid some of the I/O required for marking a MyISAM table as “in use” in the index headers.*The table cache’s design is a little MyISAM-centric—this is one of the areas where the separation between the server and the storage engines is not completely clean, for historical reasons. The table cache is a little less important for InnoDB, because InnoDB doesn’t rely on it for as many purposes (such as holding file descriptors; it has its own version of a table cache for this purpose). However, even InnoDB benefits from caching the parsed .frm files.

In MySQL 5.1, the table cache is separated into two parts: a cache of open tables and a table definition cache (configured via the table_open_cache and table_definition_cache variables). Thus, the table definitions (the parsed .frm files) are separated from
the other resources, such as file descriptors. Opened tables are still per-thread, pertable-used, but the table definitions are global and can be shared among all connections efficiently. You can generally set table_definition_cache high enough to cache all your table definitions. Unless you have tens of thousands of tables, this is likely to be the easiest approach. If the Opened_tables status variable is large or increasing, the table cache isn’t large enough, and you should increase the table_cache system variable (or table_open_cache, in MySQL 5.1). The only real downside to making the table cache very large is that it might cause longer shutdown times when your server has a lot of MyISAM tables, because the key blocks have to be flushed and the tables have to be marked as no longer open. It can also make FLUSH TABLES WITH READ LOCK take a long time complete, for the same reason.
If you get errors indicating that MySQL can’t open any more files (use the perror utility to check what the error number means), you might also need to increase the number of files MySQL is allowed to keep open. You can do this with the open_files_limit server variable in your my.cnf file.
The thread and table caches don’t really use much memory, and they are beneficial
because they conserve resources. Although creating a new thread and opening a new
file aren’t really expensive compared to other things MySQL might do, the overhead
can add up quickly under a high-concurrency workload. Caching threads and tables
can improve efficiency.

MYSQL : Checking and Reparing tables

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:

1. 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.
2. 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 accessing a table, first try checking it for errors. To check a table, shut down the server and type myisamchk at the command prompt, followed by the table's file name, as shown below:
$ myisamchk /usr/local/mysql/data/phpbb/user.myi
Checking MyISAM file: /usr/local/mysql/data//user.MYI
Data records: 1153 Deleted blocks: 0
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check record links

Use the complete path to the table file instead of just the table name. Remember to include the file extension as well.

myisamchk options :
myisamchk -- fast Perform a quick check, only verifying if the table handler closed successfully
myisamchk -- medium-check Perform a faster check, by calculating a checksum for the indexes in each record and verifying that checksum against the index tree
myisamchk -- extend-check Perform a thorough check of the table, verifying the data in each record
Command line arguments for myisamchk

Repairing tables

Next, shut down the MySQL server. This is because myisamchk makes changes to the table file itself, so you don't want other users accessing or otherwise manipulating the file during the repair operation. Also advisable table physical backup of database.

Once the table file is backed up and the server down, you can run myisamchk with the --recover option, as shown below:
$ myisamchk --recover /usr/local/mysql/data/phpbb/user.myi
- recovering (with sort) MyISAM-table '/usr/local/mysql/data/phpbb/user.MYI'Data records: 1153
- Fixing index 1

The --recover option reconstructs the MySQL table index file after weeding the corrupted table of invalid or previously deleted records that could be causing a problem.

In the unlikely event that the --recover option fails, revert to the original table file and try the --safe-recover option. This is slower, because MySQL needs to scan through the records in the data file one by one and them restore the index, but it can sometimes work better than a regular repair operation.
$ myisamchk --safe-recover /usr/local/mysql/data/phpbb/user.myi
- recovering (with keycache) MyISAM-table '/usr/local/mysql/data/phpbb/user.MYI'
Data records: 4

Using CHECK TABLE and REPAIR TABLE

check and repair operations are run from a MySQL prompt, via the CHECK TABLE and REPAIR TABLE commands.

Consider the following example of running a CHECK TABLE command:
mysql> CHECK TABLE user;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| phpbb.user | check | status | OK |
+--------------+-------+----------+----------+
1 row in set (0.02 sec)

You can add the FAST, MEDIUM, and EXTENDED keywords to the command to obtain behavior similar to that available in myisamchk. For example:
mysql> CHECK TABLE user EXTENDED;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| phpbb.user| check | status | OK |
+--------------+-------+----------+----------+
1 row in set (0.02 sec)

When it comes to repairing tables, too, MySQL offers the REPAIR TABLE option, as below:
mysql> REPAIR TABLE user;
+--------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+--------+----------+----------+
| phpbb.user | repair | status | OK |
+--------------+--------+----------+----------+
1 row in set (0.01 sec)

As with the myisamchk tool, the REPAIR TABLE command can take one of two additional options: QUICK, which tries a quick repair, and EXTENDED, which rebuilds the index after reading each record in the table.

An option here is to use the mysqlcheck utility, which provides a command line front end to the CHECK TABLE and REPAIR TABLE commands, allowing you to check and repair tables without first taking the server down. Everything that you can do with CHECK TABLE and REPAIR TABLE can be done with mysqlcheck, by passing appropriate command line options to the program.

MySQL : Calculate Query Executed per second

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"

Problem : CTRL+S in Putty

Over the years, we all have habit of using CTRL+S every few minutes during working on a document, because we all had too much work lost from stupid errors; In the Windows world, CTRL+S is used as the Save you work.
But, this habit will be a problem on working in the Linux world.

By accident while inside a terminal window (in PUTTY) we press CTRL+S, this accidental keystroke meant we had reconnect to my Linux server, kill whatever program we were running, and then start it again.

But here is solution :

CTRL+S actually does XOFF, which means the terminal will accept key strokes but won’t show the output of anything. It will appear as if your terminal is dead when it’s really just waiting to be turned back on. The fix? Simply press CTRL+Q to turn flow-control on (XON). If you pressed a whole bunch of keys before pressing CTRL+Q, you’ll see the output from those keystrokes.

memory-usage-free

There is simple command to check for the overall usage of memory including physical memory and swap memory.

What you is just type free and it will shows you the information of the memory usage. for example:

total used free shared buffers cached
Mem: 16442272 16355460 86812 0 129600 10120444
-/+ buffers/cache: 6105416 10336856
Swap: 10223608 263608 9960000

The value shows at the first line of total column, is the total physical memory I have in kilobytes. I have 16GB ram, the results (16442272kb) shown above is approximate in kb. So the system have already used almost all the physically memory, which just left about 84M as free memory.

Out of 15.6G from 5.8G that stored in the system, is actually used. And it still left 9.8G in cache. I have total 10G of swap , and it uses 9.4GM and remain 9.4G unused.

The memory usage seems high but still consider healthy. I have loaded a lots of applications and uses some minor disc space for swap, but everything still running smooth without any lags.

Free have few options, if you feel that memory result shows in kb is not detail enough, you can have more accurate measurement in bytes. You can passes -b for bytes, or less details -m for Megabytes, -g for gigabytes.

free -b

You can ask free to display results in every 5 seconds, in order to track the increases/decreases on memory usage.

free -s 5

Kontrollbase-MySQL monitoring tool

Kontrollbase - MySQL monitoring tool.
Past few days I am looking for a centralized MySQL database monitoring tool, Which will give complete details of MySQL database.
I have looked various tools but, Kontrollbase is given me the complete solution.

Kontrollbase is a monitoring, analytics, reporting, and historical analysis webapp for MySQL database administrators and advanced users of MySQL databases.

Important links:
software-kontrollbase
screenshots
demo Username: demo , Password: password
note: do not use IE, use Firefox or other browser.
downloads
Documentation

System details of Windows

Various time we required system (on Windows 2000, XP, 2003/8, Vista and Windows 7) details like hardware (motherboard, CPU , RAM , etc), installed software operating system’s security hotfixes and lots and lots of more.

One such solution is LookInMyPC – a free program that automatically generates a detailed audit report of the hardware and software on your computer. It records essential information such as, operating system and processor details, the amount of RAM installed, drive specifications, and much, much, more.

sample report