All about Linux swap space

When your computer needs to run programs that are bigger than your available physical memory, most modern operating systems use a technique called swapping, in which chunks of memory are temporarily stored on the hard disk while other data is moved into physical memory space. Here are some techniques that may help you better manage swapping on Linux systems and get the best performance from the Linux swapping subsystem.

Linux divides its physical RAM (random access memory) into chucks of memory called pages. Swapping is the process whereby a page of memory is copied to the preconfigured space on the hard disk, called swap space, to free up that page of memory. The combined sizes of the physical memory and the swap space is the amount of virtual memory available.

Swapping is necessary for two important reasons. First, when the system requires more memory than is physically available, the kernel swaps out less used pages and gives memory to the current application (process) that needs the memory immediately. Second, a significant number of the pages used by an application during its startup phase may only be used for initialization and then never used again. The system can swap out those pages and free the memory for other applications or even for the disk cache.

However, swapping does have a downside. Compared to memory, disks are very slow. Memory speeds can be measured in nanoseconds, while disks are measured in milliseconds, so accessing the disk can be tens of thousands times slower than accessing physical memory. The more swapping that occurs, the slower your system will be. Sometimes excessive swapping or thrashing occurs where a page is swapped out and then very soon swapped in and then swapped out again and so on. In such situations the system is struggling to find free memory and keep applications running at the same time. In this case only adding more RAM will help.

Linux has two forms of swap space: the swap partition and the swap file. The swap partition is an independent section of the hard disk used solely for swapping; no other files can reside there. The swap file is a special file in the filesystem that resides amongst your system and data files.

To see what swap space you have, use the command swapon -s. The output will look something like this:

Filename        Type            Size    Used    Priority
/dev/sda5       partition       859436  0       -1

Each line lists a separate swap space being used by the system. Here, the 'Type' field indicates that this swap space is a partition rather than a file, and from 'Filename' we see that it is on the disk sda5. The 'Size' is listed in kilobytes, and the 'Used' field tells us how many kilobytes of swap space has been used (in this case none). 'Priority' tells Linux which swap space to use first. One great thing about the Linux swapping subsystem is that if you mount two (or more) swap spaces (preferably on two different devices) with the same priority, Linux will interleave its swapping activity between them, which can greatly increase swapping performance.

To add an extra swap partition to your system, you first need to prepare it. Step one is to ensure that the partition is marked as a swap partition and step two is to make the swap filesystem. To check that the partition is marked for swap, run as root:

fdisk -l /dev/hdb

Replace /dev/hdb with the device of the hard disk on your system with the swap partition on it. You should see output that looks like this:

 Device Boot    Start   End     Blocks  Id      System
/dev/hdb1       2328    2434    859446  82      Linux swap / Solaris

If the partition isn't marked as swap you will need to alter it by running fdisk and using the 't' menu option. Be careful when working with partitions -- you don't want to delete important partitions by mistake or change the id of your system partition to swap by mistake. All data on a swap partition will be lost, so double-check every change you make. Also note that Solaris uses the same ID as Linux swap space for its partitions, so be careful not to kill your Solaris partitions by mistake.

Once a partition is marked as swap, you need to prepare it using the mkswap (make swap) command as root:

mkswap /dev/hdb1

If you see no errors, your swap space is ready to use. To activate it immediately, type:

swapon /dev/hdb1

You can verify that it is being used by running swapon -s. To mount the swap space automatically at boot time, you must add an entry to the /etc/fstab file, which contains a list of filesystems and swap spaces that need to be mounted at boot up. The format of each line is:

Since swap space is a special type of filesystem, many of these parameters aren't applicable. For swap space, add:

/dev/hdb1       none    swap    sw      0       0

where /dev/hdb1 is the swap partition. It doesn't have a specific mount point, hence none. It is of type swap with options of sw, and the last two parameters aren't used so they are entered as 0.

To check that your swap space is being automatically mounted without having to reboot, you can run the swapoff -a command (which turns off all swap spaces) and then swapon -a (which mounts all swap spaces listed in the /etc/fstab file) and then check it with swapon -s.

Swap file

As well as the swap partition, Linux also supports a swap file that you can create, prepare, and mount in a fashion similar to that of a swap partition. The advantage of swap files is that you don't need to find an empty partition or repartition a disk to add additional swap space.

To create a swap file, use the dd command to create an empty file. To create a 1GB file, type:

dd if=/dev/zero of=/swapfile bs=1024 count=1048576

/swapfile is the name of the swap file, and the count of 1048576 is the size in kilobytes (i.e. 1GB).

Prepare the swap file using mkswap just as you would a partition, but this time use the name of the swap file:

mkswap /swapfile

And similarly, mount it using the swapon command: swapon /swapfile.

The /etc/fstab entry for a swap file would look like this:

/swapfile       none    swap    sw      0       0

How big should my swap space be?

It is possible to run a Linux system without a swap space, and the system will run well if you have a large amount of memory -- but if you run out of physical memory then the system will crash, as it has nothing else it can do, so it is advisable to have a swap space, especially since disk space is relatively cheap.

The key question is how much? Older versions of Unix-type operating systems (such as Sun OS and Ultrix) demanded a swap space of two to three times that of physical memory. Modern implementations (such as Linux) don't require that much, but they can use it if you configure it. A rule of thumb is as follows: 1) for a desktop system, use a swap space of double system memory, as it will allow you to run a large number of applications (many of which may will be idle and easily swapped), making more RAM available for the active applications; 2) for a server, have a smaller amount of swap available (say half of physical memory) so that you have some flexibility for swapping when needed, but monitor the amount of swap space used and upgrade your RAM if necessary; 3) for older desktop machines (with say only 128MB), use as much swap space as you can spare, even up to 1GB.

The Linux 2.6 kernel added a new kernel parameter called swappiness to let administrators tweak the way Linux swaps. It is a number from 0 to 100. In essence, higher values lead to more pages being swapped, and lower values lead to more applications being kept in memory, even if they are idle. Kernel maintainer Andrew Morton has said that he runs his desktop machines with a swappiness of 100, stating that "My point is that decreasing the tendency of the kernel to swap stuff out is wrong. You really don't want hundreds of megabytes of BloatyApp's untouched memory floating about in the machine. Get it out on the disk, use the memory for something useful."

One downside to Morton's idea is that if memory is swapped out too quickly then application response time drops, because when the application's window is clicked the system has to swap the application back into memory, which will make it feel slow.

The default value for swappiness is 60. You can alter it temporarily (until you next reboot) by typing as root:

echo 50 > /proc/sys/vm/swappiness

If you want to alter it permanently then you need to change the vm.swappiness parameter in the /etc/sysctl.conf file.

Conclusion

Managing swap space is an essential aspect of system administration. With good planning and proper use swapping can provide many benefits. Don't be afraid to experiment, and always monitor your system to ensure you are getting the results you need.

Swap space

Swap Memory is a space in the Hard Disk of your computer

that Operating Systems (Linux in our case) will use to put the info that is actually on the RAM to free it for another application.

This should be done when the system needs memory for a new process and there is none, so we can see that if our system has plenty of RAM it will maybe need no SWAP memory.

How much Swap Memory do I need?

As a rule of thumb if you have 512 MB RAM put 1 GByte Swap, but this stops being true when the limit at least in x386 PCs is achieved (2 GB or swap is the maximum I could ever allocate to a System).

How do I create Swap.

Usually when you install Linux you reserve a partition to be used as swap memory, and the rest of the disk for your files, but what happens if I need more swap memory?. How to create more swap memory?

If you disk is full you can try to shrink the partitions to make room for another swap partition but an easier way is to make a swap file, so now the question is.

How to create a swap file?

dd if=/dev/zero of=/swapfile bs=1024 count=100000
This will create file (swapfile) of size 100 MB (round)
mkswap /swapfile
add this file to your swap pool
swapon /swapfile

Incresing a swap space on Linux OS

This is simple method for Increase your available swap space with a swap file

All of your devices function, and everything is configured just the way you like it. At least you think so, until you start running out of memory when you have OpenOffice.org and lots of browser tabs open simultaneously. You realize you should have specified a larger swap partition during your install. this smiple method of installing gain .swap partition.

There’s more than one way to maintain your Linux system. Instead of creating a swap file, you could instead resize and or reshuffle your partitions with parted or its graphical front end QtParted.

To start , see how much swap space you already have. At a command line by typing ,
swapon -s (you might need to prepend /sbin/ if you’re not root)
the command should produce a message :

Filename Type Size Used Priority
/dev/hda2 partition 128044 92472 -1

The numbers under “Size” and “Used” are in kilobytes.

Let’s figure out where to put it. Running df -m from a command line should produce output something like this:

Filesystem 1M-blocks Used Available Use% Mounted on
/dev/hda1 11443 6191 5252 55% /

The -m switch we used provided us with output in megabytes. Under the “Available” column we have approximately 5GB of free space on our root partition. Let’s steal 512MB of that for our auxiliary swap file. You might want more or less, depending on your memory needs, how much swap space you already have available, and how much free disk space you have. The general rule of thumb for swap size is that your total available swap space should be around double your RAM size. If you have additional partitions, and one of those is a better candidate than the / partition, feel free to use it instead.

Please make Back up your important data before proceeding. If you carefully follow the steps below you should be fine,

In order to create our supplementary swap file, we’re going to use the dd (data dump) command. You’ll need to become root to perform the next few steps. su - and enter your root password. When you’re ready,

dd if=/dev/zero of=/extraswap bs=1M count=512

replacing 512 with the number of megabytes you want in your auxiliary swap file. if= and of= are short for infile and outfile. The /dev/zero device file will give us zeroes to be written to the output file. If you want this file on a different partition, say your /var partition, you would replace /extraswap with /var/extraswap.

Now we have a file the size we want on disk, and we can prepare it for use as a swap partition. We’ll use the mkswap command to make our file swap-consumable for the Linux kernel. Again as root,
#mkswap /extraswap

To turn on our swap file, we run swapon /extraswap. Now when we run swapon -s we should see our existing swap partition and our new swapfile. Also, the free command should show an increase in total swap space.

But we’re still not done yet. If we reboot our machine now, our new swapfile won’t be active, and we’ll have to run swapon /extraswap again. to make things more permanent, you need to edit our /etc/fstab file.

make a copy of the file. Something like this should do the trick:

cp /etc/fstab /etc/fstab.mybackup

open /etc/fstab in your favorite text editor and find a line about your swapfile that looks something like this:

/dev/hda2 none swap sw 0 0

You’ll need another line like that underneath it pointing to your new swap file. Replace the first column with the location of your new swap file. For our example, the new line should look like this:

/extraswap none swap sw 0 0

Save the file. Mistaken changes to /etc/fstab could render your system unbootable, so just to make sure you didn’t accidently change anything else in /etc/fstab, run diff /etc/fstab.mybackup /etc/fstab to check for differences. That should output only the single line you added, with a “>” sign in front of it. If you see anything else in diff’s output, edit /etc/fstab again, fix it, and run the above diff command again.

In practical terms, there’s a minimal performance hit from this extra step. By the time you’ve run out of RAM and are beginning to swap, you’re already suffering a massive performance hit. After your original swap partition is full and you’re spilling into your auxiliary swap file, your system should be suffering badly enough that the added performance hit will be completely imperceptible.

In order to avoid this sort of problem entirely with your next install, using Linux’s Logical Volume Manger is probably a good idea, and there are other Linux memory management techniques. Of course the ideal solution is to just install additional RAM.

Linux creating CD-ROM ISO image

dd is a perfect tool for copy a file, converting and formatting according to the operands. It can create exact CD-ROM ISO image.

This is useful for making backup as well as for hard drive installations require a working the use of ISO images.

How do I use dd command to create an ISO image?

Put CD into CDROM

Do not mount CD. Verify if cd is mounted or not with mount command:

# mount

If cd was mouted automatically unmout it with umount command:

# umount /dev/cdrom

OR

# umount /mnt/cdrom

Create CD-ROM ISO image with dd command:

# dd if=/dev/cdrom of=/tmp/cdimg1.iso

Where,

• if=/dev/cdrom: Read from /dev/cdrom (raw format)
• of=/tmp/cdimg1.iso: write to FILE cdimg1.iso i.e. create an ISO image

Now you can use cdimg1.iso for hard disk installation or as a backup copy of cd. Please note that dd command is standard UNIX command and you should able to create backup/iso image under any UNIX like operating system.

Make an ISO Image

dd if=/dev/dvd of=dvd.iso # for dvd
dd if=/dev/cdrom of=cd.iso # for cdrom
dd if=/dev/scd0 of=cd.iso # if cdrom is scsi

To make an ISO from files on your hard drive, create a directory which holds the files you want. Then use the mkisofs command.

mkisofs -o /tmp/cd.iso /tmp/directory/

This results in a file called cd.iso in folder /tmp which contains all the files and directories in /tmp/directory/.

For more info, see the man pages for mkisofs, losetup, and dd, or see the CD-Writing-HOWTO at http://www.tldp.org.

If you want to create ISO images from a CD and you're using Windows, Cygwin has a dd command that will work. Since dd is not specific to CDs, it will also create disk images of floppies, hard drives, zip drives, etc.

For the Windows users, here are some other suggestions:

WinISO ~ http://www.winiso.com

VaporCD ~ http://vaporcd.sourceforge.net ~ "You can create ISOs from CD and mount them as 'virtual' CD drives. Works flawlessly with games and other CD based software. Unfortunately, it appears to be unmaintained now. Good thing it works so well." (P.B., 13 February 2002)

MYSQL FOREIGN KEY DOCUMENTATION

FOREIGN KEY Constraints

InnoDB supports foreign key constraints. The syntax for a foreign key constraint definition in InnoDB looks like this:

[CONSTRAINT [symbol]] FOREIGN KEY
   [index_name] (index_col_name, ...)
   REFERENCES tbl_name (index_col_name,...)
   [ON DELETE reference_option]
   [ON UPDATE reference_option]

reference_option:
   RESTRICT | CASCADE | SET NULL | NO ACTION

index_name represents a foreign key ID. If given, this is ignored if an index for the foreign key is defined explicitly. Otherwise, if InnoDB creates an index for the foreign key, it uses index_name for the index name.

Foreign keys definitions are subject to the following conditions:

• Both tables must be InnoDB tables and they must not be TEMPORARY tables.

• Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For non-binary (character) string columns, the character set and collation must be the same.

• InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.

• InnoDB allows a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

• Index prefixes on foreign key columns are not supported. One consequence of this is that BLOB and TEXT columns cannot be included in a foreign key because indexes on those columns must always include a prefix length.

• If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically.

InnoDB rejects any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table. The action InnoDB takes for any UPDATE or DELETE operation that attempts to update or delete a candidate key value in the parent table that has some matching rows in the child table is dependent on the referential action specified using ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause. When the user attempts to delete or update a row from a parent table, and there are one or more matching rows in the child table, InnoDB supports five options regarding the action to be taken. If ON DELETE or ON UPDATE are not specified, the default action is RESTRICT.

• CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, you should not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

  Note

  Currently, triggers are not activated by cascaded foreign key actions.

• SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the child table to NULL. This is valid only if the foreign key columns do not have the NOT NULL qualifier specified. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.

  If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.

• NO ACTION: In standard SQL, NO ACTION means no action in the sense that an attempt to delete or update a primary key value is not allowed to proceed if there is a related foreign key value in the referenced table. InnoDB rejects the delete or update operation for the parent table.

• RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause. (Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.)

• SET DEFAULT: This action is recognized by the parser, but InnoDB rejects table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

InnoDB supports foreign key references within a table. In these cases, “child table records” really refers to dependent records within the same table.

Here is a simple example that relates parent and child tables through a single-column foreign key:

CREATE TABLE parent (id INT NOT NULL,
                    PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
                   INDEX par_ind (parent_id),
                   FOREIGN KEY (parent_id) REFERENCES parent(id)
                     ON DELETE CASCADE
) ENGINE=INNODB;

A more complex example in which a product_order table has foreign keys for two other tables. One foreign key references a two-column index in the product table. The other references a single-column index in the customer table:

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                     price DECIMAL,
                     PRIMARY KEY(category, id)) ENGINE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                      PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                           product_category INT NOT NULL,
                           product_id INT NOT NULL,
                           customer_id INT NOT NULL,
                           PRIMARY KEY(no),
                           INDEX (product_category, product_id),
                           FOREIGN KEY (product_category, product_id)
                             REFERENCES product(category, id)
                             ON UPDATE CASCADE ON DELETE RESTRICT,
                           INDEX (customer_id),
                           FOREIGN KEY (customer_id)
                             REFERENCES customer(id)) ENGINE=INNODB;

InnoDB allows you to add a new foreign key constraint to a table by using ALTER TABLE:

ALTER TABLE tbl_name
   ADD [CONSTRAINT [symbol]] FOREIGN KEY
   [index_name] (index_col_name, ...)
   REFERENCES tbl_name (index_col_name,...)
   [ON DELETE reference_option]
   [ON UPDATE reference_option]

The foreign key can be self referential (referring to the same table). When you add a foreign key constraint to a table using ALTER TABLE, remember to create the required indexes first.

InnoDB supports the use of ALTER TABLE to drop foreign keys:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

If the FOREIGN KEY clause included a CONSTRAINT name when you created the foreign key, you can refer to that name to drop the foreign key. Otherwise, the fk_symbol value is internally generated by InnoDB when the foreign key is created. To find out the symbol value when you want to drop a foreign key, use the SHOW CREATE TABLE statement. For example:

mysql> SHOW CREATE TABLE ibtest11c\G
*************************** 1. row ***************************
      Table: ibtest11c
Create Table: CREATE TABLE `ibtest11c` (
 `A` int(11) NOT NULL auto_increment,
 `D` int(11) NOT NULL default '0',
 `B` varchar(200) NOT NULL default '',
 `C` varchar(175) default NULL,
 PRIMARY KEY  (`A`,`D`,`B`),
 KEY `B` (`B`,`C`),
 KEY `C` (`C`),
 CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
REFERENCES `ibtest11a` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARSET=latin1
1 row in set (0.01 sec)

mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY `0_38775`; 

You cannot add a foreign key and drop a foreign key in separate clauses of a single ALTER TABLE statement. Separate statements are required.

If ALTER TABLE for an InnoDB table results in changes to column values (for example, because a column is truncated), InnoDB's FOREIGN KEY constraint checks do not notice possible violations caused by changing the values.

The InnoDB parser allows table and column identifiers in a FOREIGN KEY ... REFERENCES ... clause to be quoted within backticks. (Alternatively, double quotes can be used if the ANSI_QUOTES SQL mode is enabled.) The InnoDB parser also takes into account the setting of the lower_case_table_names system variable.

InnoDB returns a table's foreign key definitions as part of the output of the SHOW CREATE TABLE statement:

SHOW CREATE TABLE tbl_name;

mysqldump also produces correct definitions of tables in the dump file, and does not forget about the foreign keys.

You can also display the foreign key constraints for a table like this:

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

The foreign key constraints are listed in the Comment column of the output.

When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit.

To make it easier to reload dump files for tables that have foreign key relationships, mysqldump automatically includes a statement in the dump output to set foreign_key_checks to 0. This avoids problems with tables having to be reloaded in a particular order when the dump is reloaded. It is also possible to set this variable manually:

mysql> SET foreign_key_checks = 0;
mysql> SOURCE dump_file_name;
mysql> SET foreign_key_checks = 1; 

This allows you to import the tables in any order if the dump file contains tables that are not correctly ordered for foreign keys. It also speeds up the import operation. Setting foreign_key_checks to 0 can also be useful for ignoring foreign key constraints during LOAD DATA and ALTER TABLE operations. However, even if foreign_key_checks = 0, InnoDB does not allow the creation of a foreign key constraint where a column references a non-matching column type. Also, if an InnoDB table has foreign key constraints, ALTER TABLE cannot be used to change the table to use another storage engine. To alter the storage engine, you must drop any foreign key constraints first.

InnoDB does not allow you to drop a table that is referenced by a FOREIGN KEY constraint, unless you do SET foreign_key_checks = 0. When you drop a table, the constraints that were defined in its create statement are also dropped.

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message.

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to error 150, that means a foreign key definition would be incorrectly formed for the altered table. You can use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server.

Important

For users familiar with the ANSI/ISO SQL Standard, please note that no storage engine, including InnoDB, recognizes or enforces the MATCH clause used in referential integrity constraint definitions. Use of an explicit MATCH clause will not have the specified effect, and also causes ON DELETE and ON UPDATE clauses to be ignored. For these reasons, specifying MATCH should be avoided.

The MATCH clause in the SQL standard controls how NULL values in a composite (multiple-column) foreign key are handled when comparing to a primary key. InnoDB essentially implements the semantics defined by MATCH SIMPLE, which allow a foreign key to be all or partially NULL. In that case, the (child table) row containing such a foreign key is allowed to be inserted, and does not match any row in the referenced (parent) table. It is possible to implement other semantics using triggers.

Additionally, MySQL and InnoDB require that the referenced columns be indexed for performance. However, the system does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL. The handling of foreign key references to non-unique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only UNIQUE and NOT NULL keys.

Furthermore, InnoDB does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. InnoDB accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification. For other storage engines, MySQL Server parses and ignores foreign key specifications.

Deviation from SQL standards: If there are several rows in the parent table that have the same referenced key value, InnoDB acts in foreign key checks as if the other parent rows with the same key value do not exist. For example, if you have defined a RESTRICT type constraint, and there is a child row with several parent rows, InnoDB does not allow the deletion of any of those parent rows.

InnoDB performs cascading operations through a depth-first algorithm, based on records in the indexes corresponding to the foreign key constraints.

Deviation from SQL standards: A FOREIGN KEY constraint that references a non-UNIQUE key is not standard SQL. It is an InnoDB extension to standard SQL.

Deviation from SQL standards: If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep.

Deviation from SQL standards: Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things will be

Mysql limit packet

A communication packet is a single SQL statement sent to the MySQL server or a single row that is sent to the client.

In MySQL 3.23, the largest possible packet is 16MB, due to limits in the client/server protocol. In MySQL 4.0.1 and up, the limit is 1GB.

When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection. With some clients, you may also get a Lost connection to MySQL server during query error if the communication packet is too large.

Both the client and the server have their own max_allowed_packet variable, so if you want to handle big packets, you must increase this variable both in the client and in the server.

If you are using the mysql client program, its default max_allowed_packet variable is 16MB. That is also the maximum value before MySQL 4.0. To set a larger value from 4.0 on, start mysql like this:

mysql> mysql --max_allowed_packet=32M

That sets the packet size to 32MB.

The server's default max_allowed_packet value is 1MB. You can increase this if the server needs to handle big queries (for example, if you are working with big BLOB columns). For example, to set the variable to 16MB, start the server like this:

mysql> mysqld --max_allowed_packet=16M

Before MySQL 4.0, use this syntax instead:

mysql> mysqld --set-variable=max_allowed_packet=16M

You can also use an option file to set max_allowed_packet. For example, to set the size for the server to 16MB, add the following lines in an option file:

[mysqld]
max_allowed_packet=16M

Before MySQL 4.0, use this syntax instead:

[mysqld]
set-variable = max_allowed_packet=16M

It's safe to increase the value of this variable because the extra memory is allocated only when needed. For example, mysqld allocates more memory only when you issue a long query or when mysqld must return a large result row. The small default value of the variable is a precaution to catch incorrect packets between the client and server and also to ensure that you don't run out of memory by using large packets accidentally.

You can also get strange problems with large packets if you are using large BLOB values but have not given mysqld access to enough memory to handle the query. If you suspect this is the case, try adding ulimit -d 256000 to the beginning of the mysqld_safe script and restarting mysqld.

MYSQL CONFIGURATIONS

Configure MySQL Options

Related Documentation

MySQL Documentation

Prerequisites

Last Updated for InterWorx version 3.0.1

No special prerequisites.

If you are running a version of MySQL previous to 4.1 on your server, the following system variables will not be available for modification through the NodeWorx MySQL interface: Query Cache Limt and Query Cache Size. See the variable descriptions below for more details.

Purpose

The following procedures explain how to configure MySQL options using InterWorx-CP. The most common configuration options are exposed in the InterWorx-CP. As with many of the system services, a system administrator still retains the ability to configure the service by editing the configuration file.

Procedure - Change a Commonly Configured MySQL Option

1. Click on the System Services menu if it is not already expanded.
2. Click on the ♦ MySQL Server submenu if it is not already expanded.
3. Click on the ◊ Overview item.
4. You should now be looking at the MySQL Server Management controls in the main content area.
5. Locate the MySQL Server Options section.
6. Change the option(s) you wish to update to the desired value(s).
7. Click the Update button.
8. You will see the following message at the top of the screen: » Directives updated successfully.

MySQL Server Options

Connections

The maximum number of simultaneous connections allowed for MySQL. The number of connections must be between 1 and 65536, with the default value set to 100. Increasing max_connections increases the number of file descriptors that mysqld requires. It is strongly suggested that you keep the maximum number of connections below 1500.

MySQL Dynamic System Variables
MySQL System Variables Overview
Receiving a Too Many Connections Error?

Connection Errors

The maximum number of allowed connection errors for a given host. The value must be between 1 and 999999999. If set, the server blocks further connections from a remote host when the number of interrupted connections from that host exceeds this number. A successful connection from a host resets the number of connection errors. You can also unblock a host with the MySQL command FLUSH HOSTS.

FLUSH Command Syntax

Connect Timeout

The maximum number of seconds before a connection to the MySQL server will time out. Possible values range from 2 to 999999999 seconds.

MySQL Dynamic System Variables

Wait Timeout

The number of seconds the server waits for activity on a connection before closing the connection. Values can be between 2 and 999999999. On thread startup, SESSION.WAIT_TIMEOUT is initialized from GLOBAL.WAIT_TIMEOUT or GLOBAL.INTERACTIVE_TIMEOUT depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option)

MySQL Server System Variables

Key Buffer Size

The key buffer is a memory buffer shared by all threads. The key buffer size mut be at least 16384 bytes. Increasing the key buffer size will result in better index handling (for all reads and multiple writes). 64M on a 256M machine that mainly runs MySQL is quite common. If you, however, make this too big (for instance more than 50% of your total memory) your system may start to page and become extremely slow. Remember that because MySQL does not cache data reads, you will have to leave some room for the OS filesystem cache.

MySQL Server System Variables

Sort Buffer Size

The size of the buffer used when sorting table data. The minimum value is 8192 bytes and the default value is 1MB. Increasing this value will lead to faster ORDER BY or GROUP BY operations. A temporary buffer of sort_buffer_size is allocated when a thread needs to do perform a sort operation.

Information About MySQL Temporary Files

Read Buffer Size

Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increase this value. The minimum value for read_buffer_size is 8192 bytes, and the default value is 128KB. In versions of MySQL prior to 4.x, this system variable was referred to as record_buffer.

MySQL Server System Variables

Maximum Allowed Packet Size

The maximum size of one packet. The minimum value for this variable is 1024 Bytes. By default, the value is small so as to catch big (possibly wrong) packets. You must increase this value if you are using big BLOB columns, as it should be as big as the biggest BLOB you want to use. The protocol limits for max_allowed_packet is 16M in MySQL 3.23 and 1G in MySQL 4.0.

Packet Too Large?

Thread Cache Size

The number of threads that are kept in the cache for reuse. This variable can be increased to improve performance if you have a lot of new connections. Increasing this value increases the number of file descriptors that mysqld requires.

MySQL Server System Variables

Table Cache

This variable controls the number of open tables that are cached. Table cache is related to max connections. For example, for 200 concurrent running connections, you should have a table cache of at leat 200 * N, where N is the maximum number of tables in a join. If you increase this value, the number of file descriptors needed by MySQLD will also increase. You can check if you need to increase the table cache by checking the Opened_tables variable. If this variable is big and you don’t do FLUSH TABLES a lot (which just forces all tables to be closed and reopenend), then you should increase the value of this variable.

How MySQL Opens and Closes Tables
FLUSH Command Syntax

Query Cache Limit

Sets the maximum amount of memory to be allocated for storage of old query results. The default value is 1MB. This variable is only available in versions of MySQL > 4.1.x.

Query Cache Size

Sets size of the query cache. The default value is 0. If this is set to 0, query caching is disabled. This variable is only available in versions of MySQL > 4.1.x.

DIFFERENT BETWEEN InnoDB AND MyISAM

• The big difference between MySQL Table Type MyISAM and InnoDB is that InnoDB supports transaction
• InnoDB supports some newer features: Transactions, row-level locking, foreign keys
• InnoDB is for high volume, high performance

Most people use MyISAM if they need speed and InnoDB for data integrity. You can use more than one or any combination of these table types in your database. Remember to asses the needs of your application before building it. Even though MyISAM is faster than InnoDB in the MySQL world, InnoDB is fast compared to any database engine.With InnoDB you get transactions, speed and integrity three features not usually used in the same sentence.

InnoDB has been designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine.

Fully integrated with MySQL Server, the InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a tablespace, which may consist of several files (or raw disk partitions). This is different from, for example, MyISAM tables where each table is stored using separate files. InnoDB tables can be of any size even on operating systems where file size is limited to 2GB.

So, what do you think about those engines?? Please feel free to discuss it.

For more information visit http://dev.mysql.com/

Thanks

MyISAM is of course the default table type i'm MySQL. It's an improvement over the now-deprecated ISAM table type. It provides a simple tree structure for quick indexing on small tables, typicall fewer than 10,000 rows of data.

There are some other types of tables supported in MySQL, most of which won't provide any noticable differences.

A MERGE table is a table that only exists in memory, and will not be saved if MySQL crashes or has to be restarted. It is, as the name suggests, a merge of two (or more) tables. If you drop a MERGE table, it only drops the reference to the merge, not to the tables that are combined in it. It's not good for much!

Another table, the one which is best for huge tables, is InnoDB. It's probably the best RDBMS out there; it's actually a whole set of drivers on it's own, and when you use it, MySQL is really just a wrapper around the InnoDB table. Slashdot and other megamammoth sites out there use it, it's fast and effecient, but the trees are pretty large, so it's not good for smaller tables.

There are a few others.. like BerkeleyDB, which isn't anything special and doesn't provide any major performance increase.

The major difference between the various table types are the data tree structures and how they're handled within the RDBMS.

In the end, you'll probably find yourself sticking to MyISAM unless you're on a very large-scale project, in which cas eyou probably woudln't use MySQL anyway

LEAN TO MAKE BETTER DECISIONS.

A PRUDENT MAN GIVES THOUGHT TO HIS STEPS.

If you're still making the same mistakes at fifty that you were at twenty, you need to ask God for wisdom. “The heart of the discerning acquires knowledge; the ears of the wise seek it out”. What you are in the present, was determined by the decisions you made in the past. If you want to change your future, learn to make better decisions. Be wise; let these principles guide you:-

1. Never make permanent decision based on temporary circumstances. If you do, you'll regret it.

2. Know that you can do things in the same way and get different results, you need to be dynamic in doing things anytime.

3. Don't let your emotions blind you to reason. Pray, weigh things carefully and base your decision on mature judgment.

4. Surround yourself with sharp people and draw on their gifts, without be intimidated by their expertise.

5. Take the time to consider all options. What look good to you today, may not look so good tomorrow.

6. You can't fight successfully on every front, so choose your battles carefully. Simply stated; some things are not worth fighting for.

7. Take time to get all the facts; conjecture leads to crisis.

8. Consider the consequences of each action. Ask yourself, 'Am I ready to handle this right now?'.

9. Make sure your expectations don't exceed your potential and your resources. Be realistic. If you can't count, don't apply for a job in the finance office. If you can't sing, don't cut am album. Focus on what God gifted you to do. That is where you'll succeed.

10. Time is your most limited and valuable resource. Don't waste it.

11. Allow yourself a 10% risk of being wrong, A 50% likelihood of betrayal, and A 100% commitment to trust God, go forward and survive it all.

FAILURE OF INNOVATIONS

Research findings vary, ranging from fifty to ninety percent of innovation projects judged to have made little or no contribution to organizational goals. One survey regarding product innovation quotes that out of three thousand ideas for new products, only one becomes a success in the marketplace. Failure is an inevitable part of the innovation process, and most successful organisations factor in an appropriate level of risk. Perhaps it is because all organisations experience failure that many choose not to monitor the level of failure very closely. The impact of failure goes beyond the simple loss of investment. Failure can also lead to loss of morale among employees, an increase in cynicism and even higher resistance to change in the future.

Innovations that fail are often potentially ‘good’ ideas but have been rejected or ‘shelved’ due to budgetary constraints, lack of skills or poor fit with current goals. Failures should be identified and screened out as early in the process as possible. Early screening avoids unsuitable ideas devouring scarce resources that are needed to progress more beneficial ones. Organizations can learn how to avoid failure when it is openly discussed and debated. The lessons learned from failure often reside longer in the organisational consciousness than lessons learned from success. While learning is important, high failure rates throughout the innovation process are wasteful and a threat to the organisation's future.

The causes of failure have been widely researched and can vary considerably. Some causes will be external to the organisation and outside its influence of control. Others will be internal and ultimately within the control of the organisation. Internal causes of failure can be divided into causes associated with the cultural infrastructure and causes associated with the innovation process itself. Failure in the cultural infrastructure varies between organisations but the following are common across all organisations at some stage in their life cycle (O'Sullivan, 2002):

1. Poor Leadership
2. Poor Organisation
3. Poor Communication
4. Poor Empowerment
5. Poor Knowledge Management

Common causes of failure within the innovation process in most organisations can be distilled into five types:

1. Poor goal definition
2. Poor alignment of actions to goals
3. Poor participation in teams
4. Poor monitoring of results
5. Poor communication and access to information

Effective goal definition requires that organisations state explicitly what their goals are in terms understandable to everyone involved in the innovation process. This often involves stating goals in a number of ways. Effective alignment of actions to goals should link explicit actions such as ideas and projects to specific goals. It also implies effective management of action portfolios. Participation in teams refers to the behaviour of individuals in and of teams, and each individual should have an explicitly allocated responsibility regarding their role in goals and actions and the payment and rewards systems that link them to goal attainment. Finally, effective monitoring of results requires the monitoring of all goals, actions and teams involved in the innovation process.

Innovation can fail if seen as an organisational process whose success stems from a mechanistic approach i.e. 'pull lever obtain result'. While 'driving' change has an emphasis on control, enforcement and structure it is only a partial truth in achieving innovation. Organisational gatekeepers frame the organisational environment that "Enables" innovation; however innovation is "Enacted" – recognised, developed, applied and adopted – through individuals.

Individuals are the 'atom' of the organisation close to the minutiae of daily activities. Within individuals gritty appreciation of the small detail combines with a sense of desired organisational objectives to deliver (and innovate for) a product/service offer.

From this perspective innovation succeeds from strategic structures that engage the individual to the organisation's benefit. Innovation pivots on intrinsically motivated individuals, within a supportive culture, informed by a broad sense of the future.

Innovation, implies change, and can be counter to an organisation's orthodoxy. Space for fair hearing of innovative ideas is required to balance the potential autoimmune exclusion that quells an infant innovative culture.

GOALS OF INNOVATIONS

Programs of organizational innovation are typically tightly linked to organizational goals and objectives, to the business plan, and to market competitive positioning.

"Companies cannot grow through cost reduction and Re engineering alone . . . Innovation is the key element in providing aggressive top-line growth, and for increasing bottom-line results"

In general, business organisations spend a significant amount of their turnover on innovation i.e. making changes to their established products, processes and services. The amount of investment can vary from as low as a half a percent of turnover for organisations with a low rate of change to anything over twenty percent of turnover for organisations with a high rate of change.

The average investment across all types of organizations is four percent. For an organisation with a turnover of say one billion currency units, this represents an investment of forty million units. This budget will typically be spread across various functions including marketing, product design, information systems, manufacturing systems and quality assurance.

The investment may vary by industry and by market positioning.

One survey across a large number of manufacturing and services organisations found, ranked in decreasing order of popularity, that systematic programs of organizational innovation are most frequently driven by:

1. Improved quality
2. Creation of new markets
3. Extension of the product range
4. Reduced labour costs
5. Improved production processes
6. Reduced materials
7. Reduced environmental damage
8. Replacement of products/services
9. Reduced energy consumption
10. Conformance to regulations

These goals vary between improvements to products, processes and services and dispel a popular myth that innovation deals mainly with new product development. Most of the goals could apply to any organisation be it a manufacturing facility, marketing firm, hospital or local government.

INNOVATIONS

The term innovation may refer to both radical and incremental changes in thinking, in things, in processes or in services. Invention that gets out in to the world is innovation. In many fields, something new must be substantially different to be innovative, not an insignificant change, e.g., in the arts, economics, business and government policy. In economics the change must increase value, customer value, or producer value. The goal of innovation is positive change, to make someone or something better. Innovation leading to increased productivity is the fundamental source of increasing wealth in an economy.

Innovation is an important topic in the study of economics, business, technology, sociology, and engineering. Colloquially, the word "INNOVATION" is often used as synonymous with the output of the process. Since innovation is also considered a major driver of the economy, the factors that lead to innovation are also considered to be critical to policy makers.

Those who are directly responsible for application of the innovation are often called pioneers in their field, whether they are individuals or organisations.

There are various definitions of "INNOVATION" that appear in the literature.

1. Introduction of a new product or a qualitative change in an existing product
2. Process innovation new to an industry
3. The opening of a new market
4. Development of new sources of supply for raw materials or other inputs
5. Changes in industrial organisation.
6. Invent new Ideas.

An innovation [..] is any new or substantially improved in technologies, good or service
which has been commercialised, or any new or substantially improved process
used for the commercial production of goods and services. ’

The Ten Faces of Innovation:

1. The Anthropologist
2. The Experimenter
3. The Cross-pollinator
4. The Hurdler
5. The Collaborator
6. The Director
7. The Experience Architect
8. The Set Designer
9. The Caregiver
10. The Storteller

MY PROFILE

I AM A PROGRAMMER AND NETWORK ADMINISTRATOR WITH LINUX OPERATING SYSTEM.
Am a good user of Linux Operation System with experience in Hardware & Software.
I can install and configure all the Linux based services like; Sendmail, Postfix, Squid Proxy and Cache Server, DNS, FTP and Apache.
I Programmed in PHP/MYSQL for CRM, HEALTHCARE, ACADEMIA APPLICATIONS. and WEB -DESIGN using CMS like JOOMLA, MAMBO.