Managing big tables

2003-07-15 Thread Alexander Schulz 
Hello,
i've got a little problem,
we're using mysql with two big tables (one has 90 Mio. Rows (60 Gb on 
HD), the other contains nearly 200.000.000 (130 Gb on HD).
Now we want to delete some rows from these tables to free diskspace. It 
seems that MySQL frees the harddisk-space which
was used by these rows only after optimization, which lasts very long on 
these tables. Both tables are "dynamic" in terms of row-format
what seems to extend the time needed for optimization. I tried to 
convert the smaller one to "fixed"-row-format, which increased the
disk-space of its data-file from 30 Gb to 60 Gb. This would not be the 
problem, but some SQLs which are run daily
on this table now run 4 times slower than with dynamic structure.

So, my questions are:
1) Did i something wrong while converting to fixed row-format ? (i found 
no indication)
2) Is the fixed structure really faster on optimization ?
3) Can anybody confirm the slow-down on big tables when converted from 
dynamic to fixed ?
   (on small tables fixed were faster)

I'm running SuSE Linux, Kernel 2.4.20, the above behaviour could be 
reproduced with our productive server (MySQL 3.23) and
with a test server (MySQL 4.0.12).

thanks in advance,
alex


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re:Managing big tables

2003-07-15 Thread Alexander Schulz 
Thanks for your efforts, and in rudys last mail he confirms what i have 
feared, the io-overhead for reading the
extremely longer rows most probably causes these longer query-times. I 
think we will have to redesign our
table-structure, because we're already working on a (hardware-)raid, so 
the mysql-raid-option won't really be
an option. Perhaps the only way to get this damned big tables smaller in 
appropriate time will be a solution
via merge-tables (if this does not slow down our productive queries on 
that table too much).

alex





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]