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 --------------------------------------#
