RSS

Increasing Ulimit for Partitioning tables

27 Jan

Data Ofen grow larger while we keep adding data to it and Retrieving data would slow down eventually and takes longer time to produce results, this is sometimes unacceptable for clients to wait that longer,  One great solution to Retrieve data faster without Archiving or purging any data is Table Partitioning,  Table will be broken into small chunks according to date range / no.of records (like 1-100, 101-200, 201-300 and so on… )  and a lot of files will be created due to this partitioning,  

MySql User  on the HOST has a limit of only 4096 files to store by default. this has to be increased  to a certain number, otherwise  MySql database will be shutdown or corrupt due to insufficient files on the host.

To overcome this situation, you need to increase the file limit on the host.  Please add the below code in bash file and run it from your host as root, this will increase to the no.of files you need, but please make sure not to give the number above OS limit . the below example will allow 30,000 files for mysql user while creating any table partitions.

**********************************************

#! /bin/csh

cat >/etc/security/limits.d/mysql.conf <<EOF
mysql soft nofile 30000
mysql hard nofile 30000
EOF

**********************************************

To test the above scritp. login as user mysql and run “ulimit -n” command.

before running the script
> ulimit -n
4096

After running the above script
> ulimit -n
30000

 

Advertisements
 
Leave a comment

Posted by on January 27, 2012 in Database Administration, linux, MySql

 

Tags: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: