469,909 Members | 1,573 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,909 developers. It's quick & easy.

Complicated search locks up MySQL on OpenBSD

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,user.username
FROM post,user
WHERE user.userid=post.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.pike.il.us
Jul 19 '05 #1
4 2297
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 indistinguishable from technology
- Arthur C Anticlarke
Jul 19 '05 #2
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 indistinguishable from technology
- Arthur C Anticlarke
Jul 19 '05 #3
In message <86************@cail.baugher.pike.il.us>, Aaron Baugher
<ab******@esc.pike.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,user.username
FROM post,user
WHERE user.userid=post.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
Jul 19 '05 #4
In message <86************@cail.baugher.pike.il.us>, Aaron Baugher
<ab******@esc.pike.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,user.username
FROM post,user
WHERE user.userid=post.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
Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Noah | last post: by
reply views Thread by Bennett Haselton | last post: by
1 post views Thread by awebguynow | last post: by
4 posts views Thread by | last post: by
1 post views Thread by nazgul | last post: by
5 posts views Thread by nazgul | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.