-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
- -----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Stuart Henderson wrote:
>>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).
>
>
> It's not idle, it's waiting for i/o to complete.
>
>
Okay... but by looking in iostat, it looks like pretty low traffic. 1 to
2 MB/sec. A higher number of transfers per second, though.
- - -bash-3.1$ iostat 1
tty sd0 cd0 fd0 cpu
tin tout KB/t t/s MB/s KB/t t/s MB/s KB/t t/s MB/s us ni sy in id
0 1 16.85 15 0.25 0.00 0 0.00 0.00 0 0.00 0 0 0 0 99
0 1145 16.09 90 1.41 0.00 0 0.00 0.00 0 0.00 0 0 0 0 99
0 1022 16.52 77 1.24 0.00 0 0.00 0.00 0 0.00 2 0 0 0 97
0 158 16.21 82 1.29 0.00 0 0.00 0.00 0 0.00 0 0 0 0 99
0 213 16.00 92 1.44 0.00 0 0.00 0.00 0 0.00 0 0 0 0100
0 149 16.00 155 2.43 0.00 0 0.00 0.00 0 0.00 0 0 0 0100
0 229 15.93 181 2.81 0.00 0 0.00 0.00 0 0.00 1 0 0 0 99
0 657 16.97 82 1.35 0.00 0 0.00 0.00 0 0.00 1 0 0 0 99
0 217 16.37 85 1.35 0.00 0 0.00 0.00 0 0.00 2 0 1 0 97
0 155 16.71 78 1.27 0.00 0 0.00 0.00 0 0.00 2 0 0 0 97
0 209 16.61 78 1.27 0.00 0 0.00 0.00 0 0.00 1 0 1 0 98
0 257 16.64 74 1.20 0.00 0 0.00 0.00 0 0.00 2 0 0 0 97
0 188 16.00 90 1.40 0.00 0 0.00 0.00 0 0.00 0 0 0 0100
0 149 16.09 95 1.49 0.00 0 0.00 0.00 0 0.00 0 0 0 0100
0 140 16.00 212 3.31 0.00 0 0.00 0.00 0 0.00 0 0 0 0100
0 153 16.06 134 2.10 0.00 0 0.00 0.00 0 0.00 0 0 0 0100
0 150 16.08 99 1.55 0.00 0 0.00 0.00 0 0.00 1 0 0 0 99
0 151 16.72 88 1.43 0.00 0 0.00 0.00 0 0.00 1 0 0 0 98
0 150 16.24 98 1.55 0.00 0 0.00 0.00 0 0.00 1 0 0 0 98
0 221 16.42 75 1.20 0.00 0 0.00 0.00 0 0.00 1 0 1 0 97
Well... it's getting data from the master all the time, so I guess, it
will be in waiting for i/o all the time.
However, this is by "design" (if you like to speak of design in regards
to MySQL).
And still I should be able to connect to mysql and do a show slave
status\G quite fast (not waiting 6 seconds to complete that task...).
It gets even worse, if I try to do a select on some database. Yeah, the
database could be locked while I do that, but since there are 50 queries
/ second coming in, the database still should have enough time to answer
(in between being locked).
Okay, flame me, but, the same replication setup like it is in use here
on a Debian Woody with Linux 2.4.31 takes 0,00 seconds to do a select
count(*) foo; while OpenBSD needs from 0,83 to 7,56 seconds to complete
the request :-/
Strange...
mysql> select count(*) from foo;
+----------+
| count(*) |
+----------+
| 1389660 |
+----------+
1 row in set (0.83 sec)
mysql> select count(*) from foo;
+----------+
| count(*) |
+----------+
| 1389665 |
+----------+
1 row in set (3.70 sec)
mysql> select count(*) from foo;
+----------+
| count(*) |
+----------+
| 1389677 |
+----------+
1 row in set (7.56 sec)
mysql> select count(*) from foo;
+----------+
| count(*) |
+----------+
| 1389697 |
+----------+
1 row in set (6.52 sec)
mysql> select count(*) from foo;
+----------+
| count(*) |
+----------+
| 1389699 |
+----------+
1 row in set (5.06 sec)
mysql>
Linux 2.4.31:
mysql> select count(*) from foo;
+----------+
| count(*) |
+----------+
| 1405115 |
+----------+
1 row in set (0.00 sec)
The Linux box is running the same hardware like the OpenBSD box. Only
difference is that the Linux box is running MySQL 4.1.14 whereas OpenBSD
runs 5.0.22
Granted, you can't compare those two systems.
On the other hand, the Linux box is in production, taking the 50 queries
/ second from replication while handling another 50 queries / second
due to being in production. Counts up to 100 queries per second avg.
Any more ideas? Should it be all related to the replication setup and
Disk I/O ?
I do know that MySQL is a bitch in regards to I/O and VM.
'tis no fun to handle huge files:
- - -bash-3.1$ ls -l /usr/local/mysql/data/*relay*
- - -rw-rw---- 1 _mysql _mysql 197288820 Sep 18 11:45
/usr/local/mysql/data/babelfish45-relay-bin.000032
- - -rw-rw---- 1 _mysql _mysql 31 Sep 18 09:59
/usr/local/mysql/data/babelfish45-relay-bin.index
- - -rw-rw---- 1 _mysql _mysql 72 Sep 18 11:45
/usr/local/mysql/data/relay-log.info
- - -bash-3.1$
Yeah, the relay binlog is _that_ big...
./Marian
- -----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (Darwin)
iD8DBQFFDmr7gAq87Uq5FMsRAoYMAKCK8xsX+xsR5s7zlbcAC2bwkA8IKACgpW5v
T2G5alP9I5IboDYbURweUEw=
=cQNh
- -----END PGP SIGNATURE-----
iD8DBQFFDnqigAq87Uq5FMsRAoD1AKCECxcXefdpAD6qPEFS7tfFBBNDQgCfa5L7
ttSmr1SH+VPxJ77B31wkJzw=
=+qcy
-----END PGP SIGNATURE-----
