I haz a sad. innodb_buffer_pool_size and OpenBSD

MySQL & MariaDB Other MySQL® Questions
1

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.

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.

4

Is that really your my.cnf file?
I’m skeptic since it doesn’t contain anything about your innodb tables that you mentioned. :wink:

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.

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