I just thought to ask, Is this database 1 table? Sam. David Phillips wrote: >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 > > > _______________________________________________ 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