Increase file handler on OS level

Open file : vi /etc/security/limits.conf

Add following two lines at bottom of limits.conf file


*       soft    nofile  65535
*       hard    nofile  65535

restart your machine. This will increase number of file handler to 65535. By-Default, Linux having 1024 handler.

Linux performance tuning

Kernels 2.6.16 and newer provide a mechanism to have the kernel drop the page cache and/or inode and dentry caches on command, which can help free up a lot of memory. Now you can throw away that script that allocated a ton of memory just to get rid of the cache...

To use /proc/sys/vm/drop_caches, just echo a number to it.

1. To free pagecache: [root@host]# echo 1 > /proc/sys/vm/drop_caches
2. To free dentries and inodes: [root@host]# echo 2 > /proc/sys/vm/drop_caches
3. To free pagecache, dentries and inodes: [root@host]# echo 3 > /proc/sys/vm/drop_caches

This is a non-destructive operation and will only free things that are completely unused. Dirty objects will continue to be in use until written out to disk and are not freeable. If you run "sync" first to flush them out to disk, these drop operations will tend to free more memory.

ERROR 1126 (HY000) at line 38: Can't open shared library 'libmemcached_functions_mysql.so'

Hi,
    I am trying to install mysql-memcached connectivity. Once am trying this

mysql -uroot Producer <sql/install_functions.sql


it is giving me this error


ERROR 1126 (HY000) at line 38: Can't open shared library 'libmemcached_functions_mysql.so' (errno: 22 libmemcached.so.3: cannot open shared object file: No such file or directory)

Can one have idea, how can I resolved it..................

MySQL Optimize Table

The MySQL Optimize Table command will effectively de-fragment a mysql table and is very useful for tables which are frequently updated and/or deleted.

This statement requires SELECT and INSERT privileges for the table.

OPTIMIZE TABLE works only for MyISAM, InnoDB, and (as of MySQL 5.0.16) ARCHIVE tables. It does not work for tables created using any other storage engine.

For MyISAM tables, OPTIMIZE TABLE works as follows:

1. If the table has deleted or split rows, repair the table.
2. If the index pages are not sorted, sort them.
3. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.
   
   

Example:

We have a table called articles which has many thousands of rows which are often inserted, updated and deleted. We can see from the table description below that the table contains variable length column data types:

mysql> desc articles;

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

| Field  | Type | Null | Key | Default | Extra |

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

| id  | int(11) | NO   | PRI | NULL    | auto_increment |

| content        | text         | NO   |     | NULL    |                |

| author_id      | int(11)      | YES  |     | NULL    |                |

| article_title  | varchar(120) | YES  |     | NULL    |                |

| article_hash   | int(11)      | YES  |     | NULL    |                |

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

6 rows in set (0.00 sec)

If we look at the size of the table on disk we can see that it around 190MB. If we query the table on a column which is indexes we can see the average query response time: 

e.g.

mysql> select count(*) from articles where article_title like 'The%';
+----------+
| count(*) |
+----------+
|    15830 |
+----------+
1 row in set (0.63 sec)

If we now optimize the table with the following command:

mysql> optimize table articles;
+-----------------------+----------+----------+----------+
| Table                 | Op       | Msg_type | Msg_text |
+-----------------------+----------+----------+----------+
| books.articles        | optimize | status   | OK       |
+-----------------------+----------+----------+----------+
1 row in set (6.27 sec)

This has the effect of defragmenting the table and reducing the size of the table on disk down to 105MB. It also has a very positive affect on query performance, reducing the select query response time from 0.63 to 0.39 seconds. N.B. the mysql query cache was turned off to demonstrate.

mysql> select count(*) from articles where article_title like 'The%';
+----------+
| count(*) |
+----------+
|    15830 |
+----------+
1 row in set (0.39 sec)