Changes between Version 3 and Version 4 of MysqlConfig
- Timestamp:
- Apr 25, 2007, 10:54:45 AM (18 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
TabularUnified MysqlConfig
v3 v4 1 1 = Configuring MySQL for BOINC = 2 2 3 A fast-and-easy script that makes recommendations for tuning server variables is [http://www.day32.com/MySQL/ here]. 3 A fast-and-easy script that makes recommendations for tuning server variables is [http://www.day32.com/MySQL/ here]. The mysqlreport utility gives a variety of information that can be useful for adjusting buffer pool allocations. It's available at [http://www.hackmysql.com/mysqlreport http://www.hackmysql.com/mysqlreport]. 4 4 5 5 ---- … … 7 7 == Introduction == 8 8 9 The note discusses how MySQL may be configured for BOINC Projects. BOINC-based projects have varying DB traffic characteristics and this note relates to our experiences with SETI@home, so it may not be entirely applicable to all projects. SETI@home currently uses MySQL 4.0+ and we expect to upgrade to 4.1 shortly and 5.0 later. Our project uses only a single instantiation of the MySQL code file and this note does not discuss the operation of multiple instances of MySQL on a single server. 9 The note discusses how MySQL may be configured for BOINC Projects. BOINC-based projects have varying DB traffic characteristics and this note relates to our experiences with SETI@home, so it may not be entirely applicable to all projects. SETI@home currently uses MySQL 4.0+ and we expect to upgrade to 4.1 shortly and 5.0 later. Our project uses only a single instantiation of the MySQL code file and this note does not discuss the operation of multiple instances of MySQL on a single server. All MySQL products and documentation are available at http://www.mysql.com/. Our experience has been of using MySQL with Sun Solaris and Linux OSes. MySQL on MS Windows or Mac OS X may be somewhat different. 10 10 11 11 == MySQL DB Engines (or Table Types) == … … 17 17 === MyISAM === 18 18 19 The MyISAM engine requires the least amount of computer resources can be used where there is a low DB activity requirement. For example with query rates lower that 5/sec this table type may be adequate. Also if one does not have a dedicated DB server this may be a good choice for all the tables since it consumes much less computer resources. It has the advantages of allowing long text indices against tables which Innodb does not allow. 20 21 On the other hand it tends to suffer from consistency glitches so will occasionally trash indices and will need rebuilding. In commercial banking environments it would not be a good idea to keep account balances in this table type since there is no guarantee that transactions even if completed and printed will remain in the DB. MyISAM updates its tables synchronously and uses memory locks to avoid data collisions. In SETI@home, MyISAM 19 The MyISAM engine requires the least amount of computer resources can be used where there is a low DB activity requirement. For example with query rates lower that 5/sec this table type may be adequate. Also if one does not have a dedicated DB server this may be a good choice for all the tables since it consumes much less computer resources. It has the advantages of allowing long text indices against tables which Innodb does not allow. MyISAM creates an OS file for each table and one for all the indices related to the specific table (and another for the table format info). 20 21 On the other hand it tends to suffer from consistency glitches so will occasionally trash indices and will need rebuilding. In commercial banking environments it would not be a good idea to keep account balances in this table type since there is no guarantee that transactions even if completed and printed will remain in the DB. MyISAM updates its tables synchronously and uses memory locks to avoid data collisions. In SETI@home, MyISAM is used for the forum tables and logging that have relatively low query rates. 22 22 23 23 === Innodb === 24 24 25 The Innodb engine is used for most of the tables in SETI@home project. It processes multiple simultaneous queries against its tables. It is a versioning DB engine that holds an image of the table at the start of a query and maintains it until that query is completed. Other updates are allowed during queries and in general for short queries there is no problem. Innodb uses the Innodb log to store changes to its tables until it flushes these changes to the actual tables at syncpoints. If for any reason there is a server event that causes a system failure, Innodb will use this log to recover the Innodb tables to consistency. There are a minimum of 2 transaction log files with a total maximum size of 4GB.Innodb tables/indices are usually stored in large OS physical files and the tables and indices are managed internally within these OS/Innodb files. It is important that these files are located on high performance devices. The transaction log files should be located on independent high performance media (away from the Innodb files) for sustained high transaction rates. At DB shutdown all modified buffers have to be flushed into the transaction logs before MySQL goes away, so slow performance drives for the transaction log could delay shutdown for over 30 minutes when there are a large number of modified buffers to be flushed.25 The Innodb engine is used for most of the tables in SETI@home project. It processes multiple simultaneous queries against its tables. It is a versioning DB engine that holds an image of the table at the start of a query and maintains it until that query is completed. Other updates are allowed during queries and in general for short queries there is no problem. Innodb uses the Innodb log to store changes to its tables until it flushes these changes to the actual tables at syncpoints. If for any reason there is a server event that causes a system failure, Innodb will use this log to recover the Innodb tables to consistency. There are a minimum of 2 transaction log files with a total maximum size of 4GB. Innodb tables/indices are usually stored in large OS physical files and the tables and indices are managed internally within these OS/Innodb files. It is important that these files are located on high performance devices. The transaction log files should be located on independent high performance media (away from the Innodb files) for sustained high transaction rates. At DB shutdown all modified buffers have to be flushed into the transaction logs before MySQL goes away, so slow performance drives for the transaction log could delay shutdown for over 30 minutes when there are a large number of modified buffers to be flushed. 26 26 27 27 == Physical Requirements == … … 29 29 === CPU === 30 30 31 Assuming the need for more than 70,000 users and 250K hosts with an average workunit turnaround of about 10 hours then one should get an Opteron dual-core class CPU. It is a 64-bit architecture and can access up to 32GB of RAM. It is qualified to run Solaris, Linux and Windows XP 64-bit (?). There are 64-bit versions of MySQL for Linux and Solaris OSes. 31 Assuming the need for more than 70,000 users and 250K hosts with an average workunit turnaround of about 10 hours then one should get an Opteron dual-core class CPU. It is a 64-bit architecture and can access up to 32GB of RAM. It is qualified to run Solaris, Linux and Windows XP 64-bit (?). There are 64-bit versions of MySQL for Linux and Solaris OSes. This is by no means the only hardware that will work with BOINC/MySQL, however SETI@home uses this type of hardware and serves over 350K user and over 630K hosts. If your requirements are smaller, then many 32bit hardware and OSes may be perfectly adequate. 32 32 33 33 … … 38 38 === IO Subsystem === 39 39 40 Assuming a high performance requirement of more than 200 DB queries/sec there should be separate controllers for for the data and the log files. In the case of Innodb log files it is very important that they are on very reliable media for example mirrored (RAID 1) drives. The tables and indices require wide band or high throughput disk configuration such as RAID 10. 40 Assuming a high performance requirement of more than 200 DB queries/sec there should be separate controllers for for the data and the log files. In the case of Innodb log files it is very important that they are on very reliable media for example mirrored (RAID 1) drives. The tables and indices require wide band or high throughput disk configuration such as RAID 10. Some consideration should be given to having online spare disk drives since this will help to minimize down times in case of failures. 41 41 42 42 == Normal Operations == … … 44 44 === General === 45 45 46 For normal operations or production there are some considerations that should be addressed to enable the project personnel to provide reliable service. For example there should be a reliable power supply with UPS protection to avoid uncontrolled shutdowns. The temperature of the hardware operations room should be regulated to hardware specifications to avoid premature aging/failure of hardware components. 46 For normal operations or production there are some considerations that should be addressed to enable the project personnel to provide reliable service. For example there should be a reliable power supply with UPS protection to avoid uncontrolled shutdowns. The temperature of the hardware operations room should be regulated to hardware specifications to avoid premature aging/failure of hardware components. And the MySQL software has to be set up to take advantage of the hardware resources that are available. 47 47 48 48 49 49 === Config File (my.cnf) === 50 50 51 The config file needs to be set up for production environment. MySQL has defaults for where it allocates the files that it needs; where they are placed depends on the OS on which it is running. For greater control, space management and performance the user should define where these files are assigned.For example the base data directory for MySQL tables etc 51 The config file needs to be set up for production environment. MySQL has defaults for where it allocates the files that it needs; where they are placed depends on the OS on which it is running. For greater control, space management and performance the user should define where these files are assigned.For example the base data directory for MySQL tables etc in Linux is /var/lib/MySQL. For SETI@home we assigned this to directory to another data partition /mydisks/a/apps/mysql/data/, to ensure that there was enough space and performance. It made it easy to do physical backups without including additional files that were not related to the database. Here are some other file directory assignments for the SETI@home environment: 52 52 53 53 {{{ … … 212 212 }}} 213 213 214 This will show the status display and repeat the display every 10 seconds. Adding the .r option will give followup displays that show delta 214 This will show the status display and repeat the display every 10 seconds. Adding the .r option will give followup displays that show delta differences with the first display values. 215 215 216 216 == Performance Tweaking ==
