Hi!
>>>>> "Jesus" == Jesus M Arias <zeus@stripped> writes:
Jesus> We have recently setup a
Jesus> PII 400 running FreeBSD 3.1-RELEASE #0: Thu Mar 25 19:16:46 EST 1999
Jesus> with 256M of memory and a 8G IBM drive
Jesus> da0: <IBM DDRS-39130D DC1B> Fixed Direct Access SCSI-2 device
Jesus> da0: 80.0MB/s transfers (40.0MHz, offset 15, 16bit), Tagged Queueing Enabled
Jesus> da0: 8715MB (17850000 512 byte sectors: 255H 63S/T 1111C)
Jesus> the machine basically replies to an Apache 1.3.6 server with PHP 3.0.7
Jesus> Normally the performance is great but on our big days, mysql seems to
Jesus> flake out.
Jesus> below are some of the tests conducted anda description of the problem frm
Jesus> the webmaster.
Jesus> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Jesus> Oddly, the mysqld responds lightning fast on any mysqladmin request, but
Jesus> on normal, simple queries, it is quite unacceptable. Here is an example
Jesus> of two exact same queries. The first is at 1:10 pm, the second is at
Jesus> 1:30. The speed on the first is .09 seconds (incredible), and 2 minutes
Jesus> 31 seconds for the second (unaccpetable) :
<cut>
Jesus> 1:30 pm
mysql> select friend, count(friend) AS it from user_ads group by friend
Jesus> order by
Jesus> it DESC limit 0,15;
Jesus> +------------------+----+
Jesus> | friend | it |
Jesus> +------------------+----+
Jesus> | 0926edbe6996e9ef | 60 |
Jesus> | 5651182466385c6c | 56 |
Jesus> | 78d3880d5902314e | 40 |
Jesus> | 667bf0f956962280 | 39 |
Jesus> | 3510282609fc009b | 38 |
Jesus> | 08dde5f946daa770 | 36 |
Jesus> | 126c3f2057e88f17 | 35 |
Jesus> | 454a00b72edf296a | 35 |
Jesus> | 1602ff1d790e2737 | 34 |
Jesus> | 444681af616636ff | 34 |
Jesus> | 514520b378533d8c | 32 |
Jesus> | 768653d8146d510c | 32 |
Jesus> | 098a3f5059e83a61 | 31 |
Jesus> | 27365d480e851b12 | 31 |
Jesus> | 06ce61ed7c478651 | 30 |
Jesus> +------------------+----+
Jesus> 15 rows in set (2 min 31.69 sec)
Jesus> mysqladmin version
Jesus> mysqladmin Ver 7.11 Distrib 3.22.21, for unknown-freebsd3.1 on i386
Jesus> TCX Datakonsult AB, by Monty
Jesus> Server version 3.22.21
Jesus> Protocol version 10
Jesus> Connection Localhost via UNIX socket
Jesus> UNIX socket /tmp/mysql.sock
Jesus> Uptime: 5 days 22 hours 50 min 21 sec
Jesus> Threads: 177 Questions: 5366198 Slow queries: 23403 Opens: 332727
Jesus> Flush tables: 2 Open tables: 296
Jesus> +----------------------------+---------------------------------+
Jesus> | Variable_name | Value |
Jesus> +----------------------------+---------------------------------+
Jesus> | back_log | 120 |
Jesus> | connect_timeout | 5 |
Jesus> | basedir | /usr/local/ |
Jesus> | datadir | /usr/local/var/ |
Jesus> | delayed_insert_limit | 100 |
Jesus> | delayed_insert_timeout | 300 |
Jesus> | delayed_queue_size | 1000 |
Jesus> | join_buffer | 131072 |
Jesus> | flush_time | 0 |
Jesus> | key_buffer | 16773120 |
Jesus> | language | /usr/local/share/mysql/english/ |
Jesus> | log | OFF |
Jesus> | log_update | OFF |
Jesus> | long_query_time | 10 |
Jesus> | low_priority_updates | OFF |
Jesus> | max_allowed_packet | 1048576 |
Jesus> | max_connections | 360 |
Jesus> | max_connect_errors | 10 |
Jesus> | max_delayed_insert_threads | 20 |
Jesus> | max_join_size | 4294967295 |
Jesus> | max_sort_length | 1024 |
Jesus> | net_buffer_length | 16384 |
Jesus> | port | 3306 |
Jesus> | protocol-version | 10 |
Jesus> | record_buffer | 2093056 |
Jesus> | skip_locking | OFF |
Jesus> | socket | /tmp/mysql.sock |
Jesus> | sort_buffer | 8388600 |
Jesus> | table_cache | 256 |
Jesus> | thread_stack | 65536 |
Jesus> | tmp_table_size | 1048576 |
Jesus> | tmpdir | /var/tmp/ |
Jesus> | version | 3.22.21 |
Jesus> | wait_timeout | 28800 |
Jesus> +----------------------------+---------------------------------+
As you may have noticed, you had '177' threads running when you did
your second query. (I assume you did mysqladmin version just after
the second query)
One reason why the above is slow is that the table was locked by some
other thread. If you try again and one another screen do 'mysqladmin
proc' you may find out what's going on!
Some pointers about the above variables:
Opens: 332727 ; This means that your table cache is way too small.
Try increasing this to 512 !
sort_buffer_Size: 838860 ; If your results aren't that big, you should
decrease this to 1M or 256K
Regards,
Monty
