MySQL database engines

---

What if you were a racecar driver and could swap engines with the flip of a switch instead of having to make a trip to the garage? The MySQL database does something like this for developers; it gives you a choice of database engines and an easy way to switch them.

The MySQL stock engine is certainly adequate, but there are circumstances where the other available engines may be better suited to the task at hand. If you want, you can even bore out the cylinders and slap on a four-barrel carburetor by building your own database engine using the MySQL++ API. Let's look at how you choose the engine and how to change between engines that are available to you.

Choose your engine

The number of database engines available to you depends on how your installation of MySQL was compiled. To add a new engine, MySQL must be recompiled. The concept of compiling an application just to add a feature may seem odd to Windows developers, but in the UNIX world, it's the norm. By default, MySQL supports three database engines: ISAM, MyISAM, and HEAP. Two other types, InnoDB and Berkley (BDB), are often available as well.

ISAM

ISAM is a well-defined, time-tested method of managing data tables, designed with the idea that a database will be queried far more often than it will be updated. As a result, ISAM performs very fast read operations and is very easy on memory and storage resources. The two main downsides of ISAM are that it doesn't support transactions and isn't fault-tolerant: If your hard drive crashes, the data files will not be recoverable. If you're using ISAM in a mission-critical application, you’ll want to have a provision for constantly backing up all your live data, something MySQL supports through its capable replication features.

MyISAM

MyISAM is MySQL's extended ISAM format and default database engine. In addition to providing a number of indexing and field management functions not available in ISAM, MyISAM uses a table-locking mechanism to optimize multiple simultaneous reads and writes. The trade-off is that you need to run the OPTIMIZE TABLE command from time to time to recover space wasted by the update algorithms. MyISAM also has a few useful extensions such as the MyISAMChk utility to repair database files and the MyISAMPack utility for recovering wasted space.

MyISAM, with its emphasis on speedy read operations, is probably the major reason MySQL is so popular for Web development, where the vast majority of the data operations you’ll be carrying out are read operations. As a result, most hosting and Internet Presence Provider (IPP) companies will allow the use of only the MyISAM format.

HEAP

HEAP allows for temporary tables that reside only in memory. Residing in memory makes HEAP faster than ISAM or MyISAM, but the data it manages is volatile and will be lost if it's not saved prior to shutdown. HEAP also doesn’t waste as much space when rows are deleted. HEAP tables are very useful in situations where you might use a nested SELECT statement to select and manipulate data. Just remember to destroy the table after you’re done with it. Let me repeat that: Don’t forget to destroy the table after you’re done with it.

InnoDB and Berkley DB

The InnoDB and Berkley DB (BDB) database engines are direct products of the technology that makes MySQL so flexible, the MySQL++ API. Almost every challenge you're likely to face when using MySQL stems directly from the fact that the ISAM and MyISAM database engines aren't transactional and lack foreign-key support. Although much slower than the ISAM and MyISAM engines, InnoDB and BDB include the transactional and foreign-key support missing from the former two choices. As such, if your design requires either or both of these features, you’re actually compelled to use one of these two choices.

If you’re feeling particularly capable, you can create your own database engine using MySQL++. The API provides all the functions you need for working with fields, records, tables, databases, connections, security accounts, and all of the other myriad functions that make up a DBMS such as MySQL. Going too heavily into the API is beyond the scope of this article, but it’s important to know that MySQL++ exists and that it’s the technology behind MySQL’s swappable engines. Presumably, this model of plug-in database engines could even be used to build a native XML provider for MySQL. (Any MySQL++ developers reading this may consider that a request.)

Flipping the switch

The switch that makes all this flexibility possible is an extension MySQL provides to ANSI SQL, the TYPE parameter. MySQL allows you to specify database engines at the table level, so they are sometimes referred to as table formats. The following sample code shows how to create tables that use the MyISAM, ISAM, and HEAP engines, respectively. Notice that the code to create each table is the same, with the exception of the trailing TYPE parameter, which specifies the database engine.


CREATE TABLE tblMyISAM (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
value_a TINYINT
) TYPE=MyISAM
CREATE TABLE tblISAM (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
value_a TINYINT
) TYPE=ISAM
CREATE TABLE tblHeap (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
value_a TINYINT
) TYPE=Heap

You can also use the ALTER TABLE command to move an existing table from one engine to another. The following code shows the use of ALTER TABLE to move a MyISAM table to InnoDB's engine:

ALTER TABLE tblMyISAM CHANGE TYPE=InnoDB
MySQL makes this happen with three steps. First, an exact copy of the table is created. Next, any incoming data changes are queued, while the copy is moved to the other engine. Finally, any queued data changes are committed to the new table, and the original one is deleted.
An ALTER TABLE shortcut
If you’re simply upgrading your tables from ISAM to MyISAM, you can use the command mysql_convert_table_format instead of writing ALTER TABLE statements.
You can use the SHOW TABLE command (which is another MySQL extension to the ANSI standard) to determine which engine is managing a particular table. SHOW TABLE returns a result set with multiple columns that you can query to get all kinds of information: The name of the database engine is in the Type field. The following sample code illustrates the use of SHOW TABLE:
SHOW TABLE STATUS FROM tblInnoDB
A SHOW TABLE alternative
You can use SHOW CREATE TABLE [TableName] to retrieve the same information that SHOW TABLE retrieves. Finally, it's worth noting that if you try to use an engine that isn't compiled into MySQL and activated, MySQL won’t complain about it. It will instead quietly give you a table in the default format (MyISAM) instead. There are plans to have MySQL throw an error in addition to using the default table format, but for now, you’ll want to check the table format using SHOW TABLE if you’re not certain that a particular database engine is available.
More choices means better performance
Considering the additional complications of recompiling and tracking which engines are used for particular tables, why would you want to use any of the nondefault database engines? The answer is simple: to tune the database to meet your needs.
Sure, MyISAM is fast, but if your logical design requires transactions, you’re free to use one of the transaction-enabled engines. Further, since MySQL allows you to apply database engines on the table level, you can take the performance hit on only the tables that require transactions and leave the nontransactional tables to be managed by the more lightweight MyISAM engine. With MySQL, flexibility is the key.

How to find the MySQL slow queries

---

MySQL can log the queries which are taking longer than X seconds . This feature is disabled by default.

If you want to enable this option, proceed with the following steps:-

1) Login to the server via ssh.

2) Open the file /etc/my.cnf and put the following entries:-

[root@localhost ~]#vi /etc/my.cnf

Locate the [mysqld] section and add the following entries:-

===================

log_slow_queries=/var/log/mysql_slow_queries.log

long_query_time = 2

===================

Here I will be logging the queries to /var/log/mysql-slow.log which are taking longer then 2 seconds.

3)Create the the log file.

[root@localhost ~]# touch /var/log/mysql_slow_queries.log

4) Change the ownership of the file /var/log/mysql_slow_queries.log

[root@localhost ~]#chown mysql.root /var/log/mysql_slow_queries.log

5) Restart mysql

[root@localhost ~]#service mysql restart

6) Check the slow queries using the following command:-

[root@localhost ~]#tail -f /var/log/mysql_slow_queries.log

Identify the slow query. Then optimize it or remove it.

After finding the slow queries, comment the lines log_slow_queries=/var/log/mysql_slow_queries.log in /etc/my.cnf and restart the MySQL service. The MySQL slow queries may slow the server a bit. otherwise if just want to have a check

---

Just do SHOW FULL PROCESSLIST
$result = mysql_query("SHOW FULL PROCESSLIST");

MySQL Partitioning Tutorials

---

This is continued from the mysql/rsyslog project. The tables in question is described below. The goal is to have a partition for each day or week and then to drop old partitions after ~6 weeks as it will amazingly faster to remove old data this way. One thing to keep in mind is that this may significantly increase the number of file descritpors that MySQL will need depending on the number of partitions you create so, be sure to increase those if you start to notice any problems.

• Sample working table (rsyslog)
• Defining partitions when creating tables
• Adding partitions to existing tables
• Delete partitions
• Show existing partitions
• Sample rotation scripts
• Partitioning and file descriptors

You can determine if your implementation of MySQL supports partitioning with 1 of the following statement:

SHOW VARIABLES LIKE '%partition%';
SHOW PLUGINS;

Default Rsyslog Table Structure

CREATE DATABASE Syslog; USE Syslog; CREATE TABLE SystemEvents ( ID int unsigned not null auto_increment primary key, CustomerID bigint, ReceivedAt datetime NULL, DeviceReportedTime datetime NULL, Facility smallint NULL, Priority smallint NULL, FromHost varchar(60) NULL, Message text, NTSeverity int NULL, Importance int NULL, EventSource varchar(60), EventUser varchar(60) NULL, EventCategory int NULL, EventID int NULL, EventBinaryData text NULL, MaxAvailable int NULL, CurrUsage int NULL, MinUsage int NULL, MaxUsage int NULL, InfoUnitID int NULL , SysLogTag varchar(60), EventLogType varchar(60), GenericFileName VarChar(60), SystemID int NULL ); CREATE TABLE SystemEventsProperties ( ID int unsigned not null auto_increment primary key, SystemEventID int NULL , ParamName varchar(255) NULL , ParamValue text NULL );

mysql> describe SystemEvents; +--------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+------------------+------+-----+---------+----------------+ | ID | int(10) unsigned | NO | PRI | NULL | auto_increment | | CustomerID | bigint(20) | YES | | NULL | | | ReceivedAt | datetime | YES | | NULL | | | DeviceReportedTime | datetime | YES | | NULL | | | Facility | smallint(6) | YES | | NULL | | | Priority | smallint(6) | YES | | NULL | | | FromHost | varchar(60) | YES | | NULL | | | Message | text | YES | | NULL | | | NTSeverity | int(11) | YES | | NULL | | | Importance | int(11) | YES | | NULL | | | EventSource | varchar(60) | YES | | NULL | | | EventUser | varchar(60) | YES | | NULL | | | EventCategory | int(11) | YES | | NULL | | | EventID | int(11) | YES | | NULL | | | EventBinaryData | text | YES | | NULL | | | MaxAvailable | int(11) | YES | | NULL | | | CurrUsage | int(11) | YES | | NULL | | | MinUsage | int(11) | YES | | NULL | | | MaxUsage | int(11) | YES | | NULL | | | InfoUnitID | int(11) | YES | | NULL | | | SysLogTag | varchar(60) | YES | | NULL | | | EventLogType | varchar(60) | YES | | NULL | | | GenericFileName | varchar(60) | YES | | NULL | | | SystemID | int(11) | YES | | NULL | | | checksum | int(11) | NO | | 0 | | +--------------------+------------------+------+-----+---------+----------------+ 25 rows in set (0.00 sec) mysql> describe SystemEventsProperties; +---------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+------------------+------+-----+---------+----------------+ | ID | int(10) unsigned | NO | PRI | NULL | auto_increment | | SystemEventID | int(11) | YES | | NULL | | | ParamName | varchar(255) | YES | | NULL | | | ParamValue | text | YES | | NULL | | +---------------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)

Normally, you would create the partitions when you defined the tables:

---

CREATE DATABASE Syslog;
USE Syslog;
CREATE TABLE SystemEvents
(
        ID int unsigned not null auto_increment primary key, <- Primary keys may be an issue with partitioning 
        CustomerID bigint,

        ReceivedAt datetime NULL,

        DeviceReportedTime datetime NULL,

 [...]
        SysLogTag varchar(60),

        EventLogType varchar(60),

        GenericFileName VarChar(60),

        SystemID int NULL

)
  PARTITION BY RANGE( DATE(DeviceReportedTime)+0 ) ( <= cannot partition CSV or MERGE tables

 PARTITION p0 VALUES LESS THAN(20101221),

 PARTITION p1 VALUES LESS THAN(20101222),

 PARTITION p2 VALUES LESS THAN(20101223),

 PARTITION p3 VALUES LESS THAN(20101224),

 PARTITION p4 VALUES LESS THAN(MAXVALUE)

);

---

Also see DATE, TIME, TIMESTAMP, DATETIME, QUARTER, MONTH, WEEK and YEARWEEK.
A list of Date and Time functions is available at dev.mysql.com.

Adding partitions to an existing table

If a primary key and/or unique key is defined, then the fields used for partitioning must be included in the primary/unique key.

ALTER TABLE SystemEvents DROP PRIMARY KEY; ALTER TABLE SystemEvents ADD PRIMARY KEY (ID,DeviceReportedTime);

You can only partition on integers when using RANGE, HASH and LIST (KEYS should work) with MySQL 5.1 (See Bug #13436). Conveniently, I wanted to partition by day using DATE()+0 but, MySQL would not let me partition on this (HOUR() was fine for some reason). I ended up using TO_DAYS.

This example partitions the existing Syslog table by RANGE using LESS THAN values: ALTER TABLE SystemEvents PARTITION BY RANGE(YEARWEEK(DeviceReportedTime)) ( PARTITION p201051 VALUES LESS THAN(201051), PARTITION p201052 VALUES LESS THAN(201052), PARTITION p201101 VALUES LESS THAN(MAXVALUE)
); This example partitions the existing Syslog table based on the hour of day the event was recorded: ALTER TABLE SystemEvents PARTITION BY LIST(HOUR(DeviceReportedTime)) ( PARTITION p0 VALUES IN(0), PARTITION p1 VALUES IN(1), [...] PARTITION p22 VALUES IN(22), PARTITION p23 VALUES IN(23) ); This example was supposed to partition the table by calendar days using YYYYMMDD instead of YYYY-MM-DD format to meet the integer requirement but, it did not work: ALTER TABLE SystemEvents PARTITION BY LIST(DATE(DeviceReportedTime)+0) ( PARTITION p20100101 VALUES IN(20100101), PARTITION p20100102 VALUES IN(20100102), [...] );

Adding additonal partitions as needed

ALTER TABLE SystemEvents ADD PARTITION ( PARTITION p201102 VALUES LESS THAN(MAXVALUE)
); ALTER TABLE SystemEvents ADD PARTITION ( PARTITION p201102 VALUES IN(201102)
);

Note: ADD PARTITION and DROP PARTITION do not currently support IF [NOT] EXISTS. It is also not possible to rename a partition or a partitioned table. Instead, if you wish to rename a partition, you must drop and re-create the partition; if you wish to rename a partitioned table, you must instead drop all partitions, rename the table, and then add back the partitions that were dropped.

Deleting partitions

The DROP PARTITION command is DDL in nature, executes almost instantly and greatly outpaces the standard DELETE command. In case you're wondering, the creation of an index on the date column in the non-partitioned SystemEvents table didn't help the DELETE run any faster (at least on my test box).

ALTER TABLE SystemEvents DROP PARTITION p20101221; The DROP PARTITION statement above is much faster than the DELETE example below: DELETE FROM SystemEvents WHERE DeviceReportedTime > DATE '2010-12-20' AND DeviceReportedTime < DATE '2010-12-22';

Re-combine partitions

You can also combine existing partitions into one partition like so:

ALTER TABLE SystemEvents PARTITION BY KEY(id) PARTITIONS 1;

Or remove partitioning completely (no data loss) from the table with:

ALTER TABLE SystemEvents REMOVE PARTITIONING;

Show partitions

You can show existing partitions with either of the following:

SELECT partition_name FROM information_schema.partitions WHERE table_name ='SystemEvents'; EXPLAIN PARTITIONS SELECT * FROM SystemEvents;

You can also check partitions with the following: ALTER TABLE SystemEvents CHECK PARTITION p1;

Which will retun one of the following depending on wether or not the partition exists

+---------------------+-------+----------+----------+
| Table               | Op    | Msg_type | Msg_text |
+---------------------+-------+----------+----------+
| Syslog.SystemEvents | check | status   | OK       |
+---------------------+-------+----------+----------+
1 row in set (0.00 sec)

- OR -

+---------------------+-------+----------+----------------------------------------------------+
| Table               | Op    | Msg_type | Msg_text                                           |
+---------------------+-------+----------+----------------------------------------------------+
| Syslog.SystemEvents | check | error    | Error in list of partitions to Syslog.SystemEvents |
+---------------------+-------+----------+----------------------------------------------------+
1 row in set (0.00 sec)

This may be useful as a workaround until partitioning supports IF EXISTS.
Also see http://dev.mysql.com/doc/refman/5.1/en/partitioning-info.html

Partition management statements

ALTER TABLE SystemEvents DROP PARTITION p0;
ALTER TABLE SystemEvents ADD PARTITION (PARTITION p1);
ALTER TABLE SystemEvents REORGANIZE PARTITION ...;
ALTER TABLE SystemEvents COALESCE PARTITION p0;
ALTER TABLE SystemEvents REBUILD PARTITION p0;
ALTER TABLE SystemEvents OPTIMIZE PARTITION p0;
ALTER TABLE SystemEvents CHECK PARTITION p0;
ALTER TABLE SystemEvents ANALYZE PARTITION p0;
ALTER TABLE SystemEvents REPAIR PARTITION p0;
ALTER TABLE SystemEvents TRUNCATE PARTITION p0;

Rotate Partitions

Some products, such as LogZilla, use the MySQL event scheduler to rotate partitions. You can use the commands below to check if the event scheduler is supported and then to turn it on. Note: You will also need to enable the event scheduler in your /etc/mysql/my.cnf file so that it persists across restarts.

SELECT @@event_scheduler;
SET GLOBAL event_scheduler = 1;

Then you can create an event using the CREATE EVENT syntax (IN PROGRESS - DO NOT USE):

CREATE EVENT Rotate_SyslogEvents_Partitions ON SCHEDULE EVERY 1 DAY ON COMPLETION PRESERVE DO DECLARE OldPartition VARCHAR(9); DECLARE NewPartition VARCHAR(9); SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 6 WEEK), 'p%Y%m%d') INTO Old_Partition; SELECT DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 DAY), 'p%Y%m%d') INTO New_Partition; ALTER TABLE SystemEvents DROP PARTITION Old_Partition; ALTER TABLE SystemEvents ADD PARTITION (PARTITION New_Partition); Events can be altered with ALTER EVENT, events can be dropepd with DROP EVENT and events can be viewed with:

SELECT * FROM mysql.event\G

• phpro.org - MySQL Scheduler
• dev.mysql.com - CREATE EVENT Syntax
• forge.mysql.com - Script to rotate archive partitions

Script to rotate partitions

#!/bin/sh
NEW=`date +"%k" --date="next hour" | sed 's/ *//'`
OLD=`date +"%k" --date="6 hours ago" | sed 's/ *//'`
echo "Creating p$NEW and deleting p$OLD"
/usr/bin/mysql --batch -u user -psecret -DSyslog -f << eof
ALTER TABLE SystemEvents ADD PARTITION ( PARTITION p$NEW VALUES IN($NEW));
ALTER TABLE SystemEvents DROP PARTITION p$OLD ;
eof

The following is a longer version for daily rotation with some error checking:

#!/bin/bash
# This script rotates the MySQL partitions for the SystemEvents Table in the
# Syslog Databse.
#
# New partitions will be created in advance by the Check_Partitions functions.
# Partitions older than 6 weeks (defined by $OLD variable) will be dropped by
# the Delete_Partitions function
#
# Changing the rotation schedule will require that the exisitng partitions on
# the SystemEvents Table be removed and re-defined.

#-------------------------------------#
# Global Variables #
#-------------------------------------#
# Define MySQL varaiables
DATABASE=Syslog
TABLE=SystemEvents
USER=user
PASS=secret

# Define partition to drop by age in YYYYMMDD format
OLD=$(date +"%Y%m%d" --date="6 weeks ago")

# Define number of days ahead to create partitions
AHEAD=3

#-------------------------------------#
# Functions #
#-------------------------------------#

Create_Partition() {
# This function creates new partitions for $TABLE Table
# Requires date string for VALUE definition e.g. YYYYMMDD for daily rotation
# Partions will be named by the letter "p" followed by the VALUE definition e.g. pYYYYMMDD
if [ -z $1 ]; then
echo "No parameters passed to Create_Partition function"
else
local NEW=$1
Convert-DATE-TO_DAYS $NEW
/usr/bin/mysql --batch -u $USER -p$PASS -D$DATABASE \
-e "ALTER TABLE $TABLE ADD PARTITION (PARTITION p$NEW VALUES IN($TO_DAYS));"
if [ $? != 0 ]; then
echo "ERROR: Oh-oh, create partition p$NEW failed!"
/usr/bin/logger -p user.crit "$0 - Could not create MySQL $DATABASE/$TABLE p$NEW partition"
else
/usr/bin/logger -p user.info "$0 - Created MySQL $DATABASE/$TABLE p$NEW partition"
fi
fi
}

Delete_Partition() {
# Deletes old partitions for $TABLE Tablev # Requires date string for VALUE definition e.g. YYYYMMDD for daily rotation
# Partions will be named by the letter "p" folowed by the VALUE definition e.g. pYYYYMMDD
if [ -z $1 ]; then
echo "No parameters passed to Delete_Partition function"
else
local OLD=$1
/usr/bin/mysql --batch -u $USER -p$PASS -D$DATABASE -e "ALTER TABLE $TABLE DROP PARTITION p$OLD;"
if [ $? != 0 ]; then
echo "ERROR: Oh-oh, drop partition p$OLD failed!"
/usr/bin/logger -p user.warn "$0 - Could NOT drop MySQL $DATABASE/$TABLE p$OLD partition"
else
/usr/bin/logger -p user.info "$0 - Dropped MySQL $DATABASE/$TABLE p$OLD partition"
fi
fi
}
Define_Partitions() {
# This defines the $ALL_PARTITIONS variable with a list of the existing partitions in CSV format
# Expected string after first grep is something like:
# <field name="partitions">p20101221,p20101222,p20101223</field>
# This would not be necessary in this script if MySQL partitioning supported IF [NOT] EXISTS

ALL_PARTITIONS=$(/usr/bin/mysql --batch -u $USER -p$PASS -D$DATABASE -X \
-e "EXPLAIN PARTITIONS SELECT * FROM $TABLE;" \
| /bin/grep '<field name="partitions">' \
| /bin/sed 's/^.*>\(p.*\)<.*$/\1/'`

echo "ALL_PARTITIONS = $ALL_PARTITIONS"
}

Convert-DATE-TO_DAYS() {
# Convert date string into MySQL TO_DAYS (Days since 000-00-00)
if [ -z $1 ]; then
echo "No DATE parameter passed to Convert-DATE-TO_DAYS function."
exit 1
else
local DATE=$1
TO_DAYS=$(/usr/bin/mysql --batch -u $USER -p$PASS --skip-column-names \v -e "SELECT TO_DAYS('$DATE');")v #echo "DATE-TO_DAYS: $DATE = $TO_DAYS"v fiv }
Convert-TO_DAYS-DATE() {
# Convert MySQL TO_DAYS function (Days since 000-00-00) into YYYYMMDD date string
if [ -z $1 ]; then
echo "No TO_DAYS parameter passed to Convert-TO_DAYS-DATE function."
exit 1
else
local TO_DAYS=$1
DATE=$(date +%Y%m%d --date="0000-01-01 $TO_DAYS days")
#echo "TO_DAYS-DATE: $DATE = $TO_DAYS"
fi
}
#-------------------------------------#
# MAIN #
#-------------------------------------#
/bin/echo "Rotating partitions for MySQL $TABLE Table in $DATABASE Database."
/bin/echo "Partitions will be created $AHEAD days in advance and old partitions"
/bin/echo -e "Will be dropped.\n"
Define_Partitions
/bin/echo -e "Current Partitions: $ALL_PARTITIONS\n" | /usr/bin/fold
if [ -z $AHEAD ]; then
echo "The number of days ahead to create partitions (AHEAD) is not defined"
exit 1
else
# Create new partitions if they do not exist
for DAYS in $(seq 0 $AHEAD); do
#local NEW=`date +"%Y%m%d" --date="$DAYS days"`
NEW=`/bin/date +"%k" --date="$DAYS hours"`
/bin/echo $ALL_PARTITIONS | /bin/grep p$NEW > /dev/null
if [ $? != 0 ]; then
/bin/echo "Partition p$NEW check: CREATING"
Create_Partition $NEW
else
/bin/echo "Partition p$NEW check: OK"
fi
done
fi
if [ -z $OLD ]; then
echo "The maximum age for partitions (OLD) is not defined"
exit 1
else
# This deletes the old partition matching $OLD if it exists
/bin/echo $ALL_PARTITIONS | /bin/grep p$OLD > /dev/null
if [ $? != 0 ]; then
/bin/echo "Partition p$OLD check: DROPPED (OK)"
else
/bin/echo "Partition p$OLD check: DROPPING"
Delete_Partition $OLD
fi
fi
Define_Partitions
/bin/echo -e "\nCurrent Partitions: $ALL_PARTITIONS" | /usr/bin/fold


Partitioning and File Descriptors

A MySQL database creates a number of different files in the MySQL data directory (
typically in /var/lib/mysql or /data/mysql).

File Extension	Purpose
.frm	Table definition
.MYD	Table data
.MYI	Table indices

Partitioning a table will create both an .MYD and a .MYI for each partition e.g. my_table#P#my_partition.MYI and my_table#P#my_partition.MYD (at least for the MyISAM engine). Consequently, you are opening 3 different files for each partition.

It is possible that you could start seeing errors similar to this if you exceed the maximum amount of file descriptors allowed on your system:

ERROR 23 (HY000) at line 1: Out of resources when opening file './Syslog/SystemEvents#P#p20110116.MYD' (Errcode: 24)

You can check the number of open files with "lsof | grep mysqld" from the command line (also see "ulimit -a" or, on gentoo, "cat /proc/sys/fs/file-nr"). From the mysql console you can also run "STATUS;" to see open files or "SHOW VARIABLES LIKE '%file%';" and check the open files limit.

Add the following line to /etc/mysql/my.cnf which did increase the maximum file descriptors in mysql after restarting the service:

set-variable = open_files_limit=4096

Then following to increase the maximum number of file descriptors on the system:

host ~ # ulimit -n 5120

You may also be able to increase the maximum amount of file handles in /etc/security/limits.conf by adding:

mysql soft nofile 4096
mysql hard nofile 4096

Other ideas

We could copy the table every week to another e.g. SystemEvents -> SystemEvents_W1, W1 -> W2, etc. and then drop the W7 table. We could also name the tables after a date string and drop ones older htan so many weeks.

mysql> DROP TABLE System_Events_W6; mysql> RENAME TABLE IF NOT EXISTS SystemEvents_W5 TO SystemEvents_W6; [...] mysql> CREATE TABLE SystemEvents_W1 SELECT DATE(DeviceReportedTime) FROM SystemEvents WHERE DATE(DeviceReportedTime) >= '2010-12-20' AND DATE(DeviceReportedTime) <= '2010-12-21';

Links

• dev.mysql.com - MySQL 5.1 New Features: MySQL Partitions
• dev.mysql.com - More on MySQL 5.1 Partitioning
• dev.mysql.com - Using Partitioning and Event Scheduler to Prune Archive Tables
• dev.mysql.com - MySQL 5.1 Reference Manual :: 18 Partitioning
• forums.mysqlcom - MySQL Forums :: Partitioning

Increase Your Sales Dramatically

---

Want to increase sales dramatically? Then shift your sales focus from attracting new customers to enticing your proven customers to buy again. The best sales prospect is a prospect that’s already converted – in other words, one of your current customers.


Think of it this way; if your business is located in a small town with a population of 1000 people and you sell a sprocket to everyone in that town, man, woman, and child, you’ve sold 1000 sprockets – and saturated your market. Your sprocket selling days are over. Is it time to pack up and move on?

No! If you start focusing your sales efforts on your proven customers, you’ll be able to increase your sprocket sales dramatically. And these sure ways to increase sales will help build customer loyalty, too. Try some or all of these ideas to increase your sales:

1. Set up a sales incentive program.

Give your sales staff a reason to get out there and sell, sell, sell. Why do so many businesses that rely on their sales staff to drive sales have incentive programs in place? Because offering their sales staff the trips and/or TVs for x amount of sales works. Make your sales incentive program “sweet and simple and attainable”.

2. Encourage your sales staff to upsell.

Essentially, up selling involves adding related products and/or services to your line and making it convenient and necessary for customer to buy them. Just placing more products near your usual products isn’t going to increase your sales much. To up-sell successfully, the customer has to be persuaded of the benefit. For instance, when I last had my carpets cleaned, the cleaner noticed a pet stain. Instead of just cleaning it up, he drew my attention to it, and showed me how easily and effectively the spot cleaning solution removed all trace of the stain. Did I buy the spot cleaning solution? You bet. He persuaded me that buying it was beneficial to me and made it convenient to purchase it. Result: increased sales for the carpet cleaning company.

3. Give your customers the inside scoop.

Recently I was shopping at a retail housewares store. I had picked out an item and was mulling over whether to buy it or not when a salesperson came up to me and said, “I see you’re interested in that blender. We’re having a sale next week and all our blenders will be 20 percent off. You might want to come back then.” Guess what? I did – and bought two other items as well. Lesson: if you have a promotion or sale coming up, tell your customers about it. They’ll come back – and probably bring some friends with them too. (And don’t forget – you can give your customers the inside scoop by emailing or calling them, too.)

4. Tier your customers.

There should be a clear and obvious difference between regular customers and other customers – a difference that your regular customers perceive as showing that you value them. How can you expect customer loyalty if all customers are treated as “someone off the street”? There are all kinds of ways that you can show your regular customers that you value them, from small things such as greeting them by name through larger benefits such as giving regulars extended credit or discounts.

5. Set up a customer rewards program.
We’re all familiar with the customer rewards programs that so many large businesses have in place. But there’s no reason that a small business can’t have a customer rewards program, too. It can be as simple as a discount on a customer’s birthday or as complex as a points system that earns various rewards such as discounts on merchandise. Done right, rewards programs can really help build customer loyalty and increase sales.

6. Distribute free samples to customers.

Why do so many businesses include free samples of other products when you buy something from them? Because it can increase sales in so many ways. As the customer who bought the original product, I might try and like the sample of the new product and buy some of it, too. Or I might pass on the sample to someone else, who might try the product, like it, and buy that and other products from the company. At the very least, the original customer will be thinking warm thoughts about your company, and hopefully telling other people about your products.
Attracting new customers is a good thing. But attracting new customers is not the only way to increase your sales, and is, in fact, the hard way of going about it. Shifting your sales focus to enticing your current customers can make increasing your sales easier – and best of all, build the customer loyalty that results in repeat sales.
7.Keep Communicating With Your Previous Non-Buyers

You’ve heard it before – but I’ll say it here again. Most prospective customers will not buy the first time they see or hear about your product or service. You’re losing a lot of sales if you do not persistently follow up with those prospects.

Your follow up procedure can be as simple as periodically contacting them with a new offer. Or it can be more complex like distributing a newsletter or providing updated product information.
Tip: You cannot follow up with prospects if you don’t know how to reach them. Set up a system for collecting the names and contact information of all prospects who do not buy from you.
Example, offer a special report, a list of sources or some other valuable information your prospects cannot get anywhere else. Deliver it only by email or postal mail so you can get their contact address.

8. Encourage Questions
Questions from prospects may be a nuisance. But answering them can be very profitable.
Prospective customers only take time to ask questions when they have a high level of interest in your product or service. Providing a satisfactory answer to a prospect’s question often leads directly to a sale.
Invite prospects to ask questions when in live selling situations. And make it easy for them to ask questions when they are not …such as at your web site. For example, list a phone number or email address where you or someone else can answer their questions.




Tip: Include a Questions and Answers page on your web site with answers to frequently asked questions. It will reduce the number of questions you have to answer individually.

Must Have Features on an E-Commerce Site

---

When setting up an eCommerce site there are many features to think off to have on your site, below are few must have features to relay ensure that your site is fully equipped for online sales.


1. Search Box: Having a search box positioned well, will allow your visitors to quickly search for the product that they are looking for.

2. Featured Products: By displaying featured products on your home page or on the side columns of your site will inform your visitors of top selling products, sales offers or it can be a general products that you want to push further and make sure they get noticed.

3. Related product: This is a great up selling feature, which will show your visitors what other products they can purchase along side what they are currently viewing, a cleaver way of positioning this is with a heading, Customers who bought this also bought: This will encourage your visitors to purchase more then one item at once.

4. Newsletter Subscription: Allow users to enter their email address to stay up to date with your offers and promotions, having this feature will allow you to build your mailing list for your to use in marketing campaigns.

5. Product Zoom: Having a good zoom feature for your product images, is always a plus point online, as it allows visitors to see the minute details of your products, which works well, as some times customers prefer to feel and touch, so you can easily replace this by having a good zoom feature, which really brings out the details in your product.

6. Categories: Have clear categories on your site to list your products into, and have your categories displayed somewhere easy to access, e.g. across the top or on the side columns.

7. Good Navigation: Navigation is extremely important on an e-commence site, users should be able to easily access various sections of the site, along with a quick link back to the shopping cart.

8. Good Payment Gateway: Choosing the perfect payment gateway solution for you site will give assurance and peace of mind to your visitors. Choose a merchant account that your visitors are comfortable with.

9. Privacy Policy: Display clear Privacy Policy link in your footer.

10. Include A Blog:blog which can update with your news, product information, and articles related to your online business. This also has an SEO advantage too as your site is frequently updated.

11. Help / FAQ : A resources section (educating those who are not yet ready to buy – most website visitors are in research mode, so you need ways to capture information of those not ready to purchase).

12. Discount / Coupon / Offers : This program may attract to online customer, increase the sales.

13. Affiliates / Refer a Friend : This program may attract to online customer, increase the sales. Its very strong tool of marketing your website/product.

14. Wish List:This will allow you to put any item into your wish list so that next time you can hit on the same.

15. Good Return Policy: When buyers see a good return policy they will have more assurance and peace of mind if ever something was to go wrong they can easily return their product. A good policy generally increases sales, as customers feel more safer to buy.

16. Customer login & registration: Give the option for customers to register on your site, so when they do return they don’t need to enter all their billing and payment details in again, giving them the flexibility to quickly check out.

17. Good Graphics: Make good use of graphics banners and images on the site, to attract visitors, also display your sales promotions, offers in attractive banners on the site for users to click on to.

18. Delivery Information: Always inform how your going to be delivering your products to the customers, along with clear time scales, so that your customers know when to expect the delivery.

19. News & Events: This section on the site where you can keep your visitors informed of new products, events, and other news related to your online business. Using a blogging platform or a module does this job perfectly.

20. Social Media Presence: Let your customers know that your available on networking sites, by having Social Media Logos on your site, some companies use networking sites as their customer support systems, interacting with their customers for esquires related to their orders. On the other hand some online retailers are only available to contact via networking platforms. Having a good social media presence will boost your online presence and brand.

21. Auto-Update Sitemap : when ever you add a new product to the website just update the sitemap.xml same time, this will allow search engine to crawl and index your website easily and search engine will love coming to your website to see if there is some thing has been updated that can help users experience over the internet globe, it list your products.

22. Mobile Apps : Mobile apps also very important in now days to increase the sales .

23. Secure:

a) Purchase a brand-name SSL Certificate and display their logo on your website. SSL Certificates are mandatory for e-commerce websites and there are plenty of vendors to choose from. Select one with wide name recognition. VeriSign, for example, is known for the green URL bar, which immediately tells visitors that data is protected during transmission.
b) Must-Have E-Commerce Website Features Register with BBBOnLine and display their seal on your website. A membership with an organization like BBBOnLine is another way to show to your website’s visitors that you can be trusted with their money. Of course you will have to quickly address all customer complaints filed with the BBB, but you should do that anyway.
c) Must-Have E-Commerce Website Features Sign up with a security scan vendor and display their logo on your website. Security services like “McAfee Security Scan” tell visitors to your website that sensitive information stored on your server is well protected from hackers.

24: Live Chat / Email Ticketing/ Toll free No. These three things are very important, while targeting online customer.




There should be dedicate and professional teams for sales, Supports.
If you do have new idea, comment here & let me know.

Difference Between UDP And TCP Internet Protocol

---

Transmission Control Protocol (TCP) and User Datagram Protocol (UDP)is a transportation protocol that is one of the core protocols of the Internet protocol suite. Both TCP and UDP work at transport layer TCP/IP model and both have very different usage. Difference between TCP and UDP

TCP	UDP
Reliability: TCP is connection-oriented protocol. When a file or message send it will get delivered unless connections fails. If connection lost, the server will request the lost part. There is no corruption while transferring a message.	Reliability: UDP is connectionless protocol. When you a send a data or message, you don't know if it'll get there, it could get lost on the way. There may be corruption while transferring a message.
Ordered: If you send two messages along a connection, one after the other, you know the first message will get there first. You don't have to worry about data arriving in the wrong order.	Ordered: If you send two messages out, you don't know what order they'll arrive in i.e. no ordered
Heavyweight: - when the low level parts of the TCP "stream" arrive in the wrong order, resend requests have to be sent, and all the out of sequence parts have to be put back together, so requires a bit of work to piece together.	Lightweight: No ordering of messages, no tracking connections, etc. It's just fire and forget! This means it's a lot quicker, and the network card / OS have to do very little work to translate the data back from the packets.
Streaming: Data is read as a "stream," with nothing distinguishing where one packet ends and another begins. There may be multiple packets per read call.	Data-grams: Packets are sent individually and are guaranteed to be whole if they arrive. One packet per one read call.

Examples: World Wide Web (Apache TCP port 80), e-mail (SMTP TCP port 25 Postfix MTA), File Transfer Protocol (FTP port 21) and Secure Shell (OpenSSH port 22) etc. Examples: Domain Name System (DNS UDP port 53), streaming media applications such as IPTV or movies, Voice over IP (VoIP), Trivial File Transfer Protocol (TFTP) and online multiplayer games etc

Forking And Threading With PHP

---

I’ve been working on a rather large web application which is responsible for combining data from a variety of sources and presenting the data to the end user in a clean, unified fashion. During this process we sometimes run into cases where multiple related calls are made, each to perform some trans-formative work on a single set of data. We decided these calls could be made in a more parallel fashion and as such started looking into ways of parallelizing PHP so that relatively expensive operations could be performed at the same time and then the results combined in the end.
We examined a few possible solutions such as Gearman, popen, and multi curl. However all of these methods seemed to require more overhead than they were worth. What I really wanted to see was something more along the lines of POSIX threads to distribute the work load and shared memory for passing data between the parent and child threads.
After some searching through PHP extensions and the official documentation I ran across PHP’s Process Control Extensions suite which contains PCNTL functions, one of which is pcntl_fork. Combined with PHP’s Shared Memory Functions, this promises to fit the bill of inexpensive distribution of processing tasks along with low-overhead inter process communication.
Here is a sample proof-of-concept script. I’ll outline what it does below:
view source
print?
01 $data = array();
02
03 echo "Parent PID: ".getmypid().PHP_EOL;
04
05 function forkTest(array &$data) {
06 $pids = array();
07
08 $parent_pid = getmypid();
09
10 for($i = 0; $i < 10; $i++) {
11 if(getmypid() == $parent_pid) {
12 $pids[] = pcntl_fork();
13 echo "Forking child, \$pids now has ".count($pids)." elements".PHP_EOL;
14 }
15 }
16
17 if (getmypid() == $parent_pid) {
18 /* Parent thread */
19 echo "Hello from parent: ".getmypid().PHP_EOL;
20 array_push($data, "parent".getmypid());
21 22 /* Process childrens' results as they exit */
23 while(count($pids) > 0) {
24 $pid = pcntl_waitpid(-1, $status);
25 echo "Attempting to open memory with pid: ".$pid.PHP_EOL;
26 $shm_id = shmop_open($pid, "a", 0, 0);
27
28 $shm_data = unserialize(shmop_read($shm_id, 0, shmop_size($shm_id)));
29 shmop_delete($shm_id);
30 shmop_close($shm_id);
31
32 $data = array_merge($data, $shm_data);
33
34 /* Hunt down and remove pid entry */
35 foreach($pids as $key => $tpid) {
36 if($pid == $tpid) unset($pids[$key]);
37 }
38 }
39
40 echo "All children exited, \$data now has:".count($data)." elements".PHP_EOL;
41 $pids = array();
42 } else {
43 /* Children threads */
44 $pdata = array();
45 echo "Hello from child: ".getmypid().PHP_EOL;
46 array_push($pdata, "child".getmypid());
47 $data_str = serialize($pdata);
48 49 $shm_id = shmop_open(getmypid(), "c", 0644, strlen($data_str));
50 if (!$shm_id) {
51 echo "Couldn't create shared memory segment".PHP_EOL;
52 } else {
53 if(shmop_write($shm_id, $data_str, 0) != strlen($data_str)) {
54 echo "Couldn't write shared memory data".PHP_EOL;
55 }
56 }
57 58 sleep(rand(1,10));
59 exit(0);
60 }
61 }
62
63 /* Run the test 10 times */
64 for($f = 0; $f < 10; $f++) {
65 echo "Running $f forkTest()".PHP_EOL;
66 forkTest($data);
67 }
68
69 echo "Fork test finished, \$data now contains ".count($data)." elements".PHP_EOL;
70 echo "\$data:".PHP_EOL.json_encode($data);

---

Code Descriptions

---

This code describes a function that spawns 10 child worker threads, each of which gets a reference to the global $data array. Each child thread pushes a string element containing the child thread’s process identifier into the array, serializes it, and then places it into a shared memory slot with the process id serving as the shared memory id. The parent process waits for each child thread to exit, gathers the data from shared memory, clears the shared memory, and then combines the results into the master $data array. My test application runs through this function 10 times to demonstrate how forking in PHP can be safe and memory efficient. The result should be a $data array with 110 elements in it. I’ve thrown in sleep commands with a random time between 1 and 10 seconds to show how threads can return at different times.

No doubt optimizations can be made but this should serve as at least a rudimentary example of true and efficient threading in PHP. Well, provided that the work you are planning on doing is worth the overhead (which, small as it may be, still exists and should be factored in) and provided that you do not mind locking your application down to a POSIX environment (meaning the above code will not work on windows platforms).