Re: Problems with MySQL since upgrade

2006-08-03 Thread usleepless

Dear Ricky,

On 8/2/06, Richard Morse [EMAIL PROTECTED] wrote:

No, they were all normal (the latter two were 0, the first was about
110).  FWIW, the query usually proceeds normally; it's only when it
doesn't that things go bad.

I'm wondering if this is a problem with threads -- I've been doing
research, and a number of places say that there are threading issues
on FreeBSD 5 with MySQL thread when using the standard threading
library.  I'm going to recompile MySQL with the linuxpthreads option,
and see if that stops this...


i understand if this is of no use to you, but: switch to PostgreSQL as
soon as you can. MySQL has given me nothing but trouble, especially on
FBSD ( threads ).

PGSQL is more complete, and faster on harder queries.

regards,

usleep
___
freebsd-questions@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to [EMAIL PROTECTED]


Re: Problems with MySQL since upgrade

2006-08-03 Thread Richard Morse

On Aug 3, 2006, at 3:36 PM, [EMAIL PROTECTED] wrote:


Dear Ricky,

i understand if this is of no use to you, but: switch to PostgreSQL as
soon as you can. MySQL has given me nothing but trouble, especially on
FBSD ( threads ).

PGSQL is more complete, and faster on harder queries.


Hi!  Thanks for the suggestion -- I already run a number of  
PostgreSQL databases.  We're using MySQL for one particular  
application which, even though it has partial PostgreSQL  
compatibility, really needs MySQL.


Ricky

___
freebsd-questions@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to [EMAIL PROTECTED]


Re: Problems with MySQL since upgrade

2006-08-03 Thread Richard Morse
Hi!  I finally figured out what was going on.  Thanks for the people  
who gave suggestions.


The query I thought was the problem really wasn't -- at some point  
between 5.0.13 and 5.0.22 a change was introduced which affected  
inner joins.  I had a view which was aggresively created using inner  
joins (in order to take an EAV-like table and view it as though it  
were a regular table), and if I tried to do a three table inner join  
with a view involved, it sat there and entered some kind of loop.   
This either caused the tables to be locked, and later queries  
involving these tables were waiting for a freed lock, or eventually  
the number of open connections / threads climbed too high and all  
later connections were waiting.  I was able to (for now) solve the  
problem by recreating the view as a realized table which gets rebuilt  
every hour.


Thanks,
Ricky
___
freebsd-questions@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to [EMAIL PROTECTED]


Re: Problems with MySQL since upgrade

2006-08-02 Thread Richard Morse


On Aug 1, 2006, at 9:24 AM, Richard Morse wrote:

Hi!  I recently updated MySQL to version 5.0.22 using the ports  
system (portupgrade -r -R mysql-\*).  Previous to this, MySQL was  
behaving perfectly normally.  Since the upgrade, I have found that  
every so often -- sometimes two or three times a day, sometimes  
every other day, but no more often than that -- one of the  
databases MySQL is hosting starts misbehaving, MySQL starts  
climbing in processor usage, and I have to restart MySQL to  
recover.  By misbehaving, I mean that some subset of queries to  
this database start not returning -- they take forever.  By  
climbing in processor usage, I mean that my load averages, which  
normally sit around 0, start going up to 3, 5, even 7.


- How can I determine what query it is that is causing this to  
happen?  I have turned on the log files by adding the following  
line to /etc/rc.conf:


Hi!  Since this time, I have, I think, found what query is causing  
the problem:


SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid = s.uid  
WHERE s.sid = d9fe25949f79f2c767a0d237b4fdf841 AND u.status  3  
LIMIT 0, 1


How can I determine _why_ this is causing MySQL to hang/enter some  
kind of infinite loop?


Thanks,
Ricky
___
freebsd-questions@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to [EMAIL PROTECTED]


Re: Problems with MySQL since upgrade

2006-08-02 Thread Jim Stapleton

Not sure, seems more of a mysql question, but here's some guesses I
have for diagnostics.

could you run these queries?

SELECT COUNT(*) FROM users WHERE status  3
SELECT COUNT(*) FROM sessiions WHERE sid = d9fe25949f79f2c767a0d237b4fdf841
SELECT COUNT(*) FROM FROM users u INNER JOIN sessions s ON u.uid =
s.uid WHERE s.sid = d9fe25949f79f2c767a0d237b4fdf841 AND u.status 
3

-Jim Stapleton



On 8/2/06, Richard Morse [EMAIL PROTECTED] wrote:


On Aug 1, 2006, at 9:24 AM, Richard Morse wrote:

 Hi!  I recently updated MySQL to version 5.0.22 using the ports
 system (portupgrade -r -R mysql-\*).  Previous to this, MySQL was
 behaving perfectly normally.  Since the upgrade, I have found that
 every so often -- sometimes two or three times a day, sometimes
 every other day, but no more often than that -- one of the
 databases MySQL is hosting starts misbehaving, MySQL starts
 climbing in processor usage, and I have to restart MySQL to
 recover.  By misbehaving, I mean that some subset of queries to
 this database start not returning -- they take forever.  By
 climbing in processor usage, I mean that my load averages, which
 normally sit around 0, start going up to 3, 5, even 7.

 - How can I determine what query it is that is causing this to
 happen?  I have turned on the log files by adding the following
 line to /etc/rc.conf:

Hi!  Since this time, I have, I think, found what query is causing
the problem:

SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid = s.uid
WHERE s.sid = d9fe25949f79f2c767a0d237b4fdf841 AND u.status  3
LIMIT 0, 1

How can I determine _why_ this is causing MySQL to hang/enter some
kind of infinite loop?

Thanks,
Ricky
___
freebsd-questions@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to [EMAIL PROTECTED]


___
freebsd-questions@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to [EMAIL PROTECTED]


Re: Problems with MySQL since upgrade

2006-08-02 Thread Richard Morse
Hi!  All of these queries worked just fine, without causing any  
problems.


As this is more of a mysql issue, do you have any suggestions where  
it would be best for me to query them?


Thanks,
Ricky

On Aug 2, 2006, at 9:36 AM, Jim Stapleton wrote:


Not sure, seems more of a mysql question, but here's some guesses I
have for diagnostics.

could you run these queries?

SELECT COUNT(*) FROM users WHERE status  3
SELECT COUNT(*) FROM sessiions WHERE sid =  
d9fe25949f79f2c767a0d237b4fdf841

SELECT COUNT(*) FROM FROM users u INNER JOIN sessions s ON u.uid =
s.uid WHERE s.sid = d9fe25949f79f2c767a0d237b4fdf841 AND u.status 
3

-Jim Stapleton



On 8/2/06, Richard Morse [EMAIL PROTECTED] wrote:


On Aug 1, 2006, at 9:24 AM, Richard Morse wrote:

 Hi!  I recently updated MySQL to version 5.0.22 using the ports
 system (portupgrade -r -R mysql-\*).  Previous to this, MySQL was
 behaving perfectly normally.  Since the upgrade, I have found that
 every so often -- sometimes two or three times a day, sometimes
 every other day, but no more often than that -- one of the
 databases MySQL is hosting starts misbehaving, MySQL starts
 climbing in processor usage, and I have to restart MySQL to
 recover.  By misbehaving, I mean that some subset of queries to
 this database start not returning -- they take forever.  By
 climbing in processor usage, I mean that my load averages, which
 normally sit around 0, start going up to 3, 5, even 7.

 - How can I determine what query it is that is causing this to
 happen?  I have turned on the log files by adding the following
 line to /etc/rc.conf:

Hi!  Since this time, I have, I think, found what query is causing
the problem:

SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid = s.uid
WHERE s.sid = d9fe25949f79f2c767a0d237b4fdf841 AND u.status  3
LIMIT 0, 1

How can I determine _why_ this is causing MySQL to hang/enter some
kind of infinite loop?

Thanks,
Ricky
___
freebsd-questions@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to freebsd-questions- 
[EMAIL PROTECTED]



___
freebsd-questions@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to freebsd-questions- 
[EMAIL PROTECTED]


___
freebsd-questions@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to [EMAIL PROTECTED]


Re: Problems with MySQL since upgrade

2006-08-02 Thread Jim Stapleton

I thought there were mysql forums, but I am not sure.

In the queries I gave you, none of the results were too large I take
it (not above the low thousands)?

-Jim Stapleton

On 8/2/06, Richard Morse [EMAIL PROTECTED] wrote:

Hi!  All of these queries worked just fine, without causing any
problems.

As this is more of a mysql issue, do you have any suggestions where
it would be best for me to query them?

Thanks,
Ricky

On Aug 2, 2006, at 9:36 AM, Jim Stapleton wrote:

 Not sure, seems more of a mysql question, but here's some guesses I
 have for diagnostics.

 could you run these queries?

 SELECT COUNT(*) FROM users WHERE status  3
 SELECT COUNT(*) FROM sessiions WHERE sid =
 d9fe25949f79f2c767a0d237b4fdf841
 SELECT COUNT(*) FROM FROM users u INNER JOIN sessions s ON u.uid =
 s.uid WHERE s.sid = d9fe25949f79f2c767a0d237b4fdf841 AND u.status 
 3

 -Jim Stapleton



 On 8/2/06, Richard Morse [EMAIL PROTECTED] wrote:

 On Aug 1, 2006, at 9:24 AM, Richard Morse wrote:

  Hi!  I recently updated MySQL to version 5.0.22 using the ports
  system (portupgrade -r -R mysql-\*).  Previous to this, MySQL was
  behaving perfectly normally.  Since the upgrade, I have found that
  every so often -- sometimes two or three times a day, sometimes
  every other day, but no more often than that -- one of the
  databases MySQL is hosting starts misbehaving, MySQL starts
  climbing in processor usage, and I have to restart MySQL to
  recover.  By misbehaving, I mean that some subset of queries to
  this database start not returning -- they take forever.  By
  climbing in processor usage, I mean that my load averages, which
  normally sit around 0, start going up to 3, 5, even 7.
 
  - How can I determine what query it is that is causing this to
  happen?  I have turned on the log files by adding the following
  line to /etc/rc.conf:

 Hi!  Since this time, I have, I think, found what query is causing
 the problem:

 SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid = s.uid
 WHERE s.sid = d9fe25949f79f2c767a0d237b4fdf841 AND u.status  3
 LIMIT 0, 1

 How can I determine _why_ this is causing MySQL to hang/enter some
 kind of infinite loop?

 Thanks,
 Ricky
 ___
 freebsd-questions@freebsd.org mailing list
 http://lists.freebsd.org/mailman/listinfo/freebsd-questions
 To unsubscribe, send any mail to freebsd-questions-
 [EMAIL PROTECTED]

 ___
 freebsd-questions@freebsd.org mailing list
 http://lists.freebsd.org/mailman/listinfo/freebsd-questions
 To unsubscribe, send any mail to freebsd-questions-
 [EMAIL PROTECTED]



___
freebsd-questions@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to [EMAIL PROTECTED]


Re: Problems with MySQL since upgrade

2006-08-02 Thread Richard Morse
No, they were all normal (the latter two were 0, the first was about  
110).  FWIW, the query usually proceeds normally; it's only when it  
doesn't that things go bad.


I'm wondering if this is a problem with threads -- I've been doing  
research, and a number of places say that there are threading issues  
on FreeBSD 5 with MySQL thread when using the standard threading  
library.  I'm going to recompile MySQL with the linuxpthreads option,  
and see if that stops this...


Thanks,
Ricky

On Aug 2, 2006, at 10:00 AM, Jim Stapleton wrote:


I thought there were mysql forums, but I am not sure.

In the queries I gave you, none of the results were too large I take
it (not above the low thousands)?

-Jim Stapleton

On 8/2/06, Richard Morse [EMAIL PROTECTED] wrote:

Hi!  All of these queries worked just fine, without causing any
problems.

As this is more of a mysql issue, do you have any suggestions where
it would be best for me to query them?

Thanks,
Ricky

On Aug 2, 2006, at 9:36 AM, Jim Stapleton wrote:

 Not sure, seems more of a mysql question, but here's some guesses I
 have for diagnostics.

 could you run these queries?

 SELECT COUNT(*) FROM users WHERE status  3
 SELECT COUNT(*) FROM sessiions WHERE sid =
 d9fe25949f79f2c767a0d237b4fdf841
 SELECT COUNT(*) FROM FROM users u INNER JOIN sessions s ON u.uid =
 s.uid WHERE s.sid = d9fe25949f79f2c767a0d237b4fdf841 AND  
u.status 

 3

 -Jim Stapleton



 On 8/2/06, Richard Morse [EMAIL PROTECTED] wrote:

 On Aug 1, 2006, at 9:24 AM, Richard Morse wrote:

  Hi!  I recently updated MySQL to version 5.0.22 using the ports
  system (portupgrade -r -R mysql-\*).  Previous to this, MySQL  
was
  behaving perfectly normally.  Since the upgrade, I have found  
that

  every so often -- sometimes two or three times a day, sometimes
  every other day, but no more often than that -- one of the
  databases MySQL is hosting starts misbehaving, MySQL starts
  climbing in processor usage, and I have to restart MySQL to
  recover.  By misbehaving, I mean that some subset of  
queries to

  this database start not returning -- they take forever.  By
  climbing in processor usage, I mean that my load averages, which
  normally sit around 0, start going up to 3, 5, even 7.
 
  - How can I determine what query it is that is causing this to
  happen?  I have turned on the log files by adding the following
  line to /etc/rc.conf:

 Hi!  Since this time, I have, I think, found what query is causing
 the problem:

 SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid =  
s.uid

 WHERE s.sid = d9fe25949f79f2c767a0d237b4fdf841 AND u.status  3
 LIMIT 0, 1

 How can I determine _why_ this is causing MySQL to hang/enter some
 kind of infinite loop?

 Thanks,
 Ricky
 ___
 freebsd-questions@freebsd.org mailing list
 http://lists.freebsd.org/mailman/listinfo/freebsd-questions
 To unsubscribe, send any mail to freebsd-questions-
 [EMAIL PROTECTED]

 ___
 freebsd-questions@freebsd.org mailing list
 http://lists.freebsd.org/mailman/listinfo/freebsd-questions
 To unsubscribe, send any mail to freebsd-questions-
 [EMAIL PROTECTED]



___
freebsd-questions@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to freebsd-questions- 
[EMAIL PROTECTED]


___
freebsd-questions@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to [EMAIL PROTECTED]


Re: Problems with MySQL since upgrade

2006-08-01 Thread Richard Morse

On Aug 1, 2006, at 9:38 AM, William Woodhams wrote:


My first question would be what version of MySQL did you come from?
This sometimes can have an effect on queries.


Hi!  My apologies for leaving this out.  It was 5.0.13.

Ricky
___
freebsd-questions@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to [EMAIL PROTECTED]