We have recently setup a
PII 400 running FreeBSD 3.1-RELEASE #0: Thu Mar 25 19:16:46 EST 1999
with 256M of memory and a 8G IBM drive
da0: <IBM DDRS-39130D DC1B> Fixed Direct Access SCSI-2 device
da0: 80.0MB/s transfers (40.0MHz, offset 15, 16bit), Tagged Queueing Enabled
da0: 8715MB (17850000 512 byte sectors: 255H 63S/T 1111C)
the machine basically replies to an Apache 1.3.6 server with PHP 3.0.7
Normally the performance is great but on our big days, mysql seems to
flake out.
below are some of the tests conducted anda description of the problem frm
the webmaster.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Oddly, the mysqld responds lightning fast on any mysqladmin request, but
on normal, simple queries, it is quite unacceptable. Here is an example
of two exact same queries. The first is at 1:10 pm, the second is at
1:30. The speed on the first is .09 seconds (incredible), and 2 minutes
31 seconds for the second (unaccpetable) :
1:10 pm
mysql> select friend, count(friend) AS it from user_ads group by friend
order by
it DESC limit 0,15;
+------------------+----+
| friend | it |
+------------------+----+
| 3510282609fc009b | 38 |
| 08dde5f946daa770 | 36 |
| 126c3f2057e88f17 | 35 |
| 454a00b72edf296a | 35 |
| 514520b378533d8c | 32 |
| 768653d8146d510c | 32 |
| 098a3f5059e83a61 | 31 |
| 27365d480e851b12 | 31 |
| 06ce61ed7c478651 | 30 |
| 39b91e461861f4bc | 27 |
| 1602ff1d790e2737 | 26 |
| 1374bad76d34929b | 25 |
| 442131156f5e6867 | 24 |
| 48931a5502b975c8 | 24 |
| 658c75be5b9a6b88 | 22 |
+------------------+----+
15 rows in set (0.09 sec)
1:30 pm
mysql> select friend, count(friend) AS it from user_ads group by friend
order by
it DESC limit 0,15;
+------------------+----+
| friend | it |
+------------------+----+
| 0926edbe6996e9ef | 60 |
| 5651182466385c6c | 56 |
| 78d3880d5902314e | 40 |
| 667bf0f956962280 | 39 |
| 3510282609fc009b | 38 |
| 08dde5f946daa770 | 36 |
| 126c3f2057e88f17 | 35 |
| 454a00b72edf296a | 35 |
| 1602ff1d790e2737 | 34 |
| 444681af616636ff | 34 |
| 514520b378533d8c | 32 |
| 768653d8146d510c | 32 |
| 098a3f5059e83a61 | 31 |
| 27365d480e851b12 | 31 |
| 06ce61ed7c478651 | 30 |
+------------------+----+
15 rows in set (2 min 31.69 sec)
mysqladmin version
mysqladmin Ver 7.11 Distrib 3.22.21, for unknown-freebsd3.1 on i386
TCX Datakonsult AB, by Monty
Server version 3.22.21
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 5 days 22 hours 50 min 21 sec
Threads: 177 Questions: 5366198 Slow queries: 23403 Opens: 332727
Flush tables: 2 Open tables: 296
+----------------------------+---------------------------------+
| Variable_name | Value |
+----------------------------+---------------------------------+
| back_log | 120 |
| connect_timeout | 5 |
| basedir | /usr/local/ |
| datadir | /usr/local/var/ |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| join_buffer | 131072 |
| flush_time | 0 |
| key_buffer | 16773120 |
| language | /usr/local/share/mysql/english/ |
| log | OFF |
| log_update | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| max_allowed_packet | 1048576 |
| max_connections | 360 |
| max_connect_errors | 10 |
| max_delayed_insert_threads | 20 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| net_buffer_length | 16384 |
| port | 3306 |
| protocol-version | 10 |
| record_buffer | 2093056 |
| skip_locking | OFF |
| socket | /tmp/mysql.sock |
| sort_buffer | 8388600 |
| table_cache | 256 |
| thread_stack | 65536 |
| tmp_table_size | 1048576 |
| tmpdir | /var/tmp/ |
| version | 3.22.21 |
| wait_timeout | 28800 |
+----------------------------+---------------------------------+
