Re: mysql RAID
http://assets.en.oreilly.com/1/event/27/Linux%20Filesystem%20Performance%20for%20Databases%20Presentation.pdf 2010/3/10 John G. Heim jh...@math.wisc.edu Hi, I am working on configuring a new hardware database server. I'm a little confused as to what to do about disk. We have several mysql databases but by far the 2 most active are spamassassin bayesian rules and horde3/imp web mail. Both do a lot of updates. The bayesian rules are added to each time a spam message comes in for any of our 200 users. And the horde3/imp writes address book updates and preferences quite often. I have read (and have been told) to stay away from RAID-5 for update-intensive systems. Are there performance concerns with RAID-10 as well? We will be buying from Dell (done deal for reasons too complicated to go into) and the disks they're selling are 146 Gb. I can get up to 8 of them in the server we're buying. I asked them about just getting 2 big disks and going with RAID-1. My understanding is that with RAID-10, the system can do multiple reads and writes simultaneously so throughput is improved oversystems w/o RAID or with RAID-1. But the same logic would apply to RAID-5 only it doesn't work out that way. I just want to make sure I'm configuring this system correctly before I order it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ekilimc...@gmail.com -- Best regards, Eugene Kilimchuk ekilimc...@gmail.com
Re: mysql RAID
What are your plans for OS as this can have an impact on hardware choice especially if you are considering Solaris or Open Solaris. Regards John On Tue, Mar 16, 2010 at 3:22 PM, Евгений Килимчук ekilimc...@gmail.comwrote: http://assets.en.oreilly.com/1/event/27/Linux%20Filesystem%20Performance%20for%20Databases%20Presentation.pdf 2010/3/10 John G. Heim jh...@math.wisc.edu Hi, I am working on configuring a new hardware database server. I'm a little confused as to what to do about disk. We have several mysql databases but by far the 2 most active are spamassassin bayesian rules and horde3/imp web mail. Both do a lot of updates. The bayesian rules are added to each time a spam message comes in for any of our 200 users. And the horde3/imp writes address book updates and preferences quite often. I have read (and have been told) to stay away from RAID-5 for update-intensive systems. Are there performance concerns with RAID-10 as well? We will be buying from Dell (done deal for reasons too complicated to go into) and the disks they're selling are 146 Gb. I can get up to 8 of them in the server we're buying. I asked them about just getting 2 big disks and going with RAID-1. My understanding is that with RAID-10, the system can do multiple reads and writes simultaneously so throughput is improved oversystems w/o RAID or with RAID-1. But the same logic would apply to RAID-5 only it doesn't work out that way. I just want to make sure I'm configuring this system correctly before I order it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ekilimc...@gmail.com -- Best regards, Eugene Kilimchuk ekilimc...@gmail.com
Re: mysql RAID
--- On Wed, 10/3/10, John G. Heim jh...@math.wisc.edu wrote: Hi, I am working on configuring a new hardware database server. I'm a little confused as to what to do about disk. We have several mysql databases but by far the 2 most active are spamassassin bayesian rules and horde3/imp web mail. Both do a lot of updates. The bayesian rules are added to each time a spam message comes in for any of our 200 users. And the horde3/imp writes address book updates and preferences quite often. I have read (and have been told) to stay away from RAID-5 for update-intensive systems. Are there performance concerns with RAID-10 as well? We will be buying from Dell (done deal for reasons too complicated to go into) and the disks they're selling are 146 Gb. I can get up to 8 of them in the server we're buying. I asked them about just getting 2 big disks and going with RAID-1. My understanding is that with RAID-10, the system can do multiple reads and writes simultaneously so throughput is improved oversystems w/o RAID or with RAID-1. But the same logic would apply to RAID-5 only it doesn't work out that way. I just want to make sure I'm configuring this system correctly before I order it. As dan already stated, the write penalty of raid 5 doesn't really make it a good fit for databases, go with raid 10. Like you for reasons beyond my control I've been stuck with dell hardware, just beware of dell raid controllers. The perc6 isn't too bad, however I still swap them out for more capable controllers. Pretty much everything before the perc6 is complete junk. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysql RAID
Hi, I am working on configuring a new hardware database server. I'm a little confused as to what to do about disk. We have several mysql databases but by far the 2 most active are spamassassin bayesian rules and horde3/imp web mail. Both do a lot of updates. The bayesian rules are added to each time a spam message comes in for any of our 200 users. And the horde3/imp writes address book updates and preferences quite often. I have read (and have been told) to stay away from RAID-5 for update-intensive systems. Are there performance concerns with RAID-10 as well? We will be buying from Dell (done deal for reasons too complicated to go into) and the disks they're selling are 146 Gb. I can get up to 8 of them in the server we're buying. I asked them about just getting 2 big disks and going with RAID-1. My understanding is that with RAID-10, the system can do multiple reads and writes simultaneously so throughput is improved oversystems w/o RAID or with RAID-1. But the same logic would apply to RAID-5 only it doesn't work out that way. I just want to make sure I'm configuring this system correctly before I order it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql RAID
In the last episode (Mar 10), John G. Heim said: I have read (and have been told) to stay away from RAID-5 for update-intensive systems. Are there performance concerns with RAID-10 as well? We will be buying from Dell (done deal for reasons too complicated to go into) and the disks they're selling are 146 Gb. I can get up to 8 of them in the server we're buying. I asked them about just getting 2 big disks and going with RAID-1. My understanding is that with RAID-10, the system can do multiple reads and writes simultaneously so throughput is improved oversystems w/o RAID or with RAID-1. But the same logic would apply to RAID-5 only it doesn't work out that way. RAID-5 has an extra penalty on small random writes due to the I/O required to maintain the parity blocks (it does 2 reads and 2 writes for every write your app does). RAID-10 is just a mirror so it doesn't have to worry about that. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql RAID
On Wed, March 10, 2010 09:04, Dan Nelson wrote: In the last episode (Mar 10), John G. Heim said: I have read (and have been told) to stay away from RAID-5 for update-intensive systems. Are there performance concerns with RAID-10 as well? We will be buying from Dell (done deal for reasons too complicated to go into) and the disks they're selling are 146 Gb. I can get up to 8 of them in the server we're buying. I asked them about just getting 2 big disks and going with RAID-1. My understanding is that with RAID-10, the system can do multiple reads and writes simultaneously so throughput is improved oversystems w/o RAID or with RAID-1. But the same logic would apply to RAID-5 only it doesn't work out that way. RAID-5 has an extra penalty on small random writes due to the I/O required to maintain the parity blocks (it does 2 reads and 2 writes for every write your app does). RAID-10 is just a mirror so it doesn't have to worry about that. -- Dan Nelson dnel...@allantgroup.com If you can get the disk cheap you might want to get one or two extras and keep them as spares. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Virtual servers, Raid 10 or Raid 01 - your opinions?
Hi, I do get two new Sun Fire X4170 servers with 8 SAS 300GB HDs, 24 GB RAM each. Right now, we do not have a lot of data in our databases (5 small LAMP servers), which should be consolidated. (The second Sun Fire X4170 will be the Webserver-Sun, the DB-Traffic will have his own gbit switch (Webserver-Sun - MySQL-Sun) ) There are more reads than writes. To simplify matters, I'd like to use virtual MySQL-Servers (Master and Slave) on the sun and use one large raid. Also I was thinking, to use one RAID 1 for the virtualisation-system and the other six harddisks for the virtual servers. What RAID would be the better choice? 10 or 01? What do you think? Thanks for any suggestions and best regards, Götz -- Götz Reinicke IT-Koordinator Tel. +49 7141 969 420 Fax +49 7141 969 55 420 E-Mail goetz.reini...@filmakademie.de Filmakademie Baden-Württemberg GmbH Akademiehof 10 71638 Ludwigsburg www.filmakademie.de Eintragung Amtsgericht Stuttgart HRB 205016 Vorsitzende des Aufsichtsrats: Prof. Dr. Claudia Hübner Staatsrätin für Demographischen Wandel und für Senioren im Staatsministerium Geschäftsführer: Prof. Thomas Schadt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Virtual servers, Raid 10 or Raid 01 - your opinions?
Obviously raid 10 would be better choice!. Recommended for safety and performance. Thanks, Krishna Ch. Prajapati On Wed, Nov 25, 2009 at 4:34 PM, Götz Reinicke - IT-Koordinator goetz.reini...@filmakademie.de wrote: Hi, I do get two new Sun Fire X4170 servers with 8 SAS 300GB HDs, 24 GB RAM each. Right now, we do not have a lot of data in our databases (5 small LAMP servers), which should be consolidated. (The second Sun Fire X4170 will be the Webserver-Sun, the DB-Traffic will have his own gbit switch (Webserver-Sun - MySQL-Sun) ) There are more reads than writes. To simplify matters, I'd like to use virtual MySQL-Servers (Master and Slave) on the sun and use one large raid. Also I was thinking, to use one RAID 1 for the virtualisation-system and the other six harddisks for the virtual servers. What RAID would be the better choice? 10 or 01? What do you think? Thanks for any suggestions and best regards, Götz -- Götz Reinicke IT-Koordinator Tel. +49 7141 969 420 Fax +49 7141 969 55 420 E-Mail goetz.reini...@filmakademie.de Filmakademie Baden-Württemberg GmbH Akademiehof 10 71638 Ludwigsburg www.filmakademie.de Eintragung Amtsgericht Stuttgart HRB 205016 Vorsitzende des Aufsichtsrats: Prof. Dr. Claudia Hübner Staatsrätin für Demographischen Wandel und für Senioren im Staatsministerium Geschäftsführer: Prof. Thomas Schadt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: Virtual servers, Raid 10 or Raid 01 - your opinions?
Decidedly RAID 10 - that is, a concatenation of mirrors. You get a lot more redundancy that way. Raid 01: [A+B+C+D] [E+F+G+H] Here, a single disk lost in each concatenation (so two in total) loses you the set. Probability is 1/8 * 4/7 (0.0714). Raid 10: [A] [B] [C] [D] [E] [F] [G] [H] Here, you will only lose your data if the exact two disks that make up a single mirror die. Probability is 1/8 * 1/7 (0.0178). On Wed, Nov 25, 2009 at 12:04 PM, Götz Reinicke - IT-Koordinator goetz.reini...@filmakademie.de wrote: Hi, I do get two new Sun Fire X4170 servers with 8 SAS 300GB HDs, 24 GB RAM each. Right now, we do not have a lot of data in our databases (5 small LAMP servers), which should be consolidated. (The second Sun Fire X4170 will be the Webserver-Sun, the DB-Traffic will have his own gbit switch (Webserver-Sun - MySQL-Sun) ) There are more reads than writes. To simplify matters, I'd like to use virtual MySQL-Servers (Master and Slave) on the sun and use one large raid. Also I was thinking, to use one RAID 1 for the virtualisation-system and the other six harddisks for the virtual servers. What RAID would be the better choice? 10 or 01? What do you think? Thanks for any suggestions and best regards, Götz -- Götz Reinicke IT-Koordinator Tel. +49 7141 969 420 Fax +49 7141 969 55 420 E-Mail goetz.reini...@filmakademie.de Filmakademie Baden-Württemberg GmbH Akademiehof 10 71638 Ludwigsburg www.filmakademie.de Eintragung Amtsgericht Stuttgart HRB 205016 Vorsitzende des Aufsichtsrats: Prof. Dr. Claudia Hübner Staatsrätin für Demographischen Wandel und für Senioren im Staatsministerium Geschäftsführer: Prof. Thomas Schadt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
AW: Virtual servers, Raid 10 or Raid 01 - your opinions?
-Ursprüngliche Nachricht- Von: Götz Reinicke - IT-Koordinator [mailto:goetz.reini...@filmakademie.de] Gesendet: Mittwoch, 25. November 2009 12:05 An: mysql@lists.mysql.com Betreff: Virtual servers, Raid 10 or Raid 01 - your opinions? Hi, Hi, I do get two new Sun Fire X4170 servers with 8 SAS 300GB HDs, 24 GB RAM each. Right now, we do not have a lot of data in our databases (5 small LAMP servers), which should be consolidated. (The second Sun Fire X4170 will be the Webserver-Sun, the DB-Traffic will have his own gbit switch (Webserver-Sun - MySQL-Sun) ) There are more reads than writes. To simplify matters, I'd like to use virtual MySQL-Servers (Master and Slave) on the sun and use one large raid. Putting the question a bit higher for better understanding. What RAID would be the better choice? 10 or 01? Generally RAID 10 is more robust and fault tolerant. Some things depending on the controller if it's smart or not. But rebuilding the raid in case of an error is much faster with RAID10. If you want to know more read http://www.pcguide.com/ref/hdd/perf/raid/levels/multXY-c.html Concl.: RAID 10 is a good choice! Also I was thinking, to use one RAID 1 for the virtualisation-system and the other six harddisks for the virtual servers. It's not fully clear to me what you mean with virtual MySQL-Servers. You should buy 2 * 73k 15RPM for the OS to have the mysql data separated. If this is not practical/possible you have to put the os and mysqldata together. Not nice, but well. Nothing is perfect ;) 2 RAID10 with 4*300GB. ~ 600GB cap. each mount FIRST_RAID10 /data/mysql_master mount SECOND_RAID10 /data/mysql_slave ifconfig bond0:0|eth1:0 virtualip netmask ifconfig bond0:1|eth1:1 virtualip1 netmask /etc/my.cnf [mysqld101] bind-address virtualip1 datadir = /data/mysql_master ... [mysqld102] bind-address virtualip2 data-dir = /data/mysql_slave Is this what you are searching for? HTH! Regards Majk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: AW: Virtual servers, Raid 10 or Raid 01 - your opinions?
majk.sko...@eventim.de schrieb: -Ursprüngliche Nachricht- Von: Götz Reinicke - IT-Koordinator [mailto:goetz.reini...@filmakademie.de] Gesendet: Mittwoch, 25. November 2009 12:05 An: mysql@lists.mysql.com Betreff: Virtual servers, Raid 10 or Raid 01 - your opinions? Hi, Hi, I do get two new Sun Fire X4170 servers with 8 SAS 300GB HDs, 24 GB RAM each. Right now, we do not have a lot of data in our databases (5 small LAMP servers), which should be consolidated. (The second Sun Fire X4170 will be the Webserver-Sun, the DB-Traffic will have his own gbit switch (Webserver-Sun - MySQL-Sun) ) There are more reads than writes. To simplify matters, I'd like to use virtual MySQL-Servers (Master and Slave) on the sun and use one large raid. Putting the question a bit higher for better understanding. What RAID would be the better choice? 10 or 01? Generally RAID 10 is more robust and fault tolerant. Some things depending on the controller if it's smart or not. But rebuilding the raid in case of an error is much faster with RAID10. If you want to know more read http://www.pcguide.com/ref/hdd/perf/raid/levels/multXY-c.html Concl.: RAID 10 is a good choice! Thanks. Also I was thinking, to use one RAID 1 for the virtualisation-system and the other six harddisks for the virtual servers. It's not fully clear to me what you mean with virtual MySQL-Servers. I'll install Citrix Xen Server and than two or more virtual Redhat EL / Centos/ Servers on one SUN Fire server. (Next year I'll get some more physical servers for clustering/failover/loadbalance) So I'll use two disks for the xen server software and one raid 10 for the virtual machines. HTH! It did. Thanks. /Götz -- Götz Reinicke IT-Koordinator Tel. +49 7141 969 420 Fax +49 7141 969 55 420 E-Mail goetz.reini...@filmakademie.de Filmakademie Baden-Württemberg GmbH Akademiehof 10 71638 Ludwigsburg www.filmakademie.de Eintragung Amtsgericht Stuttgart HRB 205016 Vorsitzende des Aufsichtsrats: Prof. Dr. Claudia Hübner Staatsrätin für Demographischen Wandel und für Senioren im Staatsministerium Geschäftsführer: Prof. Thomas Schadt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: AW: Virtual servers, Raid 10 or Raid 01 - your opinions?
I'm not really clear on what you hope to accomplish by putting two virtual servers on the same host ? On Wed, Nov 25, 2009 at 1:35 PM, Götz Reinicke - IT-Koordinator goetz.reini...@filmakademie.de wrote: majk.sko...@eventim.de schrieb: -Ursprüngliche Nachricht- Von: Götz Reinicke - IT-Koordinator [mailto:goetz.reini...@filmakademie.de] Gesendet: Mittwoch, 25. November 2009 12:05 An: mysql@lists.mysql.com Betreff: Virtual servers, Raid 10 or Raid 01 - your opinions? Hi, Hi, I do get two new Sun Fire X4170 servers with 8 SAS 300GB HDs, 24 GB RAM each. Right now, we do not have a lot of data in our databases (5 small LAMP servers), which should be consolidated. (The second Sun Fire X4170 will be the Webserver-Sun, the DB-Traffic will have his own gbit switch (Webserver-Sun - MySQL-Sun) ) There are more reads than writes. To simplify matters, I'd like to use virtual MySQL-Servers (Master and Slave) on the sun and use one large raid. Putting the question a bit higher for better understanding. What RAID would be the better choice? 10 or 01? Generally RAID 10 is more robust and fault tolerant. Some things depending on the controller if it's smart or not. But rebuilding the raid in case of an error is much faster with RAID10. If you want to know more read http://www.pcguide.com/ref/hdd/perf/raid/levels/multXY-c.html Concl.: RAID 10 is a good choice! Thanks. Also I was thinking, to use one RAID 1 for the virtualisation-system and the other six harddisks for the virtual servers. It's not fully clear to me what you mean with virtual MySQL-Servers. I'll install Citrix Xen Server and than two or more virtual Redhat EL / Centos/ Servers on one SUN Fire server. (Next year I'll get some more physical servers for clustering/failover/loadbalance) So I'll use two disks for the xen server software and one raid 10 for the virtual machines. HTH! It did. Thanks. /Götz -- Götz Reinicke IT-Koordinator Tel. +49 7141 969 420 Fax +49 7141 969 55 420 E-Mail goetz.reini...@filmakademie.de Filmakademie Baden-Württemberg GmbH Akademiehof 10 71638 Ludwigsburg www.filmakademie.de Eintragung Amtsgericht Stuttgart HRB 205016 Vorsitzende des Aufsichtsrats: Prof. Dr. Claudia Hübner Staatsrätin für Demographischen Wandel und für Senioren im Staatsministerium Geschäftsführer: Prof. Thomas Schadt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
Re: Raid level suggestions for mysql-server
In one of the more effective high-loads shops I have worked in, we deployed RAID 1 for logs and RAID 10 for data. The number of disks we put into those RAID 10's depended on anticipated load of the specific application. We often found ourselves needing additional spindles to meet high I/O needs, often leaving a lot of unused raw storage space. The system was usually deployed on a single disk, being more-or-less static and easily reproducible.. Once you are booted and primed, the system disk barely gets touched.. all the important stuff is in RAM. - michael dykman On Sun, Nov 1, 2009 at 2:42 PM, Per Jessen p...@computer.org wrote: Gšötz Reinicke - IT Koordinator wrote: Hi, soon I'll get a SUN X4170 with 8*2,5 SAS 300 GB harddisks. (24 GB RAM) This system could be our new central mysql-server for some LAMP-systems. (right now about 50 GB mysql data total, roughly 60-70% reads.) What would be a good raid-Layout for the server? I was thinking of one large 1+0 or 0+1 as 1.2TB would be more than enought. Or may be I do split things up like this: one raid 1 for the system, one raid 1 for logfiles, one raid 1+0/0+1 for the database. Any suggestions? I have a very similar HP box with 8 drives too - I've got it running one RAID1 (2x72Gb) for system and one RAID6 (6x146Gb) for data. /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Raid level suggestions for mysql-server
Hi, soon I'll get a SUN X4170 with 8*2,5 SAS 300 GB harddisks. (24 GB RAM) This system could be our new central mysql-server for some LAMP-systems. (right now about 50 GB mysql data total, roughly 60-70% reads.) What would be a good raid-Layout for the server? I was thinking of one large 1+0 or 0+1 as 1.2TB would be more than enought. Or may be I do split things up like this: one raid 1 for the system, one raid 1 for logfiles, one raid 1+0/0+1 for the database. Any suggestions? Thanks and best regards, Götz -- Götz Reinicke IT-Koordinator Tel. +49 7141 969 420 Fax +49 7141 969 55 420 E-Mail goetz.reini...@filmakademie.de Filmakademie Baden-Württemberg GmbH Akademiehof 10 71638 Ludwigsburg www.filmakademie.de Eintragung Amtsgericht Stuttgart HRB 205016 Vorsitzende des Aufsichtsrats: Prof. Dr. Claudia Hübner Staatsrätin für Demographischen Wandel und für Senioren im Staatsministerium Geschäftsführer: Prof. Thomas Schadt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Raid level suggestions for mysql-server
Götz Reinicke - IT Koordinator wrote: Hi, soon I'll get a SUN X4170 with 8*2,5 SAS 300 GB harddisks. (24 GB RAM) This system could be our new central mysql-server for some LAMP-systems. (right now about 50 GB mysql data total, roughly 60-70% reads.) What would be a good raid-Layout for the server? I was thinking of one large 1+0 or 0+1 as 1.2TB would be more than enought. Or may be I do split things up like this: one raid 1 for the system, one raid 1 for logfiles, one raid 1+0/0+1 for the database. Any suggestions? I have a very similar HP box with 8 drives too - I've got it running one RAID1 (2x72Gb) for system and one RAID6 (6x146Gb) for data. /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RAID-10 perf numbers
Does anybody have any RAID-10 sysbench fileio numbers for random writes with any number of disks(4/6/8/12...) to compare with write cache(512,256,...) backed by BBU.. I really appreciate your nos.. or whats the decent requests/sec for pure rndwrs ?
Re: Best RAID for a DB + LVM?
Hi Waynn, On Sun, Feb 22, 2009 at 7:46 AM, Waynn Lue waynn...@gmail.com wrote: I currently have a RAID 5 setup for our database server. Our space is running out, so I'm looking to increase the disk space. Since I'm doing that anyway, I decided to re-evaluate our current disk array. I was told that RAID 5 isn't a good choice for databases since it's slower to write. In theory it is. It really depends on the controller though. Some companies put more effort into optimizing RAID 5 than RAID 10, and the performance can actually be comparable! At my last job we used all Dell servers with PERC6i controllers and RAID 5, and we got a lot of mileage out of it. But I have never benchmarked that setup against RAID 10 on the same controller, because I didn't do benchmarks there, and now that I'm in a job where I do benchmarks, I've never done it for anything but RAID 10. It would be interesting to benchmark the two setups and see. One could get a box with 4 disks and configure it one way, then the other, and run sysbench or iozone or something to see what comes out. In addition, I've also been considering setting up LVM to take quick db snapshots, after reading various links on the web (and posts to this list). So on to the questions! First, if that's what I eventually want to do (get a new RAID server with LVM), do I need to do anything special to set up LVM Not really. Just use pvcreate, vgcreate and then lvcreate. Leave some space unallocated at the last step. You need some space to create snapshot volumes. on the new system? Second, what is a good RAID setup for databases? RAID Theoretically, for max performance you want RAID 10, and if money is a concern, well, see if a RAID 5 setup is good enough. 10? 0+1? Third, I have the choice of using SATA or SCSI in conjuction with the RAID drives I choose. How much of a difference is there in using SATA instead of SCSI, especially in light of whatever RAID I end up going with? I haven't gotten to benchmark that either, but others posted on that topic already. Baron -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Best RAID for a DB + LVM?
SCSI/FC/SAS drives are absolutely faster, especially at 15kRPM. Your requirement of IOPS vs Usable space may actually make it less expensive to use FC drives (ex. if you don't have to retain much data but need it to be really fast _all_ the time). This can be especially true if you take datacenter costs into consideration. There's also a difference between Enterprise SATA drives and regular Desktop SATA drives. The Enterprise class drives tend to be optimized for workloads that a database may throw at them. One thing to keep in mind if your dataset isn't terribly large would be to cram as much RAM in the host as you can. If you've only got a portion of your data that's heavily accessed, keeping it in RAM would be ideal. -Aaron On Mon, Feb 23, 2009 at 9:17 AM, Brent Baisley brentt...@gmail.com wrote: SCSI isn't necessarily faster now. The big difference used to be SCSI's support for command queueing, which is why it was faster in multi-user environments. Command queueing is now fairly common in SATA drives. The highest end SCSI is probably still faster than the highest end SATA, but you will have less disk space and it will cost much more. I would recommend using one of the RAID in a box solution. They have big caches for the whole RAID and they are optimized to the controllers. If money isn't really an issue, you may look into something like NetApp. That would have everything you need. Brent Baisley On Mon, Feb 23, 2009 at 6:58 AM, Andy Smith a.sm...@ukgrid.net wrote: What RAID level to use, whether to use SCSI or SATA etc are all pretty much how long is a piece of string? questions. If you have a really high end hardware array RAID 5 may be faster than RAID1+0 is on a cheaper system. Basically RAID 5 = slower SATA = slower RAID 1+0 = faster SCSI = faster more physical disks = faster more expensive controller = faster ;) If you want to compare specific hardware you'll need to get your hands on it or find someone else who has already done a comparison. But it will make a huge difference to performance what disk array you have hooked up, just depends how much you want to spend Quoting Waynn Lue waynn...@gmail.com: I currently have a RAID 5 setup for our database server. Our space is running out, so I'm looking to increase the disk space. Since I'm doing that anyway, I decided to re-evaluate our current disk array. I was told that RAID 5 isn't a good choice for databases since it's slower to write. In addition, I've also been considering setting up LVM to take quick db snapshots, after reading various links on the web (and posts to this list). So on to the questions! First, if that's what I eventually want to do (get a new RAID server with LVM), do I need to do anything special to set up LVM on the new system? Second, what is a good RAID setup for databases? RAID 10? 0+1? Third, I have the choice of using SATA or SCSI in conjuction with the RAID drives I choose. How much of a difference is there in using SATA instead of SCSI, especially in light of whatever RAID I end up going with? Thanks for any insights, Waynn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=brentt...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=aaronb...@gmail.com
Re: Best RAID for a DB + LVM?
What RAID level to use, whether to use SCSI or SATA etc are all pretty much how long is a piece of string? questions. If you have a really high end hardware array RAID 5 may be faster than RAID1+0 is on a cheaper system. Basically RAID 5 = slower SATA = slower RAID 1+0 = faster SCSI = faster more physical disks = faster more expensive controller = faster ;) If you want to compare specific hardware you'll need to get your hands on it or find someone else who has already done a comparison. But it will make a huge difference to performance what disk array you have hooked up, just depends how much you want to spend Quoting Waynn Lue waynn...@gmail.com: I currently have a RAID 5 setup for our database server. Our space is running out, so I'm looking to increase the disk space. Since I'm doing that anyway, I decided to re-evaluate our current disk array. I was told that RAID 5 isn't a good choice for databases since it's slower to write. In addition, I've also been considering setting up LVM to take quick db snapshots, after reading various links on the web (and posts to this list). So on to the questions! First, if that's what I eventually want to do (get a new RAID server with LVM), do I need to do anything special to set up LVM on the new system? Second, what is a good RAID setup for databases? RAID 10? 0+1? Third, I have the choice of using SATA or SCSI in conjuction with the RAID drives I choose. How much of a difference is there in using SATA instead of SCSI, especially in light of whatever RAID I end up going with? Thanks for any insights, Waynn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Best RAID for a DB + LVM?
SCSI isn't necessarily faster now. The big difference used to be SCSI's support for command queueing, which is why it was faster in multi-user environments. Command queueing is now fairly common in SATA drives. The highest end SCSI is probably still faster than the highest end SATA, but you will have less disk space and it will cost much more. I would recommend using one of the RAID in a box solution. They have big caches for the whole RAID and they are optimized to the controllers. If money isn't really an issue, you may look into something like NetApp. That would have everything you need. Brent Baisley On Mon, Feb 23, 2009 at 6:58 AM, Andy Smith a.sm...@ukgrid.net wrote: What RAID level to use, whether to use SCSI or SATA etc are all pretty much how long is a piece of string? questions. If you have a really high end hardware array RAID 5 may be faster than RAID1+0 is on a cheaper system. Basically RAID 5 = slower SATA = slower RAID 1+0 = faster SCSI = faster more physical disks = faster more expensive controller = faster ;) If you want to compare specific hardware you'll need to get your hands on it or find someone else who has already done a comparison. But it will make a huge difference to performance what disk array you have hooked up, just depends how much you want to spend Quoting Waynn Lue waynn...@gmail.com: I currently have a RAID 5 setup for our database server. Our space is running out, so I'm looking to increase the disk space. Since I'm doing that anyway, I decided to re-evaluate our current disk array. I was told that RAID 5 isn't a good choice for databases since it's slower to write. In addition, I've also been considering setting up LVM to take quick db snapshots, after reading various links on the web (and posts to this list). So on to the questions! First, if that's what I eventually want to do (get a new RAID server with LVM), do I need to do anything special to set up LVM on the new system? Second, what is a good RAID setup for databases? RAID 10? 0+1? Third, I have the choice of using SATA or SCSI in conjuction with the RAID drives I choose. How much of a difference is there in using SATA instead of SCSI, especially in light of whatever RAID I end up going with? Thanks for any insights, Waynn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=brentt...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Best RAID for a DB + LVM?
I currently have a RAID 5 setup for our database server. Our space is running out, so I'm looking to increase the disk space. Since I'm doing that anyway, I decided to re-evaluate our current disk array. I was told that RAID 5 isn't a good choice for databases since it's slower to write. In addition, I've also been considering setting up LVM to take quick db snapshots, after reading various links on the web (and posts to this list). So on to the questions! First, if that's what I eventually want to do (get a new RAID server with LVM), do I need to do anything special to set up LVM on the new system? Second, what is a good RAID setup for databases? RAID 10? 0+1? Third, I have the choice of using SATA or SCSI in conjuction with the RAID drives I choose. How much of a difference is there in using SATA instead of SCSI, especially in light of whatever RAID I end up going with? Thanks for any insights, Waynn
Re: Best RAID for a DB + LVM?
I work for a high-volume web site and we use nothing but RAID 10 on all databases which requires a minimum of 4 disks. The write penalty for raid 5 is just too high for our application. Much of that space goes unused, but we need the stripe to keep up with the I/O. - michael dykman On Sun, Feb 22, 2009 at 7:46 AM, Waynn Lue waynn...@gmail.com wrote: I currently have a RAID 5 setup for our database server. Our space is running out, so I'm looking to increase the disk space. Since I'm doing that anyway, I decided to re-evaluate our current disk array. I was told that RAID 5 isn't a good choice for databases since it's slower to write. In addition, I've also been considering setting up LVM to take quick db snapshots, after reading various links on the web (and posts to this list). So on to the questions! First, if that's what I eventually want to do (get a new RAID server with LVM), do I need to do anything special to set up LVM on the new system? Second, what is a good RAID setup for databases? RAID 10? 0+1? Third, I have the choice of using SATA or SCSI in conjuction with the RAID drives I choose. How much of a difference is there in using SATA instead of SCSI, especially in light of whatever RAID I end up going with? Thanks for any insights, Waynn -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysql on raid 1 or raid 5?
hi all, currently, i have a machine with raid 1 and raid 5 (backplane). in order to boost up the mysql performance, does it advise install /var/ (mysql data directory) into raid 1 or raid 5? please advise. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql on raid 1 or raid 5?
On Thursday, 20 October 2005 at 16:57:36 +0800, Hiu Yen Onn wrote: hi all, currently, i have a machine with raid 1 and raid 5 (backplane). in order to boost up the mysql performance, does it advise install /var/ (mysql data directory) into raid 1 or raid 5? please advise. thanks RAID-5 optimizes storage space, not performance. If you're looking for performance, RAID-1 is a better choice, though obviously there are lots of things to consider. In particular, if you're striping (RAID-10), choose as large a stripe size as possible. And of course it can use nearly twice as much disk space. Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID stripe size recommendations
On Sep 28, 2005, at 5:05 PM, Atle Veka wrote: I am planning on running some tests on a SATA server with a 3ware 9000 series RAID card to see if there's a stripe size that performs better than This might be able to help you out: http://hashmysql.org/index.php?title=Opteron_HOWTO These are difficult questions. you also should figure out what the block size of your filesystem is. I think ideally it should be N*stripe_size where N is the number of disks you have. This way you can read one block as a set of N IOs in parallel across your disks. Also note that SATA is probably not what you want if you need decent IO. SCSI will still give you a win. Let us know what you find out... Check the archives too. I think there was some commentary about using a 16k strip and seeing a significant performance boost. Also some RAID controllers don't allow you to change the strip size. Moral of the story is that disk sucks... Disk is the new tape... Kevin Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://www.feedblog.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RAID stripe size recommendations
I am planning on running some tests on a SATA server with a 3ware 9000 series RAID card to see if there's a stripe size that performs better than the current setting (which I don't recall at the moment, probably whatever the max is). This will be RAID10 and our databases are either in the 500Mb or 10G range (both data and index). Most of the queries use proper indexing so there shouldn't be a need for full table scans. This is on a FreeBSD 4.10 system and so far the SATA+RAID10 performance is shitty compared to a single SCSI server. IO is constantly at or above the below and the OS is noticably laggy (which even at twice the IO is not noticable on a single SCSI disk): tty da0pass0 cpu tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id 08 16.00 2320 36.25 0.00 0 0.00 1 7 5 2 85 0 61 18.38 148 2.65 0.00 0 0.00 0 9 4 2 86 0 59 21.03 157 3.23 0.00 0 0.00 0 8 3 1 88 0 60 22.38 151 3.31 0.00 0 0.00 0 13 2 1 84 0 60 19.95 152 2.97 0.00 0 0.00 0 11 1 1 88 0 59 22.04 186 4.01 0.00 0 0.00 0 10 2 2 86 0 60 17.07 133 2.21 0.00 0 0.00 0 21 7 2 71 0 60 21.86 192 4.10 0.00 0 0.00 0 2 1 2 95 0 60 18.88 149 2.74 0.00 0 0.00 0 5 3 2 91 0 59 19.63 161 3.09 0.00 0 0.00 0 5 0 1 94 What are other users experiences with setups like this (stripe size, OS settings like sysctl, etc)? Is linux preferred, if so why? Thanks! Atle - Flying Crocodile Inc, Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RAID/MySQL configuration question
G'morning all! (Using Red Hat Linux Enterprise 4.1) I have a Dell PowerEdge 2800 with a PERC 4 RAID controller. The RAID controller has one RAID 1 mirror and one RAID 5 stripe volume created. We installed most of the OS stuff on the RAID 1 set and the /usr/local and /var on the RAID 5 set (since the Red Hat MySQL rpm puts the data directory under /var ). I'd like to know if there are any better ways to configure this (I can repartition and reinstall the OS, if necessary). background I'm having problems with a Tomcat application (OSP - ePortfolios) that uses a lot of disk space for uploaded files (under Tomcat directory which I install under /usr/local). Not sure how large the MySQL database will grow to be. I installed the Red Hat MySQL rpm, but not sure if it is RAID-aware and considering compiling MySQL from source ( --with raid ?). Or my problem may be with the MySQL Connector/J driver (which would be a question for the mysql-java list). The application builds and installs fine with no errors, but Tomcat only works for the static directories (i.e. /jsp-examples ) and not with the application that interacts with MySQL. I've installed this application successfully on an identical non-raid system. The only differenced between the two machines is that the problem child is RAID (configured as above) and the java sdk version changed from _08 to _09. /background 1) Best way to configure the RAID/partitions for best MySQL performance? 2) Is MySQL RAID-aware if not compiled: - - with raid? (unsure if the Red Hat rpm used that) 3) Is there a way to tell if a problem is specifically related to the MySQL Connector/J driver or a problem connecting to MySQL? (probably should direct that one to the mysql-java list, eh?) Thanks in advance for any help. This is the first RAID machine I've ever worked with. : \ Darren Addy University of Nebraska at Kearney -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID/MySQL configuration question
My guess is that the RAID has nothing to do with it -- it seems very unlikely. In any case, if you want top performance out of your raid, you may want to change things up. You'd get better performance if you didn't use RAID5. Use RAID1 or RAID10 for your data drives. RAID5 is slower than these other methods. Based on the information you've given, I'm assuming a few things: 1) your raid controller supports RAID10 2) you have an even number of drives. If this is the case, I would recreate the raid as a RAID10 (pair up your drives and then create a stripe out of the pairs). Then you can feel free to allocate space to whatever partition struction you need. On 8/25/05, Curious George [EMAIL PROTECTED] wrote: G'morning all! (Using Red Hat Linux Enterprise 4.1) I have a Dell PowerEdge 2800 with a PERC 4 RAID controller. The RAID controller has one RAID 1 mirror and one RAID 5 stripe volume created. We installed most of the OS stuff on the RAID 1 set and the /usr/local and /var on the RAID 5 set (since the Red Hat MySQL rpm puts the data directory under /var ). I'd like to know if there are any better ways to configure this (I can repartition and reinstall the OS, if necessary). background I'm having problems with a Tomcat application (OSP - ePortfolios) that uses a lot of disk space for uploaded files (under Tomcat directory which I install under /usr/local). Not sure how large the MySQL database will grow to be. I installed the Red Hat MySQL rpm, but not sure if it is RAID-aware and considering compiling MySQL from source ( --with raid ?). Or my problem may be with the MySQL Connector/J driver (which would be a question for the mysql-java list). The application builds and installs fine with no errors, but Tomcat only works for the static directories (i.e. /jsp-examples ) and not with the application that interacts with MySQL. I've installed this application successfully on an identical non-raid system. The only differenced between the two machines is that the problem child is RAID (configured as above) and the java sdk version changed from _08 to _09. /background 1) Best way to configure the RAID/partitions for best MySQL performance? 2) Is MySQL RAID-aware if not compiled: - - with raid? (unsure if the Red Hat rpm used that) 3) Is there a way to tell if a problem is specifically related to the MySQL Connector/J driver or a problem connecting to MySQL? (probably should direct that one to the mysql-java list, eh?) Thanks in advance for any help. This is the first RAID machine I've ever worked with. : \ Darren Addy University of Nebraska at Kearney -- 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: RAID/MySQL configuration question
On Thu, 25 Aug 2005, Gary Richardson wrote: My guess is that the RAID has nothing to do with it -- it seems very unlikely. In any case, if you want top performance out of your raid, you may want to change things up. You'd get better performance if you didn't use RAID5. Use RAID1 or RAID10 for your data drives. RAID5 is slower than these other methods. Based on the information you've given, I'm assuming a few things: 1) your raid controller supports RAID10 2) you have an even number of drives. If this is the case, I would recreate the raid as a RAID10 (pair up your drives and then create a stripe out of the pairs). Then you can feel free to allocate space to whatever partition struction you need. On 8/25/05, Curious George [EMAIL PROTECTED] wrote: G'morning all! (Using Red Hat Linux Enterprise 4.1) I have a Dell PowerEdge 2800 with a PERC 4 RAID controller. The RAID controller has one RAID 1 mirror and one RAID 5 stripe volume created. We installed most of the OS stuff on the RAID 1 set and the /usr/local and /var on the RAID 5 set (since the Red Hat MySQL rpm puts the data directory under /var ). use LVM to set things up, so you can resize later without taking the system down. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID, MySQL and SATA - benchmarks
I found the article very interesting. It seems they couldn't trash 3ware cards enough. We swear by 3ware cards -- other than the PCIX riser card issue, we haven't a single problem with them. Our production database server is running off of a RAID1 for the OS and a RAID10 for the data and every time we are doing schema maintenance or database migration, we are blown away by the speed of the machine. BTW, never use Western Digital Raptor drives. Some people may remember me posting about them around October. Out of 6 drives in the machine, we've had 10 failures. We're currently replacing the drives with Seagates. We'll take the 3K RPM hit for piece of mind. On Wed, 9 Mar 2005 17:04:54 +1100, Richard Dale [EMAIL PROTECTED] wrote: Tweakers.net has completed a comparison of 9 serial ATA RAID 0/1/5/10 controllers at: http://www.tweakers.net/reviews/557 There is a specific section on MySQL performance in the section: http://www.tweakers.net/reviews/557/25 Just thought these articles would be of interest to some (it's interesting to see the difference between single drive operations and multiple drive operations - up to 12 drives, with the different RAID levels). Here's my rough speed comparison based upon eyeballing the graphs. Some controllers were better than others so this represents a rough average of the entire set of controllers: Single drive - 1.0 RAID 1 - 2 disks - 1.4 RAID5 - 3 disks - 1.7 RAID5 - 4 disks - 2.0 RAID10 - 4 disks - 2.0 RAID5 - 6 disks - 2.3 RAID5 - 8 disks - 2.4 RAID5 - 10 disks - 2.9 RAID5 - 12 disks - 3.1 The article also highlighted the difference between the reliable write-through mode and the write-back mode. In write-through mode, performance is degraded by approximately 50%. Clearly if you want reliability, a controller with a battery backup is highly recommended. On the issue of SCSI version SATA performance, it would appear that SCSI still performas somewhat better (about 20% more transactions but the test was comparing 15K RPM SCSI drives to 10K RPM SATA drives) but the reduced cost of SATA drives allows you to add more drives to achieve the same performance levels at lesser cost. With Serial ATA II drives around the corner (with Native Command Queueing) then I think we'll find SATA will take a much bigger lead in database performance. Really nice work from tweakers.net - would have been interesting to see the Linux performance too though. Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- 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]
RAID, MySQL and SATA - benchmarks
Tweakers.net has completed a comparison of 9 serial ATA RAID 0/1/5/10 controllers at: http://www.tweakers.net/reviews/557 There is a specific section on MySQL performance in the section: http://www.tweakers.net/reviews/557/25 Just thought these articles would be of interest to some (it's interesting to see the difference between single drive operations and multiple drive operations - up to 12 drives, with the different RAID levels). Here's my rough speed comparison based upon eyeballing the graphs. Some controllers were better than others so this represents a rough average of the entire set of controllers: Single drive - 1.0 RAID 1 - 2 disks - 1.4 RAID5 - 3 disks - 1.7 RAID5 - 4 disks - 2.0 RAID10 - 4 disks - 2.0 RAID5 - 6 disks - 2.3 RAID5 - 8 disks - 2.4 RAID5 - 10 disks - 2.9 RAID5 - 12 disks - 3.1 The article also highlighted the difference between the reliable write-through mode and the write-back mode. In write-through mode, performance is degraded by approximately 50%. Clearly if you want reliability, a controller with a battery backup is highly recommended. On the issue of SCSI version SATA performance, it would appear that SCSI still performas somewhat better (about 20% more transactions but the test was comparing 15K RPM SCSI drives to 10K RPM SATA drives) but the reduced cost of SATA drives allows you to add more drives to achieve the same performance levels at lesser cost. With Serial ATA II drives around the corner (with Native Command Queueing) then I think we'll find SATA will take a much bigger lead in database performance. Really nice work from tweakers.net - would have been interesting to see the Linux performance too though. Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RAID Question
Paul: Thank you for your response. Another question: Would it cause a problem if I configure --with-raid and then never use it in any programming? Will it add any overhead? It would help if this feature is available for future use. Thanks 2nd time. Kirti -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 10, 2004 8:18 PM To: Kirti S. Bajwa; [EMAIL PROTECTED] Subject: RE: RAID Question At 15:19 -0500 11/10/04, Kirti S. Bajwa wrote: Hello List: System: RH9, MySQL 4.1.7 I am in the process of re-setting up (I have test setup 4-5 times) a data server with the above software. This server consists of 2-CPU (Intel) RAID-1, 1-40GB IDE HDD for O/S 2-250GB IDE HDD for storing data. 250 GB IDE HDD are mirrored (RAID-1). Previously, I setup RAID while setting up RH9. Recently, while reviewing the MySQL, documentation, I noticed the following directive for configure command: # ./configure -prefix=/usr/local/mysql -with-raid While researching on GOOGLE, I did find quite a bit of information on MySQL RAID HOWTO search, but nothing to answer my question. Can someone explain how the aboce directive in configure works? In my setup, do I need the above directive as shown? It doesn't have anything to do with hardware raid. It enables support for the RAID table options that allow you to split the data file for MyISAM tables into several files. See the description for RAID_TYPE, RAID_CHUNKS, and RAID_CHUNKSIZE options at: http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- 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: RAID Question
Hello List: System: RH9, MySQL 4.1.7 I am in the process of re-setting up (I have test setup 4-5 times) a data server with the above software. This server consists of 2-CPU (Intel) RAID-1, 1-40GB IDE HDD for O/S 2-250GB IDE HDD for storing data. 250 GB IDE HDD are mirrored (RAID-1). Previously, I setup RAID while setting up RH9. Recently, while reviewing the MySQL, documentation, I noticed the following directive for configure command: # ./configure prefix=/usr/local/mysql with-raid While researching on GOOGLE, I did find quite a bit of information on MySQL RAID HOWTO search, but nothing to answer my question. Can someone explain how the aboce directive in configure works? In my setup, do I need the above directive as shown? Thanks. Kirti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL over Raid Mirror
The problem with putting the OS on the same drive as the database is that they will be competing for the drive resources. On the flip side, following the safety first rule will make sure your system will stay up if a drive fails. I always go for safety first. If you are going to put everything on the mirrored drives, I would make sure you have plenty of RAM. Your worst case scenario is having the OS doing a lot of paging, due too lack of RAM, while your database is very active. The OS will always use the disk (i.e. writing logs), but if you can keep the OS activity to minimum, your performance hit should be negligible. Get all the performance you can out of tweaking the MySQL and other settings first, then you can resort to the hardware. I assume you are using software mirroring and perhaps even just one IDE controller for both drives (master/slave setup). Both give you performance hits, especially having one controller. But it's definitely a workable setup that still should give you decent performance. On Jul 28, 2004, at 3:30 PM, Rick Dwyer wrote: Hello all. I want to install MySQL on a RAID mirror drive using two ATA 125GB for the mirror. I know in a webserver config, it's best to put the boot OS on one drive and the shared serving folder on the RAID mirror drive. Under a MySQL server, if I install the OS and MySQL all on the mirror drive ( I can get rid of the standard 80gb drive the computer ships with) are there draw backs or problems with such a config??? (ex. speed) My config is a Mac Dual Processor G4 1.25 Ghtz running 10.3 with MysQL 4.0.15. both drives are 120GB 7200RM IDE Hitachi Deskstar's. Thanks. Rick -- 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: Setting up MySQL on Raid Mirror
Years ago I designed a system using Lasso (3.5 I think) with FileMaker running on OS 9 on a 300Mhz G3 that handled about 4K hits per day, most hits during typical works hours. Your system is definitely not in the high-performance realm (no offense) and your hardware should handle the load quite well. Of course, that's a bold statement not knowing the size of your data or complexity of your queries. On Jul 28, 2004, at 3:59 PM, Rick Dwyer wrote: Richard, what would you define as super-high performance? This MySQL database server will serve as the backend for a Lasso/ OS X Apache webserver handling thousands of hits per day. Will installing OS and MySQL on the Mirrored Drive be within the scope of that type of activity? The database basically collects customer data. Thanks. Rick -- 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: Setting up MySQL on Raid Mirror
Rick Dwyer [EMAIL PROTECTED] wrote: I want to install MySQL on a RAID mirror drive using two ATA 125GB for the mirror. I know in a webserver config, it's best to put the boot OS on one drive and the shared serving folder on the RAID mirror drive. Under a MySQL server, if I install the OS and MySQL all on the mirror drive ( I can get rid of the standard 80gb drive the computer ships with) are there draw backs or problems with such a config??? (ex. speed) Speed is always a drawback. We, at Ensita.NET, install all our servers completely on RAID1 (slow but reliable), RAID1+0 (fast and reliable), or RAID5 (something in between) arrays, including OS, excluding /tmp and swap :) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setting up MySQL on Raid Mirror
Hello all. I want to install MySQL on a RAID mirror drive using two ATA 125GB for the mirror. I know in a webserver config, it's best to put the boot OS on one drive and the shared serving folder on the RAID mirror drive. Under a MySQL server, if I install the OS and MySQL all on the mirror drive ( I can get rid of the standard 80gb drive the computer ships with) are there draw backs or problems with such a config??? (ex. speed) Thanks. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL over Raid Mirror
Hello all. I want to install MySQL on a RAID mirror drive using two ATA 125GB for the mirror. I know in a webserver config, it's best to put the boot OS on one drive and the shared serving folder on the RAID mirror drive. Under a MySQL server, if I install the OS and MySQL all on the mirror drive ( I can get rid of the standard 80gb drive the computer ships with) are there draw backs or problems with such a config??? (ex. speed) My config is a Mac Dual Processor G4 1.25 Ghtz running 10.3 with MysQL 4.0.15. both drives are 120GB 7200RM IDE Hitachi Deskstar's. Thanks. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Setting up MySQL on Raid Mirror
Rick Dwyer wrote: Hello all. I want to install MySQL on a RAID mirror drive using two ATA 125GB for the mirror. I know in a webserver config, it's best to put the boot OS on one drive and the shared serving folder on the RAID mirror drive. Under a MySQL server, if I install the OS and MySQL all on the mirror drive ( I can get rid of the standard 80gb drive the computer ships with) are there draw backs or problems with such a config??? (ex. speed) Thanks. Rick There are pro's and con's to either setup: - The more volumes you have (e.g. both an 80GB and a mirrored 125GB volume), the more opportunity you have to balance overall system IO and get the best performance. Mind you, that will take some tuning and understanding what portions of your system cause IO (e.g Operating system code, versus MySQL code, swap and actual read/write of the database data). - Putting your OS on a unmirrored volume is more risky than having it on the RAID 1 volume. Assuming its not a super-high performance situation, I would put everything on the RAID 1 volume. If its really high performance, you need an altogether different disk setup anyway. Hope this helps - Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Setting up MySQL on Raid Mirror
Assuming its not a super-high performance situation, I would put everything on the RAID 1 volume. Richard, what would you define as super-high performance? This MySQL database server will serve as the backend for a Lasso/ OS X Apache webserver handling thousands of hits per day. Will installing OS and MySQL on the Mirrored Drive be within the scope of that type of activity? The database basically collects customer data. Thanks. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Setting up MySQL on Raid Mirror
Rick Dwyer wrote: Assuming its not a super-high performance situation, I would put everything on the RAID 1 volume. Richard, what would you define as super-high performance? This MySQL database server will serve as the backend for a Lasso/ OS X Apache webserver handling thousands of hits per day. Will installing OS and MySQL on the Mirrored Drive be within the scope of that type of activity? The database basically collects customer data. Thanks. Rick Rick, That's a little harder question. I'll go through a typical calculation, but you'll have to adjust it for your situation. 1) It sounds like the typical database transactions are not that intensive, basically storing info about a single customer. MySQL should handle this in fractions of a second - even with everything on the same harddrive - assuming you do not have lots of other conflicting disk-intensive processes. 2) Let's say that your thousands is 10,000 customer add/updates per day, evenly spread over a 10 hour day (36000 seconds). Obviously customers do not evenly spread the load across the day, but initially let's say they do. 3) If you divide 36000 seconds by 10,000 update requests you end up with 3.6 seconds per request. 4) Now the only think you have to factor in is how the load will actually be distributed. You have to supply this intelligence, based on your customer knowledge. For example, if your customers are all in a single time zone and 90% of the updates happen during lunchtime your hardware may not be up to it - 9,000 transactions in an hour. I know this does not give you the answer you were looking for, but hope it helps. Sizing new hardware for an existing applications is not too bad if, you can usually figure out how much more processor and disk IO you need to buy. But for a new application, you really need to run some load testing on comparable hardware before deployment. Basic load testing is not hard, but it does take time. Really sophisticated load testing is a whole discipline in itself, but it does not seem like you need this. - Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb filesystem on software raid
I bought a supermicro 6013p-t for the 4 sata raid hard drives support. Unfortunately, it doesn't really have raid at all. So I'm forced to use software raid. What I'd like to use is fedora core 2 with an innodb filesystem on a software raid partition according to these instructions: http://dev.mysql.com/doc/mysql/en/InnoDB_Raw_Devices.html Has anybody done this? I'm not a linux expert and so I'm not sure exactly how to set this up. When I try to setup software raid in linux, I'm forced to pick a filesystem and its mount point as part of the process of creating a software raid partition. So this is the part that's stumping me. How do I create a software raid raw device only to use as an innodb filesystem? Is this possible? Or maybe this can't be done without hardware raid and I need to buy a new server? GNU's Parted software (http://www.gnu.org/software/parted/) has this to say regarding features, Supported disk labels: raw access (useful for RAID and LVM), ms-dos, etc. But I've been unable to create a raw software raid device with that as well. Scott Mueller AccelerateBiz Managed Hosting http://www.acceleratebiz.com http://www.acceleratebiz.com/ Phone: (800) 360-7360 Fax: (270) 778-3081
Re: innodb filesystem on software raid
I highly recommend simply using ext3 for your Linux setup. The 1 or 2 percent performance benefit that you may get from raw partitions is way outweighed by complexness of backups of the raw data. either way: First I would suggest you read the Linux RAID howto: http://www.tldp.org/HOWTO/Software-RAID-HOWTO.html Here are the basic steps: create a /etc/raidtab file for your array (probably md0) using a 32k or 64k chunk size (hint: man raidtab) run mkraid to initialize the new raid array (md0) (hint: man mkraid) if you want to use raw partitions: - #this is redhat/fedora specific add /dev/md0 to /etc/sysconfig/rawdevices (hint: man raw) add chown mysql:mysql /dev/raw/raw0 to /etc/init.d/rc.local if you want to use ext3: - mke2fs -j -T largefile4 /dev/md0 (hint: man mke2fs) --- Scott Mueller [EMAIL PROTECTED] wrote: I bought a supermicro 6013p-t for the 4 sata raid hard drives support. Unfortunately, it doesn't really have raid at all. So I'm forced to use software raid. What I'd like to use is fedora core 2 with an innodb filesystem on a software raid partition according to these instructions: http://dev.mysql.com/doc/mysql/en/InnoDB_Raw_Devices.html Has anybody done this? I'm not a linux expert and so I'm not sure exactly how to set this up. When I try to setup software raid in linux, I'm forced to pick a filesystem and its mount point as part of the process of creating a software raid partition. So this is the part that's stumping me. How do I create a software raid raw device only to use as an innodb filesystem? Is this possible? Or maybe this can't be done without hardware raid and I need to buy a new server? GNU's Parted software (http://www.gnu.org/software/parted/) has this to say regarding features, Supported disk labels: raw access (useful for RAID and LVM), ms-dos, etc. But I've been unable to create a raw software raid device with that as well. Scott Mueller AccelerateBiz Managed Hosting http://www.acceleratebiz.com http://www.acceleratebiz.com/ Phone: (800) 360-7360 Fax: (270) 778-3081 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
raid configure option?
Can anyone explain what the raid configure option does? Is this for use when storing mysql on hardware raid? if so, what type(s)- ie. striping, mirroring? Thanks yonah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: raid configure option?
In the last episode (Apr 13), Yonah Russ said: Can anyone explain what the raid configure option does? Is this for use when storing mysql on hardware raid? if so, what type(s)- ie. striping, mirroring? It's mainly to support tables over 2gb on old Linux kernels that can't do large files. You can also do a poor-man's RAID with it by creating a RAID table, moving the files to separate disks, and creating symlinks that point to the new locations. Only striping is supported. If you have it, use hardware RAID and regular mysql tables instead. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimal RAID stripe size(s) for InnoDB?
Jeremy, I am not sure if I have seen benchmarks of this. I think the stripe size is not very important, as long as you make it significantly bigger than the InnoDB page size of 16 kB. Since it is not guaranteed that the OS will align InnoDB's data pages to stripes, having a small, 16 kB stripe size might cause 2 disk reads for a random read of an InnoDB page. Since there are usually unexplained performance phenomena in file i/o and disk i/o, a real-world test is needed for your particular software/hardware combination to determine a good stripe size. Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html . List:MySQL General Discussion« Previous MessageNext Message » From: Jeremy Zawodny Date:March 18 2004 11:48pm Subject: Optimal RAID stripe size(s) for InnoDB? Has anyone done much testing with RAID stripe sizes for heavy concurrency InnoDB-based applications? I'm expecting that using a stripe size that matches InnoDB's page size would make sense, but it could save a lot of testing if someone else has already done this. Thanks, Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimal RAID stripe size(s) for InnoDB?
Has anyone done much testing with RAID stripe sizes for heavy concurrency InnoDB-based applications? I'm expecting that using a stripe size that matches InnoDB's page size would make sense, but it could save a lot of testing if someone else has already done this. Thanks, Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 186 days, processed 2,941,866,394 queries (182/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to install data on a RAID HDD??
Hello List: First my apology: I have previously asked this question and saved the response. But we lost our Win NT server and all the saved data, so I ask the same question again. I have looked in archives for the last three months and have not been able to find my previous Q/A. I will be setting up MySQL server, as soon as I know how. I plan to install MySQL on a RH 9 Server named data. This server has one main disk (which is a boot disk with all the O/S ) and two RAID-1 HDD mirroring one another. My plan is to install MySQL on the main HDD but install/create all the SQL data, tables, Indexes, etc., on the RAID HDD. RAID HDD will have only SQL data. I have no problem installing MySQL on the main HDD but can not figure out what changes are needed so all the data goes to RAID disks. I am a newbie so please, give direction!! Kirti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to install data on a RAID HDD??
Configure your my.cnf data_dir parameter to point to the raid Hard Drive. Original Message On 2/24/04, 12:44:13 PM, Kirti S. Bajwa [EMAIL PROTECTED] wrote regarding How to install data on a RAID HDD??: Hello List: First my apology: I have previously asked this question and saved the response. But we lost our Win NT server and all the saved data, so I ask the same question again. I have looked in archives for the last three months and have not been able to find my previous Q/A. I will be setting up MySQL server, as soon as I know how. I plan to install MySQL on a RH 9 Server named data. This server has one main disk (which is a boot disk with all the O/S ) and two RAID-1 HDD mirroring one another. My plan is to install MySQL on the main HDD but install/create all the SQL data, tables, Indexes, etc., on the RAID HDD. RAID HDD will have only SQL data. I have no problem installing MySQL on the main HDD but can not figure out what changes are needed so all the data goes to RAID disks. I am a newbie so please, give direction!! Kirti -- 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: How to install data on a RAID HDD??
I have no problem installing MySQL on the main HDD but can not figure out what changes are needed so all the data goes to RAID disks. I am a newbie so please, give direction!! The easiest way to do this would be to move the data directory and all its contents onto the RAID disks. Then symlink to that directory from the original location. Example only from basic source install: mv /usr/local/mysql/var /hdRAID/var ln -s /hdRAID/var /usr/local/mysql/var Please make sure that mysql is shutdown before doing this task. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to install data on a RAID HDD??
Worked like a charm. Thanks. You are a life saver. Kirti -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 24, 2004 2:21 PM To: Kirti S. Bajwa Cc: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] Subject: Re: How to install data on a RAID HDD?? I have no problem installing MySQL on the main HDD but can not figure out what changes are needed so all the data goes to RAID disks. I am a newbie so please, give direction!! The easiest way to do this would be to move the data directory and all its contents onto the RAID disks. Then symlink to that directory from the original location. Example only from basic source install: mv /usr/local/mysql/var /hdRAID/var ln -s /hdRAID/var /usr/local/mysql/var Please make sure that mysql is shutdown before doing this task. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to install data on a RAID HDD??
Kirti S. Bajwa [EMAIL PROTECTED] wrote: Worked like a charm. Thanks. You are a life saver. The easiest way to do this would be to move the data directory and all its contents onto the RAID disks. Then symlink to that directory from the original location. Example only from basic source install: mv /usr/local/mysql/var /hdRAID/var ln -s /hdRAID/var /usr/local/mysql/var You're quite welcome. Now if I could just find a company in need of these skills :) Anyone in the SF Bay Area know of any openings that could use someone skilled in handling RDBMS tasks? My background includes 9 years of RDBMS experience - mySQL, Oracle, Informix, Sybase, SQL-server, 8 years of web/back-end integration PHP, perl/CGI, and 13 years of Unix kernel internals. 22 years using C and 20 years of perl history (3, 4, and 5). Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Improving seek/access times -- does RAID help?
Matt W wrote: Hi Ted, Heh. :-) This could be many GBs. There's no problem reading rows that are in RAM (cached by the OS) -- can read over 10,000/second. If there's enough RAM, the OS will take care of it (you could cat table.MYD to /dev/null). No ramdisk necessary. :-) BTW, this is for MySQL's full-text search. It works pretty well (fast) as far as doing the lookups and searching in the index. That's not a concern at all. The problem is that it *has to* read the data file for each matching row (and possibly non-matching rows, depending on the search). :-( Searches need to be reasonably fast on millions of rows, while possibly reading 10s of thousands of data rows. It takes a lot more time when those rows aren't cached. The only thing I've thought of so far is symlinking the data file on a separate drive, but I'm not sure how much that will actually help. Matt Matt: Post your schema (use SHOW CREATE TABLE), and give an example of a couple of queries that are slow including the output of EXPLAIN. It is quite possible that we can find a fairly simple solution to avoid excessive random disk access. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Improving seek/access times -- does RAID help?
Can anyone tell me whether or not some kind of RAID will improve the seek/access times during lots of random reads from, say, MyISAM data files? I *do not care* about improved [sequential] transfer rates; I want the fastest possible random access. RAID will only help reduce the average random access time not an individual random access. This would require you to have a large number of accesses/sec, with multiple accesses in progress at the same time. If you are dealing with a single-threaded type situation I doubt that RAID will help the situation much. The read-ahead most disks provide may prove useful depending on how much of the data actually gets scanned. But large requests are where this excells, typical of a table scan should it occur. For random small reads, like an index access, you would need many of them in progress simultaneously to get real benefit from RAID. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Improving seek/access times -- does RAID help?
Hi all, Can anyone tell me whether or not some kind of RAID will improve the seek/access times during lots of random reads from, say, MyISAM data files? I *do not care* about improved [sequential] transfer rates; I want the fastest possible random access. I'm thinking that RAID won't give an improvement in this case, because the disks can't know where to read from until MySQL issues the seek calls. :-( About the only thing I can think of that may help is if you're using striping, there won't be as much data on each disk so the head would need shorter seeks. If RAID doesn't help the situation, any other ideas if the sub-6ms access times of the fastest 15K SCSI drives isn't fast enough? :-) Thanks, Matt
RE: Improving seek/access times -- does RAID help?
Run everything off a ramdisk ;-) Ted Gifford -Original Message- From: Matt W [mailto:[EMAIL PROTECTED] Sent: Friday, February 20, 2004 5:21 PM To: [EMAIL PROTECTED] Subject: Improving seek/access times -- does RAID help? Hi all, Can anyone tell me whether or not some kind of RAID will improve the seek/access times during lots of random reads from, say, MyISAM data files? I *do not care* about improved [sequential] transfer rates; I want the fastest possible random access. I'm thinking that RAID won't give an improvement in this case, because the disks can't know where to read from until MySQL issues the seek calls. :-( About the only thing I can think of that may help is if you're using striping, there won't be as much data on each disk so the head would need shorter seeks. If RAID doesn't help the situation, any other ideas if the sub-6ms access times of the fastest 15K SCSI drives isn't fast enough? :-) Thanks, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Improving seek/access times -- does RAID help?
Hi Ted, Heh. :-) This could be many GBs. There's no problem reading rows that are in RAM (cached by the OS) -- can read over 10,000/second. If there's enough RAM, the OS will take care of it (you could cat table.MYD to /dev/null). No ramdisk necessary. :-) BTW, this is for MySQL's full-text search. It works pretty well (fast) as far as doing the lookups and searching in the index. That's not a concern at all. The problem is that it *has to* read the data file for each matching row (and possibly non-matching rows, depending on the search). :-( Searches need to be reasonably fast on millions of rows, while possibly reading 10s of thousands of data rows. It takes a lot more time when those rows aren't cached. The only thing I've thought of so far is symlinking the data file on a separate drive, but I'm not sure how much that will actually help. Matt - Original Message - From: [EMAIL PROTECTED] Sent: Friday, February 20, 2004 7:24 PM Subject: RE: Improving seek/access times -- does RAID help? Run everything off a ramdisk ;-) Ted Gifford -Original Message- From: Matt W Sent: Friday, February 20, 2004 5:21 PM Subject: Improving seek/access times -- does RAID help? Hi all, Can anyone tell me whether or not some kind of RAID will improve the seek/access times during lots of random reads from, say, MyISAM data files? I *do not care* about improved [sequential] transfer rates; I want the fastest possible random access. I'm thinking that RAID won't give an improvement in this case, because the disks can't know where to read from until MySQL issues the seek calls. :-( About the only thing I can think of that may help is if you're using striping, there won't be as much data on each disk so the head would need shorter seeks. If RAID doesn't help the situation, any other ideas if the sub-6ms access times of the fastest 15K SCSI drives isn't fast enough? :-) Thanks, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Improving seek/access times -- does RAID help?
In the last episode (Feb 20), Matt W said: Can anyone tell me whether or not some kind of RAID will improve the seek/access times during lots of random reads from, say, MyISAM data files? I *do not care* about improved [sequential] transfer rates; I want the fastest possible random access. I'm thinking that RAID won't give an improvement in this case, because the disks can't know where to read from until MySQL issues the seek calls. :-( About the only thing I can think of that may help is if you're using striping, there won't be as much data on each disk so the head would need shorter seeks. Depends on whether you're talking about a single client doing these random reads or multiple ones. The bottleneck with random I/O is fact that a read means physically moving the disk head. If you have multiple disks striping (or mirroring) the data, you distribute the load across all the heads. It won't speed up a single client, but with more and more disks, it's less and less likely that multiple clients will be needing to read data off the same disk. Mirrors speed up reads more than stripes, but since you have to write to both mirrors, they don't help writes. Raid-5 lets you safely scale past 2 disks. With a single client, get enough RAM to cache the entire table in memory :) Or at least the entire index. That way you only need to do one seek per query. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL on RAID server????
You can afterwards the installation specify the path to the data in the my.cnf config file. Marc. -Message d'origine- De : Kirti S. Bajwa [mailto:[EMAIL PROTECTED] Envoyé : mercredi 31 décembre 2003 01:08 À : [EMAIL PROTECTED] Objet : MySQL on RAID server Hello List: Here comes a newbie's newbie!! I want to install MySQL 5.0.0 on a machine with RAID. It has the following hardware/Software configuration: DISTRO: RH9 1-Disk which has all the software installed. 2-Disks, which are RAID1 format with one folder /data I plan to use the RAID for data only. I am trying to install Source 5.0.0-alpha version of MySQL. I am not sure how to go about installing. I have two chain of thought: (1) Install MySQL at /usr/local as suggested in the Source installation directive, but install the MySQL data on the RAID disk (/data folder) -or- (2) Install the entire MySQL package on the RAID disk. If my explanation needs better explanation, I will be more than happy to do it. I prefer the (1) method but I have no idea how I can install just data on the RAID disk? I think the ./configure statement needs additional directive, but what? I have reviewed the information on the MySQL web site under Source installation page. There are 4-5 notes from people. Please shoe me the way. Thanks in advance. Kirti -- 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 on RAID server????
Hello List: Here comes a newbie's newbie!! I want to install MySQL 5.0.0 on a machine with RAID. It has the following hardware/Software configuration: DISTRO: RH9 1-Disk which has all the software installed. 2-Disks, which are RAID1 format with one folder /data I plan to use the RAID for data only. I am trying to install Source 5.0.0-alpha version of MySQL. I am not sure how to go about installing. I have two chain of thought: (1) Install MySQL at /usr/local as suggested in the Source installation directive, but install the MySQL data on the RAID disk (/data folder) -or- (2) Install the entire MySQL package on the RAID disk. If my explanation needs better explanation, I will be more than happy to do it. I prefer the (1) method but I have no idea how I can install just data on the RAID disk? I think the ./configure statement needs additional directive, but what? I have reviewed the information on the MySQL web site under Source installation page. There are 4-5 notes from people. Please shoe me the way. Thanks in advance. Kirti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID Strip size
On Thu, Dec 04, 2003 at 10:33:17AM -0500, Brent Baisley wrote: Actually, you want to try to match the stripe size to your data size. The ideal would be to have a stripe size equal to the size of a record in your database. This way the disk needs only one read or write for each database record. That depends on the storage engine you're using. For MyISAM, yes, the record size is a good way to do it. But for InnoDB, you'd probably want to use its page size. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 88 days, processed 3,535,395,556 queries (464/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID Strip size
Actually, you want to try to match the stripe size to your data size. The ideal would be to have a stripe size equal to the size of a record in your database. This way the disk needs only one read or write for each database record. You really don't want to fragment a record. A large stripe size is good if you have large files, like graphic files, that you read in their entirety. It's bad if you are reading small amounts of data, like in a database. For instance, if you set a stripe size of 128K and you need to read 100 records that are not in the same disk sector, the disk ends up retrieving over 12MB of data. If your typical record only contains 2K of data, that's only 200K of data you need out of the 12MB that the disk retrieved. That's a big waste. On the flip side, a stripe size too small will fragment your records and cause excessive disk access. On the other hand, if you are typically doing full table scans, then you are reading most of a large file and a large stripe size would be good. You really need to know your data and how it is accessed in order to set an optimal stripe size. Even then, you need to benchmark to see if what you set if correct, especially since different tables have different data sizes and access patterns. When in doubt, it's usually best to leave the stripe size at the typical default of 4K. The reason for this is that most operating systems track RAM in 4K increments, so there is a one-to-one relation between disk sector size and RAM sector size. At least that's what I learned a few years ago in an IBM class, perhaps RAM is tracked differently now with the extremely large RAM configurations that are now possible. On Dec 3, 2003, at 4:05 PM, trevor%tribenetwork.com wrote: Greetings Mysqlians, Please comment on the validity of my logic: In setting the RAID(10/2disks) strip size everything I read says you must benchmark your particular system. Since that is not an option, my current logic is to have a large strip size (1024) with the reasoning that fewer writes/reads (yet longer writes) will be better in a database which has a large amount of disk access. The disk cache size is 1GB on our disk device but that is not quite enough to hold all the tables which get accessed(written to and read from) frequently. I figure setting a large stripe size is a conservative approach allowing for better scalability. Many Thanks, Trevor -- 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]
RAID Strip size
Greetings Mysqlians, Please comment on the validity of my logic: In setting the RAID(10/2disks) strip size everything I read says you must benchmark your particular system. Since that is not an option, my current logic is to have a large strip size (1024) with the reasoning that fewer writes/reads (yet longer writes) will be better in a database which has a large amount of disk access. The disk cache size is 1GB on our disk device but that is not quite enough to hold all the tables which get accessed(written to and read from) frequently. I figure setting a large stripe size is a conservative approach allowing for better scalability. Many Thanks, Trevor
Re: Hardware Raid and 2 Gig Limit
THanks everyone for all your help! Steve - Original Message - From: Pete Harlan [EMAIL PROTECTED] To: David T-G [EMAIL PROTECTED] Cc: mysql users [EMAIL PROTECTED]; Steve Vernon [EMAIL PROTECTED] Sent: Friday, October 31, 2003 4:16 PM Subject: Re: Hardware Raid and 2 Gig Limit On Fri, Oct 31, 2003 at 05:44:02AM -0500, David T-G wrote: % % Does the 2 Gig file size limit on Linux get broken when I have a hardware % raid controller? The limit applies only to ext2 filesystems, and not all of them at that; ext3 and reiserfs (and others) can happily write much larger files. The 2GB filesize limit was due to glibc and the linux kernel, not the ext2 filesystem. Any linux distro from the past year or so should be able to handle 2GB files on any filesystem. Hardware raid is invisible to Linux, so won't affect the maximum usable filesize. --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hardware Raid and 2 Gig Limit
Hello, Does the 2 Gig file size limit on Linux get broken when I have a hardware raid controller? My ISP says I don't need the raid option activated on MySQL. Thanks, Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hardware Raid and 2 Gig Limit
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Steve -- ...and then Steve Vernon said... % % Hello, Hi! % % Does the 2 Gig file size limit on Linux get broken when I have a hardware % raid controller? The limit applies only to ext2 filesystems, and not all of them at that; ext3 and reiserfs (and others) can happily write much larger files. % % My ISP says I don't need the raid option activated on MySQL. I would generally agree. I can't imagine an ISP not using a journalled filesystem such as those above. More to the point, though, the mysql raid option has nothing to do with disk RAID; they are completely separate. % % Thanks, % % Steve HTH HAND :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/ojzyGb7uCXufRwARAtEmAJ9i3oIMbLTA4yq8koPcEOUwD7SpRwCfe+bn rvzVjuD8tIxO8AVj3jp02CI= =QdfG -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hardware Raid and 2 Gig Limit
Hiya! Thanks for the quick reply!!! But dosen't it make more sense to have 20 0.5 Gig files rather than one 10 Gig file? I know you can split files, but basically because we have raid I trust it to a point. But I would like to make backup's. It costs a lot to have our server provider to do backup's themselves. We can't afford replication and backups at the moment. So I plan to stop MySQL. Copy the database files to a temp directory. THen download them at my leisure. Oh and I suppose I need to start MySQL then! ;-) The backup will be to Windows 2000, and I want to be able to use the local copy of the files. So if I have smaller files, I can download them, and keep a local copy. Theres no way I want to start downloading a 10 Gig file from the server. Ok I can do it, I have a download manager etc, Iv'e downloaded bigger. But I guess it must be a massive strain on the server sending the data, and we dont have that fast an internet connection so it's easier. We only have 512 broadband and it sometimes messes up files larger than 2 Gig. The download manager realises there is a problem, seems to backtrack or something, so takes ages. Cable is not available where I live. If we go beyond 10 Gigs, it just increases the complexity. Do you or anyone know of any serious speed differences between having one file or 20 smaller files for one table? With one files, isn't there a worry if it gets corrupted you loose the lot? A couple of years ago I looked into big tables for myself and everyone said you need the raid option in MySQL. Now I'm not sure! Thanks, Steve - Original Message - From: David T-G [EMAIL PROTECTED] To: mysql users [EMAIL PROTECTED] Cc: Steve Vernon [EMAIL PROTECTED] Sent: Friday, October 31, 2003 10:44 AM Subject: Re: Hardware Raid and 2 Gig Limit -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Steve -- ...and then Steve Vernon said... % % Hello, Hi! % % Does the 2 Gig file size limit on Linux get broken when I have a hardware % raid controller? The limit applies only to ext2 filesystems, and not all of them at that; ext3 and reiserfs (and others) can happily write much larger files. % % My ISP says I don't need the raid option activated on MySQL. I would generally agree. I can't imagine an ISP not using a journalled filesystem such as those above. More to the point, though, the mysql raid option has nothing to do with disk RAID; they are completely separate. % % Thanks, % % Steve HTH HAND :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/ojzyGb7uCXufRwARAtEmAJ9i3oIMbLTA4yq8koPcEOUwD7SpRwCfe+bn rvzVjuD8tIxO8AVj3jp02CI= =QdfG -END PGP SIGNATURE- -- 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: Hardware Raid and 2 Gig Limit
Hi We have a similar challenge. Offsite backups with huge amounts of data, without spending a fortune. We have a local Linux box and the remote server both running rsync. http://samba.anu.edu.au/rsync/ Rsync is brilliant because it only updates file sectors that have changed. We would only use it if we can stop MySql but then synchronising takes minutes (1MB broadband connection), particularly using compression. If you cannot add your own server software MySql files also compress very well (gzip 90%+), but this means some server load. HTH Peter --- Excellence in internet and open source software --- Sunmaia Birmingham UK www.sunmaia.net tel. 0121-242-1473 International +44-121-242-1473 --- -Original Message- From: Steve Vernon [mailto:[EMAIL PROTECTED] Sent: 31 October 2003 12:00 To: Mysql List Cc: [EMAIL PROTECTED] Subject: Re: Hardware Raid and 2 Gig Limit Hiya! Thanks for the quick reply!!! But dosen't it make more sense to have 20 0.5 Gig files rather than one 10 Gig file? I know you can split files, but basically because we have raid I trust it to a point. But I would like to make backup's. It costs a lot to have our server provider to do backup's themselves. We can't afford replication and backups at the moment. So I plan to stop MySQL. Copy the database files to a temp directory. THen download them at my leisure. Oh and I suppose I need to start MySQL then! ;-) The backup will be to Windows 2000, and I want to be able to use the local copy of the files. So if I have smaller files, I can download them, and keep a local copy. Theres no way I want to start downloading a 10 Gig file from the server. Ok I can do it, I have a download manager etc, Iv'e downloaded bigger. But I guess it must be a massive strain on the server sending the data, and we dont have that fast an internet connection so it's easier. We only have 512 broadband and it sometimes messes up files larger than 2 Gig. The download manager realises there is a problem, seems to backtrack or something, so takes ages. Cable is not available where I live. If we go beyond 10 Gigs, it just increases the complexity. Do you or anyone know of any serious speed differences between having one file or 20 smaller files for one table? With one files, isn't there a worry if it gets corrupted you loose the lot? A couple of years ago I looked into big tables for myself and everyone said you need the raid option in MySQL. Now I'm not sure! Thanks, Steve - Original Message - From: David T-G [EMAIL PROTECTED] To: mysql users [EMAIL PROTECTED] Cc: Steve Vernon [EMAIL PROTECTED] Sent: Friday, October 31, 2003 10:44 AM Subject: Re: Hardware Raid and 2 Gig Limit -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Steve -- ...and then Steve Vernon said... % % Hello, Hi! % % Does the 2 Gig file size limit on Linux get broken when I have a hardware % raid controller? The limit applies only to ext2 filesystems, and not all of them at that; ext3 and reiserfs (and others) can happily write much larger files. % % My ISP says I don't need the raid option activated on MySQL. I would generally agree. I can't imagine an ISP not using a journalled filesystem such as those above. More to the point, though, the mysql raid option has nothing to do with disk RAID; they are completely separate. % % Thanks, % % Steve HTH HAND :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/ojzyGb7uCXufRwARAtEmAJ9i3oIMbLTA4yq8koPcEOUwD7SpRwCfe+bn rvzVjuD8tIxO8AVj3jp02CI= =QdfG -END PGP SIGNATURE- -- 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: Hardware Raid and 2 Gig Limit
Don't confuse hardware RAID with MySQL RAID. The 2GB file size limit is a function of the operating system and the file system in use, it has nothing to do with the disk hardware you have installed. Your ISP may say you don't need the raid option activated because the system they are using doesn't have the 2GB limit. On Friday, October 31, 2003, at 05:08 AM, Steve Vernon wrote: Does the 2 Gig file size limit on Linux get broken when I have a hardware raid controller? My ISP says I don't need the raid option activated on MySQL. -- 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: Hardware Raid and 2 Gig Limit
Hiya! Thanks for the help!!! Do RSync like big files? Or does it prefer smaller files? I see it supports larger than 2 Gig files, but, Ive not seen any speed comparrisons etc. E.g. one big file, or 10 small ones for example. THANKS! Steve - Original Message - From: Peter Lovatt [EMAIL PROTECTED] To: Steve Vernon [EMAIL PROTECTED]; Mysql List [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, October 31, 2003 12:17 PM Subject: RE: Hardware Raid and 2 Gig Limit Hi We have a similar challenge. Offsite backups with huge amounts of data, without spending a fortune. We have a local Linux box and the remote server both running rsync. http://samba.anu.edu.au/rsync/ Rsync is brilliant because it only updates file sectors that have changed. We would only use it if we can stop MySql but then synchronising takes minutes (1MB broadband connection), particularly using compression. If you cannot add your own server software MySql files also compress very well (gzip 90%+), but this means some server load. HTH Peter --- Excellence in internet and open source software --- Sunmaia Birmingham UK www.sunmaia.net tel. 0121-242-1473 International +44-121-242-1473 --- -Original Message- From: Steve Vernon [mailto:[EMAIL PROTECTED] Sent: 31 October 2003 12:00 To: Mysql List Cc: [EMAIL PROTECTED] Subject: Re: Hardware Raid and 2 Gig Limit Hiya! Thanks for the quick reply!!! But dosen't it make more sense to have 20 0.5 Gig files rather than one 10 Gig file? I know you can split files, but basically because we have raid I trust it to a point. But I would like to make backup's. It costs a lot to have our server provider to do backup's themselves. We can't afford replication and backups at the moment. So I plan to stop MySQL. Copy the database files to a temp directory. THen download them at my leisure. Oh and I suppose I need to start MySQL then! ;-) The backup will be to Windows 2000, and I want to be able to use the local copy of the files. So if I have smaller files, I can download them, and keep a local copy. Theres no way I want to start downloading a 10 Gig file from the server. Ok I can do it, I have a download manager etc, Iv'e downloaded bigger. But I guess it must be a massive strain on the server sending the data, and we dont have that fast an internet connection so it's easier. We only have 512 broadband and it sometimes messes up files larger than 2 Gig. The download manager realises there is a problem, seems to backtrack or something, so takes ages. Cable is not available where I live. If we go beyond 10 Gigs, it just increases the complexity. Do you or anyone know of any serious speed differences between having one file or 20 smaller files for one table? With one files, isn't there a worry if it gets corrupted you loose the lot? A couple of years ago I looked into big tables for myself and everyone said you need the raid option in MySQL. Now I'm not sure! Thanks, Steve - Original Message - From: David T-G [EMAIL PROTECTED] To: mysql users [EMAIL PROTECTED] Cc: Steve Vernon [EMAIL PROTECTED] Sent: Friday, October 31, 2003 10:44 AM Subject: Re: Hardware Raid and 2 Gig Limit -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Steve -- ...and then Steve Vernon said... % % Hello, Hi! % % Does the 2 Gig file size limit on Linux get broken when I have a hardware % raid controller? The limit applies only to ext2 filesystems, and not all of them at that; ext3 and reiserfs (and others) can happily write much larger files. % % My ISP says I don't need the raid option activated on MySQL. I would generally agree. I can't imagine an ISP not using a journalled filesystem such as those above. More to the point, though, the mysql raid option has nothing to do with disk RAID; they are completely separate. % % Thanks, % % Steve HTH HAND :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/ojzyGb7uCXufRwARAtEmAJ9i3oIMbLTA4yq8koPcEOUwD7SpRwCfe+bn rvzVjuD8tIxO8AVj3jp02CI= =QdfG -END PGP SIGNATURE- -- 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
RE: Hardware Raid and 2 Gig Limit
Hi We are working with a lot of smaller databases, (The biggest is 600MB) so I am not sure about single large files. It works by checksumming parts of the file (not sure at what level), so if only one of twenty sections has changed it will only update that section. So if most of your 10GB is static data then it will need very little updating. Peter --- Excellence in internet and open source software --- Sunmaia Birmingham UK www.sunmaia.net tel. 0121-242-1473 International +44-121-242-1473 --- -Original Message- From: Steve Vernon [mailto:[EMAIL PROTECTED] Sent: 31 October 2003 14:19 To: Peter Lovatt; Mysql List Subject: Re: Hardware Raid and 2 Gig Limit Hiya! Thanks for the help!!! Do RSync like big files? Or does it prefer smaller files? I see it supports larger than 2 Gig files, but, Ive not seen any speed comparrisons etc. E.g. one big file, or 10 small ones for example. THANKS! Steve - Original Message - From: Peter Lovatt [EMAIL PROTECTED] To: Steve Vernon [EMAIL PROTECTED]; Mysql List [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, October 31, 2003 12:17 PM Subject: RE: Hardware Raid and 2 Gig Limit Hi We have a similar challenge. Offsite backups with huge amounts of data, without spending a fortune. We have a local Linux box and the remote server both running rsync. http://samba.anu.edu.au/rsync/ Rsync is brilliant because it only updates file sectors that have changed. We would only use it if we can stop MySql but then synchronising takes minutes (1MB broadband connection), particularly using compression. If you cannot add your own server software MySql files also compress very well (gzip 90%+), but this means some server load. HTH Peter --- Excellence in internet and open source software --- Sunmaia Birmingham UK www.sunmaia.net tel. 0121-242-1473 International +44-121-242-1473 --- -Original Message- From: Steve Vernon [mailto:[EMAIL PROTECTED] Sent: 31 October 2003 12:00 To: Mysql List Cc: [EMAIL PROTECTED] Subject: Re: Hardware Raid and 2 Gig Limit Hiya! Thanks for the quick reply!!! But dosen't it make more sense to have 20 0.5 Gig files rather than one 10 Gig file? I know you can split files, but basically because we have raid I trust it to a point. But I would like to make backup's. It costs a lot to have our server provider to do backup's themselves. We can't afford replication and backups at the moment. So I plan to stop MySQL. Copy the database files to a temp directory. THen download them at my leisure. Oh and I suppose I need to start MySQL then! ;-) The backup will be to Windows 2000, and I want to be able to use the local copy of the files. So if I have smaller files, I can download them, and keep a local copy. Theres no way I want to start downloading a 10 Gig file from the server. Ok I can do it, I have a download manager etc, Iv'e downloaded bigger. But I guess it must be a massive strain on the server sending the data, and we dont have that fast an internet connection so it's easier. We only have 512 broadband and it sometimes messes up files larger than 2 Gig. The download manager realises there is a problem, seems to backtrack or something, so takes ages. Cable is not available where I live. If we go beyond 10 Gigs, it just increases the complexity. Do you or anyone know of any serious speed differences between having one file or 20 smaller files for one table? With one files, isn't there a worry if it gets corrupted you loose the lot? A couple of years ago I looked into big tables for myself and everyone said you need the raid option in MySQL. Now I'm not sure! Thanks, Steve - Original Message - From: David T-G [EMAIL PROTECTED] To: mysql users [EMAIL PROTECTED] Cc: Steve Vernon [EMAIL PROTECTED] Sent: Friday, October 31, 2003 10:44 AM Subject: Re: Hardware Raid and 2 Gig Limit -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Steve -- ...and then Steve Vernon said... % % Hello, Hi! % % Does the 2 Gig file size limit on Linux get broken when I have a hardware % raid controller? The limit applies only to ext2 filesystems, and not all of them at that; ext3 and reiserfs (and others) can happily write much larger files. % % My ISP says I don't need the raid option activated on MySQL. I would generally agree. I can't imagine an ISP not using a journalled filesystem such as those above. More to the point, though, the mysql raid option has nothing to do with disk RAID; they are completely separate. % % Thanks, % % Steve HTH HAND :-D - -- David T-G * There is too much animal
Re: Hardware Raid and 2 Gig Limit
On Fri, Oct 31, 2003 at 05:44:02AM -0500, David T-G wrote: % % Does the 2 Gig file size limit on Linux get broken when I have a hardware % raid controller? The limit applies only to ext2 filesystems, and not all of them at that; ext3 and reiserfs (and others) can happily write much larger files. The 2GB filesize limit was due to glibc and the linux kernel, not the ext2 filesystem. Any linux distro from the past year or so should be able to handle 2GB files on any filesystem. Hardware raid is invisible to Linux, so won't affect the maximum usable filesize. --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hardware Raid and 2 Gig Limit
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Steve -- ...and then Steve Vernon said... % % Hiya! Hi! % % Thanks for the quick reply!!! Sure thing :-) % % But dosen't it make more sense to have 20 0.5 Gig files rather than one 10 % Gig file? I wouldn't think so. The manual doesn't seem to think so, either: * The RAID_TYPE option will help you to break the 2G/4G limit for the MyISAM datafile (not the index file) on operating systems that don't support big files. Note that this option is not recommended for filesystem that supports big files! You can get more speed from the I/O bottleneck by putting RAID directories on different physical disks. RAID_TYPE will work on any OS, as long as you have configured MySQL with --with-raid. For now the only allowed RAID_TYPE is STRIPED (1 and RAID0 are aliases for this). If you specify RAID_TYPE=STRIPED for a MyISAM table, MyISAM will create RAID_CHUNKS subdirectories named 00, 01, 02 in the database directory. In each of these directories MyISAM will create a table_name.MYD. When writing data to the datafile, the RAID handler will map the first RAID_CHUNKSIZE *1024 bytes to the first file, the next RAID_CHUNKSIZE *1024 bytes to the next file and so on. % % I know you can split files, but basically because we have raid I trust it to % a point. But I would like to make backup's. It costs a lot to have our Ah! Now that's a completely different problem. Go ahead and figure out what your real issue is and then let us know. Go ahead; I'll wait. OK. If it's still backups, let's go on :-) % server provider to do backup's themselves. We can't afford replication and % backups at the moment. So I plan to stop MySQL. Copy the database files to a How on earth do you have 10G of data to handle without any budget for replication or backups? What happens when your hardware breaks, or even someone fat-fingers a delete command? % temp directory. THen download them at my leisure. Oh and I suppose I need to % start MySQL then! ;-) The backup will be to Windows 2000, and I want to be % able to use the local copy of the files. OK; I don't know how large Win2k can go, but you might need to have smaller files. % % So if I have smaller files, I can download them, and keep a local copy. Sure. % Theres no way I want to start downloading a 10 Gig file from the server. Ok That would be a bit tough, admittedly. You might look into unison or rsync for such... % I can do it, I have a download manager etc, Iv'e downloaded bigger. But I % guess it must be a massive strain on the server sending the data, and we Not really; I'm sure the network is your bottleneck. % dont have that fast an internet connection so it's easier. We only have 512 % broadband and it sometimes messes up files larger than 2 Gig. The download I can imagine. That's a fair bit of data to pull over a fairly small line. % manager realises there is a problem, seems to backtrack or something, so % takes ages. Cable is not available where I live. Get a T1 :-) % % If we go beyond 10 Gigs, it just increases the complexity. Yep. % % Do you or anyone know of any serious speed differences between having one % file or 20 smaller files for one table? With one files, isn't there a worry % if it gets corrupted you loose the lot? I'm sure that's not an issue; if you're going to lose a bit, it's just as likely to happen in one of twenty small files as in one large file. % % A couple of years ago I looked into big tables for myself and everyone said % you need the raid option in MySQL. Now I'm not sure! That's probably because ext3 and reiser and xfs weren't around :-) % % Thanks, % % Steve HTH HAND :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/oxOnGb7uCXufRwARAqFYAJwOmQS2rSj3ETIdh8W58LQTZO8mPwCg3/+b xgBqMyArFJveTUjm+eW/NyY= =zQ3E -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need a Tutorial on RAID with MySQL
Hi group, Does anyone know of a good tutorial on RAID? When used with MySQL. Thanks, __ NZEYIMANA Emery Fabrice NEFA Computing Services, Inc. P.O. Box 5078 Kigali Office Phone: +250-51 11 06 Office Fax: +250-50 15 19 Mobile: +250-08517768 Email: [EMAIL PROTECTED] http://www.nefacomp.net/
RE: RAID, miiror OR replication?
Hi, Having implemented all the solutions you suggest, I would need more information to answer this problem. 1. What is the acceptable uptime of the system? 95%, 99%, 99.9%, 99.99% ? 2. In the event of a failure, what is the acceptable recovery time? None, 20 mins, 1 hr, 5 hrs, 1 day ? 3. What hardware is running the DB now? 4. How many queries per second is the system running? Is it read heavy or write heavy? (and what about the future) 5. What is the hardware budget? Just the existing hardware, $1000, $5000 etc.. 6. How much time can you afford to spend on it? With this info, I could help to suggest a solution... But without it, You may receive ideas for solutions that are overkill or underkill for your needs. Hope this helps, Andrew -Original Message- From: Richard Reina [mailto:[EMAIL PROTECTED] Sent: Monday 06 October 2003 20:36 To: [EMAIL PROTECTED] Subject: RAID, miiror OR replication? I am wanting to protect myself against future potential hard drive failures on my database server running version 3.23.49a. Should I try and set up a RAID, a mirror or would the best solution be to set up MySQL replication. Any suggestions would be greatly appreciated. Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RAID, miiror OR replication?
Hi, Having implemented all the solutions you suggest, I would need more information to answer this problem. 1. What is the acceptable uptime of the system? 95%, 99%, 99.9%, 99.99% ? 2. In the event of a failure, what is the acceptable recovery time? None, 20 mins, 1 hr, 5 hrs, 1 day ? 3. What hardware is running the DB now? 4. How many queries per second is the system running? Is it read heavy or write heavy? (and what about the future) 5. What is the hardware budget? Just the existing hardware, $1000, $5000 etc.. 6. How much time can you afford to spend on it? With this info, I could help to suggest a solution... But without it, You may receive ideas for solutions that are overkill or underkill for your needs. Hope this helps, Andrew -Original Message- From: Richard Reina [mailto:[EMAIL PROTECTED] Sent: Monday 06 October 2003 20:36 To: [EMAIL PROTECTED] Subject: RAID, miiror OR replication? I am wanting to protect myself against future potential hard drive failures on my database server running version 3.23.49a. Should I try and set up a RAID, a mirror or would the best solution be to set up MySQL replication. Any suggestions would be greatly appreciated. Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RAID, mirror OR replication?
I am wanting to protect myself against future potential hard drive failures on my DB server running version 3.23.49a. Should I try and set up a RAID, a mirror or would the best solution be to set up MySQL replication. Any suggestions would be greatly appreciated. Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RAID, miiror OR replication?
I am wanting to protect myself against future potential hard drive failures on my database server running version 3.23.49a. Should I try and set up a RAID, a mirror or would the best solution be to set up MySQL replication. Any suggestions would be greatly appreciated. Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID, mirror OR replication?
On Mon, 2003-10-06 at 14:21, Richard Reina wrote: I am wanting to protect myself against future potential hard drive failures on my DB server running version 3.23.49a. Should I try and set up a RAID, a mirror or would the best solution be to set up MySQL replication. Any suggestions would be greatly appreciated. Richard, if you have the resources available I would suggest doing both RAID and Replication. RAID 5 maximizes your disk space, while making your system pretty fault tolerant. (this of course assumes Hot Swappable SCSI Drives). The replication gives you the added level of fault tolerance, plus on a busy server DB reads can be offloaded to the replicant freeing up resources on the Master. Don't know how familiar you are with RAID so this is a breakdown of the most common options. RAID0 - disk Stripeing (very fast reads but one drive fails and everything is lost). Absolutely no fault tolerance. But an option for a Replicant. RAID1 - disk mirroring (Duplicate copy of everything on another harddrive - the problem is that you have to duplicate your drives. If you have a 80GB disk, you need 2 of them, but you still only use 80GB. RAID0+1 - disk striping w/ Mirroring, you have 2 RAID0 volumes of identical size that mirror to each other. You get the speed of RAID0, and the fault tolerance of RAID1. If you have 2 80GB disk striped, now you need 4 80GB disks and you only get space of 2 of the 80GBs. RAID5 - In my opinion the best choice. You maximize available space, since its (N-1) * Drive capacity. Meaning The number of drives - 1 is your capacity. The equivilent of 1 drive is used to store parity information. If one drive fails, the RAID Controller can autocorrect the missing information on the fly so your system slows down, but stays available. You remove the bad drive, put a new one in, and the new drive gets rebuilt and in a few hours you are back to full steam. You build a raid set with 4 80GB drives, your available capacity would be 240GB (4 Drives - 1 for parity) * 80GB. Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Woody In a world without boundaries why do we need Gates and Windows? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RAID, miiror OR replication?
Richard, If you want to protect against hard drive failures then a RAID setup is probably the best option. A RAID will ensure that you always have an up-to-the-instant backup of all of your data in case a drive goes bad; however, a RAID won'tstop a bug, virus, or error from screwing up your database. If this is your production server then I would suggest that you also invest in a secondary backup system (such as a tape drive). -Rob -Original Message- From: Richard Reina [mailto:[EMAIL PROTECTED] Sent: Monday, October 06, 2003 3:36 PM To: [EMAIL PROTECTED] Subject: RAID, miiror OR replication? I am wanting to protect myself against future potential hard drive failures on my database server running version 3.23.49a. Should I try and set up a RAID, a mirror or would the best solution be to set up MySQL replication. Any suggestions would be greatly appreciated. Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RAID or not?
--On Friday, August 22, 2003 1:21 PM -0400 Lefevre, Steven [EMAIL PROTECTED] wrote: that is not true. mirroring gives you double the read speed and half the write speed. RAID5 gives you less than half the write speed. - OK, I see how it can give you double the read speed, but how can it give you have the write speed? Does it split the data between disks and then sync them later? No. You write 2x, remember. ;P Your write speed, best case and assuming no other bottlenecks (say an IDE a drive sharing the same controller with another IDE drive, esp. in the same mirror set) will be only as fast as the slowest drive in write mode. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID or not?
--On Friday, August 22, 2003 8:37 PM -0600 Jim McAtee [EMAIL PROTECTED] wrote: I don't quite understand the need to read data before any write. Why wouldn't it just calculate the parity of whatever is being written and just write it to disk? Wouldn't there be slack space, as with any disk system? Write a 1 byte file and it uses an N byte block on one disk, plus an N byte parity block on another. This wholly depends on the RAID subsystem, but better than 80% will need to either read the entire stripe, or hold off until they're writing the whole stripe at once. Remember the RAID is below the filesystem layer, and *separate* from it, esp. in the case of a hardware controller. Really big systems may (do) keep 'maps' of the space so they can cheat by not reading a strip when it knows it hasn't been written since (destructive) initialization and is thus all 0's. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID or not?
[EMAIL PROTECTED] wrote: I do not think it is true that mirroring gives no performance benefit (on a well implemented controller). For reads, the raid controller can read either copy of the data, so that effectively two reads can be in progress at the same time, doubling read performance. Yes, doubling read performance (best case) when compared with a non-RAID disk system. But with RAID5, you have more disks and can therefore read more than just two disks simultaneously. The more spindles you have, the better the performance. I was assuming that your number of spindles was set by your need to store daya. If this is so, the number of primary data drives, and hence spindles, is fixed. It is noly whtehr you have one parity drive, or N mirror drives. The latter is, of course, more expensive for a given storage capacity - but gives you higer performance for your money. On the write side, for small writes a raid 5 has to read the overwritten data (in order to remove it from the parity) then do a read/modify/write on the parity. I don't quite understand the need to read data before any write. Why wouldn't it just calculate the parity of whatever is being written and just write it to disk? Wouldn't there be slack space, as with any disk system? Write a 1 byte file and it uses an N byte block on one disk, plus an N byte parity block on another. Call the disks A, B, C, D, P. For some sector we are about to overwrite A with A'. The current contents of P are A^B^C^D (^ used for XOR). The final contents of P must be A'^B^C^D. We can read B, D and D to produce this valure ab initio. However, it is more efficient to read A and P, XOR them to produce B^C^D then XOR in the new data A'. In this case, both A and P must do read followed by write. Since this involves a full rotation between the read and the write, this is a relatively slow operation. This does not occur if we are overwrting all of A, B, C, and D because *in this case* we can do as you expect and calculate P on the fly. Performance again should be doubled (two writes on the mirrored system, read/overwrite and read/modify/write on the Raid5 system, with the two halves of each operation requiring a full rotation between them). For large writes, the Raid5 system catches up, because the parity can be entirely calculated from the data sent, so it does not need to do the reads. I would therefore expect a mirrored system to approach, but not reach, twice the performance of a Raid5 system A mirrored system when writing should, at best, be no faster than a non-mirrored system. Unlike reading, it has to write a complete copy of the data to both disks, so there are no performance gains to be had. RAID5 should be a bit slower than RAID1, due to the need for calculating parity. See explanation above. For large writes, you are correct. For smaller ones, and for the scruffy bits at the beginning and end of large writes, RAID 5 performance will approach 50% of Raid 1. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RAID or not?
I say go with RAID 5, on a controller card. Mirroring just gives you backup, and you lose half your diskspace. It offers no performance benefit, and actually the computer might have to work harder to make sure the drives are in sync. I do not think it is true that mirroring gives no performance benefit (on a well implemented controller). For reads, the raid controller can read either copy of the data, so that effectively two reads can be in progress at the same time, doubling read performance. On the write side, for small writes a raid 5 has to read the overwritten data (in order to remove it from the parity) then do a read/modify/write on the parity. Performance again should be doubled (two writes on the mirrored system, read/overwrite and read/modify/write on the Raid5 system, with the two halves of each operation requiring a full rotation between them). For large writes, the Raid5 system catches up, because the parity can be entirely calculated from the data sent, so it does not need to do the reads. I would therefore expect a mirrored system to approach, but not reach, twice the performance of a Raid5 system - at the cost of nearly doubling the number of disks. This does depend on appropriate intelligence in the Raid controller. A badly designed controller can fail to take advantage of these gains. If you are concerned abut ultimate performance, it would be well worth benchmarking the actual raid controllers you are considering. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RAID or not?
-Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Jon Drukman Sent: Thursday, August 21, 2003 9:24 PM To: [EMAIL PROTECTED] Subject: Re: RAID or not? Lefevre, Steven wrote: I say go with RAID 5, on a controller card. Mirroring just gives you backup, and you lose half your diskspace. It offers no performance benefit, and actually the computer might have to work harder to make sure the drives are in sync. that is not true. mirroring gives you double the read speed and half the write speed. RAID5 gives you less than half the write speed. - OK, I see how it can give you double the read speed, but how can it give you have the write speed? Does it split the data between disks and then sync them later? that's why i said if your database app is mostly selects go for mirroring. the OP said his app is about 50% select, so i say mirroring is a good choice. and hey, what's wrong with having a backup? the computer doesn't work any harder, it's all handled through the RAID controller card anyway. - Of course, there's nothing wrong with having a backup, in fact he should. But I was under the mistaken impression that that was all disk mirroing did for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID or not?
[EMAIL PROTECTED] wrote: I say go with RAID 5, on a controller card. Mirroring just gives you backup, and you lose half your diskspace. It offers no performance benefit, and actually the computer might have to work harder to make sure the drives are in sync. I do not think it is true that mirroring gives no performance benefit (on a well implemented controller). For reads, the raid controller can read either copy of the data, so that effectively two reads can be in progress at the same time, doubling read performance. Yes, doubling read performance (best case) when compared with a non-RAID disk system. But with RAID5, you have more disks and can therefore read more than just two disks simultaneously. The more spindles you have, the better the performance. On the write side, for small writes a raid 5 has to read the overwritten data (in order to remove it from the parity) then do a read/modify/write on the parity. I don't quite understand the need to read data before any write. Why wouldn't it just calculate the parity of whatever is being written and just write it to disk? Wouldn't there be slack space, as with any disk system? Write a 1 byte file and it uses an N byte block on one disk, plus an N byte parity block on another. Performance again should be doubled (two writes on the mirrored system, read/overwrite and read/modify/write on the Raid5 system, with the two halves of each operation requiring a full rotation between them). For large writes, the Raid5 system catches up, because the parity can be entirely calculated from the data sent, so it does not need to do the reads. I would therefore expect a mirrored system to approach, but not reach, twice the performance of a Raid5 system A mirrored system when writing should, at best, be no faster than a non-mirrored system. Unlike reading, it has to write a complete copy of the data to both disks, so there are no performance gains to be had. RAID5 should be a bit slower than RAID1, due to the need for calculating parity. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grown defects in a RAID array
In the last episode (Aug 20), Dathan Vance Pattishall said: I've notice that when grown defects (bad blocks on the disk caused by usage over time) that my dedicated mysql server is adversely effected. Affected how? The grown defect list is usually reserved for blocks that were discovered to be marginal (i.e. the data was recovered from the ECC data or after multiple reads, and no error was reported to the system). If you are really seeing read errors reported back to the OS, return the drive for a replacement immediately if it's in warranty. There's no reason for you to accept read errors from any modern drive. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RAID or not?
I am setting up a dedicated MySQL server with some pretty heavy usage. I am not much of a sys admin (mostly a programmer). I have some questions about the best drive configuration. I have 4 SCSI drives currently. I would like to have 1 drive run the OS, 1 drive to be the MySQL data directory and 1 drive to be InnoDB (possibly raw partition). What is the best way for me to configure RAID? Here is the kind of load I am talking about: Uptime: 1749850 Threads: 44 Questions: 1266402021 Slow queries: 16923 Opens: 162177 Flush tables: 1 Open tables: 64 Queries per second avg: 723.720 Thanks, -Jackson -- jackson miller cold feet creative 615.321.3300 / 800.595.4401 [EMAIL PROTECTED] cold feet presents Emma the world's easiest email marketing Learn more @ http://www.myemma.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID or not?
Jackson Miller wrote: I am setting up a dedicated MySQL server with some pretty heavy usage. I am not much of a sys admin (mostly a programmer). I have some questions about the best drive configuration. I have 4 SCSI drives currently. I would like to have 1 drive run the OS, 1 drive to be the MySQL data directory and 1 drive to be InnoDB (possibly raw partition). What is the best way for me to configure RAID? Here is the kind of load I am talking about: Uptime: 1749850 Threads: 44 Questions: 1266402021 Slow queries: 16923 Opens: 162177 Flush tables: 1 Open tables: 64 Queries per second avg: 723.720 if you're mostly running SELECTs then i would recommend a mirrored configuration. my server is in your ballpark: Uptime: 20689 Threads: 77 Questions: 11493312 Slow queries: 21 Opens: 1892 Flush tables: 1 Open tables: 512 Queries per second avg: 555.528 i expect it to average higher after it's been running for more than 24 hrs... unfortunately we had to renumber its IP today so it was rebooted. it is cranking along great with mirrored 10K SCSI drives. we have tons more room to grow. don't forget about lots of RAM and a big query cache. they help a lot. this box actually only has 2G of RAM in it but it's doing fine. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grown defects in a RAID array
I've notice that when grown defects (bad blocks on the disk caused by usage over time) that my dedicated mysql server is adversely effected. Since the action of Grown defects does not flush the table with a write lock-that the block marked as bad will effect prior to that block becoming a grown defect-what is another way for mysql to recover from this situation? I'm currently using EXT3 does this same effect happen with Reiser-Fs / UFS / Veritas? I assume you are talking about Scsi disks, which I know about. If not, ignore the following. Defect handling on Scsi disks is done by the drive itself, at a level below both the RAID controller, the file system, or MySQL caching. The drive, once it has noticed a defect, flags it as faulty and substitutes a spare sector from a stock of hidden sectors it keeps. Usually, when it flags a defect, the drive has managed to retrieve your data, but with an excessive level of retries or after having to use its second level, software implemented ECC instead of the hardware based first level ECC. Therefore, by the time the drive manages to report a grown defect, the data has been salvaged and the drive is presenting the appearance of an undamaged disk. This is independent of filesystem, since the drive is managing to simulate a perfect unit even though it is defective. Perfect, that is, in data storage, but not in performance. Defects are replaced in units of sectors - blocks of 512 bytes. However, most systems tend to page files in and out in units of several sectors, for (normal) performance reasons. I don't know what page size MySQL uses, but if it more than 512 bytes, accessing pages with grown defects will be inefficient. For example, imagine a page occupies sectors 100-103 - a 2048 byte page. If sector 102 goes faulty, the drive has to read sectors 100 and 101, seek to wherever it has put the replacement for 102, then seek back to read 103,. This effectively trebles access time. (Actually, the drive is cleverer, and will probably only double access time). Drives vary dramatically in their efficiencies in handling grow defects. It is not a parameter that is usually tested in performance tests (nor, I think, would it be easy to test). Two algorithms that I know had wildly different results. On family was very efficient for the first defect on any cylinder, then dropped off dramatically. Another was less efficient for the first, but could handle several adjacent defects before performance fell off a cliff. All of them, AFAIK, emphasize data retention rather than performance - which I think most users would want. However, I would say that if a disk shows (a) any grown defects in its first three months of life, or (b) more than three or four grown defects *ever*, that disk is on its way out, and should be replaced at the first opportunity. Drive warranties specify a maximum number of grown defects during the warranty period, and it is usually quite small (ten springs from memory, but read your warranty), so if you are getting enough problems to get a significant performance drop, you should be able to exchange your drive. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID or not?
I like using either raid 0+1.. it really cooks, or if you can'y spare the disks, raid 1 ...Something pushing that many queries, should probably be protected from disk failure. On Wed, 20 Aug 2003, Jackson Miller wrote: I am setting up a dedicated MySQL server with some pretty heavy usage. I am not much of a sys admin (mostly a programmer). I have some questions about the best drive configuration. I have 4 SCSI drives currently. I would like to have 1 drive run the OS, 1 drive to be the MySQL data directory and 1 drive to be InnoDB (possibly raw partition). What is the best way for me to configure RAID? Here is the kind of load I am talking about: Uptime: 1749850 Threads: 44 Questions: 1266402021 Slow queries: 16923 Opens: 162177 Flush tables: 1 Open tables: 64 Queries per second avg: 723.720 Thanks, -Jackson -- jackson miller cold feet creative 615.321.3300 / 800.595.4401 [EMAIL PROTECTED] cold feet presents Emma the world's easiest email marketing Learn more @ http://www.myemma.com -- 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: RAID or not?
Jackson, Jackson Miller [EMAIL PROTECTED] writes: I have 4 SCSI drives currently. Well, is you want Redundancy you don't have a choice. Mirror them. 2x 2 drives. You might want to put OS and write-ahead-log on one and InnoDB/MyISAM-data on the other. I would like to have 1 drive run the OS, 1 drive to be the MySQL data directory and 1 drive to be InnoDB (possibly raw partition). Why do you want to use both backends? MyISAM and InnoDB have their own index-cache (key_buffer and innodb_buffer_pool), so you might be better off with just one of them. What is the best way for me to configure RAID? Here is the kind of load I am talking about: Uptime: 1749850 Threads: 44 Questions: 1266402021 Slow queries: 16923 Opens: 162177 Flush tables: 1 Open tables: 64 Queries per second avg: 723.720 These figures are useless. 723q/s is nothing if the layout is simple or the dataset is small or if these are only selects. I've seen quite old servers do 7000q/s with little or no tuning. -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID or not?
On Thursday 21 August 2003 2:23, Jon Drukman wrote: if you're mostly running SELECTs then i would recommend a mirrored configuration. I would say I am running about %50 SELECTS, 30% UPDATE, 20% INSERT. However I don't know how to find that out for sure. Would that affect how I set up the RAID? -Jackson jackson miller cold feet creative 615.321.3300 / 800.595.4401 [EMAIL PROTECTED] cold feet presents Emma the world's easiest email marketing Learn more @ http://www.myemma.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RAID or not?
I say go with RAID 5, on a controller card. Mirroring just gives you backup, and you lose half your diskspace. It offers no performance benefit, and actually the computer might have to work harder to make sure the drives are in sync. Disk striping makes things *fast*, BUT THERE IS NO PROTECTION. If you lose a drive, you are screwed; hope you have a backup. Raid 5 spreads data out over all the disks, and keeps one for checksums or whatever. You lose only one drive to the checksum. You get better performance than mirroring or regular drive, because the data is spread out over your drives. It's not as good as disk striping, though. You get great redudancy, because if you lose one disk, the RAID still operates (in 'degraded mode') -- it's slower, but the server is still up. When you get your replacement drive in, you just hook it up, and the RAID rebuilds itself. So, all in all, RAID 5 gives fault tolerance and better performance. You can have the OS do the RAID, but that puts a lot of burden on the processor and OS. I recommend getting a RAID card, and not a cheap one, either. Plan on spending ~$500. -Original Message- From: Jackson Miller [mailto:[EMAIL PROTECTED] Sent: Thursday, August 21, 2003 11:56 AM To: Jon Drukman Cc: [EMAIL PROTECTED] Subject: Re: RAID or not? On Thursday 21 August 2003 2:23, Jon Drukman wrote: if you're mostly running SELECTs then i would recommend a mirrored configuration. I would say I am running about %50 SELECTS, 30% UPDATE, 20% INSERT. However I don't know how to find that out for sure. Would that affect how I set up the RAID? -Jackson jackson miller cold feet creative 615.321.3300 / 800.595.4401 [EMAIL PROTECTED] cold feet presents Emma the world's easiest email marketing Learn more @ http://www.myemma.com -- 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: RAID or not?
3Ware makes reasonably priced ATA and SATA RAID-5 cards (IDE, not SCSI). You can get hot-swappable enclosures so that when a drive fails, you swap it without shutting down the machine. We are gradually adding this hardware to our webservers, etc so that we don't have to rebuild them when a drive dies. Some good URLs: http://www.3ware.com/ These guys sell the cards at a good price, plus they sell 3rd-party enclosures (I've used them with 3ware, and they work great): http://www.pc-pitstop.com/ David. - Original Message - From: Lefevre, Steven [EMAIL PROTECTED] To: Jackson Miller [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, August 21, 2003 10:20 AM Subject: RE: RAID or not? I say go with RAID 5, on a controller card. Mirroring just gives you backup, and you lose half your diskspace. It offers no performance benefit, and actually the computer might have to work harder to make sure the drives are in sync. Disk striping makes things *fast*, BUT THERE IS NO PROTECTION. If you lose a drive, you are screwed; hope you have a backup. Raid 5 spreads data out over all the disks, and keeps one for checksums or whatever. You lose only one drive to the checksum. You get better performance than mirroring or regular drive, because the data is spread out over your drives. It's not as good as disk striping, though. You get great redudancy, because if you lose one disk, the RAID still operates (in 'degraded mode') -- it's slower, but the server is still up. When you get your replacement drive in, you just hook it up, and the RAID rebuilds itself. So, all in all, RAID 5 gives fault tolerance and better performance. You can have the OS do the RAID, but that puts a lot of burden on the processor and OS. I recommend getting a RAID card, and not a cheap one, either. Plan on spending ~$500. -Original Message- From: Jackson Miller [mailto:[EMAIL PROTECTED] Sent: Thursday, August 21, 2003 11:56 AM To: Jon Drukman Cc: [EMAIL PROTECTED] Subject: Re: RAID or not? On Thursday 21 August 2003 2:23, Jon Drukman wrote: if you're mostly running SELECTs then i would recommend a mirrored configuration. I would say I am running about %50 SELECTS, 30% UPDATE, 20% INSERT. However I don't know how to find that out for sure. Would that affect how I set up the RAID? -Jackson jackson miller cold feet creative 615.321.3300 / 800.595.4401 [EMAIL PROTECTED] cold feet presents Emma the world's easiest email marketing Learn more @ http://www.myemma.com -- 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: RAID or not?
[EMAIL PROTECTED] (Lefevre, Steven) writes: I say go with RAID 5, on a controller card. .. You get better performance than mirroring or regular drive, because the data is spread out over your drives. It's not as good as disk striping, though. Ehh. Wrong. That is not how it works. If you have RAID5 with 4 disks, as we have here, one single write() will have the following effect. 1. The controller will have to read the whole stripe off the array. 3 reads from 3 diffrent discs. 2. Calculate the new checksum for the stripe. 3. Write the modified block back to the disk where it was changed 4. Updated the checksum This works Ok for multimedia and file storage, where you write()-call might be the size of a stripe or bigger. Then you can skip phase 1) on the list above. Ask any DBA; they will all tell you to never_ use RAID 5 for databases with dynamic content. Just don't. As for performance, seek times tend to be higher on a RAID5 array then on a mirror. The only thing which is good with RAID5 is read througput - which might be important for full table scans, but not much else. So, all in all, RAID 5 gives fault tolerance and better performance. Why do you think people use RAID1? You can have the OS do the RAID, but that puts a lot of burden on the processor and OS. CPU is almost never an issue anymore - not for database servers, anyway. The increase in CPU-usage is seldom noticable. I've seen software raid (on Linux 2.4) outrun $2000+ RAID-cards. CPUs are many times faster than the puny i960 or Strongarm CPU which are put on RAID controllers. I recommend getting a RAID card, and not a cheap one, either. Plan on spending ~$500. If you get one. Get one with a battery and write-back cache. They will give you kick-ass performance for those pesky fsync's. -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID or not?
Lefevre, Steven wrote: I say go with RAID 5, on a controller card. Mirroring just gives you backup, and you lose half your diskspace. It offers no performance benefit, and actually the computer might have to work harder to make sure the drives are in sync. that is not true. mirroring gives you double the read speed and half the write speed. RAID5 gives you less than half the write speed. that's why i said if your database app is mostly selects go for mirroring. the OP said his app is about 50% select, so i say mirroring is a good choice. and hey, what's wrong with having a backup? the computer doesn't work any harder, it's all handled through the RAID controller card anyway. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID or not?
In the last episode (Aug 21), Jon Drukman said: Lefevre, Steven wrote: I say go with RAID 5, on a controller card. Mirroring just gives you backup, and you lose half your diskspace. It offers no performance benefit, and actually the computer might have to work harder to make sure the drives are in sync. that is not true. mirroring gives you double the read speed and half the write speed. RAID5 gives you less than half the write speed. Software raid5 gives you around 1/4 the write speed, to be exact. Hardware raid5 with battery-backed cache can completely remove the penalty, by either waiting for an entire stripe of data to flush in one operation, or delaying the extra I/O operations until the disk head happens to be near that block anyway (or until the disk is otherwise idle). Make sure you max out the RAM in your raid card; it's cheap. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Grown defects in a RAID array
I've notice that when grown defects (bad blocks on the disk caused by usage over time) that my dedicated mysql server is adversely effected. Since the action of Grown defects does not flush the table with a write lock-that the block marked as bad will effect prior to that block becoming a grown defect-what is another way for mysql to recover from this situation? I'm currently using EXT3 does this same effect happen with Reiser-Fs / UFS / Veritas? Maybe Jeremy Zawodny can help me with this?
Re: RAID hardware suggestions/experience
Thanks to everybody for the nice discussion. Just to let you know about my (not necessary final) decisions: We will upgrade our SCSI -II controller to an Ultra SCSI 160 controller (always a good idea). Next we are looking into buying a RAID-5 system from RAIDking. While we do this we hope for the best Thanks again for you kind help. Bernd = Please note that this e-mail and any files transmitted with it may be privileged, confidential, and protected from disclosure under applicable law. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reading, dissemination, distribution, copying, or other use of this communication or any of its attachments is strictly prohibited. If you have received this communication in error, please notify the sender immediately by replying to this message and deleting this message, any attachments, and all copies and backups from your computer. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
