Complicated search locks up MySQL on OpenBSD

From: Aaron Baugher (abaugher_at_esc.pike.il.us)
Date: 09/25/03 

Date: Thu, 25 Sep 2003 14:42:26 -0500

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
abaugher@esc.pike.il.us

Relevant Pages

  • Re: mysql - kern.maxfiles limit exceeded by uid 500 please see tuning(7)
    ... MySQL 4.1.x I can't speak to 5.x, even though I do want to look into it ... One thing you may wish to consider is upgrading the OS. ... At least upgrade your ports tree. ... Since the standard install will put these ...
    (comp.unix.bsd.freebsd.misc)
  • Re: Upgrade from 32-bit to AMD-64?
    ... MySQL is OK but Berkley and PostgreSQL need ... and you have to immediately recompile ALL of your installed ports (and ... temporary amd64 root filesystem, installworld/kernel into that, boot into ...
    (freebsd-stable)
  • Re: Upgrade from 32-bit to AMD-64?
    ... MySQL is OK but Berkley and PostgreSQL need dump/restore. ... and you have to immediately recompile ALL of your installed ports (and ... temporary amd64 root filesystem, installworld/kernel into that, boot into ...
    (freebsd-stable)
  • RE: Courier-IMAP - Postfix - MySQL 4
    ... > authentication working with mySQL 4. ... A LOT of ports do this. ... as I install so that it won't try to install MySQL 3.x as a dependancy. ... > protocol unsupported by the server". ...
    (freebsd-isp)
  • Re: CLARITY re: challenge: end of life for 6.2 is premature withbuggy 6.3
    ... long term stability is the platform through which such agreement can be ... Backporting security and bug fixes to *STABLE* versions of ports would ... MySQL 4.1.11 was the "stable" MySQL 4.1 in Debian Sarge. ...
    (freebsd-stable)