List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:April 21 1999 12:06am
Subject:HELP: new db slowness
View as plain text  
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
Thread
HELP: new db slownessJesus M. Arias Jr.21 Apr
  • HELP: new db slownessMichael Widenius21 Apr
  • Re: fetchrow_array() failed: fetch() without execute() ..., with intermittant syntax errorGreg Meckes2 May