OpenBSD and MySQL - BSD

This is a discussion on OpenBSD and MySQL - BSD ; As probably discussed here and in other groups earlier, there is a known problem with using OpenBSD as a MySQL server. The problem is that MySQL opens more files than the default kernel is allowed to (open files per process), ...

+ Reply to Thread
Results 1 to 5 of 5

Thread: OpenBSD and MySQL

  1. OpenBSD and MySQL

    As probably discussed here and in other groups earlier, there is a
    known problem with using OpenBSD as a MySQL server.

    The problem is that MySQL opens more files than the default kernel is
    allowed to (open files per process), and the only way to change this
    seems to be to edit the hardcoded value for OPEN_MAX in the syslimits.h
    file in /usr/src/sys/sys

    I have tried with MySQL 5.0.18 in ports (OpenBSD 3.9), but before
    finding out what caused the file access problem, I changed it for
    5.0.19 (src) with my usual options for compiling. After changing
    OPEN_MAX (and a couple of other settings in my.cnf and login.conf) it
    ran for about 16 hours without a problem.

    I removed the bin-log from my.cnf and restarted, and this time the
    server survived only about 30 minutes. It broke down (MySQL, not
    OpenBSD) while running a backup using mysqldump, which for one of the
    databases opens up a lot of tables/files.

    The hardware I'm using is a home-built AMD64 2GHz with 4GB RAM, so
    there is room for some memory-eating tuning if needed.

    Result (at this moment) from
    # mysqladmin -u root version
    mysqladmin Ver 8.41 Distrib 5.0.19, for unknown-openbsd3.9 on x86_64
    Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
    This software comes with ABSOLUTELY NO WARRANTY. This is free software,
    and you are welcome to modify and redistribute it under the GPL license

    Server version 5.0.19
    Protocol version 10
    Connection Localhost via UNIX socket
    UNIX socket /tmp/mysql.sock
    Uptime: 12 hours 16 min 16 sec

    Threads: 1 Questions: 1080571 Slow queries: 0 Opens: 0 Flush
    tables: 1 Open tables: 513 Queries per second avg: 24.461

    # pstat -T |grep files
    511/13196 open files

    # fstat |grep sql |wc -l
    435

    /etc/my.cnf:
    default my-huge.cnf with the following changes (when the defaults
    didn't work well):

    27a28
    > datadir = /home/mysql_data

    28a30,33
    > max_tmp_tables = 64
    > tmpdir = /tmp
    > open_files = 8192
    > innodb_open_files=2048

    30,34c35,41
    < max_allowed_packet = 1M
    < table_cache = 512
    < sort_buffer_size = 2M
    < read_buffer_size = 2M
    < read_rnd_buffer_size = 8M
    ---
    > max_allowed_packet = 16M
    > table_cache = 5000
    > open-files-limit=8192
    > max_connections = 1000
    > sort_buffer_size = 32M
    > read_buffer_size = 32M
    > read_rnd_buffer_size = 16M

    51c58
    < log-bin=mysql-bin
    ---
    > #log-bin=mysql-bin

    56c63
    < server-id = 1
    ---
    > server-id = 93


    my /etc/login.conf
    (at the end I have added)

    mysql:\
    :datasize=infinity:\
    :maxproc=infinity:\
    penfiles-cur=8192:\
    penfiles-max=16000:\
    :stacksize-cur=8M:\
    :localcipher=blowfish,8:\
    :tc=default:

    the _mysql user belongs to user class mysql, and a 'su - _mysql' tells
    me the correct values show by ulimit -a

    >From the mysql error log when the problem started..


    060413 19:18:34 [Note] /usr/local/libexec/mysqld: ready for
    connections.
    Version: '5.0.19' socket: '/tmp/mysql.sock' port: 3306 Source
    distribution
    060413 20:00:04 [ERROR] Error in accept: Bad file descriptor
    060413 20:00:46 [ERROR] /usr/local/libexec/mysqld: Sort aborted
    ....
    the last (Sort aborted) repeated a lot of times, with a few
    060413 20:45:11 [ERROR] Error in accept: Bad file descriptor
    in between (this was the first, 45 minutes after the first line)

    --

    So the question is:
    Do someone (here) know what has to be tuned (in my.cnf, login.conf and
    the kernel settings) to have a known well-working MySQL server running
    on OpenBSD ?

    For someone who thinks the system performs well, I can contribute with
    a (small) database with a lot of tables...

    /PeO


  2. Re: OpenBSD and MySQL

    "PeO" wrote in message
    news:1145006840.738430.323790@g10g2000cwb.googlegr oups.com...
    > As probably discussed here and in other groups earlier, there is a
    > known problem with using OpenBSD as a MySQL server.
    >
    > The problem is that MySQL opens more files than the default kernel is
    > allowed to (open files per process), and the only way to change this
    > seems to be to edit the hardcoded value for OPEN_MAX in the syslimits.h
    > file in /usr/src/sys/sys




    Sounds familiar - see my thread entitled "mysql / openbsd 3.8" (dated
    2/2/06) in this newsgroup. Not sure I can offer any definite fixes, though.

    Steve
    http://www.fivetrees.com



  3. Re: OpenBSD and MySQL

    PeO wrote:
    > As probably discussed here and in other groups earlier, there is a
    > known problem with using OpenBSD as a MySQL server.
    >
    > The problem is that MySQL opens more files than the default kernel is
    > allowed to (open files per process), and the only way to change this
    > seems to be to edit the hardcoded value for OPEN_MAX in the syslimits.h
    > file in /usr/src/sys/sys


    See sysctl(8).

    > I have tried with MySQL 5.0.18 in ports (OpenBSD 3.9), but before
    > finding out what caused the file access problem, I changed it for
    > 5.0.19 (src) with my usual options for compiling.


    Not a good move, ports are there for a reason. And don't go blindly
    changing stuff - try to stick as close as possible to what works.

    > After changing
    > OPEN_MAX (and a couple of other settings in my.cnf and login.conf) it
    > ran for about 16 hours without a problem.


    Not a good move, the GENERIC kernel is built that way for a reason, and
    sysctl(8) is provided for a reason.

    > I removed the bin-log from my.cnf and restarted, and this time the
    > server survived only about 30 minutes. It broke down (MySQL, not
    > OpenBSD) while running a backup using mysqldump, which for one of the
    > databases opens up a lot of tables/files.
    >
    > The hardware I'm using is a home-built AMD64 2GHz with 4GB RAM, so
    > there is room for some memory-eating tuning if needed.
    >
    > Result (at this moment) from
    > # mysqladmin -u root version
    > mysqladmin Ver 8.41 Distrib 5.0.19, for unknown-openbsd3.9 on x86_64
    > Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
    > This software comes with ABSOLUTELY NO WARRANTY. This is free software,
    > and you are welcome to modify and redistribute it under the GPL license
    >
    > Server version 5.0.19
    > Protocol version 10
    > Connection Localhost via UNIX socket
    > UNIX socket /tmp/mysql.sock
    > Uptime: 12 hours 16 min 16 sec
    >
    > Threads: 1 Questions: 1080571 Slow queries: 0 Opens: 0 Flush
    > tables: 1 Open tables: 513 Queries per second avg: 24.461
    >
    > # pstat -T |grep files
    > 511/13196 open files


    Okay, so kern.maxfiles seems to have been increased.

    > # fstat |grep sql |wc -l
    > 435
    >
    > /etc/my.cnf:
    > default my-huge.cnf with the following changes (when the defaults
    > didn't work well):
    >
    > 27a28
    >> datadir = /home/mysql_data

    > 28a30,33
    >> max_tmp_tables = 64
    >> tmpdir = /tmp
    >> open_files = 8192
    >> innodb_open_files=2048

    > 30,34c35,41
    > < max_allowed_packet = 1M
    > < table_cache = 512
    > < sort_buffer_size = 2M
    > < read_buffer_size = 2M
    > < read_rnd_buffer_size = 8M
    > ---
    >> max_allowed_packet = 16M
    >> table_cache = 5000
    >> open-files-limit=8192
    >> max_connections = 1000
    >> sort_buffer_size = 32M
    >> read_buffer_size = 32M
    >> read_rnd_buffer_size = 16M

    > 51c58
    > < log-bin=mysql-bin
    > ---
    >> #log-bin=mysql-bin

    > 56c63
    > < server-id = 1
    > ---
    >> server-id = 93


    Diffs should be -u, but okay. open-files-limit is likely set higher than
    kern.maxfiles (1772 on 3.8-stable/i386), which is of course a recipe for
    problems.

    I have no clue how open_files and open-files-limit, etc, interact.

    > my /etc/login.conf
    > (at the end I have added)
    >
    > mysql:\
    > :datasize=infinity:\
    > :maxproc=infinity:\
    > penfiles-cur=8192:\
    > penfiles-max=16000:\
    > :stacksize-cur=8M:\
    > :localcipher=blowfish,8:\
    > :tc=default:
    >
    > the _mysql user belongs to user class mysql, and a 'su - _mysql' tells
    > me the correct values show by ulimit -a


    Some margin between openfiles-cur and what MySQL is told to use might be
    wise. Because it just might happen that MySQL doesn't count its log
    files, or somesuch...

    >>From the mysql error log when the problem started..

    >
    > 060413 19:18:34 [Note] /usr/local/libexec/mysqld: ready for
    > connections.
    > Version: '5.0.19' socket: '/tmp/mysql.sock' port: 3306 Source
    > distribution
    > 060413 20:00:04 [ERROR] Error in accept: Bad file descriptor
    > 060413 20:00:46 [ERROR] /usr/local/libexec/mysqld: Sort aborted
    > ...
    > the last (Sort aborted) repeated a lot of times, with a few
    > 060413 20:45:11 [ERROR] Error in accept: Bad file descriptor
    > in between (this was the first, 45 minutes after the first line)


    Hmm, I wouldn't know what this is about, but running out of file
    descriptors might be the problem.

    Then again, this sounds like EBADF, not ENFILE (see accept(2)). Not sure
    what would cause that.

    > So the question is:
    > Do someone (here) know what has to be tuned (in my.cnf, login.conf and
    > the kernel settings) to have a known well-working MySQL server running
    > on OpenBSD ?
    >
    > For someone who thinks the system performs well, I can contribute with
    > a (small) database with a lot of tables...


    It doesn't perform all that well. MySQL uses threads extensively, and
    OpenBSD is not very good at that.

    In fact, MySQL is one of the least well-performing applications on
    OpenBSD. Go with PostgreSQL - it is much better in many ways, and quite
    likely performs better on OpenBSD.

    However, MySQL should not crash.

    Please do the following:
    1. Revert to MySQL from ports
    2. Revert to a GENERIC kernel from -stable
    3. Set kern.maxfiles to some appropriately large number
    4. Set openfiles in login.conf to an appropriately large, but
    smaller, number. Don't bother with -max and -cur, MySQL will not try to
    increase the limit so just set a hard number.
    5. Set the various MySQL file descriptor limits to some
    appropriately large, but smaller, number. In particular, check that the
    number of connections plus the number open_files plus anything else with
    'open' in it is smaller than openfiles, by an appropriate margin (to
    allow other processes to consume file descriptors).
    6. Check that open-files-limit is really a my.cnf setting (hint:
    it is a command-line parameter). I'm fairly certain it isn't, if only
    because it uses - instead of _.
    7. Run mysqldump again, and other tests, &c
    8. If this doesn't work, report back.

    Personally, not knowing MySQL all that well, I'm inclined to think it
    might well be something else - accept(2) seems to indicate that ENFILES
    should be returned, instead of EBADF.

    Joachim

  4. Re: OpenBSD and MySQL


    > > After changing
    > > OPEN_MAX (and a couple of other settings in my.cnf and login.conf) it
    > > ran for about 16 hours without a problem.

    >
    > Not a good move, the GENERIC kernel is built that way for a reason, and
    > sysctl(8) is provided for a reason.
    >

    ok, I realized now that I did a lot of changes "just in case" (and
    using suggestions from the httpd 1.3 documentation on changing
    OPEN_MAX)..

    This is what I have now:
    Generic kernel with RAIDframe added
    maxusers raised to 256 (to prevent problems with kmap)
    Reverted to the supplied my-huge.cnf MySQL configuration
    Modified that, line by line to find out when the problem disappears

    mysqldump crashed exactly when fstat |grep sql |wc -l reached 128,
    until I added
    open-files-limit = 8192

    in the my.cnf file


    > > # pstat -T |grep files
    > > 511/13196 open files

    >
    > Okay, so kern.maxfiles seems to have been increased.
    >

    yes, because of the increased maxusers value in the kernel
    configuration file

    > Please do the following:


    > 6. Check that open-files-limit is really a my.cnf setting (hint:
    > it is a command-line parameter). I'm fairly certain it isn't, if only
    > because it uses - instead of _.


    how odd that MySQL didn't crash anymore after setting this value, and
    it did only work with '-' in between the words of the variable..

    After a dump, 'fstat |grep sql |wc -l' returns 382 open files..

    /PeO


  5. Re: OpenBSD and MySQL

    PeO wrote:
    >
    >> > After changing
    >> > OPEN_MAX (and a couple of other settings in my.cnf and login.conf) it
    >> > ran for about 16 hours without a problem.

    >>
    >> Not a good move, the GENERIC kernel is built that way for a reason, and
    >> sysctl(8) is provided for a reason.
    >>

    > ok, I realized now that I did a lot of changes "just in case" (and
    > using suggestions from the httpd 1.3 documentation on changing
    > OPEN_MAX)..
    >
    > This is what I have now:
    > Generic kernel with RAIDframe added
    > maxusers raised to 256 (to prevent problems with kmap)
    > Reverted to the supplied my-huge.cnf MySQL configuration
    > Modified that, line by line to find out when the problem disappears
    >
    > mysqldump crashed exactly when fstat |grep sql |wc -l reached 128,
    > until I added
    > open-files-limit = 8192
    >
    > in the my.cnf file
    >
    >
    >> > # pstat -T |grep files
    >> > 511/13196 open files

    >>
    >> Okay, so kern.maxfiles seems to have been increased.
    >>

    > yes, because of the increased maxusers value in the kernel
    > configuration file
    >
    >> Please do the following:

    >
    >> 6. Check that open-files-limit is really a my.cnf setting (hint:
    >> it is a command-line parameter). I'm fairly certain it isn't, if only
    >> because it uses - instead of _.

    >
    > how odd that MySQL didn't crash anymore after setting this value, and
    > it did only work with '-' in between the words of the variable..
    >
    > After a dump, 'fstat |grep sql |wc -l' returns 382 open files..


    Hmm, that solved it.

    I'm not sure this is documented/intended behaviour of MySQL; if you
    could discover whether or not that is the case, the MySQL people might
    appreciate a mail pointing this out.

    Joachim

+ Reply to Thread