Hi,
I have been working with MySQL 5.1.20 (64 bits
compiled on Mac OS for MacPro Intel Xeon Quad-Core
qith 2 processors and 8GB of RAM) and MySQL 5.0.41 (32
bits).
While running some of the select statements
(independently of the storage engine used, either
InnoDB or MyISAM) I have noticed that
the response time of a query degrades badly (double
degrades) as soon as another query is run.
E.g. This is a case for a very simple query that faces
the problem.
SELECT SQL_NO_CACHE count(DISTINCT user_id) as
res_count FROM t1 WHERE (acc > 0 AND cat=34 AND thr
between 42 and 52) AND genre='SF';
Using explain
+----+-------------+---------------------------+------+-------------------+---------------+---------+-------+-------+-------------+
| id | select_type | table | type
| possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+---------------------------+------+-------------------+---------------+---------+-------+-------+-------------+
| 1 | SIMPLE | t1 | ref | uag,genre_idx |
genre_idx | 6 | const | 61324 | Using where |
+----+-------------+---------------------------+------+-------------------+---------------+---------+-------+-------+-------------+
show index from t1;
+---------------------------+------------+---------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part |
Packed | Null | Index_type | Comment |
+---------------------------+------------+---------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| t1 | 1 | user_id_idx | 1 |
user_id | A | 178219 | NULL |
NULL | | BTREE | |
| t1 | 1 | uag | 1 |
acc | A | 8 | NULL |
NULL | | BTREE | |
| t1 | 1 | uag | 2 |
cat | A | 8 | NULL |
NULL | | BTREE | |
| t1 | 1 | uag | 3 |
thr | A | 538 | NULL |
NULL | | BTREE | |
| t1 | 1 | genre_idx | 1 |
genre | A | 6 | NULL |
NULL | | BTREE | |
+---------------------------+------------+---------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
If there is only one query running the response time
is x, if there two queries running at the same time
the response time of each select is approximately 2x,
if there 3 queries running the response time is
approximately 4x and if there are 4 queries running
concurrently the response time of each query is
approximately 8x
I have been using "show profiling" in 5.0.x and I have
noticed that the amunt of increased time is due to the
step called "Sending data".
Doing a little more digging, the variables that
approximately double increase (during the "Sending
Data" step) are "Context_voluntary",
"Messages__received" and "Messages_sent" .
Question:
Which could be the MySQL variables that could be
changed and that could provide a major increase ?
Could you please help ?
Thanks in advance for your help,
Mariella
____________________________________________________________________________________
Be a better Heartthrob. Get better relationship answers from someone who knows.
Yahoo! Answers - Check it out.
http://answers.yahoo.com/dir/?link=list&sid=396545433
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
