Hi Banyan, I'm really just talking about basic optimization techniques:
1. Install lots of RAM 2. Convert all table to innodb 3. Allocate about 80% of memory to innodb_buffer_pool_size If you haven't seen this script yet, I suggest you start here: https://launchpad.net/mysql-tuning-primer Regards, Gavin Towey -----Original Message----- From: Banyan He [mailto:ban...@rootong.com] Sent: Friday, August 07, 2009 11:12 AM To: Gavin Towey; joerg.bru...@sun.com; Peter Chacko Cc: mysql Subject: Re: Question about MySQL Hi Gavin, I am interested in the things you made for the optimization. Can you share with us such things? Thanks a lot, -- Banyan He Network & System Security Infrastructure Mail: ban...@rootong.com Blog: http://www.rootong.com/blog LinkedIn: http://www.linkedin.com/in/banyanhe Website: http://www.rootong.com > From: Gavin Towey <gto...@ffn.com> > Date: Fri, 7 Aug 2009 11:07:19 -0700 > To: "joerg.bru...@sun.com" <joerg.bru...@sun.com>, Peter Chacko > <peterchack...@gmail.com> > Cc: mysql <mysql@lists.mysql.com> > Subject: RE: Question about MySQL > > I always accepted that NFS was unacceptably slow for database access, until I > actually tested it. Turns out that with lots of RAM and properly tuned > caches, you're optimizing for minimal IO anyway. A good file server will have > massive amounts of IO OPS. On top of that if you're using GigE over few hops, > then it's really not slower than local disks. > > Remember: benchmark and test your assumptions! > > Regards, > Gavin Towey > > -----Original Message----- > From: joerg.bru...@sun.com [mailto:joerg.bru...@sun.com] > Sent: Friday, August 07, 2009 1:19 AM > To: Peter Chacko > Cc: mysql > Subject: Re: Question about MySQL > > Hi Peter, all, > > > let me just concentrate on the NFS aspect: > > > Peter Chacko wrote: >> [[...]] >> >> Another question is , whats the general experience of running MySQL >> servers on NFS shares ? > > I would *never* use NFS storage for any DBMS (except for some testing): > NFS access is slower than local disk access, and it adds more components > to the critical path. So your operations get slower, reliability > decreases, and (in case of any trouble) analysis becomes more difficult. > > I cannot imagine any setup where you have a machine strong enough to run > your DBMS on it, but not capable of driving sufficient local disks. > > The typical argument for having centralized disks serving many machines > is based on economies of scale (huge disks), flexibility (partitioning), > and centralized management (RAID replacement, backup). > There may be some merit to this in a specialized setup (NAS systems - > I'm not convinced of them, but don't claim expert knowledge about them), > but IMO not using general-purpose machines and NFS. > > Whatever the economical advantages during normal operation may be, you > should not forget the huge costs you would incur if any in-between > component breaks and your database stops operating. > This may be tolerable for some applications, depending on the required > availability, but simply intolerable for others. > > >> We are working on developing a custom >> protocol for MySQL clustering that takes care of all file management >> as part of the database clustering protocol, rather than a storage >> engine feature. > > Sorry, I don't get the meaning of this in sufficient detail to comment. > > >> What are the likely setup of a database storage ? Is >> it on RAW partition or on a File system ? > > That may depend on the storage engine used. > MyISAM uses a file-per-table approach, so must be run on a file system; > InnoDB may be able to use a raw partition (I'm not sure there); > for others, I can't tell. > > >> Will ocfs2 be better used >> for mySQL as well, in a clustered environment ? > > I strongly doubt it. > > AIUI, the big advantage of cluster file systems is that they allow all > machines to access and modify shared data. > With a DBMS, you don't want to share data across machines, you want the > database to be accessed only be the one machine (possibly multi-CPU, but > shared memory) running the database processes, because on that machine > you have the various database caches. > Then, that machine makes the data available to all clients, so you get a > logical sharing on a higher protocol level (SQL). > > To have multiple machines accessing the same database storage, you would > first need some protocol to ensure cache coherency, and that is not > contained in MySQL (in the general server). > To use MySQL on multiple machines for the same data, you set up replication. > > The alternative approach would be to use MySQL Cluster, which is > designed to hold the data in main memory (for extremely low latency) and > to use the disk only for backup purposes. > > >> >> I would appreciate if any one share with me their thoughts on this. > > My comments above are based on my experience during DBMS development > (including distributed DBMS), but not on any financial calculations or > DBA work. Weigh them with other answers. > > > Regards, > Jörg > > -- > Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com > Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin > Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel > Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=gto...@ffn.com > > > The information contained in this transmission may contain privileged and > confidential information. It is intended only for the use of the person(s) > named above. If you are not the intended recipient, you are hereby notified > that any review, dissemination, distribution or duplication of this > communication is strictly prohibited. If you are not the intended recipient, > please contact the sender by reply email and destroy all copies of the > original message. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=ban...@rootong.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
