Debugging a MySQL Issue with PHP Using XMP instead of PRE

Recently, I was helping a co-worker debug a MySrQL issue with an old app that started breaking after the dev team switched the app’s textareas to TinyMCE editor.  Typically, for quick-and-dirty debugging sessions that we want to wrap in minutes, we use a simple dumpQuery() function in PHP that prints out any query used in the app.

When dumping an UPDATE query we noticed thast mysql_error() was reporting that the query was breaking because one of the field appearntly had bad data — something that with CSS style-sheet data. Unfortunately, we were not looking at the actual log file that we generate from query dumps but instead we decided to be lazy and looked at the on-screen dump information shown on the browser.

Even though we were seeing mysql_error() reporting an issue with the UPDATE statement, we just could not see why the query was breaking until we decided to review our simple dumpQuery function:

function dumpQuery($query = null)
{
     echo "<pre>";
     print_r($query)
     echo "</pre>"
}

The co-worker quickly realized that the dumpQuery() was not going to show us the actual reason for the mysql_error() on the browser because the data had HTML elements that were getting rendered by the browser; so it was causing us pull our hair out thinking why we cannot see the simple error in the UPDATE statement.

The co-worker quickly replaced the echo “<pre>” call to echo “<xmp>” and also the same for the echo “</pre>” call to echo “</xmp>”.  We were immediately able to see the actual MySQL error for the UPDATE statement and fixed the field that had the TinyMCE data by calling the mysql_real_escape_string() for the data.

The “<xmp>” tag renders all HTML tags inside it, which allowed us to see the HTML elements in one of the fields being set as the problem.

Text within this tag is rendered in a monospaced font - usually Courier New. Also spaces and line breaks are preserved. The difference between this tag and PRE is that all characters (e.g. ‘<', '>‘ and ‘&’ etc.) are rendered i.e. you cannot use any other tag within XMP.

The mysql_real_escape_string() allowed us to escape the HTML elements appropriately for MySQL to handle and thus our debugging session concluded in happy ending.

We love XML tag for debugging.

Cheers.

Category: Debugging, PHP, Programming | Tags: | Be the First to Comment »

Using mysqladmin for monitoring MySQL server (Part I)

Problem Statement

Unfortunately, the community edition of MySQL server does not come with any fancy monitoring tools so we decided to put together a shell-script / Perl solution until we find something better. Our goals were simple: how can we find out if a MySQL server is up and then how can we find out if something nasty is about to happen or already happening. In this article, we will attempt to provide a simple solution for these questions but be aware, this ain’t your enterprise solution. :)

Is your MySQL server alive and kicking?

First question we want our monitoring solution to answer is: is mysql alive? If you are just interested in finding this answer, here is your solution:

$ mysqladmin -u [username] -p[password] ping

A sample output is shown below:

mysqld is alive

This message is the result of the above command only if MySQL is alive. If MySQL server is dead, you are likely to get something like:

mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists!

If you just interested in knowing if MySQl server is alive or not, you can use the following simple shell script:

#!/bin/sh
#
# Simple MySQL Server Monitoring Tool
# Version 1.0
# Copyright (c) EVOKNOW, Inc.
# Use it AS-IS without any guarantees
##############################################

# Change the username and password to suit your environment
USER=godmin
PASS=toomanysecrets

# Change the admin email to your email
ADMIN_EMAIL=you@example.com

# You can change the subject if you like
MYSQL_DOWN_SUBJECT="Alert: MySQL Down on $HOSTNAME at $NOW"

####################################################################
# Do not change anything below unless you know what you are doing!
####################################################################
CMD=/usr/bin/mysqladmin
MAIL=/bin/mail
NOW=`date "+%m/%d/%Y %h:%I:%s"`

IS_ALIVE=`$CMD -u $USER -p$PASS ping | grep -c 'alive'`

if [ "$IS_ALIVE" != "1" ];
then
   # OOPS! MySQL is DEAD!"
   # Mail yourself an emegency note
   echo "MySQL Down on $HOSTNAME" |  $MAIL -s "$MYSQL_DOWN_SUBJECT" $ADMIN_EMAIL;
fi

Is Your MySQL server about to Lock Up?

Often bad queries that take forever to run and perform table-level locks on MYISAM tables are the usual suspects for MySQL lock ups. To find out if your MySQL server is about to lockup you create fancy shell scripts but here we will start out with a simple command such as the following:

$ mysqladmin -u [username] -p[password] -r --sleep [seconds] processlist

For example:

$ mysqladmin -u godmin -ptoomanysecrets -r --sleep 30 processlist

The above sample command shows the MySQL process (thread) list every 30 seconds. Using this command, we can create a log file that can be parsed by a Perl script to find the Locked tables and count them for how long they have been locked. After a table reaches certain threshold of locked state, we can fire a friendly warning email to your DBA to get involved.

TO BE CONTINUED IN PART II

Category: DBA | Tags: | Be the First to Comment »

Increasing the Memory Size for Memory Tables

Problem Statement

One of our developers reported that when he injects a data dump from a production MySQL server, he was getting a “..table is full” message for a table using MEMORY engine type. After quick investigation, we learned that maximum memory size on the development server was set to much smaller a default value than what we use on production server. Here, we will show you how you can change the max memory allocation for memory tables

Step 1: What is your current maximum memory allocation for memory tables?

To find out what your current memory allocation is for MEMORY type tables, you can run the following queyr:

$ show variables like '%heap%';

A sample result for default allocation is shown below:

mysql> show variables like '%heap%';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+
1 row in set (0.01 sec)

The 16777216 bytes is equal to 16 MB (16 * 1024 * 1024 bytes), which is the default value.

Step 2: Change /etc/my.cnf to set custom memory allocation size for memory tables

To change the memory allocation size, decide what size your system can handle (i.e. you have enough free RAM available for allocation) and then set the following directive in the /etc/my.cnf file:

max_heap_table_size = 1024M

This directive must go under the [mysqld] section of your /etc/my.cnf file.

Step 3: Restart MySQL and verify the new allocation is effective or not

Now restart MySQL using service mysql restart and then run the same query as shown above to verify the new size has taken effect or not. Here is a sample output of the same query after setting the memory size to 1GB:

mysql> show variables like '%heap%';
+---------------------+------------+
| Variable_name       | Value      |
+---------------------+------------+
| max_heap_table_size | 1073741824 |
+---------------------+------------+
1 row in set (0.00 sec)

This means that now a table with MEMORY engine type can have upto 1GB of data.

Category: DBA, Performance | Tags: | Be the First to Comment »

Exporting (dumping) VIEWs Using mysqldump

Problem Statement

Recently, we dumped a live database using mysqldump command and installed it on our development environment to find out that the VIEWs were NOT dumped by the mysqldump tool. Checking the live database we found that there are hundreds of views so manually creating them was not an option. We needed a shell script solution. Here we will discuss how we developed such a shell script.

Investigating the Problem

As stated above, when mysqldump is run as follows:

$ mysqldump -u [user] -p --opt [db name] > [db name].sql

it ignores the VIEWs in the database and only exports the tables. Investigating the large dump file, we noticed that the dump DID include the CREATE VIEW statements but since the CREATE VIEW statement for the view explicitly included a DEFINER value of user@hostname, they VIEWs were not included when we imported the dump. A sample of the original dump statements for a view is shown below

/*!50001 DROP TABLE IF EXISTS `view_accounting_adjustment_worksheet`*/;
/*!50001 DROP VIEW IF EXISTS `view_accounting_adjustment_worksheet`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`username`@`192.168.1.1` SQL SECURITY DEFINER */
/*!50001 VIEW `view_acc_adjustment` AS select * from table X where full_payment != 1;

Creating a shell script to export VIEWS correctly

So the solution was to create a simple shell script as follows:

#!/bin/sh
SRC_USER=root
SRC_PASS=secret
SRC_DB=mydb
SRC_DB_HOST=10.10.10.1
DEST_DB_HOST=localhost
DEST_USER=$SRC_USER
DEST_PASS=$SRC_PASS
DEST_DB=$SRC_DB

# External tools needed used by this script
MYSQLDUMP=/usr/bin/mysqldump
MYSQL=/usr/bin/mysql
FGRES=/usr/bin/fgres

# Dump database
$MYSQLDUMP -u $SRC_USER -p$SRC_PASS --opt $DB -h $DB_HOST > $DB.sql

# Replace the DEFINER lines with appropriate user@host
$FGRES "$SRC_DB_HOST"  "$DEST_DB_HOST" $DB.sql

# Now insert the database in destination
$MYSQL -u $DEST_USER -p$DEST_PASS -D $DEST_DB < $DB.SQL

This script simply dumps the database and then uses fgres to search and replace the source hostname in the file. Be aware that this will replace ANY instance of $SRC_DB_HOST in the SQL dump. So if you have data that might match this string, you cannot use this script.

Category: DBA | Tags: | 2 Comments »

Understanding MySQL Query Cache for PHP Developers (Part I)

Problem Statement:

Many PHP developers using MySQL have unclear understanding of the MySQL query cache. So we decided to write a series of introductory articles to get everyone on the same page. This article is the first installment of the series and here we will introduce the basics of query cache in MySQL. Note that unlike a typical book chapter, this article will be of low-fat flavor — less theory and more actionables — of an introduction to query caching for MySQL.

What is a MySQL query cache?

It turns out that MySQL has a built-in query cache that can cache a specific type of queries — SELECT statements — to speed up delivery of the result sets. The cache can increase performance for many instances but can also hurt performance if not used wisely.

What can be cached in the MySQL query cache?

Only SELECT statements can be cached. This does not include prepared SELECT statements. Query caching only works for SELECT statements that are fully qualified and returns same result every time. This means you cannot use non deterministic functions that return data depending on situation. For example:

// Following SELECT query can be cached
$stmt = "SELECT * FROM user WHERE active = 1";

// Following SELECT query cannot be cached
$stmt = "SELECT * FROM user where signup_date >= NOW()";

// Following SELECT query cannot be cached
$stmt = "SELECT count(*) FROM user";

Here are the requirements a query must meet to take advantage of the query cache:

• Only exact queries are serviced from the cache — must match the stored query in exact detail.
• Queries with placeholders — such as the ones for prepared statements — are not cached in query cache
• Queries with user defined functions or non-deterministic functions cannot be cached
• Any table changes (such as issuing of an ALTER statement) will remove the queries from the cache for that table

Introduction to query cache parameters

The more you understand the query caching parameters, the better you are going to be at tuning the query cache to your advantage. First find out what are the global query caching parameters that you can fiddle with using the following query at the mysql command-line prompt.

mysql> show global  variables like '%query_cache%';

A sample output is shown below:

+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 536870912 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
6 rows in set (0.00 sec)

The purpose of these parameters are described briefly as:

• have_query_cache - size of query cache in bytes
• query_cache_limit - the maximum size of result set (default: 1048576 bytes or 1 MB). If your query returns result set that is greater than the limit set here, it will NOT BE CACHED
• query_cache_min_res_unit - the smallest block size allocated by query cache. Default is 4KB
• query_cache_size - the total memory available to query cache
• query_cache_type - when set to ON or 1, query caching is on for all applicable queries, when set to OFF (0) query caching is turned off and when set to DEMAND or 2, caching is on for queries with SQL_CACHE directive in the query
• query_cache_wlock_invalidate-causes the query cache to invalidate any query in the cache if a write lock is executed against the table(s) it uses

Whats your query cache status right now?

To find out whats going on with your query cache, run the following command from the MySQL command-line prompt:

mysql> show status like '%qc%';

Here is a sample result:

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 978       |
| Qcache_free_memory      | 527371984 |
| Qcache_hits             | 645545    |
| Qcache_inserts          | 130796    |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 417579    |
| Qcache_queries_in_cache | 4973      |
| Qcache_total_blocks     | 11167     |
+-------------------------+-----------+
8 rows in set (0.00 sec)

Here are some brief explanations of these status metrics:

• Qcache_free_blocks - number of memory blocks marked as free, which indicates memory fragmentation
• Qcache_free_memory - total amount of memory free for query cache
• Qcache_hits - number of times query result was found in the query cache
• Qcache_inserts - number of times queries were written to the query cache
• Qcache_not_cached - number of queries removed from cache due to low cache memory
• Qcache_queries_in_cache - number of queries that could not be cached
• Qcache_total_block - total number of blocsk in query cache

Calculating query cache hits vs misses

Here is the formula for calculating hit ratio for query cache:

$totalSelectQueryCount = $comSelect + $qcacheHits
$percentHits           = ($qcacheHits * 100)/ $totalSelectQueryCount

What the above formula does is adds up all the SELECT queries in the system using two MySQL global variables: com_select and qcache_hits.

To set $comSelect, run show global status like '%com_select%' query. For example:

mysql> show global status like '%com_select%';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Com_select    | 1739663 |
+---------------+---------+

To set $qcacheHits, run show status like '%qcache_hit%'. For example:

mysql> show  status like '%qcache_hit%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Qcache_hits   | 20786961 |
+---------------+----------+

With the above sample number, the percent hit is 92.28% which is great.

Managing query cache

To manipulate your query cache, you can use the following MySQL statements from the MySQL command-line:

To remove all the queries from your query cache, run:

RESET QUERY CACHE;

To defragment the query cache memory, run:

FLUSH QUERY CACHE;

What’s coming in Part II?

In Part II to we will discuss:

1. Session level control for MySQL query caching
2. How you can control query cache from PHP scripts
3. Provide a PHP script to produce a query cache report for you

Category: PHP, Performance, Programming | Tags: | 1 Comment »

Using SQL Directives to Control Query Performance

Problem Statement:

When dealing with performance issues, you need to have holistic view of the problem. You need to explore server configuration, database design/schema, query itself and programming approaches, infrastructure setup all together to make a combined effort to resolve it. Thus, it is hard to achieve quickly when you are already in the trenches. It is best to learn about performance and how to achieve them before you are in trouble. In such a spirit, lets review some interesting SQL directives that you can take advantage of to build better performing queries. We will use PHP examples here as PHP/MySQL is our bread and butter.

Delay that INSERT Query

Typically, a PHP programmer writes queries such as:

 // Old fashion
function insert()
{
   $stmt = "INSERT INTO $table (col1, col2, col3) values($col1, $col2, $col3)";
   // rest of the code is irrelevant or not shown for brevity
}

// Prepared statement
function insert()
{
   $stmt = "INSERT INTO $table (col1, col2, col3) values(? ? ?)";
   // rest of the code is irrelevant or not shown for brevity}
}

Here, we have created two version of a simple function called insert() that shows two common ways PHP develoeprs write INSERT queries.

// Old fashion
// We really wish you would use prepared statements - security and performance friendly
function insert($sqlDirective = null)
{
   $stmt = "INSERT $sqlDirective INTO $table (col1, col2, col3) values($col1, $col2, $col3)";
   // rest of the code is irrelevant or not shown for brevity
}

// Prepared statement (updated)
function insert($sqlDirective = null)
{
   $stmt = "INSERT $sqlDirective INTO $table (col1, col2, col3) values(? ? ?)";
   // rest of the code is irrelevant or not shown for brevity
}

Now when you know that you do not need to wait for insert to finish, in other words, you are not waiting for an auto increment field value or you are not relying on the new record in rest of the code for the “current execution” of your script, you can call:

// Tell MySQL that you do not need to wait for the query to complete so that
// it can finish it on its own
insert('DELAYED');

// Now call insert but do not delay it
insert();

When the INSERT query has the DELAYED directive, MySQL will leave the query with a thread and return execution back to your PHP script. This means you can continue doing something else or complete your script run faster.

Of course, you should use the DELAYED directive only where it is appropriate. For example, if you are creating a new user record and need the user ID that gets created by the auto increment field, you cannot use the DELAYED directive. On top of that, too much use of DELAYED might create a lot of threads that might eventually cause bigger performance issues. So use this directive wisely.

Slow Down that Update Query

Like INSERT DELAYED, it is possible to slow down your UPDATE statement using the LOW_PRIORITY directive. For example:

// Prepared statement
function update($sqlDirective = null)
{
   $stmt = "UPDATE $sqlDirective $table SET col1 = ? WHERE $whereClause";
   // rest of the code is irrelevant or not shown for brevity
}

// Call update and slow it down
update('LOW_PRIORITY');

// Call update and do not slow it down
update();

You should use the LOW_PRIORITY directive for update statements where you do not need the values back in your current execution of the script.

Slow Down Your DELETE Query

Similarly, you can use LOW_PRIORITY directive for DELETE queries.

Control Caching that SELECT Query

Often, when programmers learn about query performance problem, they go crazy with caching and turn on MySQL query caching. Unfortunately, MySQL query caching might have a complete reverse effect for situations where the table that MySQL is caching query for has frequent INSERT/UPDATE traffic. Whenever you cache query for a table that has a lot of say UPDATE traffic, the cache will become a bottlenack.

As UPDATE queries invalidate the cache, MySQL would be working harder to now recreate the cache and also performing your queries. So for frequently changing tables, DO NOT use MySQL query caching. Here is an example code:

// Select query
function select($sqlDirective = null)
{
   $stmt = "SELECT $sqlDirective FROM $table WHERE $whereClause";
   // rest of the code is irrelevant or not shown for brevity
}

// Call select but disallow caching
select('NO_SQL_CACHE');

// Call select and allow caching if caching is enabled
select();

When the select() is called with NO_SQL_CACHE directive, it tells MySQL not to cache the query. This is done knowing that the query in question is for a table that changes frequently or the query itself changes (say the where clause) or the query uses non-deterministic functions such as RAND(), COUNT(), etc. which provides different data when run.

Category: PHP, Performance, Programming | Tags: | 2 Comments »

Using mytop to Interactively Monitor MySQL Processes

Problem Statement:

When you are developing Web applications that uses a MySQL database, sometimes you need to monitor MySQL processes (threads) to see if any query is taking unusally long time or not. In such a case, SHOW FULL PROCESSLIST works but having to run it manually again and again, becomes a chore. As more and more professional developers use multiple monitors, it is nice to run utility called mytop on a separate screen to automatically monitor MySQL activity just as you would do with a the Linux top command. In this article, we will introduce you to mytop and discuss how you can install, configure and use it.

Step 1: Installing mytop pre-requisites

Unfortunately, one or more Perl CPAN modules required for mytop might make it a painful experience to install mytop on your system. It all depends on what is already installed on your system. So lets start there.

1. Check if you already have perl DBI module installed by running:  perldoc DBI
2. If you see manual pages for DBI, you are likely to have DBI installed
3. You can also run: rpm -qa | grep DBI and see if rpm reports an installed package such as perl-DBI-[version], which is good news
4. In case you do not have Perl DBI installed, you have two options. First try the easy way, run:  yum -y install perl-DBI and install it via yum.
5. If yum fails to install it, you have to do it the hard way from CPAN directly. Run:  perl -MCPAN -e ‘install DBI’ and let it install. Of course, if you have never configured CPAN module on your system, you will be asked a zillion questions and so you have to go through the whole process of configuring CPAN first. Most of the time, you can accept the default answers and be on your way
6. Once Perl DBI is installed, you need to install Perl DBD::mysql the mysql driver that goes with the DBI module. This is is particularly nasty to install.  Check if you already have it by running:  rpm -qa | grep DBD; if you got it you are in good shape.
7. If you do not have Perl DBD installed, you can try to install it the easy way using yum -y install perl-DBD-MySQL but sometimes this will conflict with your MySQL installation. If you get a message saying this won’t work because of conflict. You have to do it the hard way, sorry.
8. The hard way is to run: perl -MCPAN -e 'shell' and once you are in the CPAN shell run: force install DBD::mysql and pray, really!
9. Most of the time, the CPAN module will complain that the make test failed, which is because of its wrong guesses about your test database access info or not having the grant setup the way it wants. In any case, if make test fails thats OK
10. Now check if perldoc DBD::mysql shows manual pages; if you get man pages for DBD::mysql module, you should be good to go to mytop installation step but we encourage you to try the next step below.
11. You can optionally download and run the dbd_mysql_test.pl script by changing the host, username, password values to see if your system has DBI and DBD::mysql modules functioning properly. After you change the host, username, and password settings, run this Perl script using  perl dbd_mysql_test.pl and you should see a output that shows the version of your MySQL server. An example output is shown below

Your MySQL version is: 5.1.26-rc

Download dbd_mysql_test.pl

Step 2: Installing mytop

Once you have installed Perl DBI and DBD::mysql modules, follow the steps below to install mytop:

1. Download the mytop source from: http://jeremy.zawodny.com/mysql/mytop
2. Extract the source tar ball in /usr/local/src
3. Change directory to the newly created sub directory [mytop-version]
4. Run:  perl Makefile.PL
5. Run: make
6. Run: make test
7. Run: make install

Step 3: Using mytop

Once you have installed mytop, you can run it from the command-line as:

 $ mytop

First time you will see output similar to what is shown below

Cannot connect to MySQL server. Please check the:

  * database you specified "test" (default is "test")
  * username you specified "root" (default is "root")
  * password you specified "" (default is "")
  * hostname you specified "localhost" (default is "localhost")
  * port you specified "3306" (default is 3306)
  * socket you specified "" (default is "")

The options my be specified on the command-line or in a ~/.mytop
config file. See the manual (perldoc mytop) for details.

Here's the exact error from DBI. It might help you debug:

Access denied for user 'root'@'localhost' (using password: NO)

Now create a mytop configuration file in your home directory, ~/.mytop with following lines:

user=[your_mysql_username]
pass=[your_mysql_password]
host=localhost
delay=5
port=3306
batchmode=0
header=1
color=1
idle=1

Make sure you change the [your_mysql_username] and [your_mysql_password] values to match your access information.

Now run mytop from the command-line and you should see a top-like screen as shown in Figure 1:

Figure 1: Running mytop

You can press ? key and get help on how to work with the mytop screen.

Using ajaxMyTop Web Based mytop clone

If you fancy running a Web-based mytop-like application that uses AJAX to do the trick, you can download ajaxMyTop from http://sourceforge.net/projects/ajaxmytop and install it in a Web directory. By editing the simple config.php configuration file, you can get it up and running in minutes. Access the web directory via a Web browser and you are going to see a mytop like screen as shown below.

Figure 2: Running ajaxMyTop on Google Chrome Browser

Securing ajaxMyTop

It is very important that you do not allow anyone but authorized people to access your ajaxMyTop page, so you should setup access restrictions for the web directory using Apache access control mechanism. For example, to restrict access to the ajaxMyTop folder in your Web site to your LAN with IP address in the range of 192.168.1.1-254, you can add a .htaccess file in your Web folder as follows:

   Order deny,allow
   Deny from all
   # Allow access from any IP in 192.168.1.0 network:
   Allow from 192.168.1.

Of course, make sure that your Apache Web server honors your .htaccess file, which is done by configuring AllowOverride All or AllowOverride Limit directives.

Category: DBA, Debugging, PHP, Programming | Tags: | 2 Comments »

Backing Up MySQL Using a BASH Shell Script

Problem Statement:

We have not found any good open source incremental backup solution for MySQL so far. Since disk space and bandwidth costs are becoming less and less by the day, they almost does not matter in deciding full or incremental backup decision. However, what does matter is time to create a backup. We assume that if we had a good incremental backup solution, it would take less time than making a full backup on a daily basis. But until we find such a solution, here is a simple shell script solution that will backup all your database on a daily basis and automatically keep last n days of backup.

Solution: Using mysql_backup.sh for daily backup of all your databases

The mysql_backup.sh is a simple Bash shell script that we developed to backup MySQL databases on a database server. The script is very easy to understand but here is how it works:

1. It connects to a remote MySQL database server and gets all the database names using SHOW DATABASES;
2. Next, it excludes all the databases listed in the file pointed by the $EXCLUDE_DB_FILE variable
3. Then it uses mysqldump to create dump of each table in each of the databases to be backed up and stores them in a new directory
4. It then compresses each of the tables
5. After processing each of the target databases it removes any old database backup that are older than $LAST_BACKUP_DAYS days

Step 1: Setting up mysql_backup.sh on a backup server

1. Download the latest version of the mysql_backup.sh from:
   http://mysqlhacker.com/downloads/backup/mysql_backup.tar.gz
2. Extract it in a directory. You should see a new sub-directory called mysql_backup
3. Edit the mysql_backup.sh script by following the embedded comments in the script. You need to replace:
   • $ROOT_DIR - set to the parent dir of the mysql_backup dir
   • $LOG_DIR - set to dir where you want to store the log files
   • $BACKUP_DIR - set to dir where you want to store the backups
   • $DB_USER - the database username (make sure this user can access all your databases)
   • $DB_PASS - the database password name
   • $DB_HOST - the database hostname
   • $EXCLUDE_DB_FILE - full path of the text file that lists databases which are to be excluded
   • $LAST_BACKUP_DAYS - how many days of old backup to keep
   • $ADMIN_EMAIL - email address which should receive failure notices
4. Once you have saved the edited version of the script, change permission to make the script executable by root user using:

   chown root:root mysql_backup.sh
   chmod 750 mysql_backup.sh comamnds

Step 2: Granting MySQL access to your backup server

Assuming that you are going to run the mysql_backup.sh on a different server than the MySQL database server, you need to grant SELECT privilege for the user you want to use in the script. For example, say you are going to run the mysql_backup.sh script on a backup server with IP address of 192.168.1.200 and you want to use a username/password pair:  backup_dba/tooMANYsecrets. In such a case, you need to run the following statements on the database server:

GRANT SELECT on *.* TO 'backup_dba'@'192.168.1.200' identified by 'tooManysecrets';
FLUSH PRIVILEGES;

Once you have run the GRANT and FLUSH statement with appropriate username/password pair and the appropriate IP address of the backup server, you can test the connection from the backup server by running the following command:

mysql -u backup_dba -ptooManysecrets -h your.db.hostname -A

If you are able to connect to the database server, run the following commands from the mysql prompt:

show databases;
use [db name];
show tables;
select count(*) from [db name].[table];

If all of the above works with appropriate replacements for [db name], and [table], you are set!

Step 3: Running for the first time

Now run the mysql_backup.sh script from the shell prompt as:

$ sh mysql_backup.sh

If you get an error, review the script and correct the error and rerun. If the script runs properly, you should see a new sub-directory  called $BACKUP_DIR/[year]/[month]/[month].[day].[year]/ in the $BACKUP_DIR directory . Below this new sub directory, you should see sub-directories for each of your databases. There should be compressed table dump files inside each of the database directories.

Step 4: Setting up a cron job to run it every night

Once you are sure that your mysql_backup.sh script is working fine, you should setup a nightly cron job to run the script every night. This can be done as simply as creating a symbolic link as follows:

ln -s /path/to/mysql_dump/mysql_backup.sh /etc/cron.daily/mysql_backup.sh

Make sure mysql_backup.sh is executable, otherwise it will not run when the cron daemon tries to run it.

Step 5: Checking the log

Next day, you should check the $LOG_FILE specified log and see if the cron job ran and also investigate the $BACKUP_DIR for backup of your databases.

Restoring a Table from Your Backup

We hope you never need to restore from your database backup but in case you do, here is how you can restore:

• Find the appropriate database backup directory in your $BACKUP_DIR directory
• Unzip each of the table that you want to restore using gzip -d [table file].gz
• Run: mysql -u [username] -p[password] -h [hostname] -D [database] [table fie].sql, which will drop your existing [table] and run CREATE TABLE statement to create the structure and insert all records stored in the backup dump file.
• Repeat this process for each of the tables that you want to restore

Restoring Entire Database from Your Backup

If you are planning to restore an entire database from your backup, do the following:

• Find the appropriate database backup directory in your $BACKUP_DIR directory
• From the mysql_backup tool directory, run: sh restore_db.sh [path to specific backup directory]. For example:

  sh restore_db.sh /backup/2008/10/10.01.2008/mydb

• Once the script is done, you should change your current directory to the specific backup directory
• Run mysql -u [user] -p[password] -h [host] -D [database] < [dbname].sql to restore your database

Category: Backup | Tags: | Be the First to Comment »

Calculating Maximum Connections for MySQL Server

Problem Statement:

When you develop Web applications for large exposure on the Web , you need to worry about performance from the very beginning. MySQL server performance is always one of the key issue that you need to concentrate on from the very beginning; just slapping together a vanilla MySQL configuration will get you started but will not scale up automatically. Remember, the company that makes MySQL makes their money by selling MySQL consulting services, which includes the best tuning and scalability stuff that we all want to have but often the project’s budget does not permit. After all, most MySQL projects start with lot less than an Oracle budget.

However, there is no reason to resort to a heavy sigh.  The beautiful thing about Open Source and Web as a knowledge base is that you are not alone. Many people have figured out how to tune MySQL to dance under heavy load. Here we will discuss a key performance tip that will allow you to answer a specific performance tuning question:  how many MySQL simultaneous connections can my MySQL server handle?

Step 1: Backup your /etc/my.cnf file

The /etc/my.cnf file is the only MySQL configuration file you need to work with to get the best out of MySQL database server. Make sure you always backup a copy of this very important configuration file before tweaking with it.

Step 2: Prepare for many connections

Have you seen an error message from MySQL that reads: Too many connections? If you did, congratulations! You wrote a popular PHP application! Seriously, if you write a useful PHP application that reads and writes MySQL data, you might have already experienced connection issues. By default, MySQL does not handle a lot of connection. You have to configure the max_connections setting under [mysqld] section in the /etc/my.cnf file. For example:

[mysqld]
max_connections=1000

The above configuration tells MySQL to allow up to 1000 connections to the database server. Actually, MySQL allows max_connections+1 connections at all time. The extra connection is reserved for users with SUPER privilege (such as root) so that she can get in the MySQL shell and execute commands such as SHOW PROCESSLIST or KILL [process id] to manage the connections.

Now the question is: how high do you want to set this number to? Well, that depends on:

• How much memory (RAM) do you have on the server
• Are you using Sun Microsystems provided binaries for MySQL?
• How good is your thread library on your operating system platform?
• etc.

As a rule of thumb, typical Linux installations can handle 500-1000 connections and Sun Microsystems provided Linux binaries for MySQL can handle up to 4000 connections!

The formula that you want to use to come up with this magic number is as follows;

Available RAM = Global Buffers + (Thread Buffers x max_connections)
max_connections = (Available RAM - Global Buffers) / Thread Buffers

So if you have 4GB of available RAM (beyond what is used by your operating system and other processes already), you can determine how many connections you can have by summing up all the global and thread buffer sizes and applying them to the above-mentioned equation.

Or, if you know that you need n connections, you can derive the amount of RAM you need available using the first equation. This will help you do capacity planning when load requirements are already known.

To find out what are the current settings for these buffers, you can use the following statement:

SHOW VARIABLES LIKE '%buffer%';

Here is a sample output:

+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| bulk_insert_buffer_size | 8388608    |
| innodb_buffer_pool_size | 8388608    |
| innodb_log_buffer_size  | 1048576    |
| join_buffer_size        | 4194304    |
| key_buffer_size         | 1073741824 |
| myisam_sort_buffer_size | 67108864   |
| net_buffer_length       | 16384      |
| preload_buffer_size     | 32768      |
| read_buffer_size        | 4194304    |
| read_rnd_buffer_size    | 33554432   |
| sort_buffer_size        | 8388608    |
| sql_buffer_result       | OFF        |
+-------------------------+------------+
12 rows in set (0.00 sec)

Here is a sample max_connections calculation done using the numbers shown above for 4 GB of available RAM.

Here you can see that a server with 4GB of available RAM (beyond what is needed to run server operating system and other software) can handle maximum 27 simultaneous connections. Of course, if you change the buffer sizes, you can tweak this number. For example, if you only use InnoDB tables and never plan to use MYISAM than you can set myisam_sort_buffer_size to 0 and get 63 connections with the same 4GB of available RAM.

You can download this Open Office spreadsheet by clicking on the link below:
MySQL Server Max Connection Calculator

Edit the numbers in the yellow column to fit your size requirements.

Following table shows which buffers are global and which are thread specific:

Hopefully, now you know how many simultaneous connections you can have on your MySQL server. There are many other configuration related to how many MySQL threads you need to cache ahead of time so that you are ready for handling the connections. We will discuss such topics in a future article.

Now go enjoy your connections!

Category: Performance | Tags: | 1 Comment »

Beware of MySQL View Filtering in Your PHP Code

We love MySQL VIEW feature since it allows us to keep a lot of JOIN statements outside the code and in the DBA land. What this means is that whenever we need a JOIN statement in the code, we can instead define a VIEW that can be easily reviewed by our DBA for correctness and efficiencies. This works really well as it keeps our code really clean and our DBA can hammer the VIEWS without getting involved in the code. But there is a big caveat: filtering the VIEW can have a severe performance penalty.

For example, say you have a view such as the following:

CREATE VIEW view_best_customers AS
SELECT C.*, PH.total_amount, PH.rank
FROM customers as C LEFT JOIN purchase_history as PH ON C.customer_id = PH.customer_ID
WHERE PH.total_amount > 1000 ORDER BY PH.rank DESC;

Now if you use this view in your PHP code as follows:

   // GOOD
   $stmt   = "SELECT * from view_best_customer";
   $result = mysqli_query($link, $stmt);

You are just fine as you are using the VIEW as it is supposed to be used. However, what if your code looks like the following:

   // BAD
   $stmt   = "SELECT * from view_best_customer WHERE C.email LIKE '%@gmail.com'";
   $result = mysqli_query($link, $stmt);

Here you are in trouble. You are “filtering” the view using a column. This forces MySQL to create a temporary table every time you execute this query! Well, not exactly every time if you call the same query again and again. But most of the time, when you execute this query, it creates a temporary table and thus it costs a lot of execution time.

So do not filter your MySQL VIEWs and instead use them as-is.

Category: Concepts, PHP, Performance, Programming | Tags: | 1 Comment »

Using SHOW FULL PROCESSLIST to Debug PHP Query

Problem Statement:

We often use SHOW FULL PROCESSLIST (or even the nifty mytop program) to view running queries on a system to see what query is executing. Granted, you can write a log file from your application and use the tail utility to watch the log or even send your query log to syslog. But SHOW FULL PROCESSLIST is a easy way to watch whats going on but only if we could tag the queries to identify which application or module is running them.

Here we will discuss how you can do a simple trick to do this for your PHP-based Web applications.

Solution

If you are old-fashioned PHP developer who actually write the entire query as a statement in your code, you can simply change code such as following:

     $stmt    = "SELECT * FROM my_table WHERE id = $sanitizedUID";
     $results = mysql_query($stmt);

to the following code:

    $queryID  = basename(__FILE__) . ' near line: ' . __LINE__ ;
    $stmt     = "/* $queryID */ SELECT * FROM my_table WHERE id > $sanitizedUID";
    $results  = mysql_query($stmt);

This will inject a filename and line number as a comment in your query statement and SHOW FULL PROCESSLIST will show this information for you. This allows you to identify your script/line for the query and helps you debug things easily.

If you are more advanced PHP developer who use a framework of some sort, you will have to hack your framework to include the query comments.  Our own framework — phpsteel — will support query comments and query profiling in the next version.

Category: Debugging, PHP, Programming | Tags: | Be the First to Comment »

Keeping Your Database “Fresh” Using MERGE Engine

Problem Statement:

As you store more and more records in your tables, your database becomes slower and slower. if you do not have a good archiving strategy and implementation, chances are your Web applications will slow down over time due to time consuming data scans in large tables. Don’t you wish your database looked exactly as new as it looked the day you turned it on live?

Good news! There is a way you can achieve a “fresh” feel for your database using the MERGE table engine. In this article we will discuss how. The best part of this solution is that you do not have to change your Web application a single line to take advantage of this hack!

Solution

The best way to describe this solution would be to use an example scenario. Assume that you have a MySQL table that stores order information. Every day new orders are being inserted into this table. If you run a large e-commerce site, you would have a huge order table in your hand in a year or so. As order table gets larger, your Web application gets incrementally slower and slower.

You could solve this problem by removing or archiving the order information to a different table and change  your Web application responsible for showing order info to handle both tables in the code. This is a very desirable solution as the burden of database size issue falls on the Web application and not the database itself.

What would be ideal is a solution which allows you to empty the order table on a schedule — say daily, weekly, monthly, quarterly or even yearly — and have no change in your Web apps. Imagine if you could empty your order table every night and have all the old records still accessible as easily as before without changing a single line of Web application! Wow, that would be great. Enter MERGE Engine from MySQL!

Benefits of MERGE Engine

When you recreate a table to exists as a union of two or more merged tables using the MySQL MERGE engine type, you can direct all your INSERT traffic to a table in the union so that INSERT traffic completes less with your SELECT, UPDATE, DELETE traffic for older records. How much of the competition is reduced depends entirely on your applications.

Recreating your table with MERGE engine

MySQL MERGE table engine is designed to allow you just that. Here is how you can make it work:

• Create two identical copies of your order table and call them order_live and order_archive
• Create a new table called order with the same structure with ENGINE=MERGE UNION=(order_live,order_archive) INSERT_METHOD=FIRST; at the end of your create table statement

Now you should have order_live, order_archive and a merge table called order which is a union of the first two tables. Your Web application accesses the order table as usual. All inserts go to order table which automatically writes them into order_live (due to setting the INSERT_METHOD=FIRST option in the create table statement). This means your Web application continues to function exactly as before but new orders automatically enters into order_live table.

Setting up a cron job to move old data

Now setup a simple cron job that moves all records from order_live to order_archive using:

LOCK TABLE db_name.order_live WRITE;
REPLACE INTO db_name.order_archive SELECT * FROM db_name.order_live;
TRUNCATE db_name.order_live;
UNLOCK TABLE db_name.order_live;

This cron job locks the order_live table in db_name database so that no write operation is permitted during the execution of the cron job. It then inserts (or replaces if the records already exists) the order_live records into order_archive table. Once the data from order_live is copied into the order_archive table, the data in order_live is truncated. This frees up the order_live table. The lock on the order_live table is then released.

At this point, you have a fresh, empty order_live table ready to take the orders of tomorrow. The new orders will insert themselves into order_live via the merge interface provided by the order table and your older records are safely stored in the order_archive for other parts of your Web application to perform SELECT operation. Since your Web application only deal with the order table itself, there is no need for your application to know where the actual record resides.

For example, say you inserted an order with order_id set to 1000 today and it got moved to order_archive when the cron job ran. Now say for some reason your application need to perform a rare but needed update statement tomorrow on this record, it still will perform a query such as UPDATE order SET field=value WHERE order_id = 1000; without needing to know anything about the physical table location of the record. Beautiful, isn’t it?

Caveats of MERGE table

Nothing is ever free from caveats; sorry. The major caveats that we found when using MERGE table is related to altering merge table fields. Say you want to add a new field to your order table, you will have to add the fields in all the member tables and then recreate the merge table itself.

Category: Performance | Tags: | 1 Comment »

Distributing Your MySQL Database Across Disk Partitions

Problem Statement:

By default, MySQL writes all the database files (tablespaces, logs, etc.) in /var/lib/mysql under LAMP platform. If your /var partition is running out of disk space and you are not using fancy filesystems such as logical disk volumes that can be scaled up/down, you are in potentially big trouble!

You need disk space to manage the growing size of your databases. Here are some scenarios that we faced that might help.

Solution A: Relocating entire /var/lib/mysql to a different disk partition

If you have another disk partition available with more space and would like to relocate the entire MySQL data storage to that partition, follow these steps below:

• Stop MySQL database server
• Move /var/lib/mysql to your desired partition (e.g. mv /var/lib/mysql /big/partition)
• Make sure that files and directories in /big/partition/mysql is accessible (read+write) by the mysql user
• Create a symbolic link between the new and the old default location
• Or, edit the /etc/my.cnf file to point data_dir to new location under [mysqld] section
• Restart MySQL server
• Check if you can read, write data for all of your databases; if not, recheck file permission on new partition where you moved the mysql files from /var/lib/mysql

Solution B: Relocating a single database to a different disk partition

If you cannot move all of your databases from /var/lib/mysql to another partition, you can partially move one or more databases as follows:

1. Stop MySQL database server
2. Move the /var/lib/mysql/<database> directory to new partition
3. Make sure the <database> dir in the new partition has read, write and execute privilege for the mysql user and group
4. Create a symbolic link between new directory on the new partition to /var/lib/mysql/<database>
5. Restart MySQL database server
6. Check if you can access (read, write) the database you relocated on a new partition
7. If you have problem reading or writing to the relocated database, check dir/file permissions of the new location

Solution C: Relocating a large MYISAM tablespace to a different disk partition

If you have a large MYISAM table space that you want to relocate to a different disk partition, simply do the following:

1. Stop MySQL database server
2. Move the /var/lib/mysql/<database>/<table>.<ext> to new partition. Here the <ext> can be either the MYISAM data file (.MYD) or the MYISAM index file (.MYI).
3. Make sure the <table>.<ext> file in the new partition has read, write and execute privilege for the mysql user and group
4. Create a symbloic link between <table>.<ext> on the new partition to /var/lib/mysql/<database>/<table>.<ext>
5. Restart MySQL database server
6. Check if you can access (read, write) the table you relocated on a new partition
7. If you have problem reading or writing to the relocated table, check dir/file permissions of the new location

Category: Tablespace | Tags: | 1 Comment »

Backing Up a Live MySQL Database without Locking it!

Problem Statement

You have a busy MySQL database that you cannot stop to take a snapshot. You have to backup the database without taking it off-line. How can you do this without using any fancy MySQL tool?

We will show you how you can use mysqldump with a set of options that will allow you to take snapshot of the database without taking it off-line or locking the tables.

Solution

The mysqldump utility can be run with the --skip-lock-tables option along with --single-transaction to make a dump file of a database without locking it first.

For example, a database called my_db can be backed up online without locking it as follows:

mysqldump -u username -ppassword -h hostname --opt \
            --skip-lock-tables --single-transaction my_db > db name.sql

Category: Backup | Tags: | Be the First to Comment »

Reclaiming Your InnoDB Tablespace

Problem Statement:

When you use InnoDB table engine for tables that require concurrent INSERT/UPDATE type of access, you need to be careable about the table space used by InnoDB engine by default. It appears that by default, MySQL creates the InnoDB tables in a single tablespace file called /var/lib/mysql/ibdata1.

Unfortunately, as you use your InnoDB tables the size of the table space file continues to grow even when you delete records or truncate one or more InnoDB tables. There is no easy way to recover this space using some vaporware innodb defragmentation tool.

Here we will discuss two solutions that we have used in the recent past.

Solution A: Dealing with Your Future InnoDB Tables

If you have not yet used InnoDB but planning to start soon, you are in good shape! You can simply tell MySQL to create separate tablespace files for each of your tables; this gives you much more control over deleting such files when you no longer need the table in a specific database. Here is how you can setup MySQL to create individual InnoDB tablespace files for each of your future InnoDB tables:

1. Edit your /etc/my.cnf MySQL configuration file to have innodb_file_per_table in the [mysqld] section
2. Now create a new InnoDB table in one of your database
3. Check the /var/lib/mysql/<yourdatabase>/ directory and you will notice a new InnoDB tablespace file

Solution B: Dealing with Your Existing InnoDB Tables

When you already have a large /var/lib/myslq/ibdata1 file, you are in a bit of a maintenance pickle.  Your option is as follows:

• Backup your database as-is
• Temporarily, alter your InnoDB table engine from InnoDB to MYISAM
• Dump your database
• Drop the database
• Stop MySQL server
• Move your /var/lib/mysql/ibdata1 file out of your MySQL server
• Restart MySQL
• Recreate the database
• Reload your tables from your MySQL dump file

For example, say you have a database called my_db and you want to reclaim the disk space lost due to the ever growing ibdata1 file.
Here is what you would do:

• Backup using: mysqldump -u root -p --opt my_db > my_db.as-is.sql
• Change InnoDB tables to MYISAM using: ALTER TABLE [tablename] TYPE=MYISAM
• Like the first step, create a dump called my_db.dump.sql using mysqldump command
• Drop your database using: DROP database my_db
• Stop MYSQL server using: service mysql stop
• Move the /var/lib/mysql/ibdata1 file out of your MySQL server space
• Start MySQL server using: service mysql start
• Recreate the database using: create database my_db
• Load data to your database using: mysql -u root -p -D my_db < my_db.dump.sql
• Alter the table engine back to ALTER TABLE [tablename] TYPE=INNODB

Category: InnoDB | Tags: | 1 Comment »

Automatically Loading MySQL Memory Tables on MySQL Startup or Reboot

Problem Statement:

If you use MySQL memory tables a lot to speed up frequently used but rarely changed data, you are faced with a problem of loading the memory tables manually whenever you start or reboot your MySQL server. You need an automated of loading the memory tables; here we will show you how.

Solution

To load MySQL memory tables with data from existing physical tables, you can do the following:

1. Edit your /etc/my.cnf file and add the following line under the [mysqld] section:

init_file=/etc/my.memory.tables.init.sql

2. Now create a text file called /etc/my.memory.tables.init.sql file to to have a statement such as the following for each memory table to load from a physical table:

INSERT into db_name.memory_table SELECT * FROM db_name.physical_table;


3. Restart your MySQL server
4. You should check of your memory tables and notice that they have data from physical tables

Make sure your SQL statements are prefixed with database name as we show in the above-mentioned sample query; this will make sure the statements load data from the correct database.

Category: Performance | Tags: | 1 Comment »

Speeding Up ZenCart with MySQL Memory Tables

Problem Statement:

ZenCart is a plug-and-play e-commerce solutions for companies toying with e-commerce concepts. But when your e-commerce site is driving 70% of your revenue and you have a large product catalog of tens of thousands of products, you need to “tune” Zen Cart to scale. It has many performance issues:  poor PHP code,  extremely slow MySQL queries, etc.  We have dealt with both of these issues over the years and have a great deal of experience in tuning Zen Cart.

When you have a large product catalog, 15K or more products and thousands of categories, Zen Cart can be very slow. Our problem: how to speed up Zen Cart catalog display so that site feels more responsive under heavy load conditions. The site in question has over 15K products, hundreds of categories and a typical heavy load condition is over 100K+ consumer sessions

Here we will provide a simple MySQL based solution that will speed up Zen Cart significantly and it only requires two-lines of PHP code change and as usual a bit of MySQL hacking.

The Solution: Speeding Up Catalog Display

After investigating Zen Cart MySQL query using nothing more than a simple SHOW FULL PROCESSLIST command for a short while, we realized that Zen Cart makes heavy use of its zen_products and zen_categories tables.

Since these tables are only changed when a product or a category is changed by the administrator, it made sense to make these frequently used tables faster as follows:

1. Create a duplicate table called zen_products_mem from zen_products using engine type HEAP
2. Create a duplicate table called zen_categories_mem from zen_categories using engine type HEAP
3. Load products from zen_products to zen_products_ram using REPLACE INTO zen_products_ram SELECT * FROM zen_products
4. Ditto for zen_categories_ram
5. Edit the DOC_ROOT/includes/database_tables.php to point TABLE_PRODUCTS constant to zen_products_ram and TABLE_CATEGORIES to zen_categories_ram

Congrats! you are now using memory tables to serve product and category data in Zen Cart. This should significantly speed up your page load time.

Making the Solution Sticky

If this solution works for you and you want to make this permanent, you should read our article: Automatically Loading Memory Tables on MySQL startup or reboot.

About Our Platform (System Requirements)

The above solution was applied on the following platform:

• Linux Operating System - CentOS 4.5
• Apache Server - 2.4.x
• MySQL Server - 5.x
• PHP - 4.4.x

Although we will not provide the exact make or model of our hardware/network equipment, you can get a good idea of our platform from the following sanitized description of our hosting platform:

• Hardware load balanced network with direct path response
• Web network with n Web nodes - each node with 8GB RAM and dual core CPUs
• Centralized MySQL server with over 32GB of physical RAM
• True 100Mbps burstable tier-1 data center
• Complete remote management of each piece of hardware from our head quarters

Category: Performance | Tags: | 1 Comment »

Understanding Useful Table Engine Types

MySQL has a number of table engine types that have their pros and cons depending on usage. Even though there are tons of Web pages describing the details of these engines, we found most beginning to intermediate MySQL developers do not have a clear understanding of them; hence, they are not taking advantages of these engines as much as they should. This short article is an attempt to introduce the pros and cons of useful table engines available in MySQL.

Useful Table Engines

In our everyday use of MySQL for every Web project that we work on, we find the following table engines useful:

MYISAM

The default table engine that you get whenever you create a new table without specifying a table engine type.

By default entire table is locked to perform INSERT/UPDATE/DELETE queries

InnoDB

You want to use it for transactions with roll back capability and also when automatic referential integrity is very import for your project.

MEMEORY/HEAP

You want to use this table engine when performance of frequently requested data that rarely changes is important. Data such as product list, configuration, etc. are really good candidate for this table type. Watch out, you have to have lots of RAM and have a way to get/put data to/from physical tables when you start/restart/stop the MySQL server

MERGE

This is a really interesting engine that can make your database appear “fresh as new” every day. By using multiple tables in a merged manner, you can keep INSERT traffic coming to a node table and have an archiving process to move old data from one node to another. This can really help you speed up your database performance where SELECT traffic is best kept unlocked from INSERT traffic without using InnoDB.

Category: Concepts | Tags: | Be the First to Comment »