Running MySQL in RAMDisk
Have a very large database and due to performance requirements it seems that running MySQL in RAMDisk might be a good solution. Access to the database is through PHP. Any suggestions or experiences? Creigh 305-541-1122 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running MySQL in RAMDisk
Depends on db size... kinda risky putting it in memory if it's being updated and power goes bye-bye.. You should be able to get alot more performance just tuning my.cnf for a larger memory box.. On Mon, 18 Aug 2003, Creigh Shank wrote: Have a very large database and due to performance requirements it seems that running MySQL in RAMDisk might be a good solution. Access to the database is through PHP. Any suggestions or experiences? Creigh 305-541-1122 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running MySQL in RAMDisk
At 09:17 AM 8/18/2003, you wrote: Have a very large database and due to performance requirements it seems that running MySQL in RAMDisk might be a good solution. Access to the database is through PHP. Any suggestions or experiences? Creigh 305-541-1122 Creigh, How large is the database? How many rows? How many MB? Are you using MyISAM or InnoDb? Are you mainly reading or writing to the database? External RAM disks that are around 8g are extremely expensive. Some have built in in battery protection and operate independently from the OS so if the OS reboots, you still have your data. A couple of years ago I priced these devices at around $25,000 and that is quite a lot to spend for only 8g. There are disk caches that work almost as fast as a RAM disk by speeding up writes by caching them, of course if your computer goes down you are very likely to get corruption. MySQL 4.x has a read cache that is very good for retrieving repetitive queries so in this respect a RAM disk may not help that much if your application is mainly reading from the tables. (The cache is reset if a write is done to the table so if you are doing and equal number of reads and writes to the same table, the MySQL cache won't speed things up that much.) Speed up suggestions: 1) Use a LIMIT 100 on all select statements because users usually are only willing to scroll through that many rows. If they want to see more than that, use LIMIT 100,100 to get the next 100 rows etc.. 2) Don't use Persistent connections because that doesn't speed things up. 3) PHP takes a great deal of time loading scripts off of the hard disk so if you want to use a RAM disk, put your PHP scripts and web page images on the RAM disk (around 5-10 MB). This will increase the speed of your scripts by about 30%-50%. 4) Replicate the data to other database(s) and use those for read only queries. You can have your PHP script use a different MySQL connection for read queries and another for updates. The updates gets written to the master database and the changes then gets replicated out to your readonly databases. This takes the load off of the master database (since it is only doing updates), and the replicated databases will likely have rows cached longer. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running MySQL in RAMDisk
How would I tune my.cnf for a larger memory box? (Running on UPS; production machine(s) will go into Co-Lo with UPS and generator.) I realize there is some risk here. I am more concerned with the system failing and some kind of mirroring solution to protect data. Creigh At 10:34 AM 8/18/2003 -0400, you wrote: Depends on db size... kinda risky putting it in memory if it's being updated and power goes bye-bye.. You should be able to get alot more performance just tuning my.cnf for a larger memory box.. On Mon, 18 Aug 2003, Creigh Shank wrote: Have a very large database and due to performance requirements it seems that running MySQL in RAMDisk might be a good solution. Access to the database is through PHP. Any suggestions or experiences? Creigh 305-541-1122 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running MySQL in RAMDisk
If you can post your current my.cnf + box configuration I'm sure we can come up with some suggestions.. On Mon, 18 Aug 2003, Creigh Shank wrote: How would I tune my.cnf for a larger memory box? (Running on UPS; production machine(s) will go into Co-Lo with UPS and generator.) I realize there is some risk here. I am more concerned with the system failing and some kind of mirroring solution to protect data. Creigh At 10:34 AM 8/18/2003 -0400, you wrote: Depends on db size... kinda risky putting it in memory if it's being updated and power goes bye-bye.. You should be able to get alot more performance just tuning my.cnf for a larger memory box.. On Mon, 18 Aug 2003, Creigh Shank wrote: Have a very large database and due to performance requirements it seems that running MySQL in RAMDisk might be a good solution. Access to the database is through PHP. Any suggestions or experiences? Creigh 305-541-1122 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running MySQL in RAMDisk
Mike, This is very helpful. Database consists of approximately 220,000,000 records. Raw data total (roughly) 5.5 GBytes. I don't know how large the database will be loaded (don't know about overhead, etc.) Running InnoDb. Probably split 90% read and 10% writees. If nothing else replication might be good for disasters. RAM costs are down to about $350 per Gbyte. I was thinking of using internal RAM. I know I can get to 12 Gbytes for sure; maybe 24 Gbytes. Any further ideas? Creigh At 09:42 AM 8/18/2003 -0500, you wrote: At 09:17 AM 8/18/2003, you wrote: Have a very large database and due to performance requirements it seems that running MySQL in RAMDisk might be a good solution. Access to the database is through PHP. Any suggestions or experiences? Creigh 305-541-1122 Creigh, How large is the database? How many rows? How many MB? Are you using MyISAM or InnoDb? Are you mainly reading or writing to the database? External RAM disks that are around 8g are extremely expensive. Some have built in in battery protection and operate independently from the OS so if the OS reboots, you still have your data. A couple of years ago I priced these devices at around $25,000 and that is quite a lot to spend for only 8g. There are disk caches that work almost as fast as a RAM disk by speeding up writes by caching them, of course if your computer goes down you are very likely to get corruption. MySQL 4.x has a read cache that is very good for retrieving repetitive queries so in this respect a RAM disk may not help that much if your application is mainly reading from the tables. (The cache is reset if a write is done to the table so if you are doing and equal number of reads and writes to the same table, the MySQL cache won't speed things up that much.) Speed up suggestions: 1) Use a LIMIT 100 on all select statements because users usually are only willing to scroll through that many rows. If they want to see more than that, use LIMIT 100,100 to get the next 100 rows etc.. 2) Don't use Persistent connections because that doesn't speed things up. 3) PHP takes a great deal of time loading scripts off of the hard disk so if you want to use a RAM disk, put your PHP scripts and web page images on the RAM disk (around 5-10 MB). This will increase the speed of your scripts by about 30%-50%. 4) Replicate the data to other database(s) and use those for read only queries. You can have your PHP script use a different MySQL connection for read queries and another for updates. The updates gets written to the master database and the changes then gets replicated out to your readonly databases. This takes the load off of the master database (since it is only doing updates), and the replicated databases will likely have rows cached longer. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running MySQL in RAMDisk
Sorry the MySQL list server seems to have bounced my attachment. I have include the text below. my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # skip_innodb innodb_data_file_path=idbdata1:200M:autoextend set-variable = innodb_buffer_pool_size=80M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_log_file_size=20M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid At 11:23 AM 8/18/2003 -0400, you wrote: plz forward the my.cnf inline to the mysql list... i got it uuencoded and it doesn't seem to want to decode for some reason... thanks.. On Mon, 18 Aug 2003, Creigh Shank wrote: Here it is. Included php.ini, too. At 10:49 AM 8/18/2003 -0400, you wrote: If you can post your current my.cnf + box configuration I'm sure we can come up with some suggestions.. On Mon, 18 Aug 2003, Creigh Shank wrote: How would I tune my.cnf for a larger memory box? (Running on UPS; production machine(s) will go into Co-Lo with UPS and generator.) I realize there is some risk here. I am more concerned with the system failing and some kind of mirroring solution to protect data. Creigh At 10:34 AM 8/18/2003 -0400, you wrote: Depends on db size... kinda risky putting it in memory if it's being updated and power goes bye-bye.. You should be able to get alot more performance just tuning my.cnf for a larger memory box.. On Mon, 18 Aug 2003, Creigh Shank wrote: Have a very large database and due to performance requirements it seems that running MySQL in RAMDisk might be a good solution. Access to the database is through PHP. Any suggestions or experiences? Creigh 305-541-1122 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running MySQL in RAMDisk
Your MySQL installation should have a directory called support-files. In there are a couple of configuration files you can use with obvious names like my-huge.cnf, my-large.cnf, ..., my-small.cnf. There are comments in each file about what kind of system they were designed for. You can read up on what each option does in the on line manual so you can really customize it to what you will be doing. To use one of the files, you will put it in the appropriate place and name it my.cnf. On a Mac, and other BSD Unix I think, it goes in /etc. Shutdown and relaunch MySQL and see if you get a boost. On Monday, August 18, 2003, at 10:53 AM, Creigh Shank wrote: How would I tune my.cnf for a larger memory box? (Running on UPS; production machine(s) will go into Co-Lo with UPS and generator.) I realize there is some risk here. I am more concerned with the system failing and some kind of mirroring solution to protect data. Creigh At 10:34 AM 8/18/2003 -0400, you wrote: Depends on db size... kinda risky putting it in memory if it's being updated and power goes bye-bye.. You should be able to get alot more performance just tuning my.cnf for a larger memory box.. On Mon, 18 Aug 2003, Creigh Shank wrote: Have a very large database and due to performance requirements it seems that running MySQL in RAMDisk might be a good solution. Access to the database is through PHP. Any suggestions or experiences? Creigh 305-541-1122 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running MySQL in RAMDisk
At 10:19 AM 8/18/2003, you wrote: Mike, This is very helpful. Database consists of approximately 220,000,000 records. Raw data total (roughly) 5.5 GBytes. I don't know how large the database will be loaded (don't know about overhead, etc.) Running InnoDb. Probably split 90% read and 10% writees. If nothing else replication might be good for disasters. RAM costs are down to about $350 per Gbyte. I was thinking of using internal RAM. I know I can get to 12 Gbytes for sure; maybe 24 Gbytes. Any further ideas? Creigh Creigh, I assume you're running this on Linux and not Windows? BTW, using RAM disks is usually a last resort to speed things up. Optimizing your PHP code and database queries are the first thing to at. Also the number of php scripts that get loaded to display a page will have an impact on speed. I used PHP classes for an application and it takes quite a while to load in 10-15 PHP scripts off of the hard disk. Using one PHP script would have been much faster. A simple 5MB ram disk speeded PHP script loading considerably. I'm running Win2k and Intel MB's typically support no more than 4g of RAM (some will support 8g), so you will reach an upper limit quite fast. Don't forget if you use a conventional RAM disk it takes memory away from the OS. For Windows see http://www.superspeed.com/allproducts.html . Going with a 64 bit OS should eliminate the 4g limit but that means recompiling MySQL for 64 bits. Conventional RAM disks won't retain data during a reboot or system crash. External ram devices will! External RAM devices can be very expensive (you need to have very deep pockets). For very very expensive RAM devices see http://www.superssd.com/products_sub.htm. Their ram disks support 8gb to 1TB of solid state memory. If you have lots of money, buy two and send me one.g Prices start at $45,000 for an 8g unit. See a review at http://www.eweek.com/article2/0,3959,857374,00.asp. Also get yourself a PHP profiler so you can see which lines of PHP code is slowing things down. I had a free one for Windows but the author sold the rights to www.nusphere.com and it then became PHP Ed. If your web page has images, I'd recommend using Photo Shop 7 to reduce the image storage requirements without losing image quality. (It's quite good at this.) Mike At 09:42 AM 8/18/2003 -0500, you wrote: At 09:17 AM 8/18/2003, you wrote: Have a very large database and due to performance requirements it seems that running MySQL in RAMDisk might be a good solution. Access to the database is through PHP. Any suggestions or experiences? Creigh 305-541-1122 Creigh, How large is the database? How many rows? How many MB? Are you using MyISAM or InnoDb? Are you mainly reading or writing to the database? External RAM disks that are around 8g are extremely expensive. Some have built in in battery protection and operate independently from the OS so if the OS reboots, you still have your data. A couple of years ago I priced these devices at around $25,000 and that is quite a lot to spend for only 8g. There are disk caches that work almost as fast as a RAM disk by speeding up writes by caching them, of course if your computer goes down you are very likely to get corruption. MySQL 4.x has a read cache that is very good for retrieving repetitive queries so in this respect a RAM disk may not help that much if your application is mainly reading from the tables. (The cache is reset if a write is done to the table so if you are doing and equal number of reads and writes to the same table, the MySQL cache won't speed things up that much.) Speed up suggestions: 1) Use a LIMIT 100 on all select statements because users usually are only willing to scroll through that many rows. If they want to see more than that, use LIMIT 100,100 to get the next 100 rows etc.. 2) Don't use Persistent connections because that doesn't speed things up. 3) PHP takes a great deal of time loading scripts off of the hard disk so if you want to use a RAM disk, put your PHP scripts and web page images on the RAM disk (around 5-10 MB). This will increase the speed of your scripts by about 30%-50%. 4) Replicate the data to other database(s) and use those for read only queries. You can have your PHP script use a different MySQL connection for read queries and another for updates. The updates gets written to the master database and the changes then gets replicated out to your readonly databases. This takes the load off of the master database (since it is only doing updates), and the replicated databases will likely have rows cached longer. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running MySQL in RAMDisk
On Mon, Aug 18, 2003 at 09:42:51AM -0500, mos wrote: External RAM disks that are around 8g are extremely expensive. Some have built in in battery protection and operate independently from the OS so if the OS reboots, you still have your data. A couple of years ago I priced these devices at around $25,000 and that is quite a lot to spend for only 8g. Well, it's not really the 8GB that you're paying for. It's the performance. $25,000 for virtually 0ms seek times isn't that bad. How many spindles would you have to buy to do that yourself? Way too many. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 17 days, processed 875,114,080 queries (589/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
