Re: MYSQL eats 100% CPU
Claudio,
I would be very curious if you could post the output of a SET EXPLAIN from the queries that you have.
There are a couple of things that will help the situation (withouth looking at your explain plan)
1) Make sure that your column types are correct. For example, if state only has a few values, then why not make it a tinyint instead of an int? Same goes for type. This will make the index more compact, in that more index entries can be read per disk i/o
2) Your indexing strategy may need to be looked at. Perhaps the first index on (type, state, id) is not being used correctly? What is te teh result of SELECT COUNT(*) FROM payloads where type = 201 AND tag = 'X_SHOW_DATE' for example? If this count is really low, then perhaps an index based on these 2 columns would be best to limit the amount of data filtered. Again, the explain plan will be more telling.
- Partha
Claudio Destro wrote:
> Hello folks,
> I am experiencing a problem with Mysql 5.0.15
> under Mac OS X 10.3.9, when I re-execute a quite
> complex (?) query on the same JDBC connection.
>
> I use the following schema to hold vcards of
> teachers, schools, etc. in a way that should be
> extensible (in fact, I don't know the real number
> of names or addresses or phone numbers ... an
> entry can have, so it is scattered in many lines,
> however my table is not very large, it holds ~
> 96000 rows)
>
> -- just one arena.
> -- "state": -1 == dead, 0 == alive
> -- "type": 0 == users
> -- 100 == teachers
> -- 101 == schools
> -- 200 == show names
> -- 201 == show dates
> -- 202 == reservations
>
> CREATE TABLE `payloads` (
> `id` int NOT NULL,
> `type` int NOT NULL,
> `tag` char(32) NOT NULL,
> `value` char(255) NOT NULL,
> `created` timestamp NOT NULL,
> `creator` int,
> `modified` timestamp,
> `modifier` int,
> `state` int DEFAULT 0,
> INDEX (`type`, `id`, `state`),
> INDEX (`tag`(10)),
> INDEX (`value`(10))
> ) TYPE=MyISAM;
>
> -- Select slots by date and show id
> CREATE PROCEDURE show_tags_by_date_and_show (IN
> _start_ DATE, IN _end_ DATE, IN _show_ INT)
> BEGIN
> DROP TEMPORARY TABLE IF EXISTS R0;
> CREATE TEMPORARY TABLE R0 (`id` INT, KEY(`id`))
> AS
> SELECT DISTINCT B.id AS id FROM payloads A,
> payloads B
> WHERE A.type=201 AND A.state>=0 AND
> A.tag='X_SHOW_DATE'
> AND CONVERT(A.value, DATE) BETWEEN _start_ AND
> _end_
> AND B.id=A.id AND B.type=201 AND
> B.state>=0
> AND (_show_<0 OR
> (_show_>=0 AND B.tag='X_SHOW_ID' AND
> B.value=_show_));
> DROP TEMPORARY TABLE IF EXISTS R1;
> CREATE TEMPORARY TABLE R1 (`id` INT, KEY(`id`))
> AS
> SELECT DISTINCT id FROM payloads
> WHERE type=202 AND state>=0
> AND (tag='X_SHOW_TAG:0' OR
> tag='X_SHOW_TAG:1')
> AND value IN (SELECT id FROM R0);
> END;
> //
>
> CREATE PROCEDURE compute_daily_counters()
> BEGIN
> DROP TEMPORARY TABLE IF EXISTS R2;
> CREATE TEMPORARY TABLE R2 AS
> SELECT T.id,'DATE' AS tag,S.value FROM
> payloads S, R0 T
> WHERE S.type=201 AND S.state>=0 AND
> S.id=T.id AND S.tag='X_SHOW_DATE';
> DROP TEMPORARY TABLE IF EXISTS reservations;
> CREATE TEMPORARY TABLE reservations (id INT, tag
> CHAR(10), value DOUBLE) AS
> SELECT 0 AS id,(SELECT value FROM R2 WHERE
> id=B.id) AS tag, SUM(C.value) AS value
> FROM payloads A, R0 B, payloads C
> WHERE A.type=202 AND A.state>=0 AND
> (A.tag='X_SHOW_TAG:0' OR A.tag='X_SHOW_TAG:1')
> AND A.value=B.id AND C.id=A.id AND
> C.tag='X_COST:0' AND C.type=202 AND C.state>=0
> GROUP BY tag;
> END;
> //
>
> the problem is when executing this code
>
> call show_tags_by_date_and_show('2005-01-01',
> '2006-12-31', -1);
> call compute_daily_counters();
> select * from reservations order by id,tag;
>
> it just hogs my cpu while the very first time it
> executes at full speed on the same connection.
>
> Is this a bug or am I doing something wrong?
>
> Can anyone point me out? thanks!