Hi Diego On 15 May 2009, at 20:54, Diego Figueroa wrote:
Thanks for your input Quanah,I also just noticed that top is reporting 50-90% I/O waiting times. I might have to look at my disks to further improve things.
That can be an over-simplification - you may be right, but it could be an over-simplification.
Random seeks will always create a performance slowdown on physical disks. If you optimise the DB so that you reduce the number of random seeks, you'll get dramatically faster performance.
Realise that if your db is, say, 200mb, you could probably write the whole file contiguously in 3-4 seconds on most server PCs. But if you do 1 seek per object in your 500k item database with reasonable seek- time disks (say 6.5ms), you'll be doing 500000 seeks *6.5ms = 3250000 ms = 3250 seconds = 54 minutes.
http://www.oracle.com/technology/documentation/berkeley-db/db/ref/transapp/throughput.html says that every write can do the following seeks:
1 Disk seek to database file 2 Database file read 3 Disk seek to log file 4 Log file write 5 Flush log file information to disk6 Disk seek to update log file metadata (for example, inode information)7 Log metadata write 8 Flush log file metadata to disk
So, what to do? Well, if you update cache values, you'll find less reads. If you assume each item above is equal in wall-clock time, you could remove the first 3 items and speed things up 37.5% for every one of the cache hits.
You could also put the log file on a separate disk. Or you could perhaps put the log file on a ramdisk for your build, and move it to a stable disk after it completes. I'm assuming you don't have sufficient ram to store the whole db on a ramdisk, which would be the ideal for the build process.
You can also mount your filesystems with -noatime, which will help by removing step 6. Note you'll have to check whether this breaks other things on your system.
You could also try fiddle with the DB_TXN_WRITE_NOSYNC and DB_TXN_NOSYNC flags. I've not done that, and you'd have to be 100% sure that once your db goes live, this flag is then turned off or you disk disaster if your db server reboots. I wonder if it's possible for slapadd to turn these on automatically for the load process (perhaps it already does - I'm ignorant on that fact, unfortunately).
If you're feeling brave, and are building on a throwaway system (where you can reinstall due to filesystem corruption), you could also use something like hdparm under linux to change the disks so that they always return writes as successful immediately, even if the data hasn't been written to disk. I don't recommend this, but I've been known to do it when testing on a dev system. I don't have any stats on how much it'd help.
Another thing: I read an article a while back where someone found that innodb file fragmentation on mysql dbs created a massive slowdown over time with random small writes to to the file. The solution was fairly simple - move the files to a different directory, make a copy back into the original directory, and start the db again running off the copy. The new files will be written contiguously with very little fragmentation. It's not possible to do this mid-stream in the load on a new DB, but it may be a good practice once you have a very large complete DB file that's been built over time.
I'd be really interested in which of those items helps the most. Let people know if you play and find something interesting. Hopefully it helps someone else!
Oskar