Hi,
Isn't the my-huge.cnf in the MySQL 5.0.22 distribution quite outdated?
It says "for systems with 512Mb RAM or more". Nowdays this is pretty
basic setup, and 'huge' is probably something in excess of 4Gb RAM.
I wonder if anyone has a recommendation for truly huge systems. For
example a dual CPU AMD opteron system with 4Gb (or 8Gb) RAM that is
fully devoted to serving the mysql daemon.
The config I have (see below) has been tuned to be optimal for creating
indexes on a large (100Gb+) single database table. It works fine
(although not satisfactory), but I worry that some parameters may have
an optimal value or range, and it does not make sense to increase them
like crazy.
Any opinions of the following : ?
[mysqld]
key_buffer_size=1024M
myisam_sort_buffer_size=256M
sort_buffer_size=256M
bulk_insert_buffer_size=64M
join_buffer_size=64M
max_connections=5
read_buffer_size=8M
read_rnd_buffer_size=8M
net_buffer_length=1M
max_allowed_packet=16M
# Cheers,
# Gaspar

This seems to be the way things are with mysql nowdays.

Is it not time for the developers to take a serious look
into culling all the outdated and multiple ways of
accomplishing the same thing from mysql and the
documentation?

All the excess documentation for different ways of
accomplishing the same outcome makes the learning curve
for mysql alot harder.

It doesn't make things any easier for people that want to
take the MySQL certification exams either.

Why should we have to remember many different ways to
accomplish the same thing, when one or two ways at the most
would be quite sufficient?

For example, do we really need so many ways to start a
server?

Do we really need different ways to add or drop indexes and
modify tables?

With the newer versions of mysql (5.0.21+ or maybe version
6.0.x), can we not dump the old syntax that is in mysql for
backward compatibility reasons?

I would really like to see a slim and trim version of mysql,
the SQL commands, and the supporting cli programs,
that is up to date and has a very fast learning curve.

Just my little gripe.

Kind Regards

Keith Roberts

In theory, theory and practice are the same;
in practice they are not.

Sun, 11 Jun 2006, Gaspar Bakos wrote:

To: mysqllist <mysql (AT) lists (DOT) mysql.com>
From: Gaspar Bakos <gbakos (AT) cfa (DOT) harvard.edu>
Subject: my-huge.cnf quite outdated

Hi,

Isn't the my-huge.cnf in the MySQL 5.0.22 distribution quite outdated?
It says "for systems with 512Mb RAM or more". Nowdays this is pretty
basic setup, and 'huge' is probably something in excess of 4Gb RAM.

I wonder if anyone has a recommendation for truly huge systems. For
example a dual CPU AMD opteron system with 4Gb (or 8Gb) RAM that is
fully devoted to serving the mysql daemon.

The config I have (see below) has been tuned to be optimal for creating
indexes on a large (100Gb+) single database table. It works fine
(although not satisfactory), but I worry that some parameters may have
an optimal value or range, and it does not make sense to increase them
like crazy.

Any opinions of the following : ?

[mysqld]
key_buffer_size=1024M
myisam_sort_buffer_size=256M
sort_buffer_size=256M
bulk_insert_buffer_size=64M
join_buffer_size=64M
max_connections=5
read_buffer_size=8M
read_rnd_buffer_size=8M
net_buffer_length=1M
max_allowed_packet=16M

# Cheers,
# Gaspar

Hi, Keith,

RE:
This seems to be the way things are with mysql nowdays.
Is it not time for the developers to take a serious look
into culling all the outdated and multiple ways of
accomplishing the same thing from mysql and the
documentation?

This is a somewhat different subject.
But you are right about it.

the other hand, I have been using MySQL since 2001, and I enjoy
looking at the old syntax, and seeing how it changed helps me
understanding what the new syntax means.

Back to my-huge.cnf, I am sure there are many people reading the list
who run MySQL on big-big servers, and they must have figured out how to
optimize it. I am curious about their advice.

Any opinions of the following : ?

[mysqld]
key_buffer_size=1024M
myisam_sort_buffer_size=256M
sort_buffer_size=256M
bulk_insert_buffer_size=64M
join_buffer_size=64M
max_connections=5
read_buffer_size=8M
read_rnd_buffer_size=8M
net_buffer_length=1M
max_allowed_packet=16M

Cheers,
Gaspar

I was getting a little T there Gaspar.

It might be better for me to file a feature request on mysl
bug reporting page for any un-necessary functionality and
the associated documentation to be removed from mysql
in version 6.0.x, rather than hijacking this thread!

Regards

Keith

Sun, 11 Jun 2006, Gaspar Bakos wrote:

To: Keith Roberts <mysql (AT) karsites (DOT) net>
From: Gaspar Bakos <gbakos (AT) cfa (DOT) harvard.edu>
Subject: Re: my-huge.cnf quite outdated

Hi, Keith,

RE:
This seems to be the way things are with mysql nowdays.
Is it not time for the developers to take a serious look
into culling all the outdated and multiple ways of
accomplishing the same thing from mysql and the
documentation?

This is a somewhat different subject.
But you are right about it.

the other hand, I have been using MySQL since 2001, and I enjoy
looking at the old syntax, and seeing how it changed helps me
understanding what the new syntax means.

Back to my-huge.cnf, I am sure there are many people reading the list
who run MySQL on big-big servers, and they must have figured out how to
optimize it. I am curious about their advice.

Any opinions of the following : ?

[mysqld]
key_buffer_size=1024M
myisam_sort_buffer_size=256M
sort_buffer_size=256M
bulk_insert_buffer_size=64M
join_buffer_size=64M
max_connections=5
read_buffer_size=8M
read_rnd_buffer_size=8M
net_buffer_length=1M
max_allowed_packet=16M

Cheers,
Gaspar

Gaspar Bakos schrieb:
Hi,

Isn't the my-huge.cnf in the MySQL 5.0.22 distribution quite outdated?
It says "for systems with 512Mb RAM or more". Nowdays this is pretty
basic setup, and 'huge' is probably something in excess of 4Gb RAM.

I wonder if anyone has a recommendation for truly huge systems. For
example a dual CPU AMD opteron system with 4Gb (or 8Gb) RAM that is
fully devoted to serving the mysql daemon.

The config I have (see below) has been tuned to be optimal for creating
indexes on a large (100Gb+) single database table. It works fine
(although not satisfactory), but I worry that some parameters may have
an optimal value or range, and it does not make sense to increase them
like crazy.

Any opinions of the following : ?

[mysqld]
key_buffer_size=1024M
myisam_sort_buffer_size=256M
sort_buffer_size=256M
bulk_insert_buffer_size=64M
join_buffer_size=64M
max_connections=5
read_buffer_size=8M
read_rnd_buffer_size=8M
net_buffer_length=1M
max_allowed_packet=16M

# Cheers,
# Gaspar

Seriously.
You should get a hang on the mysql.cnf vars and values.

Guess we would answer to everyone on the list who wishes to optimize his
cnf.
", i have add super X RAMs with latencies of blah blah. Please i think
my cnf is outdated can somone help me?"
:
", i have added a HD with 2times more rounds per/m can you update my
cnf PLZ?"

And "yes". You can tweak the **** out of the mysql.cnf files.
You have to test yourself on "your" system.

My only opinion would be to test it on a server which has the same
measures, so that the live project don't start acting weird.

And btw. the cnf files wrk with even bigger tables than you have.
Not "optimal" but "okay".

Every special server needs special handling. there is no "the one and
only you have to do it this way" way

Barry

John May wrote:
I've got an xserve running 10.3.9 and MySQL 4.0.27-max that is
restarting itself every 2-3 days. It appears that it is due to a kernel
panic, though I don't have direct access to the machine (colocated) to
verify when it occurs.

There are no crash.logs from MySQL, and watchdog and system.logs show
nothing. I have culled the following panic.log - can anyone tell if
MySQL is the cause of such?

Has anyone seen MySQL 4 crash on an Xserve (G4 specifically)? I have
tried two totally different servers, and the problems continue.
- John

Mon Jun 12 06:53:02 2006

Unresolved kernel trap(cpu 0): 0x300 - Data access
DAR=0x00000000FF864A5C PC=0x0000000000056218
Latest crash info for cpu 0:
Exception state (sv=0x3E6ABC80)
PC=0x00056218; MSR=0x00009030; DAR=0xFF864A5C; DSISR=0x40000000;
LR=0x00053AB4; R1=0x1C17BC70; XCP=0x0000000C (0x300 - Data access)
Backtrace:
0x00000000 0x00055DC8 0x0002F750 0x00033710 0x00032028 0x00031FE4
Proceeding back via exception chain:
Exception state (sv=0x3E6ABC80)
previously dumped as "Latest" state. skipping
Exception state (sv=0x0091F280)
PC=0x00000000; MSR=0x0000D030; DAR=0x00000000; DSISR=0x00000000;
LR=0x00000000; R1=0x00000000; XCP=0x00000000 (Unknown)

Kernel version:
Darwin Kernel Version 7.9.0:
Wed Mar 30 20:11:17 PST 2005; root:xnu/xnu-517.12.7.obj~1/RELEASE_PPC

panic(cpu 0): copyin/out has no recovery point
Latest stack backtrace for cpu 0:
Backtrace:
0x00083498 0x0008397C 0x0001EDA4 0x00090C38 0x0009402C
Proceeding back via exception chain:
Exception state (sv=0x3E6ABC80)
PC=0x00056218; MSR=0x00009030; DAR=0xFF864A5C; DSISR=0x40000000;
LR=0x00053AB4; R1=0x1C17BC70; XCP=0x0000000C (0x300 - Data access)
Backtrace:
0x00000000 0x00055DC8 0x0002F750 0x00033710 0x00032028 0x00031FE4
Exception state (sv=0x0091F280)
PC=0x00000000; MSR=0x0000D030; DAR=0x00000000; DSISR=0x00000000;
LR=0x00000000; R1=0x00000000; XCP=0x00000000 (Unknown)

Kernel version:
Darwin Kernel Version 7.9.0:
Wed Mar 30 20:11:17 PST 2005; root:xnu/xnu-517.12.7.obj~1/RELEASE_PPC

How could a user space program like mysqld possibly cause a kernel panic?
Sounds like bad kernel or bad hardware.

Hi John -
Yes, at my former workplace, we had a couple of different instances
where we experienced severe crashing problems on G4-based machines.

Prior to using a G4 Xserve, we were using a dual-G4 tower as our MySQL
and Intranet server, and as we ramped up MySQL usage, the hard crashing
got to where it was nearly every morning. Finally traced it to our
backup process, Retrospect, accessing files while MySQL was trying to
access them. Specifically excluding MySQL's data directory from your
backup process is a must. Excluding temp and log dirs is also probably
a good idea. We were already dumping out data out to another directory
daily, but didn't realize that the simultaneous access would be such a
problem. This was with MySQL 3.23 and later 4.0, and S 10.1 through
10.2 I think.

When we moved to a G4 Xserve (serving MySQL and our Intranet), we again
had horrible crashing problems. Weird, like none I'd ever seen, where
the S basically just entered a slow spiral of death that led to it
either rebooting on its own or us noticing the problem first and
hard-resetting it. Apps would stop opening, cli processes would no
longer run, running apps would slowly stop responding. Traced it to a
pair of Kingston RAM modules. those were out, the machine righted
itself and I believe continues to serve data (though no longer MySQL) to
this day. Weird I know but I swear that was it. This was with MySQL
4.0 and (I think) S 10.2 through 10.3.

HTH,
Dan

John May wrote:
I've got an xserve running 10.3.9 and MySQL 4.0.27-max that is
restarting itself every 2-3 days. It appears that it is due to a kernel
panic, though I don't have direct access to the machine (colocated) to
verify when it occurs.

There are no crash.logs from MySQL, and watchdog and system.logs show
nothing. I have culled the following panic.log - can anyone tell if
MySQL is the cause of such?

Has anyone seen MySQL 4 crash on an Xserve (G4 specifically)? I have
tried two totally different servers, and the problems continue.
- John

I've got an xserve running 10.3.9 and MySQL 4.0.27-max that is
restarting itself every 2-3 days. It appears that it is due to a
kernel panic, though I don't have direct access to the machine
(colocated) to verify when it occurs.

There are no crash.logs from MySQL, and watchdog and system.logs show
nothing. I have culled the following panic.log - can anyone tell if
MySQL is the cause of such?

Has anyone seen MySQL 4 crash on an Xserve (G4 specifically)? I have
tried two totally different servers, and the problems continue.
- John

Mon Jun 12 06:53:02 2006

Unresolved kernel trap(cpu 0): 0x300 - Data access
DAR=0x00000000FF864A5C PC=0x0000000000056218
Latest crash info for cpu 0:
Exception state (sv=0x3E6ABC80)
PC=0x00056218; MSR=0x00009030; DAR=0xFF864A5C;
DSISR=0x40000000; LR=0x00053AB4; R1=0x1C17BC70; XCP=0x0000000C (0x300
- Data access)
Backtrace:
0x00000000 0x00055DC8 0x0002F750 0x00033710 0x00032028 0x00031FE4
Proceeding back via exception chain:
Exception state (sv=0x3E6ABC80)
previously dumped as "Latest" state. skipping
Exception state (sv=0x0091F280)
PC=0x00000000; MSR=0x0000D030; DAR=0x00000000;
DSISR=0x00000000; LR=0x00000000; R1=0x00000000; XCP=0x00000000
(Unknown)

Kernel version:
Darwin Kernel Version 7.9.0:
Wed Mar 30 20:11:17 PST 2005; root:xnu/xnu-517.12.7.obj~1/RELEASE_PPC

panic(cpu 0): copyin/out has no recovery point
Latest stack backtrace for cpu 0:
Backtrace:
0x00083498 0x0008397C 0x0001EDA4 0x00090C38 0x0009402C
Proceeding back via exception chain:
Exception state (sv=0x3E6ABC80)
PC=0x00056218; MSR=0x00009030; DAR=0xFF864A5C;
DSISR=0x40000000; LR=0x00053AB4; R1=0x1C17BC70; XCP=0x0000000C (0x300
- Data access)
Backtrace:
0x00000000 0x00055DC8 0x0002F750 0x00033710 0x00032028 0x00031FE4
Exception state (sv=0x0091F280)
PC=0x00000000; MSR=0x0000D030; DAR=0x00000000;
DSISR=0x00000000; LR=0x00000000; R1=0x00000000; XCP=0x00000000
(Unknown)

Kernel version:
Darwin Kernel Version 7.9.0:
Wed Mar 30 20:11:17 PST 2005; root:xnu/xnu-517.12.7.obj~1/RELEASE_PPC

Nope, no backups happening at the time.

Anyone other ideas?
- John

At 11:42 AM -0500 6/12/06, Dan Buettner wrote:
>Hi John -
>
>Yes, at my former workplace, we had a couple of different instances
>where we experienced severe crashing problems on G4-based machines.
>
>Prior to using a G4 Xserve, we were using a dual-G4 tower as our
>MySQL and Intranet server, and as we ramped up MySQL usage, the hard
>crashing got to where it was nearly every morning. Finally traced
>it to our backup process, Retrospect, accessing files while MySQL
>was trying to access them. Specifically excluding MySQL's data
>directory from your backup process is a must. Excluding temp and
>log dirs is also probably a good idea. We were already dumping out
>data out to another directory daily, but didn't realize that the
>simultaneous access would be such a problem. This was with MySQL
>3.23 and later 4.0, and S 10.1 through 10.2 I think.
>
>When we moved to a G4 Xserve (serving MySQL and our Intranet), we
>again had horrible crashing problems. Weird, like none I'd ever
>seen, where the S basically just entered a slow spiral of death
>that led to it either rebooting on its own or us noticing the
>problem first and hard-resetting it. Apps would stop opening, cli
>processes would no longer run, running apps would slowly stop
>responding. Traced it to a pair of Kingston RAM modules.
>those were out, the machine righted itself and I believe continues
>to serve data (though no longer MySQL) to this day. Weird I know
>but I swear that was it. This was with MySQL 4.0 and (I think) S
>10.2 through 10.3.
>
>HTH,
>Dan
>
>
>John May wrote:
>>I've got an xserve running 10.3.9 and MySQL 4.0.27-max that is
>>restarting itself every 2-3 days. It appears that it is due to a
>>kernel panic, though I don't have direct access to the machine
>>(colocated) to verify when it occurs.
>>
>>There are no crash.logs from MySQL, and watchdog and system.logs
>>show nothing. I have culled the following panic.log - can anyone
>>tell if MySQL is the cause of such?
>>
>>Has anyone seen MySQL 4 crash on an Xserve (G4 specifically)? I
>>have tried two totally different servers, and the problems continue.
>>
>- John