Re: Question related to MySQL and Threads and "Context_voluntary"

On 7/19/07, Mariella Petrini <mariellapetrini@yahoo.com> wrote:
> 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).

Does that mean the host has 8 cores?
What is the disk configuration?

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

Is the server IO or CPU bound during the query? vmstat and iostat will help you determine that.

>
> 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 would guess there is a network bottleneck, but the query returns 1 row so that can't be it. My next guess is that all of the queries are competing for the same disk and the query is IO bound.

MyISAM and InnoDB have very different performance characteristics. 'show innodb status' provides useful counters on the amount of IO done by InnoDB.

>
>
> 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=mcallaghan@google.com
>
>

-- 
Mark Callaghan
mcallaghan@google.com

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

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek