Complicated search locks up MySQL on OpenBSD

[Aaron Baugher]

I have an OpenBSD 3.3 system running MySQL 3.23.55 from the ports
tree. Everything started out at the default settings. This system
holds a large vBulletin 2.2.9 forum database, and that's pretty much
the only application that uses any resources.

Everything works fine, except that when the forum administrators do an
IP search, the database locks up and won't respond to any more queries
until it gives up on that search or is restarted. The actual query
that causes the problem is:

SELECT DISTINCT user.userid,use r.username
FROM post,user
WHERE user.userid=pos t.userid
AND post.ipaddress LIKE '192.168.1.2%'
AND user.userid<>0
ORDER BY user.username

For example. The 'user' table has 14800 records, and the 'post' table
has 403000, so I realize this search could take a while, but I don't
see why it's preventing any other queries from getting through.

When it gives up, it eventually complains about a missing file
descriptor, which made me think it was running out of file
descriptors. So I increased the mysql user's open files limit to
1024, and kern.maxfiles to 8192. That doesn't seem to make any
difference, though, and if I do 'mysqladmin extended-status' while the
server is hanging on that query, it only reports around 60-70 open
tables and 120 or so open files.

Any suggestions of other mysql variables I should tweak, or known
issues with MySQL on OpenBSD that could cause this? This same
database was recently working fine on Linux, and a few months ago was
running on a FreeBSD system with the same hardware, so there must be
something about my setup or the way it was compiled from ports that's
causing the problem.
Thanks,
--
Aaron
ab******@esc.pi ke.il.us

[Ivan Voras]

Aaron Baugher wrote:

When it gives up, it eventually complains about a missing file
descriptor, which made me think it was running out of file
descriptors. So I increased the mysql user's open files limit to

I really don't know about OpenBSD, but at FreeBSD there was a bug with
realpath() function which caused similar problems. See, for example, here:
http://jeremy.zawodny.com/blog/archives/000697.html

--
--
Every sufficiently advanced magic is indistinguishab le from technology
- Arthur C Anticlarke

[Ivan Voras]

Aaron Baugher wrote:

When it gives up, it eventually complains about a missing file
descriptor, which made me think it was running out of file
descriptors. So I increased the mysql user's open files limit to

I really don't know about OpenBSD, but at FreeBSD there was a bug with
realpath() function which caused similar problems. See, for example, here:
http://jeremy.zawodny.com/blog/archives/000697.html

--
--
Every sufficiently advanced magic is indistinguishab le from technology
- Arthur C Anticlarke

[Five Cats]

In message <86************ @cail.baugher.p ike.il.us>, Aaron Baugher
<ab******@esc.p ike.il.us> writes

I have an OpenBSD 3.3 system running MySQL 3.23.55 from the ports
tree. Everything started out at the default settings. This system
holds a large vBulletin 2.2.9 forum database, and that's pretty much
the only application that uses any resources.

Everything works fine, except that when the forum administrators do an
IP search, the database locks up and won't respond to any more queries
until it gives up on that search or is restarted. The actual query
that causes the problem is:

SELECT DISTINCT user.userid,use r.username
FROM post,user
WHERE user.userid=pos t.userid
AND post.ipaddress LIKE '192.168.1.2%'
AND user.userid<>0
ORDER BY user.username

For example. The 'user' table has 14800 records, and the 'post' table
has 403000, so I realize this search could take a while, but I don't
see why it's preventing any other queries from getting through.

When it gives up, it eventually complains about a missing file
descriptor, which made me think it was running out of file
descriptors. So I increased the mysql user's open files limit to
1024, and kern.maxfiles to 8192. That doesn't seem to make any
difference, though, and if I do 'mysqladmin extended-status' while the
server is hanging on that query, it only reports around 60-70 open
tables and 120 or so open files.

Any suggestions of other mysql variables I should tweak, or known
issues with MySQL on OpenBSD that could cause this? This same
database was recently working fine on Linux, and a few months ago was
running on a FreeBSD system with the same hardware, so there must be
something about my setup or the way it was compiled from ports that's
causing the problem.

What indexes do you have on 'post' and 'user'?

--
Five Cats
Email to: cats_spam at uk2 dot net

[Five Cats]

In message <86************ @cail.baugher.p ike.il.us>, Aaron Baugher
<ab******@esc.p ike.il.us> writes

I have an OpenBSD 3.3 system running MySQL 3.23.55 from the ports
tree. Everything started out at the default settings. This system
holds a large vBulletin 2.2.9 forum database, and that's pretty much
the only application that uses any resources.

Everything works fine, except that when the forum administrators do an
IP search, the database locks up and won't respond to any more queries
until it gives up on that search or is restarted. The actual query
that causes the problem is:

SELECT DISTINCT user.userid,use r.username
FROM post,user
WHERE user.userid=pos t.userid
AND post.ipaddress LIKE '192.168.1.2%'
AND user.userid<>0
ORDER BY user.username

For example. The 'user' table has 14800 records, and the 'post' table
has 403000, so I realize this search could take a while, but I don't
see why it's preventing any other queries from getting through.

When it gives up, it eventually complains about a missing file
descriptor, which made me think it was running out of file
descriptors. So I increased the mysql user's open files limit to
1024, and kern.maxfiles to 8192. That doesn't seem to make any
difference, though, and if I do 'mysqladmin extended-status' while the
server is hanging on that query, it only reports around 60-70 open
tables and 120 or so open files.

Any suggestions of other mysql variables I should tweak, or known
issues with MySQL on OpenBSD that could cause this? This same
database was recently working fine on Linux, and a few months ago was
running on a FreeBSD system with the same hardware, so there must be
something about my setup or the way it was compiled from ports that's
causing the problem.

What indexes do you have on 'post' and 'user'?

--
Five Cats
Email to: cats_spam at uk2 dot net