It has been quiet around here. My workload has been high and I have been spending my down time reading, rather than experimenting and hence publishing.
There were three questions and my reply was going to be long. So now it is a post.
- /dev/sda/ : do you mean that it would be better to separate the tables of the database or simply use /dev/sda (if 10GB is enough for DB storage)
- I found info about db caching (http://dev.mysql.com/doc/refman/5.0/en/query-cache.html), but don’t know what you are talking about file caching. Is there any link you could share about this principle.
- what kind of filesystem do you use on /mnt and what is the size of the partition created.
- Given what the IO benchmarks are saying when they bypass the OS caching mechanism i.e. /dev/sda is faster than /dev/sdb for writes. Use /dev/sda for your binlogs and anything you want to have fast write access. Many databases are 95-98% reads so putting them on the /dev/sdb (/mnt) with OS caching is going to fine.
- There are many levels of caching. Google for Facebook MySQL presentation. Many websites which need to scale use Memcache upfront of the database. This means that most reads from the database are served from Memcache. MySQL also has a query cache as you found. File caching is just OS caching. This is normally on by default on most linux flavours. Essentially there is no point in wasting any free RAM, so the OS will cache any physical reads so that next time they can be served from memory rather than disk.
- I was just using the standard ext3 file system. You can run Logical Volumes on top of it. There are people who will argue for the pros and cons. It was more just what I was familiar with at the time. I was not stressing about the file system type, more about the difference in performance of the two virtual storages available.