Good question.
I can give you some details about the database I am using. This is about
another situation than Noor's (linux instead of FreeBSD), but the
problems looks similar.
I have not find any specific event that causes mysqld spinning around
and claiming memory. I activited the logging of slow queries (longer
than 5 seconds) but the only queries that I can find in there are quite
normal queries that go as a breeze in mormal circumstances. They only
appear in the slowlog when mysqld is confused.
The database has about 30 tables. The one with the most records is about
600.000 records large, but a small records size. It looks like this:
it looks like this:
CREATE TABLE BulkCalc (
type_id bigint(20) NOT NULL default '0',
kmperjaar int(11) NOT NULL default '0',
looptijd tinyint(4) NOT NULL default '0',
prijs_excl_br int(11) NOT NULL default '0',
prijs_incl_br int(11) NOT NULL default '0',
last_updated timestamp(14) NOT NULL,
KEY idx_bul_002(kmperjaar,looptijd),
KEY idx_bul_003(prijs_incl_br),
KEY idx_bul_004(prijs_excl_br),
PRIMARY KEY (type_id,kmperjaar,looptijd),
KEY idx_bulk_005(last_updated)
) TYPE=MyISAM;
Records in this table are accessed by combination of type_id, kmperjaar
and looptijd and are usualy ordered by prijs_excl_br or prijs_incl_br
Another remarkable table with about 5000 rows has many fields (about
185, most varchar, tinyint int and bigint, also some floats and a text
field). It has a primary key on a bigint-field and three other indexes:
on a varchar(255), on a int, on a varchar(4) and on a char(1) field.
Records are usualy accessed by primary key or by a like on the
varchar(255)-field (with or without the orther indexed fields).
Until previous week I used joins between both tables, but considering
the possiblity that mysql made a wrong decision by first making a
resultset of 600,000 x 5,000 rows, I split up the quey, making such a
situation impossible. Unfortunately, this had no effect.
Another way the database is use, is for sessions. We use Apache::Session
with the usual session table:
CREATE TABLE sessions (
id varchar(32) NOT NULL default '',
a_session mediumtext,
t timestamp(14) NOT NULL,
PRIMARY KEY (id)
) TYPE=ISAM PACK_KEYS=1;
Apache::Session uses the GET_LOCK and RELEASE_LOCK as lock mechnism for
one record, while handling a request.
When mysqld turns crazy, and we were able to stop mysqld in time, the
tables are all in good state, no corruption. In many cases myisamchk
reports 1 active client, but not in all cases.
I have studied this problem extensively, without finding any clues. Even
hardware might be the cause, although this is not likely. A IBM
Netfinity 4500 R, 2 processor, 512 Mb, RAID-5 scsi with ServeRaid-4L
controller, RedHat 6.2 intel.
I wonder what I should try first: change hardware or downgrade to mysql
3.22.x or something else.....
Any ideas?
Herald
P.S. I also found that "replace into" can make a the table corrupt in
certain situations. It is reproducable and I will report it soon. Anyway
this is not the cause of the problem I am struggling with.
[EMAIL PROTECTED] wrote:
> You didn't give any details about the database and how it is being used/accessed.
>
> There may NOT be anything wrong with your setup. It may actually be inefficiencies
>in your code or queries that are causing the problems.
>
> Also Note MySQL 3.23.31 has a serious bug!
>
> - Scott
>
>
>
>> Hello all,
>>
>> I've been trying to fix a problem we're facing without luck for the last
>> few weeks. I also searched mailing lists for a similar problem, both
>> MySQL and FreeBSD, without luck.
>>
>> The situation is as follows... A brank new server was deployed last
>> month for our company. It's a 933Mhz Xeon-based server, with 1GB of
>> memory, a RAID-5 system utilizing 4 IBM SCSI 10K RPM LVD disks. As you
>> can see, a very high-performance server for the Web. I installed FreeBSD
>> 4.1.1 on it, then upgraded to 4.2-STABLE up to last week's snapshot.
>> Currently, it has Apache 1.3.14 + mod_ssl, MySQL 3.23.31 and PHP
>> 4.0.4pl1 -- as you can imagine, most RECENT versions.
>>
>> The problem persists, though, and here it is: every once in a while,
>> sometimes once a week, the mysqld process goes into OVERHEAT state with
>> CPU usage getting as high as 97%, thus making it almost impossible for
>> clients to connect to it. I checked mysqld's process usage once while
>> this happened, and I found around 60 processes (connections). Not a big
>> deal as I saw much more processes connect to the same daemon before, and
>> still it continued to work.
>>
>> Anyways, this problem keeps bugging me all the time. I look at lists
>> over and over and cannot understand why this is happening. I've even
>> tried limiting Apache to a minimum of connections (sometimes as low as
>> 300 maximum requests for the whole Apache daemons), but it didn't
>> help -- eventualy, mysqld would go into this almost-dead state and no
>> more Web pages would be served correctly.
>>
>> I'm attaching the my.cnf file for you to examine. Remember, this is the
>> stock my.cnf file that came with MySQL for large Web sites (those having
>> more than 512MB of memory), with a little modification.
>>
>> I'd appreciate any hint or fix that you might have, and I would even let
>> serious bug-hunters/mysql gurus get a shell on our server to try to
>> check and fix this problem.
>>
>> Thanks in advance.
>>
>> Noor
>
>
>
> --
> --------------------------------------
> Scott A. Gerhardt P.Geo.
> Gerhardt Information Technologies
> [EMAIL PROTECTED]
> --------------------------------------
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
