On Thu, 6 Jan 2005, Maarten Litmaath, CERN wrote:

> David Smith has had to deal with the same problem on the testzone RB
> lxn1188.cern.ch; I suppose he will be able to respond tomorrow.
>
> It was already on our to-do list to significantly enlarge the default
> limitations on various tables and to define the necessary indexes.

Hello,

We have a couple of open issues with the database setups at the moment -
but from Torsten's description of his problem I'm not certain that either
is the problem in this case. The known issues at the moment are:

(1) The monitoring queries (QA measurements) can take some time for MySQL
to process. To reduce this time it is important that the table indexs are
analyzed (ie.  'analyze table xxxx;') when the tables have a
representative distribution of keys. Having this done allows MySQL to
speed up the monitoring queries. (During the query mysql holds other
queries that modify the tables being scanned, the result being that job
submissions can fail, since they require a registration event to be
committed to the LB service within a time limit)

(2) Another problem is with table size. By default the way MySQL writes
the tables allows them only to grow to up to 4Gb in size. The largest
table files will probably be short_fields.MYD & long_fields.MYD. Check if
either has reached 4Gb. From the number of entries you counted I don't
think they have - probably the table size is ~3Gb.

For problem (1) the tables can be analyzed with:

analyze table acls;
analyze table events;
analyze table jobs;
analyze table long_fields;
analyze table server_state;
analyze table short_fields;
analyze table states;
analyze table status_tags;
analyze table users;

(I just list all the tables, but in particular events, short_fields,
long_fields will be critical for the monitoring). These, largest, tables
can take several 10s of minutes to scan.

For (2), setting an explicit table size (in terms of a maximum number of
rows) removes the limitation. For example:

ALTER TABLE short_fields MAX_ROWS=1000000000;
ALTER TABLE long_fields MAX_ROWS=55000000;
ALTER TABLE states MAX_ROWS=9500000;
ALTER TABLE events MAX_ROWS=175000000;

which should allow the database to hold up to about 9.5M events. (At which
time it will be about 220Gb in size). With the default limit the critical
number of jobs is ~550,000.

With the tables at their limiting size the resizing will be a slow
operation. Probably taking ~12 hours for the 4 tables.  During this time
MySQL won't allow table updates, so the broker would have to be taken out
of service. There are some MySQL tricks for replacing the tables more
quickly but I've never tried them.

It is a mistake that LBServer tables aren't initially created with large
row limits - we'll have this incorporated into future broker setups.

I haven't ever seen either of these actually crash mysql, so it could be
that you have another problem.

Yours,
David

--
-------------------------------------------------------------------------
David Smith       e-mail: [log in to unmask]        tel: +41 22 76 74462
Address: D. Smith, CERN G06610, Bat 28 R-007, 1211 Geneva 23, Switzerland
-------------------------------------------------------------------------