I do not really think that optimizing (in your case "compressing", thus cleaning up free space) is much faster with fixed record length on LARGE tables. Why? When optimizing the table the DB rebuilds the file "record for record" to a temporary file and then moves it back to the original file (well, this is the theory, some optimization is done of course). So the only advantage you get with fixed record length is, that it does not need to compute the record length for your records. However, this is in the milliseconds, whereas the actual write operation eats up most of the time. So by converting to fixed size you will only get a very small increase of speed, not worth mentioning.
What would give you speed is - like already someone suggested - using raid0 or maybe using merge tables, which you then can optimize on demand (e.g. split your table by year, then only delete records from one your and optimize this hear only). Cheerio /rudy -----Original Message----- From: Alexander Schulz [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juli 2003 14:35 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Managing big tables 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
