Has anyone ever mounted their /var/lib/mysql on a tmpfs or ramfs? Are there any performance gains to be made doing this?
TIA
On Thu, 2006-07-27 at 19:58 +0200, Alexander Dalloz wrote:
Mike Kercher schrieb:
Has anyone ever mounted their /var/lib/mysql on a tmpfs or ramfs? Are there any performance gains to be made doing this?
A foolish idea to me. You imagine what happens if the machine hangs up?
I wasn't going to say anything because he used "mount", not "create", "generate", "make", etc. I presummed he just wanted to mount the directory there. No harm, but no goin either. In fact, a very smal teensy-weensy loss probably, due to the extra step to get to things.
Alexander
CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
-----Original Message----- From: centos-bounces@centos.org [mailto:centos-bounces@centos.org] On Behalf Of William L. Maltby Sent: Thursday, July 27, 2006 1:11 PM To: CentOS General List Subject: Re: [CentOS] MySQL on tmpfs?
On Thu, 2006-07-27 at 19:58 +0200, Alexander Dalloz wrote:
Mike Kercher schrieb:
Has anyone ever mounted their /var/lib/mysql on a tmpfs or ramfs? Are there any performance gains to be made doing this?
A foolish idea to me. You imagine what happens if the
machine hangs up?
I wasn't going to say anything because he used "mount", not "create", "generate", "make", etc. I presummed he just wanted to mount the directory there. No harm, but no goin either. In fact, a very smal teensy-weensy loss probably, due to the extra step to get to things.
Alexander
Thanks for the input folks!
Mike
Mike Kercher schrieb:
On Thu, 2006-07-27 at 19:58 +0200, Alexander Dalloz wrote:
Mike Kercher schrieb:
Has anyone ever mounted their /var/lib/mysql on a tmpfs or ramfs? Are there any performance gains to be made doing this?
A foolish idea to me. You imagine what happens if the
machine hangs up?
I wasn't going to say anything because he used "mount", not "create", "generate", "make", etc. I presummed he just wanted to mount the directory there. No harm, but no goin either. In fact, a very smal teensy-weensy loss probably, due to the extra step to get to things.
Alexander
Thanks for the input folks!
Mike
Besides the usual optimization like using best perfomance SQL statements and a good database layout you should take care for the [mysqld] key_buffer setting (/etc/my.cnf). This is RAM usage of MySQL and the place where the database engine tries to put as much of the indexes into it as possible. Ideally MySQL can get all indicies into the buffer. Using the STATUS variables key_read_requests and key_reads you can test whether the key_buffer is big enough: key_read_requests should be at least 100 to 1000 as big as the key_reads. It is reasonable to give the key_buffer 20% of the RAM you want to let MySQL use in total.
Alexander
http://dev.mysql.com/tech-resources/presentations/presentation-oscon2000-200...
On Thu, 27 Jul 2006 21:26:54 +0200 Alexander Dalloz ad+lists@uni-x.org wrote:
Besides the usual optimization like using best perfomance SQL statements and a good database layout you should take care for the [mysqld] key_buffer setting (/etc/my.cnf). This is RAM usage of MySQL and the place where the database engine tries to put as much of the indexes into it as possible. Ideally MySQL can get all indicies into the buffer. Using the STATUS variables key_read_requests and key_reads you can test whether the key_buffer is big enough: key_read_requests should be at least 100 to 1000 as big as the key_reads. It is reasonable to give the key_buffer 20% of the RAM you want to let MySQL use in total.
That or use heap tables or whatever are those in-ram-only short lived tables called. IIRC slashdot folks implemented them for mysql.
On 7/27/06, Mike Kercher mike@vesol.com wrote:
Has anyone ever mounted their /var/lib/mysql on a tmpfs or ramfs? Are there any performance gains to be made doing this?
Not really, the database runs in ram as much as possible anyway. You can tweak the filesystem it writes to and reads from, but for the most part, it's already in memory.
-
Alexander Dalloz -
Jim Perrin -
Jure Pečar -
Mike Kercher -
William L. Maltby