Linux – MySQL & tmpfs : performance

linuxMySQLperformancetmpfs

I was wondering if, and how much, using tmpfs could improve MySQL performance and how it should be done ? My guess would be to do mount -t tmpfs -o size=256M /path/to/mysql/data/DatabaseName, and to use the database normally but maybe I'm wrong (I'm using MyISAM tables only).

Will a hourly rsync between the tmpfs /path/to/mysql/data/DatabaseName and /path/to/mysql/data/DatabaseName_backup penalize performances ? If so, how should I make the backup of the tmpfs database ?

So, is it a good way to do things, is there a better way or am I losing my time ?

Best Answer

I answered a similar question here when someone was asking how to load a MySQL database to memory. There's a native ENGINE in MySQL among other potential solutions, which are better for most situations.

For MyISAM tables, your proposed solution would be functional.

The rsync shouldn't necessarily penalize performance but it could if your system was really low on resources, as high I/O utilization would impact system performance as a whole. In most cases, I'd say it wouldn't but only way to be sure is to test.

The ramdisk solution seems risky to me. In case of power loss, data could be lost, as your rsync would not be realtime. You could replication the database to a slave, which could store on a disk. The startup on MySQL on the ramdisk server could scp from that host into the ramdisk before starting the instance.

If your MySQL instance was not well optimized, you would notice substantial performance gains. If well optimized, I'd suspect that the gains would be less. The only way to be sure is to use tools like Super Smack, MySQL Benchmark, and sysbench to quantity the performance.

Good luck.

Related Solutions

Ubuntu – MySQL: How to get the “Maximum possible memory usage” down

You have a server with 256M, but you can't use all of that -- remember there's some OS overhead. Add to that with the fact you're over committing as other folks have mentioned and you'll definitely thrash here. 256M is only enough for a small DB, 20 connections is a lot with what you've got configured.

1) reduce your max connections to 4 (you're using 3 out of 20)

2) optimize your query cache better; 8M is really large, and 64M total is a lot based on your hits/prunes; try a 4/32 combo and see how it goes. Really I think a 2/24 combo would work for you.

3) you have no sorts requiring temp tables, why is that max_heap_table_size verb in there? Comment that out, use the defaults

4) do you actually have 128 tables? Try cutting that table_cache in half to 64 or 48

5) reduce thread_cache_size to 4

6) optimize those tables to reduce fragmenting

Those are some things to start with. It looks like you threw a bunch of numbers in a config without any actual profiling to know what you needed and have created a mess; if all else fails go back to the defaults and get rid of your custom settings and start over using some performance tuning guides you can find on Google. Get the output of SHOW VARIABLES and SHOW STATUS, find any one of a bajillion tuning guides and plug in your actual, real numbers into their equations and that'll tell you the exact-ish numbers you need to put in your config file.

MySQL: how to convert many MyISAM tables to InnoDB in a production database

You can do it. Converting from MyISAM to InnoDB should not be a problem as InnoDB has more features which are not available in MyISAM such as transactions.

You can alter the tables while the database is running. No need for shutdown, but this operation will lock the tables.

For the time it will take, this depends on the server specs/load/db size, etc.

You can do it using a script to automate the process. Using a command show tables, you can get the list of tables. Then, using a simple script you can read this list and execute alter table commands.

Here is a simple perl script that will read the tables list from a file and write the SQL queries in another file. After invoking this script, you can feed the output file to mysql command to do the required changes.

open(INFILE, "< tables_list.txt");
open(OUTFILE, "> alter_tables.sql");
foreach my $name (<INFILE>) {
   chomp($name);
   print OUTFILE "ALTER TABLE $name engine=InnoDB;\n";
}
close(INFILE);
close(OUTFILE);

Best Answer

Related Solutions

Ubuntu – MySQL: How to get the “Maximum possible memory usage” down

MySQL: how to convert many MyISAM tables to InnoDB in a production database

Related Topic