FreeBSD 8.3 + MySQL 5.0.95

[Michael Powell]

Simon wrote:

Hint: Please learn to not top post. It makes it more difficult to arrange 
answers coherently.

> Possible but extremely unlikely, I always had issues whenever I tried to
> build MySQL server myself. The hardware where this is running has been
> very stable. I don't have any issues whatsoever making world, etc...
> 
> There is no segfault which is what usually happens when you have memory
> issues. And why would MySQL community server run stable if it was somehow
> my hardware? Bottom line, if this was hardware issue, the server would
> have paniced long ago.
> 
> I wish I could get some input from someone running MySQL server with 300+
> queries a second and what MySQL version/build they are running.
> 
> -Simon
> 
> On Wed, 13 Jun 2012 11:36:48 -0700, Chuck Swiger wrote:
> 
>>On Jun 13, 2012, at 11:34 AM, Simon wrote:
>>> I upgrade to FreeBSD 8.3-p3 and installed MySQL 5.0.95 from ports.
>>> 
>>> It runs fine until it dies silently. Does anyone run a heavy loaded
>>> MySQL under such setup? how can I troubleshoot this?
>>> 
>>> I could never compile a stable MySQL server from the ports and always
>>> relied on MySQL community server binaries but there is no binary for
>>> latest 5.0.xx
> 
>>This sounds like marginal hardware which is failing under load.  Make
>>sure you can run something like memtest86 or prime95 overnight without
>>errors....
> 

I don't know about 300+ queries per second, but I have been running MySQL 
since version 3.x.x, and so on, without much difficulty. It has been very 
stable for me for many years.

Hardware related problems can be a cause of general flakiness one person can 
see while many, many others do not experience. Can be things such as old, 
weak, under rated power supply that has poor regulation and excessive ripple 
under load. This can actually resemble RAM problems at times, because with 
things like memtest there will be failures. It can be other things as well, 
such as a disk controller running a driver that has a bug. Rather than 
ramble through myriad possibilities, a general rule I've noticed over many 
years of dealing with computers: Hardware is often involved when the problem 
is very random, while when you can reproduce a specific error condition 
repeatedly by executing a set of commands or instructions in particular and 
specific order it is software related.

I also question why you would want to run such an old version. Particularly 
I am aware that versions 5.0.50 and 5.0.51 contain several serious bugs. I 
run the latest version of the 5.1.xx branch, with an eye to moving towards 
5.5.xx very soon. 

I have always compiled from the ports system. I have also tuned my.cnf 
according to the examples and the documentation recommendations. One of the 
first things you should look at is what about the compilation process on your 
machine is producing your flaky, crashy binaries. Using a GCC from ports?, 
CLANG?, remove any so-called 'optimizations' from your make.conf, etc. In 
the make config for building MySQL do _not_ select the 'build optimized 
binaries' choice (which sets -O3 optimization) and see if that makes a 
difference. I have used the -O3 in my builds for many years and never had a 
problem.

Circle outwards in looking at OS tuning. An example would be vmstat -i, 
looking for a piece of hardware with a run away interrupt storm. Other 
things like IPC, SYSVSHM, Semaphores, and other such structure pools looking 
for resource starvation.

If hardware proves not to be central to the problem, see if you can arrange 
a way to _not_ load it so heavily. If it runs at a lower load without 
crashing it might indicate you need some tuning. I would look at the 
hardware very hard. I would look at how you are building the compilation. I 
would also _not_ use this version, but rather at least 5.1.x and preferably 
(especially if this is a new start up) look at trying the latest in the 
5.5.xx series. The 5.5.xx is supposed to offer better performance, and maybe 
with your 300+ per second query rate maybe you should focus on the version 
with the best performance.

Bottom line: Many thousands of people and companies have run MySQL for many 
years and had it work just fine. Your particular situation is an aberration 
of some form.

-Mike