GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

I haz a sad. innodb_buffer_pool_size and OpenBSD

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • I haz a sad. innodb_buffer_pool_size and OpenBSD

    Now then. I come, cap in hand, begging for wisdom.

    Got an OpenBSD 4.5 server running mysql 5.0.77
    4GB RAM, Quad core server, apparently a Quad core optimised kernel.

    Server runs apache with average 100 child threads of c. 3MB memory size.
    Load averages of around 1.5 to 2.2

    single database of c. 40 innodb tables, optimised reasonably well I think - spent a long time on DESCRIBEing queries and index creation. Tables vary between 10s of rows and a few hundred thousand rows
    This is a master replicator to a slave on another box.

    Sounds lovely, I hear you say! What's the problem.

    Server keeps dying. kernel panics etc.
    We think we have narrowed it down to mysql throwing it's toys out of the pram.

    One thing that is frustrating me is the memory allocation - I cannot get mysql to run with any more than 500M in innodb_buffer_pool_size
    mysql runs under its own login class that has unlimited in most things. open file limits raised etc.

    in 'top' mysqld has a SIZE fo 597 and RES of 167M at the mo.

    load averages: 2.58, 2.13, 1.81 09:49:50
    90 processes: 88 idle, 1 zombie, 1 on processor
    CPU0 states: 3.1% user, 0.0% nice, 0.2% system, 0.2% interrupt, 96.6% idle
    CPU1 states: 1.0% user, 0.0% nice, 0.2% system, 0.0% interrupt, 98.8% idle
    CPU2 states: 0.9% user, 0.0% nice, 0.1% system, 0.0% interrupt, 99.0% idle
    CPU3 states: 1.1% user, 0.0% nice, 0.1% system, 0.0% interrupt, 98.8% idle
    Memory: Real: 246M/631M act/tot Free: 2637M Swap: 0K/8197M used/tot

    PID USERNAME PRI NICE SIZE RES STATE WAIT TIME CPU COMMAND
    20231 www 2 0 3116K 7224K sleep/0 netio 0:36 15.53% httpd
    12468 www 2 0 3072K 6924K sleep/2 netio 0:05 9.62% httpd
    10238 www 2 0 5164K 8888K sleep/1 netio 0:02 9.62% httpd
    28535 www 2 0 3064K 6972K sleep/2 netio 0:05 5.57% httpd
    16214 _mysql -5 0 596M 167M sleep/1 biowait 3:35 3.86% mysqld
    8610 www 2 0 3068K 6972K sleep/0 netio 0:05 2.44% httpd
    31817 www 14 0 3124K 7224K sleep/0 semwait 0:22 1.27% httpd
    14080 www 2 0 3116K 7216K sleep/3 netio 1:11 0.88% httpd
    20649 www 2 0 3080K 6836K sleep/0 netio 0:04 0.78% httpd
    11235 www 2 0 3092K 7132K sleep/0 netio 0:22 0.34% httpd
    31011 www 2 0 3160K 7196K sleep/1 netio 1:11 0.29% httpd
    23481 www 2 0 3108K 7164K sleep/2 netio 1:06 0.29% httpd
    1739 www 2 0 3156K 7160K sleep/0 netio 1:03 0.29% httpd
    22003 www 2 0 3176K 7308K sleep/1 netio 0:36 0.29% httpd
    16152 www 2 0 3068K 6960K sleep/2 netio 0:02 0.29% httpd
    28311 www 2 0 3088K 7012K sleep/3 netio 0:29 0.24% httpd
    23577 www 2 0 3184K 7280K sleep/3 netio 1:51 0.20% httpd
    27084 www 2 0 3068K 6900K sleep/1 netio 0:03 0.15% httpd
    20888 www 2 0 3164K 7204K sleep/3 netio 1:12 0.05% httpd
    7775 www 2 0 3100K 7024K sleep/2 netio 0:12 0.05% httpd
    30141 www 2 0 3068K 6948K sleep/3 netio 0:00 0.05% httpd
    9379 www 14 0 3144K 7104K sleep/1 semwait 1:43 0.00% httpd
    21090 www 14 0 3160K 7200K sleep/0 semwait 1:28 0.00% httpd
    44 www 2 0 3124K 7004K sleep/1 netio 1:23 0.00% httpd
    11830 www 2 0 3136K 7168K sleep/1 netio 1:15 0.00% httpd
    21924 www 2 0 3148K 7116K sleep/3 netio 1:11 0.00% httpd
    24841 www 2 0 3164K 7188K sleep/0 netio 1:09 0.00% httpd
    31528 www 14 0 3140K 7156K sleep/2 semwait 1:09 0.00% httpd
    19097 www 2 0 3116K 7128K sleep/1 netio 1:05 0.00% httpd
    16381 www 14 0 3168K 7376K sleep/2 semwait 1:03 0.00% httpd
    21046 www 2 0 3120K 7232K sleep/0 netio 0:53 0.00% httpd
    15623 www 2 0 3112K 6976K sleep/1 netio 0:50 0.00% httpd
    25203 www 2 0 3136K 7072K sleep/0 netio 0:49 0.00% httpd
    8439 www 2 0 3132K 7036K sleep/2 netio 0:49 0.00% httpd



    Sometimes server just drops, sometimes starts refusing mysql queries complaining about memory allocations for a query.

    # Example MySQL config file for medium systems.
    #
    # This is for a system with little memory (32M - 64M) where MySQL plays
    # an important part, or systems up to 128M where MySQL is used together with
    # other programs (such as a web server)
    #
    # You can copy this file to
    # /etc/my.cnf to set global options,
    # mysql-data-dir/my.cnf to set server-specific options (in this
    # installation this directory is /var/mysql) or
    # ~/.my.cnf to set user-specific options.
    #
    # In this file, you can use all long options that a program supports.
    # If you want to know which options a program supports, run the program
    # with the "--help" option.

    # The following options will be passed to all MySQL clients
    [client]
    #password = your_password
    port = 3306
    socket = /var/www/var/run/mysql/mysql.sock

    # Here follows entries for some specific programs

    # The MySQL server
    [mysqld]
    port = 3306
    socket = /var/www/var/run/mysql/mysql.sock
    skip-locking
    key_buffer = 16M
    max_allowed_packet = 1M
    table_cache = 64
    sort_buffer_size = 512K
    net_buffer_length = 8K
    read_buffer_size = 256K
    read_rnd_buffer_size = 512K
    myisam_sort_buffer_size = 8M

    # Don't listen on a TCP/IP port at all. This can be a security enhancement,
    # if all processes that need to connect to mysqld run on the same host.
    # All interaction with mysqld must be made via Unix sockets or named pipes.
    # Note that using this option without enabling named pipes on Windows
    my.cnf: unmodified, readonly: line 1


    Anyone any suggestions.
    Prizes for spotting the most stupid error first. )

    TIA
    M.

  • #2
    Just did it again.
    Fatal error: Uncaught exception 'Exception' with message 'mysql error: Can't query SELECT COUNT(DISTINCT B.Baby_ID) FROM waitinglist WL INNER JOIN baby B ON WL.Baby_ID = B.Baby_ID WHERE B.F_ID = 1:Out of memory (Needed 1048548 bytes)'

    restarted, this is EXPLAIN on the query:

    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE B ref PRIMARY,IX_Baby_FIDCarerID IX_Baby_FIDCarerID 5 const 3444 Using where; Using index
    1 SIMPLE WL ref Baby_ID Baby_ID 5 nemo.B.Baby_ID 1 Using where; Using index


    waitinglist has 4500 rows, 1MB data
    baby has 31000 rows, 4.5MB data.

    Argh.

    Comment


    • #3
      And one more bit of info.
      At the time of this happening, the memory usage of mysql according to top was not massive - still SIZE 596M and RES in the order of 200M

      No long queries in the PROCESSLIST as far as I am aware.

      Comment


      • #4
        Is that really your my.cnf file?
        I'm skeptic since it doesn't contain anything about your innodb tables that you mentioned.

        Do you really get kernel panic and can you see anything in the syslog about what happens?
        If your MySQL process does not consume more memory than that I have a hard time believing that that is the cause of the kernel panic problems.

        Regarding the problem with increasing the innodb memory size I would suggest you to double check that the limits are really raised for the mysql user that the database runs as.

        Comment


        • #5
          Damn .truncated the file .

          Right, thanks for replying.
          A little update.
          Server no longer kernel panicking at every occasion - have updated OpenBSD to 4.5 and increased file limits.

          But, SQL server keeps runnign out of memory to perform even small queries.

          we think we have narrowed it down to lack of available memory in OpenBSD - for 32bit, max per PROCESS is around 1GB due to the security bit(or something like that) so either going 64bit or moving to FreeBSD
          Which would then explain why I was having trouble raising the innodb_buffer_pool about 500M



          # more my.cnf
          # Example MySQL config file for medium systems.
          #
          # This is for a system with little memory (32M - 64M) where MySQL plays
          # an important part, or systems up to 128M where MySQL is used together with
          # other programs (such as a web server)
          #
          # You can copy this file to
          # /etc/my.cnf to set global options,
          # mysql-data-dir/my.cnf to set server-specific options (in this
          # installation this directory is /var/mysql) or
          # ~/.my.cnf to set user-specific options.
          #
          # In this file, you can use all long options that a program supports.
          # If you want to know which options a program supports, run the program
          # with the "--help" option.

          # The following options will be passed to all MySQL clients
          [client]
          #password = your_password
          port = 3306
          socket = /var/www/var/run/mysql/mysql.sock

          # Here follows entries for some specific programs

          # The MySQL server
          [mysqld]
          port = 3306
          socket = /var/www/var/run/mysql/mysql.sock
          skip-locking
          key_buffer = 16M
          max_allowed_packet = 1M
          table_cache = 64
          sort_buffer_size = 512K
          net_buffer_length = 8K
          read_buffer_size = 256K
          read_rnd_buffer_size = 512K
          myisam_sort_buffer_size = 8M

          # Don't listen on a TCP/IP port at all. This can be a security enhancement,
          # if all processes that need to connect to mysqld run on the same host.
          # All interaction with mysqld must be made via Unix sockets or named pipes.
          # Note that using this option without enabling named pipes on Windows
          # (via the "enable-named-pipe" option) will render mysqld useless!
          #
          #skip-networking

          # Replication Master Server (default)
          # binary logging is required for replication
          log-bin=mysql-bin
          sync_binlog=1
          binlog-do-db = nemo
          binlog-ignore-db = mysql

          # required unique id between 1 and 2^32 - 1
          # defaults to 1 if master-host is not set
          # but will not function as a master if omitted
          server-id = 1

          # Replication Slave (comment out master section to use this)
          #
          # To configure this host as a replication slave, you can choose between
          # two methods :
          #
          # 1) Use the CHANGE MASTER TO command (fully described in our manual) -
          # the syntax is:
          #
          # CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
          # MASTER_USER=, MASTER_PASSWORD= ;
          #
          # where you replace , , by quoted strings and
          # by the master's port number (3306 by default).
          #
          # Example:
          #
          # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
          # MASTER_USER='joe', MASTER_PASSWORD='secret';
          #
          # OR
          #
          # 2) Set the variables below. However, in case you choose this method, then
          # start replication for the first time (even unsuccessfully, for example
          # if you mistyped the password in master-password and the slave fails to
          # connect), the slave will create a master.info file, and any later
          # change in this file to the variables' values below will be ignored and
          # overridden by the content of the master.info file, unless you shutdown
          # the slave server, delete master.info and restart the slaver server.
          # For that reason, you may want to leave the lines below untouched
          # (commented) and instead use CHANGE MASTER TO (see above)
          #
          # required unique id between 2 and 2^32 - 1
          # (and different from the master)
          # defaults to 2 if master-host is set
          # but will not function as a slave if omitted
          #server-id = 2
          #
          # The replication master for this slave - required
          #master-host =
          #
          # The username the slave will use for authentication when connecting
          # to the master - required
          #master-user =
          #
          # The password the slave will authenticate with when connecting to
          # the master - required
          #master-password =
          #
          # The port the master is listening on.
          # optional - defaults to 3306
          #master-port =
          #
          # binary logging - not required for slaves, but recommended
          #log-bin=mysql-bin

          # Point the following paths to different dedicated disks
          #tmpdir = /tmp/
          #log-update = /path-to-dedicated-directory/hostname

          # Uncomment the following if you are using BDB tables
          #bdb_cache_size = 4M
          #bdb_max_lock = 10000

          # Uncomment the following if you are using InnoDB tables
          #innodb_data_home_dir = /var/mysql/
          #innodb_data_file_path = ibdata1:10M:autoextend
          #innodb_log_group_home_dir = /var/mysql/
          #innodb_log_arch_dir = /var/mysql/
          # You can set .._buffer_pool_size up to 50 - 80 %
          # of RAM but beware of setting memory usage too high
          innodb_buffer_pool_size = 512M
          innodb_additional_mem_pool_size = 2M
          # Set .._log_file_size to 25 % of buffer pool size
          #innodb_log_file_size = 256M
          #innodb_log_buffer_size = 8M
          innodb_flush_log_at_trx_commit = 1
          #innodb_lock_wait_timeout = 50

          [mysqldump]
          quick
          max_allowed_packet = 16M

          [mysql]
          no-auto-rehash
          # Remove the next comment character if you are not familiar with SQL
          #safe-updates

          [isamchk]
          key_buffer = 20M
          sort_buffer_size = 20M
          read_buffer = 2M
          write_buffer = 2M

          [myisamchk]
          key_buffer = 20M
          sort_buffer_size = 20M
          read_buffer = 2M
          write_buffer = 2M

          [mysqlhotcopy]
          interactive-timeout
          #

          Comment


          • #6
            mrmoosehead wrote on Mon, 15 June 2009 18:31


            But, SQL server keeps runnign out of memory to perform even small queries.

            we think we have narrowed it down to lack of available memory in OpenBSD - for 32bit, max per PROCESS is around 1GB due to the security bit(or something like that) so either going 64bit or moving to FreeBSD
            Which would then explain why I was having trouble raising the innodb_buffer_pool about 500M


            Yikes! I didn't know that OpenBSD had such a low memory limit per process.

            But yes the 1GB limit for the process together with that some of the mysql variables are per connection (so if you have a lot of connections they can consume quite a bit of memory).

            My suggestions is that you bite the lemon (or whatever the expression is in english ) and decrease the mysql variables so that you avoid the running out of memory errors. Since I'm guessing that you can handle a bit slower performance better than out of memory errors.

            If you are going to change OS to increase memory I would suggest you to go for 64bit directly. Since otherwise you are stuck again at 3GB which isn't so much larger.

            Comment


            • #7
              Hmm. lowering them is not ideal.

              One thing we can't work out though is why this takes approx 12 hours to occur, before restart of mysql is required.

              We've tried flushing tables regularly but to no avail.

              It smells of some kind of memory leak.

              Comment


              • #8
                mrmoosehead wrote on Tue, 16 June 2009 19:42

                Hmm. lowering them is not ideal.

                One thing we can't work out though is why this takes approx 12 hours to occur, ...

                Is this approximately the same hours every day and how many connections do you have at this point in time?
                Could it be that this is the peak hour for the site?
                Monitor this value over time because it is perfectly normal that if you have restarted, and there is a bit of interruption in the service, that the people using it are just temporary giving up and moving on. And then you can get the feeling that the problem was solved by the restart while a network outage would actually have caused the exact same effect.

                So my suggestion is still, try to reduce the Innodb_buffer_pool_size and monitor the server for performance and memory errors.

                As I said before is restarting the server every 12 hours with interruption in the service really better than degrading the performance a bit by reducing the innodb buffer
                pool?
                I live by the motto (when it comes to computers ) that a truck that is reliable is better than a sports car that breaks down all the time.

                Comment

                Working...
                X