# I haz a sad. innodb\_buffer\_pool\_size and OpenBSD **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [June 10, 2009, 3:23am UTC](https://forums.percona.com/t/i-haz-a-sad-innodb-buffer-pool-size-and-openbsd/1190 "2009-06-10T03:23:32Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![mrmoosehead](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@mrmoosehead](https://forums.percona.com/u/mrmoosehead)\ **Post date:** [June 10, 2009, 3:23am UTC](https://forums.percona.com/t/i-haz-a-sad-innodb-buffer-pool-size-and-openbsd/1190/1 "2009-06-10T03:23:32Z")
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. ---
**Author:** ![mrmoosehead](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@mrmoosehead](https://forums.percona.com/u/mrmoosehead)\ **Post date:** [June 10, 2009, 7:05am UTC](https://forums.percona.com/t/i-haz-a-sad-innodb-buffer-pool-size-and-openbsd/1190/2 "2009-06-10T07:05:17Z")
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. ---
**Author:** ![mrmoosehead](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@mrmoosehead](https://forums.percona.com/u/mrmoosehead)\ **Post date:** [June 10, 2009, 7:39am UTC](https://forums.percona.com/t/i-haz-a-sad-innodb-buffer-pool-size-and-openbsd/1190/3 "2009-06-10T07:39:24Z")
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. ---
**Author:** ![sterin71](https://avatars.discourse-cdn.com/v4/letter/s/5e9695/32.png) [@sterin71](https://forums.percona.com/u/sterin71)\ **Post date:** [June 14, 2009, 4:32pm UTC](https://forums.percona.com/t/i-haz-a-sad-innodb-buffer-pool-size-and-openbsd/1190/4 "2009-06-14T16:32:16Z")
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. ---
**Author:** ![mrmoosehead](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@mrmoosehead](https://forums.percona.com/u/mrmoosehead)\ **Post date:** [June 15, 2009, 11:01am UTC](https://forums.percona.com/t/i-haz-a-sad-innodb-buffer-pool-size-and-openbsd/1190/5 "2009-06-15T11:01:26Z")
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 #