Setting ulimit for MySQL - Red Hat

Posted on 2007-08-07
Last Modified: 2013-12-06

I have the duty of installing a large PHP / MySQL application on a Red Hat server. I've almost finished, but the data to be imported is very large - and still growing. Part of the documentation told me to:

Launch the MySQL daemon with the following options:

      ulimit diskspace>3GB memory>high
      option --max_allowed_packet=160M

I have set the max allowed packet size successfully in my.cnf, but I do not know how to go about setting the ulimit as suggested. Please provide help regarding what file needs to be edited, and the command to be inserted. The table keeps growing (over 1.5 GB now) and I don't know what may happen if we get over 3 GB. MySQL version 4.1.20.

Thanks a lot
Question by:ncatanchin
    LVL 17

    Expert Comment

    Well, `ulimit` is a Linux command/utility. You should look into the `mysqld_safe` script (ussualy found in /usr/bin/ directory) and see if it's already specified there. You generally add the command somewhere at the start of the script or uncomment it if it's commented.

    As for the size over 3GB, that's exactly what you're specifying. Please note that the max size of the chunk (single import command) is limitted by the max allowed packet size.
    LVL 45

    Expert Comment

    Hi ncatanchin,

    Step by step.  :)

    Have you (or any other admin) set up user quotas on any of the file systems?  If not, great.  It's pretty easy to check by looking in the file /etc/fstab.  For each file system you should see something that looks like this:

    LABEL=/home             /home                   ext3    defaults               1 2

    If you see something like this:

    LABEL=/home             /home                   ext3    defaults,usrquota       1 2

    someone has attempted (or succeeded) in configuring user quotas.  You'll have to use the quota tools to undo the effects of the quota.

    I'm not aware of a 3GB limit on anything.  There are potentially 2GB issues (but that probably involves systems older than yours) and 4GB issues.  There is a 4GB issue with the MyISAM engine in MySQL 4.x.  Here's a link with a good description of the problem and a solution.  (If you're using the InnoDB engine, there is no problem at 4GB.)

    Good Luck,

    Author Comment

    Thanks for the help guys, I'm still unsure of what to do:

    ulimit diskspace>3GB memory>high

    This is the command I am concerned about. I opened the mysqld_safe and found several instances of the use of ulimit. I tried to enter "ulimit diskspace>3GB memory>high" at the shell and the command is not valid. Looking at the man page, I find I can set such things as maximum size of core files created, maximum size of files created by the shell..

    What did the original developer who created the documentation mean by specifying: launch mysql with the above command? Do I need two separate ulimit commands to take care of this requirement? Is 'diskspace' equivalent to, for example, setting '-c The maximum size of core files created'?  How do I set a memory value of 'high'?

    Thanks again
    LVL 17

    Accepted Solution

    Issue the `ulimit -a` command. It will show your current limits, e.g.
    bradaric@dragon [/usr/bin]# ulimit -a
    core file size          (blocks, -c) 0
    data seg size           (kbytes, -d) unlimited
    file size               (blocks, -f) unlimited
    pending signals                 (-i) 1024
    max locked memory       (kbytes, -l) 32
    max memory size         (kbytes, -m) unlimited
    open files                      (-n) 1024
    pipe size            (512 bytes, -p) 8
    POSIX message queues     (bytes, -q) 819200
    stack size              (kbytes, -s) 10240
    cpu time               (seconds, -t) unlimited
    max user processes              (-u) 73728
    virtual memory          (kbytes, -v) unlimited
    file locks                      (-x) unlimited

    What you should look at configuring are the `-d` and `-m` switches. The first one should be over 3GB (note that the values are set in KB), and the second one, depending on the amount of memory available, as high as possible without making problems for the server. Unlimited is also fine if it's your server :)

    Author Comment

    Thank you leannonn. They were both already set to unlimited, how funny.

    Thank you also Kdo.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    The purpose of this article is to show how we can create Linux Mint virtual machine using Oracle Virtual Box. To install Linux Mint we have to download the ISO file from its website i.e. Once you open the link you will see …
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video discusses moving either the default database or any database to a new volume.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now