From: Michael Widenius Date: April 21 1999 12:06am Subject: HELP: new db slowness List-Archive: http://lists.mysql.com/mysql/2144 Message-Id: <14109.5420.976930.531117@monty.pp.sci.fi> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi! >>>>> "Jesus" == Jesus M Arias 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: 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) : 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