List:General Discussion« Previous MessageNext Message »
From:Ilya Obshadko Date:June 16 1999 5:03pm
Subject:Re: inadequate performance (3.22.20a)
View as plain text  
On Wed, 16 Jun 1999, Dan Nelson wrote:

> In the last episode (Jun 16), Ilya Obshadko said:
> > 
> > I've noticed a strange loss in performance of Mysql (FreeBSD 3.2-STABLE,
> > Dual P-II-333). Some SELECT queries take about 40 seconds, and processor
> > load is about 98%! Maybe this is a known situation, please let me know if
> > anybody had such problems...
> 
> We need to see your query, table definitions, table sizes, and EXPLAIN
> SELECT output before we can even guess at a solution.

Ok, here is an example:

=======================================================

mysql> explain SELECT * from polit_messages, polit_topics WHERE topic=text
AND id LIKE '19990616%' ORDER BY id DESC;
+----------------+------+---------------+-------+---------+-------------------+------+------------+
| table          | type | possible_keys | key   | key_len | ref               | rows |
Extra      |
+----------------+------+---------------+-------+---------+-------------------+------+------------+
| polit_topics   | ALL  | text          | NULL  |    NULL | NULL              | 2432 |    
 |
| polit_messages | ref  | PRIMARY,topic | topic |      64 | polit_topics.text |   11 |
where used |
+----------------+------+---------------+-------+---------+-------------------+------+------------+
2 rows in set (0.00 sec)

mysql> show columns from polit_messages;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| id      | timestamp(14) | YES  | PRI | NULL    |       |
| author  | varchar(64)   | YES  |     | NULL    |       |
| email   | varchar(255)  | YES  |     | NULL    |       |
| url     | varchar(255)  | YES  |     | NULL    |       |
| topic   | varchar(64)   |      | MUL |         |       |
| message | text          | YES  |     | NULL    |       |
| ipaddr  | varchar(15)   | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> show columns from polit_topics;
+---------+---------------+------+-----+---------+----------------+
| Field   | Type          | Null | Key | Default | Extra          |
+---------+---------------+------+-----+---------+----------------+
| text    | char(64)      |      | UNI |         |                |
| updated | timestamp(14) | YES  | UNI | NULL    |                |
| topicID | int(11)       |      | PRI | 0       | auto_increment |
+---------+---------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

========================================================

In addition, seems like problems began after upgrading FreeBSD from 
3.1-STABLE to 3.2-STABLE. Mysql began to dump core very frequently, and it
doesn't depend on any particular query. Problem with coredump disappeared
after I've installed mysql from 3.2-STABLE binary package (IMHO it uses
mit-pthreads and TCP/IP connection instead of UNIX socket), but
performance troubles still persist.

#-- Ilya Obshadko [IDO-RIPN] -------------------------------#
#-- email: ilya@stripped, ilya24@stripped -----------------#
#-- ICQ UIN: 10704338 --------------------------------------#

Thread
inadequate performance (3.22.20a)Ilya Obshadko16 Jun
  • Re: inadequate performance (3.22.20a)Dan Nelson16 Jun
    • Re: inadequate performance (3.22.20a)Ilya Obshadko16 Jun
  • Re: inadequate performance (3.22.20a)Sasha Pachev16 Jun
    • Re: inadequate performance (3.22.20a)Ilya Obshadko16 Jun
  • Re: inadequate performance (3.22.20a)Ozemail)16 Jun