-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hej All,
since I do prefer OpenBSD and FreeBSD on any server, and our company
policy is to run Debian Sarge, I thought "What the hack" and gave
OpenBSD a try for MySQL (inspired too by all those long threads on misc@
recently).
Well, I guess I'm doing something wrong... and perhaps someone could
enlighten me a bit :)
Situation is as follows: We have quite a huge MySQL replication setup
(Master, submaster, clients). As said, all of those run Debian at a
Linux 2.4.31 self compiled kernel. They all perform well (as good as
MySQL can get *chuckle*).
I installed mysql from packages (so it's MySQL 5.0.22) and it's
replicating from a MySQL 4.1.14 (which shouldn't matter at all).
As soon as replication starts, mysql gets very unresponsive:
- -bash-3.1$ time mysqladmin -uroot -p proc stat
Enter password:
+----+-------------+-----------+----+---------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State
| Info |
+----+-------------+-----------+----+---------+------+-----------------------------------------------------------------------+------------------+
| 4 | system user | | | Connect | 204 | Waiting for
master to send event |
|
| 5 | system user | | | Connect | 8661 | Has read all
relay log; waiting for the slave I/O thread to update it |
|
| 7 | root | localhost | | Query | 0 |
| show processlist |
+----+-------------+-----------+----+---------+------+-----------------------------------------------------------------------+------------------+
Uptime: 308 Threads: 1 Questions: 6328 Slow queries: 0 Opens: 0
Flush tables: 1 Open tables: 24 Queries per second avg: 20.545
real 0m15.463s
user 0m0.010s
sys 0m0.020s
15 bloody seconds to return mysqladmin proc stat ?
That ain't good.
A closer look at vmstat looks okay, the box is pretty idle afaik.
- -bash-3.1$ vmstat 1
procs memory page disks traps cpu
r b w avm fre flt re pi po fr sr sd0 cd0 int sys cs
us sy id
1 1 0 633641826912 5 0 0 0 0 0 1 0 3 21 6
0 0 100
0 1 0 634521826828 72 0 0 0 0 0 354 0 190 820 122
0 0 100
0 1 0 634921826788 68 0 0 0 0 0 350 0 214 1758 111
1 0 98
0 1 0 635561826724 96 0 0 0 0 0 358 0 214 2133 98
1 0 99
0 1 0 635921826688 34 0 0 0 0 0 352 0 184 1015 123
0 0 100
0 1 0 636401826640 19 0 0 0 0 0 347 0 195 1060 111
0 0 99
0 1 0 636521826628 16 0 0 0 0 0 115 0 333 443 41
0 0 99
0 1 0 636641826616 10 0 0 0 0 0 101 0 367 194 36
0 0 100
0 1 0 637121826568 19 0 0 0 0 0 414 0 229 886 130
2 0 98
0 1 0 637561826524 29 0 0 0 0 0 360 0 216 1046 118
1 0 98
1 0 0 637761826504 17 0 0 0 0 0 305 0 240 824 92
1 1 98
0 1 0 638521826428 27 0 0 0 0 0 366 0 190 626 123
0 0 100
^C
load averages: 1.06, 1.04, 1.03
16:35:39
37 processes: 1 running, 35 idle, 1 on processor
CPU0 states: 0.0% user, 0.0% nice, 0.0% system, 0.0% interrupt,
100% idle
CPU1 states: 0.0% user, 0.0% nice, 0.0% system, 0.0% interrupt,
100% idle
Memory: Real: 64M/232M act/tot Free: 1782M Swap: 0K/1024M used/tot
PID USERNAME PRI NICE SIZE RES STATE WAIT TIME CPU COMMAND
25222 _mysql -5 0 185M 50M sleep/0 biowai 0:14 1.42% mysqld
Well... to me it looks like the box is idle... why is MySQL still pretty
unresponsive (I tend to say slow).
I do know MySQL quite well (MySQL 4.1 in fact) and for the OpenBSD
Installation I followed the guidelines at www.openbsdsupport.org, which
was basicly just increasing the kern.maxfiles and changing /etc/login.conf
and now for some facts about the box I'm running MySQL on:
- -bash-3.1$ uname -a
OpenBSD babelfish45.mobile.rz 3.9 GENERIC.MP#598 i386
URL's to interesting files about this box:
http://crivens.terrorteam.de/~rabauke/OpenBSD/MySQL/dmesg.openbsd39.txt
http://crivens.terrorteam.de/~rabauke/OpenBSD/MySQL/my.cnf.txt
http://crivens.terrorteam.de/~rabauke/OpenBSD/MySQL/mysqladmin.txt
http://crivens.terrorteam.de/~rabauke/OpenBSD/MySQL/vmstat.mysql.txt
Ah, I nearly forgot:
- -bash-3.1$ sysctl kern.maxfiles
kern.maxfiles=8096
- -bash-3.1$ sudo su -m _mysql -c "ulimit -a"
time(cpu-seconds) unlimited
file(blocks) unlimited
coredump(blocks) unlimited
data(kbytes) 1048576
stack(kbytes) 8192
lockedmem(kbytes) 635692
memory(kbytes) 1905588
nofiles(descriptors) 128
processes 532
should be fine, shouldn't it ?
I'm looking forward to any suggestions in regards to tuning. And yes, 8
seconds to get a mysqladmin output is _long_ ;-)
./Marian
iD8DBQFFCr29gAq87Uq5FMsRAgd3AKDj81jv573kdNF95M5Pmignogn+hwCdGEJ2
m3wOcwxC/KbB0d83tMyKJXk=
=FhMF
-----END PGP SIGNATURE-----
