After succesfully upgrading to .39b things seemed to be running
great... However the past few days I have been having some problems with
queries not completing. This only seems to occur during higher loads and I
am not sure where to begin debuging. I was hoping someone has some ideas...
Here is the output of mysqladmin processlist... From the time field you can
see those queries have been going for a very long time, and under normal
circumstances they finish in under a second.
| 27 | forums | localhost | forums | Query | 20014 | statistics |
SELECT m.
messageid,groupid,m.userid,subject,postdate,replies,realname,replace(publice
mail
,'\'','\\\' |
| 44 | forums | localhost | forums | Query | 19941 | statistics |
SELECT m.
messageid,groupid,m.userid,subject,postdate,replies,realname,replace(publice
mail
,'\'','\\\' |
| 55 | forums | localhost | forums | Query | 20090 | statistics |
SELECT m.
messageid,groupid,m.userid,subject,postdate,replies,realname,replace(publice
mail
,'\'','\\\' |
| 77 | forums | localhost | forums | Query | 6736 | statistics |
SELECT *
FROM users WHERE username = LOWER('xxx') AND password = PASSWORD('xxx')
|
| 82 | forums | localhost | forums | Query | 19981 | statistics |
SELECT
m.messageid,groupid,m.userid,subject,postdate,replies,realname,replace(publi
cemail,'\'','\\\' |
| 84 | forums | localhost | forums | Query | 20434 | Sending data |
SELECT
m.messageid,m.parentmsgid,groupid,m.userid,subject,postdate,replies,realname
,publicemail FROM |
| 85 | forums | localhost | forums | Query | 20186 | statistics |
SELECT
m.messageid,groupid,m.userid,subject,postdate,replies,realname,replace(publi
cemail,'\'','\\\' |
| 89 | forums | localhost | forums | Query | 19826 | statistics |
SELECT
m.messageid,groupid,m.userid,subject,postdate,replies,realname,replace(publi
cemail,'\'','\\\' |
| 90 | forums | localhost | forums | Query | 19773 | statistics |
SELECT
m.messageid,groupid,m.userid,subject,postdate,replies,realname,replace(publi
cemail,'\'','\\\' |
| 91 | forums | localhost | forums | Query | 19622 | Sending data |
SELECT
m.messageid,m.parentmsgid,groupid,m.userid,subject,postdate,replies,realname
,publicemail FROM |
| 92 | forums | localhost | forums | Sleep | 11129 | |
|
| 95 | forums | localhost | forums | Sleep | 17126 | |
|
| 99 | forums | localhost | forums | Query | 6498 | statistics |
SELECT * FROM users WHERE username = LOWER('xxx') AND password =
PASSWORD('xxx') |
| 116 | forums | localhost | forums | Query | 108 | statistics |
SELECT * FROM users WHERE username = LOWER('xxx') AND password =
PASSWORD('xxx') |
| 118 | forums | localhost | forums | Sleep | 3350 | |
|
| 122 | forums | localhost | forums | Query | 1625 | statistics |
SELECT
m.messageid,groupid,m.userid,subject,postdate,replies,realname,replace(publi
cemail,'\'','\\\' |
| 126 | root | localhost | | Query | 0 | | show
processlist
Here is the query that is being 'locked':
SELECT
m.messageid,groupid,m.userid,subject,postdate,replies,realname,replace(publi
cemail,'\\'','\\\\\\'') AS publicemail FROM messages AS m LEFT JOIN users AS
u ON (m.userid = u.userid) WHERE m.groupid = $groupid AND m.parentmsgid = 0
AND m.clean = 1 ORDER BY postdate DESC limit 50;
and also:
SELECT * FROM users WHERE username = LOWER('xxx') AND password =
PASSWORD('xxx')
In both cases the connection is using auto commit = 1... so I dont think it
can be a deadlock problem. I am fairly certain that inserts have not been
done to either table in a while (a while being a day or so). Below are the
create table definitions just in case you need to take a look at them. Do
you have any idea what could be causing this? Under the non-large blob
support innobase code this worked without any problems. The only thing that
has changed since then is the upgrade to .39b code and a little bit more
data inserted into the table.
Thanks,
ryan
create table messages` (
`messageid` bigint(20) NOT NULL auto_increment,
`msgid` varchar(255) NOT NULL default '',
`groupid` int(10) unsigned NOT NULL default '0',
`userid` int(10) unsigned NOT NULL default '0',
`parentmsgid` int(10) unsigned NOT NULL default '0',
`subject` text NOT NULL,
`body` text NOT NULL,
`closedflag` char(1) NOT NULL default '',
`postdate` int(10) unsigned NOT NULL default '0',
`adddate` int(10) unsigned NOT NULL default '0',
`replies` int(10) unsigned NOT NULL default '0',
`ipaddr` int(10) unsigned NOT NULL default '0',
`clean` char(1) NOT NULL default '0',
`msent` char(1) NOT NULL default '',
PRIMARY KEY (`messageid`),
UNIQUE KEY `usenetmsgid` (`msgid`),
KEY `parentmsgid` (`parentmsgid`,`clean`),
KEY `MSGSELECT` (`groupid`,`clean`,`parentmsgid`,`postdate`),
KEY `adddatestats` (`adddate`)
) TYPE=InnoDB MAX_ROWS=100000000;
CREATE TABLE `users` (
`userid` bigint(20) unsigned NOT NULL auto_increment,
`username` varchar(30) NOT NULL default '',
`password` varchar(30) NOT NULL default '',
`realname` varchar(30) NOT NULL default '',
`publicemail` varchar(50) NOT NULL default '',
`address1` varchar(100) NOT NULL default '',
`address2` varchar(100) NOT NULL default '',
`city` varchar(15) NOT NULL default '',
`state` char(2) NOT NULL default '',
`zipcode` varchar(10) default NULL,
`email` varchar(100) NOT NULL default '',
`sig` text NOT NULL,
`numberofposts` int(10) unsigned NOT NULL default '0',
`lastvisit` int(10) unsigned NOT NULL default '0',
`numberofvisits` int(10) unsigned NOT NULL default '0',
`membersince` int(10) unsigned NOT NULL default '0',
`permisions` int(10) unsigned NOT NULL default '0',
`monitors` int(11) NOT NULL default '0',
`signupsource` varchar(10) NOT NULL default '',
`dont_email` smallint(6) NOT NULL default '0',
`sent_userinfo` smallint(6) NOT NULL default '0',
`invalidemail` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`userid`),
KEY `username` (`username`),
KEY `email_2` (`email`,`publicemail`),
KEY `publicemail` (`publicemail`),
KEY `statquery1` (`lastvisit`,`numberofvisits`)
) TYPE=InnoDB MAX_ROWS=100000000;
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
