Determining true memory usage in linux

Most of us are complaining high memory usage of applications running under linux by merely checking memory using 'top' command. Actually in Linux memory is never wasted. Almost all the free memory is used for Disk Caching. True memory usage can be determined by value of used buffers comes for 'free' command.
We also have lot of concerns over swapping. Linux mainly focus on services and greedy on allocating memory to applications. Anyway swapping can be configured using vm.swappiness parameter at /etc/sysctl.conf. This wiki has very useful information on linux memory management.

Funny answers in mysql for IS NULL queries

Have you seen mysql returns non-NULL values for NULL query. If not you can try this out.
create table null_test(a int not null auto_increment, b int not null, primary key (a));
insert into null_test(a, b) values (0, 1); select * from null_test where a is null;

           | a | b |
          +---+---+
           | 1 | 1 |
           +---+---+
Then retry               mysql> select * from null_test where a is null;
Empty set (0.00 sec)
You can prevent this by 

• setting sql mode to NO_AUTO_VALUE_ON_ZERO. (Then next sequence number is generated only when NULL value for column is inserted.)

• And setting off SQL_AUTO_IS_NULL server variable. (When ON it returns last inserted row for a table that contains an AUTO_INCREMENT column.)

This behavior is useful for ODBC programs, such as Access. But when such mysql table is restored from a dump, data become different.

Determine Resource Usage for a SQL session

Determine Resource Usage for a SQL session

Now we can use profiling session variable to determine resource usage for a sql session.
It is introduced in mysql 5.0.37.

All the information of variable usage can be found at

http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html

This gives an idea of information can be extracted from the variable.

mysql> SHOW PROFILE CPU FOR QUERY 2;

+----------------------+----------+----------+------------+

| Status               | Duration | CPU_user | CPU_system |

+----------------------+----------+----------+------------+

| checking permissions | 0.000040 | 0.000038 |   0.000002 |

| creating table       | 0.000056 | 0.000028 |   0.000028 |

| After create         | 0.011363 | 0.000217 |   0.001571 |

| query end            | 0.000375 | 0.000013 |   0.000028 |

| freeing items        | 0.000089 | 0.000010 |   0.000014 |

| logging slow query   | 0.000019 | 0.000009 |   0.000010 |

| cleaning up          | 0.000005 | 0.000003 |   0.000002 |

+----------------------+----------+----------+------------+

All the profiling information is stored in PROFILING table in INFORMATION_SCHEMA database. More details can be extracted by directly querying the table.

http://dev.mysql.com/doc/refman/5.0/en/profiling-table.html

As this information is session based, they will be lost when session ends.

Profiling information will be very useful for estimating performance of mysql queries.