Tom Penney writes: > My co worker is convinced that the ext3 partition hosting our mysql > databases needs to be defragmented to improve lookup performance. Profile. Don't speculate. > I've been > told fragmentation is not a problem at all and ext3 File systems > never need to be defragmented. I don't know if I believe that to be > the whole truth because fragmentation does occur even if it's not > really a big problem. That's not entirely true. It would be very difficult (read: impossible) to make a filesystem that is completely immune to fragmentation under all circumstances that still has good performance. There is some good reading material on the subject: http://web.mit.edu/tytso/www/linux/ext2intro.html http://docs.freebsd.org/44doc/smm/05.fastfs/paper.html > The database although it pretty big & flat, (~4Gig), it's pretty > static. If that is the case, and you are worried about fragmentation, fix it by copying the database files to a partition with lots of free space. You will need to stop MySQL before you do this. > 1. Do you think defragging this partition is going to make a > noticeable difference in performance? Maybe. If you are desperate, give it a try. But simply copying the files is much easier. > 2. Can anyone point me to any resources that would convince my > coworkers that the problem is not the drive, it's the database? Yes. That would be your profiler output. > 3. Is backing up the partition, deleting it, then restoring the only > way to deferment a ext3 partition? No, but it is the most reliable method. I wouldn't trust any defragmenting tool without a complete backup, so at that point, it's much easier to simply restore the partition. > 4. is there a way to determine the how contiguous or fragmented one > particular file is? I seem to remember a tool that does that, but I don't remember the name. You didn't give us any details about your database, but I find it highly unlikely that performance is due to the drive being fragmented. If you want to get decent performance out of a large MySQL database, you need to index every column that you are querying against. The speed difference can easily be three or four magnitudes. Learn how to use EXPLAIN for your queries. This will tell you where you need to use indexes. Also run OPTIMIZE TABLE and ANALYZE TABLE. If you have deleted a lot of records from a table, the table could be fragmented (at the MySQL level, not the filesystem level). OPTIMIZE fixes this. ANALYZE saves the key distribution for the table, which helps out the optimizer. Determine the size of your indexes. You can do this using SHOW TABLE STATUS LIKE 'table'. Index_length is the size of the index. MySQL uses the index to determine where items are in the table. As I said before, this is critically important to fast lookups. Otherwise, MySQL has to do a full table scan, which is very slow on large tables. It is important to get as much of the index as possible into memory. Ideally, key_buffer (usually set in /etc/my.cnf) should be large enough to hold the indexes from all your tables that get queried often. This may mean dropping some indexes that are never used. On a large table, sometimes the index can be larger than the data. Finally, make sure you read and understand this: http://www.mysql.com/doc/en/MySQL_Optimisation.html -- David Phillips <david at acz.org> http://david.acz.org/ _______________________________________________ TCLUG Mailing List - Minneapolis/St. Paul, Minnesota http://www.mn-linux.org tclug-list at mn-linux.org https://mailman.real-time.com/mailman/listinfo/tclug-list