Question related to MySQL and Threads and "Context_voluntary"

From: Mariella Petrini <mariellapetrini(at)yahoo.com>
Date: Thu Jul 19 2007 - 10:03:04 EDT

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

---

Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, photos & more. http://mobile.yahoo.com/go?refer=1GNXIC

-- 
MySQL Server Benchmarks Mailing List
For list archives: 
http://lists.mysql.com/benchmarks
To unsubscribe:    
http://lists.mysql.com/benchmarks?unsub=lists@pantek.com

Received on Thu Jul 19 10:03:26 2007