List:General Discussion« Previous MessageNext Message »
From:Jesus M. Arias Jr. Date:April 20 1999 8:05pm
Subject:HELP: new db slowness
View as plain text  
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                           |
+----------------------------+---------------------------------+


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