slow "sending data" phase

am 29.09.2003 21:21:26 von Kevin

I've fixed my swapping issues, but the system continues to get stuck in
a 'sending data' phase from time to time.

With mod_perl + mysql, this phase SHOULD be when mysql collects the rows
(after sorting, etc) and sends them to the perl handler for processing.

Any ideas why this phase would ever be taking 100-500 seconds? The
system appears to be stable for long times, then gets in a huge
bottleneck locking on one sending data process. These queries aren't
locked and then just finally being processed - they actually remain in
the sending data phase for most of this time.

The process varies from time to time - I believe it is something to do
with the OS, disk, or ram, but have no idea where to look. The system
is NOT swapping, and has 105 MB Ram free. I am using the same disk for
my tmp drive and data storage, which is because we were having problems
with software raid slowing down the system.

An example query stuck in sending data phase:

Id User Host db Command Time State Info
130 allpoetry localhost allpoetry Query 231
Sending data SELECT
lid,brief,title,content,collection,written,created,cat1,cat2 ,cat3,type,p
oems.contest,critical,poems.mid,name,preferred,lastlogin,del eted,image,s
yndicated FROM poems use index (type) left join poets on poems.mid =
poets.mid WHERE poems.mid =2001 ORDER BY created DESC LIMIT 0,30

explained:
+-------+--------+---------------+---------+---------+------ -----+------
--+-| table | type | possible_keys | key | key_len | ref |
rows | Extra |
+-------+--------+---------------+---------+---------+------ -----+------
--+-| poems | index | NULL | created | 4 | NULL |
272319 | Using where |
| poets | eq_ref | PRIMARY | PRIMARY | 3 | poems.mid |
1 | |
+-------+--------+---------------+---------+---------+------ -----+------
--+-

Thanks for any help anyone can give me - this is driving me nuts!

Ciao,
Kevin Watt
Community Manager, Allpoetry.com
What happened to the cow who went for a drive? He got a Moo_ving
violation
What do you call someone who is crazy about hot chocolate? A cocoa nut
What do bees use to cut wood? Buzz saws
Who eats at underwater resturants ? Suba diners
How do really small people call each other ? On Microphones
How do you fix a broken chimp? With a monkey wrench

> -----Original Message-----
> From: Dan Nelson [mailto:dnelson@allantgroup.com]
> Sent: Saturday, September 27, 2003 6:55 PM
> To: Kevin
> Cc: mysql@lists.mysql.com
> Subject: Re: # processes vs. #threads, and memory usage (Revisited for
> thread cache)
>
> In the last episode (Sep 27), Kevin said:
> > Bringing this back up again, because the number of extra 'threads'
> > mysql is using seems to vary widely, from I've noticed sometimes,
> > from 5 more than the threads I'm using to more than 50!
> >
> > My memory usage also seems to differ accordingly.
> >
> > It seems to be because my thread cache is set to 40, so 'mytop'
shows
> > 38 threads cached, with only 20 connected right now. Shouldn't it
> > kill off those threads after awhile?
> >
> > Is that what the wait_timeout field is for? I thought it was for
> > keeping the connection open, rather than the connection cached.
Mine
> > is currently at '600', and it doesn't seem to be clearing up the
> > cache...
>
> Idle threads should take up almost no RAM (thread_stack plus a little
> bit of overhead), so there should be no need to kill them. If you're
> swapping, add more RAM, or reduce mysql's memory usage by reducing its
> buffer sizes (check the manual for which ones are used when).
>
> wait_timeout is how long before an idle client connection is
> terminated. If the total thread count is greater than thread_cache,
> the thread exits too.
>
> > The reason this is important is because I suspect my 'slow queries'
> > comes up when I run out of ram and it starts using disk swap - which
> > happens because mysql is taking up so much extra memory.
>
> Well, that's easy enough to test; just watch vmstat output during a
> query. If you are swapping, either reduce the global cache settings
> (key_buffer, query_cache_size, or one of the many innodb_*_size
> variables), or the per-query settings (sort_buffer, tmp_table_size,
> join_buffer_size, etc). RAM is cheap too.
>
> --
> Dan Nelson
> dnelson@allantgroup.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/mysql?unsub=kevin@allpoetry.com

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: slow "sending data" phase

am 29.09.2003 23:59:45 von Rudy Lippan

On Mon, 29 Sep 2003, Kevin wrote:

> I've fixed my swapping issues, but the system continues to get stuck in
> a 'sending data' phase from time to time.
>
> With mod_perl + mysql, this phase SHOULD be when mysql collects the rows
> (after sorting, etc) and sends them to the perl handler for processing.
>
> Any ideas why this phase would ever be taking 100-500 seconds? The
> system appears to be stable for long times, then gets in a huge
> bottleneck locking on one sending data process. These queries aren't
> locked and then just finally being processed - they actually remain in
> the sending data phase for most of this time.
>

Which version of Apache are you using, for I have heard whisperings of
problems with socket communications in apache 1.3.28 + mod_perl -- this
might be a good place to look.

Rudy

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: slow "sending data" phase

am 29.09.2003 23:59:45 von Rudy Lippan

Re: slow "sending data" phase

am 30.09.2003 03:59:34 von Matt W

Hi Kevin,

----- Original Message -----
From: "Kevin"
Sent: Monday, September 29, 2003 2:21 PM
Subject: slow 'sending data' phase

> I've fixed my swapping issues, but the system continues to get stuck
in
> a 'sending data' phase from time to time.
>
> With mod_perl + mysql, this phase SHOULD be when mysql collects the
rows
> (after sorting, etc) and sends them to the perl handler for
processing.

Yes, it may be sending rows, but it also needs to read/process them
before sending, which takes time (if filesort is used (not for your
query), rows will be read before and after the sort, I think).

> Any ideas why this phase would ever be taking 100-500 seconds?

Yes, when a lot of data needs to be read. :-) I've had the same problem
on a smaller scale. :-( How many MB is your poems table? Let's check the
EXPLAIN and see if something can be changed...

> [snip]
> explained:
>
+-------+--------+---------------+---------+---------+------ -----+------
> --+-| table | type | possible_keys | key | key_len | ref |
> rows | Extra |
>
+-------+--------+---------------+---------+---------+------ -----+------
> --+-| poems | index | NULL | created | 4 | NULL |
> 272319 | Using where |
> | poets | eq_ref | PRIMARY | PRIMARY | 3 | poems.mid |
> 1 | |
>
+-------+--------+---------------+---------+---------+------ -----+------
> --+-

It's easier to read that output here if you use \G at the end of the
query instead of ;. :-)

OK, all 272,000 poems rows are being scanned (assuming mid isn't part of
the "created" index) and the index is being used for ORDER BY. Actually,
since there's no filesort, it will abort when and if the LIMIT is
satisfied. If mid isn't in the "created" index, MySQL needs to jump to
the data file for each row to check if mid matches the WHERE. If your
data file is too big to be cached in RAM by the OS and LIMIT rows aren't
found early, the disk seeks will REALLY slow it down.

Do all the problem queries have WHERE poems.mid= in them? Why
don't you try adding an index to poems.mid? Even if the WHERE matches a
couple thousand rows and filesort is used, it should be a lot faster
than reading the whole data file. If you're searching for a single mid
value (ref type in EXPLAIN) and you're using MySQL 4+, you can eliminate
filesort by creating a composite index on (mid, created) together.

By the way, remove "use index (type)" from the query as there's nothing
in your example that would allow an index on type to be used anyway.

> Thanks for any help anyone can give me - this is driving me nuts!

Yeah, see if indexing mid helps.

Funny little riddles in your sig BTW. :-D

Matt

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: slow "sending data" phase

am 30.09.2003 03:59:34 von Matt W

RE: slow "sending data" phase

am 30.09.2003 05:57:47 von Kevin

Hi Matt,

Thanks for the excellent play-by-play for my query. I can't believe I
didn't see the problem - you're right exactly, its using the 'created'
index instead of 'mid', and so loading every row into memory, which when
the site is under high load causes wicked slowness.

This appears to be a semi-bug, since its only when the erroneous 'use
index(type)' (a bug in my query-creation routine) appears, it uses the
'created' index. Removing the 'use index(type)' part causes it to
correctly choose the 'mid' index instead.

Hopefully the other queries I'm seeing the slow 'sending data' phase
with are plagued by similar problems. I got too used to only looking to
see whether the query in explain was using filesort/whatever, rather
than contemplating what its doing to the rows its returning. I guess
the complication involved with limiting and joining later confused me to
the problem.

Ciao,
Kevin Watt
Community Manager, Allpoetry.com
What happened to the cow who went for a drive? He got a Moo_ving
violation
What do you call someone who is crazy about hot chocolate? A cocoa nut
What do bees use to cut wood? Buzz saws
Who eats at underwater resturants ? Suba diners
How do really small people call each other ? On Microphones
How do you fix a broken chimp? With a monkey wrench

> -----Original Message-----
> From: Matt W [mailto:mysql_lists@realplain.com]
> Sent: Monday, September 29, 2003 7:00 PM
> To: mysql@lists.mysql.com; perl@lists.mysql.com
> Subject: Re: slow 'sending data' phase
>
> Hi Kevin,
>
>
> ----- Original Message -----
> From: "Kevin"
> Sent: Monday, September 29, 2003 2:21 PM
> Subject: slow 'sending data' phase
>
>
> > I've fixed my swapping issues, but the system continues to get stuck
> in
> > a 'sending data' phase from time to time.
> >
> > With mod_perl + mysql, this phase SHOULD be when mysql collects the
> rows
> > (after sorting, etc) and sends them to the perl handler for
> processing.
>
> Yes, it may be sending rows, but it also needs to read/process them
> before sending, which takes time (if filesort is used (not for your
> query), rows will be read before and after the sort, I think).
>
>
> > Any ideas why this phase would ever be taking 100-500 seconds?
>
> Yes, when a lot of data needs to be read. :-) I've had the same
problem
> on a smaller scale. :-( How many MB is your poems table? Let's check
the
> EXPLAIN and see if something can be changed...
>
>
> > [snip]
> > explained:
> >
>
+-------+--------+---------------+---------+---------+------ -----+------
> > --+-| table | type | possible_keys | key | key_len | ref
|
> > rows | Extra |
> >
>
+-------+--------+---------------+---------+---------+------ -----+------
> > --+-| poems | index | NULL | created | 4 | NULL
|
> > 272319 | Using where |
> > | poets | eq_ref | PRIMARY | PRIMARY | 3 | poems.mid |
> > 1 | |
> >
>
+-------+--------+---------------+---------+---------+------ -----+------
> > --+-
>
> It's easier to read that output here if you use \G at the end of the
> query instead of ;. :-)
>
> OK, all 272,000 poems rows are being scanned (assuming mid isn't part
of
> the "created" index) and the index is being used for ORDER BY.
Actually,
> since there's no filesort, it will abort when and if the LIMIT is
> satisfied. If mid isn't in the "created" index, MySQL needs to jump to
> the data file for each row to check if mid matches the WHERE. If your
> data file is too big to be cached in RAM by the OS and LIMIT rows
aren't
> found early, the disk seeks will REALLY slow it down.
>
> Do all the problem queries have WHERE poems.mid= in them? Why
> don't you try adding an index to poems.mid? Even if the WHERE matches
a
> couple thousand rows and filesort is used, it should be a lot faster
> than reading the whole data file. If you're searching for a single mid
> value (ref type in EXPLAIN) and you're using MySQL 4+, you can
eliminate
> filesort by creating a composite index on (mid, created) together.
>
> By the way, remove "use index (type)" from the query as there's
nothing
> in your example that would allow an index on type to be used anyway.
>
>
> > Thanks for any help anyone can give me - this is driving me nuts!
>
> Yeah, see if indexing mid helps.
>
> Funny little riddles in your sig BTW. :-D
>
>
> Matt
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/mysql?unsub=kevin@allpoetry.com

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

RE: slow "sending data" phase

am 30.09.2003 05:57:47 von Kevin

MySQL 5.0, func stored in proc table

am 30.09.2003 09:26:11 von Bob Brands

Hi,

I compiled MySQL 5.0 from the source tree to test how stored procedures
work.
When I create a function, It is not stored in de mysql.func table, but in
the mysql.proc table.

Example of the function a create:

create function fun(a int) returns int
begin
set a = a + 1;
return a;
end;

Is this a bug or do I do something wrong?

gr. Bob

http://www.beheervisie.nl/disclaimer.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: MySQL 5.0, func stored in proc table

am 30.09.2003 19:23:21 von Jeremy Zawodny

On Tue, Sep 30, 2003 at 09:26:11AM +0200, Bob Brands wrote:
> Hi,
>
> I compiled MySQL 5.0 from the source tree to test how stored procedures
> work.
> When I create a function, It is not stored in de mysql.func table, but in
> the mysql.proc table.
>
> Example of the function a create:
>
> create function fun(a int) returns int
> begin
> set a = a + 1;
> return a;
> end;
>
> Is this a bug or do I do something wrong?

What maks you think it's a bug, exactly?
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
| http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 586,098,164 queries (405/sec. avg)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org