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
Advertisement
