MySQL Administrator's Bible- P9

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

141
lượt xem 31
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

MySQL Administrator's Bible- P9: Với tập trung đặc biệt vào việc phát hành lớn tiếp theo của MySQL, nguồn tài nguyên này cung cấp một khuôn khổ vững chắc cho bất cứ ai mới vào MySQL hoặc chuyển từ một nền tảng cơ sở dữ liệu, cũng như các quản trị MySQL kinh nghiệm. Các bộ đôi tác giả cao hồ sơ cung cấp bảo hiểm thiết yếu của các nguyên tắc cơ bản của phạm vi bảo hiểm cơ sở dữ liệu quản lý, bao gồm cả cách tiếp cận độc đáo MySQL của các tính năng cơ sở...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: MySQL Administrator's Bible- P9

MySQL Server Tuning 10 InnoDB storage engine options InnoDB is a very widely used storage engine in production systems. There are a number of con- ﬁguration options for the InnoDB engine, and Table 10-4 covers these options. TABLE 10-4 InnoDB Conﬁguration Options Option Name Purpose innodb_buffer_pool_size = A static variable that speciﬁes the size of the cache buffer_size for InnoDB data and indexes. innodb_flush_log_at_trx_ There are three possible options {0|1|2}. This commit = number dynamic system variable manages how often the InnoDB log buffer is written (ﬂushed) to the log ﬁle. innodb_flush_method = This static variable determines how the InnoDB IO_access_method storage engine interacts with the operating system with respect to I/O operations. innodb_log_buffer_size = Buffer used for writes to the InnoDB logs. Unless you buffer_size use very large BLOBs this static variable should not be over 8 MB, and can be set to 2 Mb. innodb_log_file_size = A static variable that determines the size of each log_file_size Innodb log ﬁle (ib_logfile). innodb_log_files_in_group = A static variable that determines the total number of number_log_files Innodb log ﬁles. innodb_max_dirty_pages_pct= N This dynamic variable speciﬁes the maximum percentage of pages in the in Innodb buffer pool that can be dirty — that is, changed in the buffer pool in memory without being saved to disk. Defaults to 90 (%). innodb_thread_concurrency = N This dynamic variable determines the maximum number of system threads inside InnoDB. A good number to start is twice the number of CPUs. All of the InnoDB server variables are GLOBAL in nature. The single most important InnoDB conﬁguration variable is the innodb_buffer_pool_size. Assuming the server is only running mysqld and most of your tables are InnoDB tables, the majority of your memory should be dedicated to the InnoDB buffer pool. It is safe to begin at 367
Part III Core MySQL Administration 50–70 percent of your system memory allocated the InnoDB buffer, and adjust up or down as you need to. If you have a large amount of RAM (16 Gb or more) on a dedicated MySQL server, then the buffer pool can be an even larger percentage of overall memory. When conﬁguring a server, choose a starting value for the InnoDB buffer pool, set the other conﬁguration values, then determine how much memory is still available. On Unix, the vmstat, top, and free commands show memory information. In Windows, the Task Manager can show you memory usage. To determine if the InnoDB buffer pool is appropriately sized, run: SHOW GLOBAL STATUS LIKE ’innodb_buffer_pool_pages%’; ■ Innodb_buffer_pool_pages_data is the total number of used data pages (clean and dirty). ■ Innodb_buffer_pool_pages_dirty is the number of dirty data pages. The number of clean data pages can be calculated from these ﬁrst two status variables. ■ Innodb_buffer_pool_pages_flushed is the number of data pages that have been ﬂushed to disk. ■ Innodb_buffer_pool_pages_free is the number of unused data pages. ■ Innodb_buffer_pool_pages_misc is the number of data pages used for InnoDB over- head. ■ Innodb_buffer_pool_pages_total is the total number of pages. Calculate the ratio of unused data pages to the total number of pages: Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total If the ratio is high (close to 1), then the InnoDB buffer pool is probably set too high. A less likely cause is that the innodb_max_dirty_pages_pct system variable is set too low, and dirty pages are being ﬂushed very often, freeing up pages long before they are needed. Conversely, if the ratio is low, the size of the InnoDB buffer pool may need to be set higher. Using the information you have about the free memory on your system, increase the InnoDB buffer pool size, restart mysqld, and continue to monitor the status variables after the newly sized InnoDB buffer pool has been used for a while. Continue the adjust-monitor-adjust cycle, and once your system is at the right level, continue to monitor levels, making sure to check performance once every month or two. Make sure to always leave a buffer of a half-gigabyte or so of memory because mysqld performs very poorly when it is forced to use swap space. Keep in mind that under high load, mysqld will use more memory. 368
MySQL Server Tuning 10 The innodb_flush_log_at_trx_commit system variable is used to manage how often the InnoDB log buffer sends writes to the InnoDB log. When this parameter is set to 0, the log buffer is written every second and the logs ﬁle ﬂushes to disk. When this value is 1 (the default), every commit will make the log buffer write to the log ﬁle. The log ﬁle is ﬂushed to disk on each commit as well. This is required for ACID compliance. For more information on ACID compliance and transactions, see Chapter 9. When set to 2, every commit makes the log buffer write to the ﬁle, just as when the value is 1. However, the log ﬁle ﬂushes to disk every second, just as when the value is 0. Setting this vari- able to 0 or 2 changes the database to no longer be ACID–compliant — it does not meet the requirements for durability. Because of the log ﬁle ﬂushing being different from the transaction commit, it is possible that a crash could lose a second of transactions (actually, slightly more than a second, because of process-scheduling issues). When the variable is set to 0, a crash of mysqld or the operating system may cause this lack of durability; when this variable is set to 2, only an operating system crash may cause this lack of durability. Note that many operating systems and some disk hardware tell mysqld that the ﬂush has taken place even though a ﬂush has not happened. In these cases, the durability requirement of ACID compliance not met, regardless of the value of innodb_flush_log_at_trx_commit. A crash (for example, due to a power outage) can even corrupt the InnoDB database. Using a battery-backed disk cache in the disk or disk controller will protect against this scenario, and regular ﬁle ﬂushes will be faster, too. The innodb_flush_method variable has three possible values: ■ fsync is the default option and uses the fsync() system call to ﬂush both data and log ﬁles. ■ O_DIRECT will bypass the operating system cache for both reads and writes of data and log ﬁles. ■ O_SYNC uses the fsync() system call for data ﬁles but for log ﬁles uses O_SYNC. There are many times when using O_DIRECT will signiﬁcantly improve performance of mysqld. This is because it removes the buffering of the operating system. Do not use O_DIRECT with- out using a RAID controller that has a battery backed write cache. This can overcome problems because of operating system crashes that otherwise do not complete a write to the hard drives. In addition, you should enable what is called writeback on the cache. When the server sends data to be ﬂushed to the RAID controller, the controller immediately tells the server the ﬂush is complete. The server considers it committed and is free to do other task. The RAID controller then ﬂushes the writes stored in the cache periodically. This batches the writes and makes them more efﬁcient. If you are considering using O_DIRECT, carefully test your setup to make sure you are getting the best performance possible. With some conﬁgurations using O_DIRECT can actually impede performance so be careful! 369
Part III Core MySQL Administration Using O_SYNC is usually a slower alternative than using O_DIRECT, but there are some edge cases where it can prove to be faster. As with O_DIRECT, it is important that you test your setup to see if server performance beneﬁts. On very write-intensive systems a performance boost can be found by creating larger InnoDB log ﬁles. The reason why is that the larger the size of the log ﬁle, the less checkpoint ﬂush activity, which saves disk I/O. However, the larger your log ﬁles are the longer the recovery time will be after a crash. The default size for the InnoDB log ﬁles is only 5 MB. Even with 64-MB log ﬁles you should have recovery times under a minute. The maximum size is 4 GB for all the InnoDB log ﬁles. It is very common to set these to between 128 and 256 MB. See tip for how to change the InnoDB log ﬁle size. To change the size of your InnoDB log ﬁles: ■ Shut down mysqld. ■ Edit the conﬁguration ﬁle, setting a new log ﬁle size with the innodb_log_file_size option. ■ Move the existing InnoDB log ﬁles to a backup location. ■ Start mysqld. ■ Verify that the new log ﬁles are the correct size. ■ The previous InnoDB log ﬁles can be deleted. The innodb_max_dirty_pages_pct server variable sets the percentage of pages allowed to be changed ("marked dirty") before a ﬂush to disk is performed. A page in this context is a ﬁxed amount of system memory. With the InnoDB storage engine a page is 16k in size. Allowing a higher percentage of dirty pages before a disk ﬂush could increase performance. The default is 90 percent. Falcon storage engine options The new Falcon storage engine is designed to utilize large amount of memory to increase perfor- mance. Table 10-5 shows the conﬁguration option that will affect performance on your server. TABLE 10-5 Falcon Conﬁguration Options Option Name Purpose falcon_record_memory_max=buffer_size Sets the maximum size of the data cache 370
MySQL Server Tuning 10 The falcon_record_memory_max variable is used to determine the size of the buffer used for the Falcon storage engine. If your server is only using Falcon tables then this should be set to use most of the available memory (much like the previous suggestions for the InnoDB buffer pool). Maria storage engine options The Maria storage engine is designed to be a replacement for MyISAM. It has many similar characteristics to MyISAM but includes transactional support and automatic crash recovery. Table 10-6 shows the conﬁguration option that affects performance for the Maria storage engine. TABLE 10-6 Maria Conﬁguration Options Option Name Purpose maria_pagecache_ Conﬁgures the cache size for data and index pages. This buffer_size is similar to the InnoDB buffer pool. If you are using a large number of Maria tables you should increase the buffer size. By default it is only 8 MB. Query cache options Effectively utilizing the query cache can signiﬁcantly improve the performance of mysqld. The query cache is covered in great detail in Chapter 12, including explanations of the options and how to tune the query cache. Dynamic variables So far, we have been discussing how to change your system variables by modifying the con- ﬁguration ﬁle. While changing the option ﬁle is necessary for a change that will persist across mysqld restarts, there are times when you do not want to restart mysqld to change a system variable — perhaps you are testing and only want to set a variable temporarily. However, it is possible to dynamically change many of the server variables. For example, the variables relating to query cache setup and management can be changed without a server restart. However, the innodb_buffer_pool_size variable cannot be changed dynamically and requires a server restart with the option speciﬁed. Session variables, by their nature, are always dynamic, as they are set per session. 371
Part III Core MySQL Administration If you make a change to a dynamic variable while it is running, do not forget to change your conﬁguration ﬁle or the next time you restart the server you may have an unexplained performance drop, or other behavior revert to an unpleasant state. The MySQL manual maintains a list of mysqld system variables, their scope (GLOBAL or SESSION), and if they are dynamic or static. The web page for version 6.0 is: http://dev.mysql.com/doc/refman/6.0/en/server-system-variables.html Similarly, the MySQL manual page for status variables, which can be used for monitoring perfor- mance, is located at: http://dev.mysql.com/doc/refman/6.0/en/server-status-variables.html To access information for other release series (5.1, for example), just replace the 6.0 in the above address with the release series. If a system variable can be changed dynamically it is modiﬁed using the SET GLOBAL, SET SESSION, SELECT @@global or SELECT @@session command. Recall our previous example of the SHOW GLOBAL STATUS command and the temporary tables for the server with a 54 percent conversion ratio of in-memory temporary tables to on-disk temporary tables. To see the current maximum temporary table size, use the following SHOW VARIABLES command: mysql> SHOW GLOBAL VARIABLES LIKE ’%tmp%’; +-------------------+----------+ | Variable_name | Value | +-------------------+----------+ | max_tmp_tables | 32 | | slave_load_tmpdir | /tmp/ | | tmp_table_size | 33554432 | | tmpdir | /tmp | +-------------------+----------+ 4 rows in set (0.00 sec) This shows a current setting of 32 MB. To increase this to 48 MB, we issue either of the follow- ing commands: mysql> SET GLOBAL max_tmp_tables=48; Query OK, 0 rows affected (0.00 sec) mysql> SET @@global.max_tmp_tables=48; Query OK, 0 rows affected (0.00 sec) After some time, recalculate the conversion ratio to see if the change is helping. Of course, once a ﬁnal decision is made on the size allowed for temporary tables, edit the conﬁguration ﬁle and set the new value to persist when mysqld restarts. 372
MySQL Server Tuning 10 SUMMARY This chapter covers a great deal of information. While there is not enough space to cover every part of server tuning you should understand the basics of tuning the hardware, operating sys- tem, and mysqld. Topics covered included: ■ Choosing the best hardware ■ CPU choice ■ Memory ■ Disk storage ■ Operating system tuning ■ Choosing an operating system ■ File system tuning ■ Tuning mysqld ■ The conﬁguration ﬁle ■ Storage engine conﬁguration ■ Dynamic variables 373
Storage Engines T he storage engines of MySQL are one of the most unique features of the server. Approximately twenty major storage engines are IN THIS CHAPTER currently available, and though they allow for the ultimate Understanding storage engines ﬂexibility when working with your data, this diversity can be intimidating to the beginning or even intermediate level database administrator. Most Using different storage engines database administrators regularly work with two storage engines, MyISAM and InnoDB, and may use others in a handful of projects. Working with storage engines Understanding Storage Engines A storage engine is a subsystem that manages tables. Most database man- agement systems have one subsystem to manage tables; MySQL Server can use different subsystems. Because a storage engine is applied at the table level, it is sometimes called table type. CREATE TABLE and ALTER TABLE statements can use the ENGINE option to set (or change) the storage engine that is associated with the table. The MySQL pluggable storage engine is an architectural design that sepa- rates the code that manages the tables from the database server core code. This core code manages the components such as the query cache, the opti- mizer, and the connection handler. The storage engine code handles the actual I/O of the table. This separation of code allows for multiple storage engines to be used by the same core server. Once you have the ability to have multiple storage engines at the same time, the database administrator can choose a storage engine based on its ability to meet the requirements of an application. This is vastly different from most other database manage- ment systems, where there is no choice. 375
Part III Core MySQL Administration Though each storage engine handles its own I/O, mysqld requires a table format ﬁle for each ﬁle. These ﬁles have an .frm extension. Having different storage engines allows MySQL to have many different features, and many storage engines are produced by third-party companies. InnoDB, the most frequently used transactional storage engine, is actually produced by a different company. This means that people can develop storage engines that meet their own needs, without having to wait for a feature to be released. Storage engines as plugins Even though the name implies that storage engines are easily added and removed from MySQL, it was only starting in MySQL Server version 5.1 that storage engines have been able to be plugins. The pluggable part of pluggable storage engines reﬂects the separation of code, not the nature of how to add a storage engine (compiled-in vs. plugin). Innobase Oy, the company that created the InnoDB storage engine (www.innodb.com), has a plugin version of its storage engine. This plugin includes several features not available in the compiled-in InnoDB that ships with MySQL, including: ■ Ability to ADD or DROP indexes (except primary keys) without requiring a table copy ■ On-the-ﬂy compression and decompression of table data ■ New tables in the information_schema database Another storage engine that uses the same plugin methodology is the PBXT engine developed by PrimeBase Technologies (www.primebase.org). The beneﬁt to this architecture is immediately obvious; the release cycle of the storage engine plugin can be completely independent from the server. Neither Innobase nor Primebase Technologies have to wait for Sun Microsystems to release a new version of MySQL Server for a bug ﬁx or a feature addition to either stor- age engine. A new version of the storage engine plugin can be released at any time and an administrator can upgrade just that one component. One example of how this is beneﬁcial is that beginning with version 5.1, MySQL Server allows a storage engine to be able to create or drop indexes without copying the contents of the entire table. However, a storage engine has to write the code to implement this functionality. The InnoDB storage engine integrated into MySQL Server version 5.1 does not take advantage of this capability. With the InnoDB plugin, however, users can add and drop non-primary indexes much more efﬁciently than with prior releases. Using the plugin, a database administrator can upgrade the plugin instead of the entire MySQL Server, and have this functionality. Storage engine comparison You can easily use multiple storage engines in a single application. This ability to use multiple storage engines can lead to optimal results for the application. This is because different parts of the application will have different requirements. One storage engine cannot be a perfect ﬁt 376
Storage Engines 11 for every situation. Using multiple storage engines allows you to ﬁt the storage engine to the requirements of any particular area of the application. What are some of these features and abilities that make one storage engine different from another? ■ Transaction support — Support of transactions requires more overhead in terms of memory, storage space, and CPU usage. Every application does not require transactions and using a non-transactional storage engine can be faster in some cases. ■ Table-level features — MySQL provides a handler for tables to have a CHECKSUM attribute, which can be seen in the TABLES system view in the INFORMATION_SCHEMA database. Whether or not the table has a value for CHECKSUM depends on the storage engine — only MyISAM currently handles the CHECKSUM attribute. ■ Locking — MySQL Server supports the ability to lock an entire table. However, storage engines can implement their own locking methods, to be able to lock at more granular levels, such as locking a set of rows. Further locking granularity implemented by the designers of the storage engine helps determine the amount of overhead, the overall speed, the possibility for lock contention, and the ability to support higher concurrency workloads. ■ Index implementation — Different applications can beneﬁt from different index imple- mentation strategies. Several common methods of implementing indexing exist and the designers of each storage engine choose the one they think will perform best in their tar- geted situation. ■ Foreign keys — Using foreign keys to enforce relational integrity among tables is quite common. However, not every application needs foreign keys and many storage engines do not support them. ■ Buffering — Data, index, and log buffers are handled by storage engines. Some choose not to implement buffering in some areas at all, and others can allow multiple buffers. For example, MyISAM does not have a buffer for data, but supports multiple buffers for indexes. ■ File storage — Some storage engines store their data and indexes in self-contained ﬁles, meaning that a table can be copied by copying the ﬁles. Other storage engines use centralized metadata, and thus a table cannot be copied simply by copying the data and index ﬁles. ■ Backup — Some storage engines have tools to allow consistent, non-blocking backups to be taken, whereas others will cause application disruption if a backup is run while the table is in use by the application. Table 11-1 provides a summary table of some of the more common storage engines with their higher-level features. Because you have such ﬂexibility and choice with MySQL Server, you should carefully weigh your application’s requirements before selecting a particular storage engine for use. Though it is easy to change the storage engine that handles a table, the entire table must be rebuilt, which can take a long time for large tables. 377
Part III Core MySQL Administration TABLE 11-1 MySQL Server Storage Engine Overview Transactional Online Non-blocking Server Version(s) Storage Engine Support Locking Level Backup Available MyISAM / Merge No Table No 5.1, 6.0 InnoDB Yes Row Yes 5.1, 6.0 MEMORY No Table No 5.1, 6.0 Maria Yes Row No 5.1, 6.0 Falcon Yes Row Yes 6.0 PBXT Yes Row Yes 5.1, 6.0 FEDERATED No Not applicable Not applicable 5.1, 6.0 NDB Yes Row Yes 5.1 up to 5.1.24; After that, available in MySQL Cluster Archive No Row No 5.1, 6.0 Blackhole No Not applicable Not applicable 5.1, 6.0 CSV No Table No 5.1, 6.0 Using Different Storage Engines Now that we have covered the basics of the storage engines, it is time to cover in some depth the most used storage engines available. This will give you a good idea of which storage engine might be best for your application and how to best utilize the storage engine you choose. The default storage engine for mysqld is MyISAM. To change this, set the ON the WEBSITE default_storage_engine option in the conﬁguration ﬁle. For example: default_storage_engine=InnoDB MyISAM storage engine MyISAM is the default storage engine in mysqld, and is the storage engine used by the system tables. It has been a reliable storage engine for MySQL Server since MySQL Server version 3.2, replacing the original ISAM engine. It is non-transactional and does not implement additional locking mechanisms. MyISAM depends on the global table-level locking in MySQL, but because it has very little overhead can be quite fast for reads. However, a large number of concurrent 378
Storage Engines 11 writes to a MyISAM table can be problematic. If your application has a lot of write activity, the writes will end up blocking the reads and your database server might exhibit a high number of connections to the server as SELECT statements are blocked waiting for any write threads to complete. If your application has this problem you should consider using a storage engine such as InnoDB or Falcon that locks at the row level instead of the table level. One method of reducing the contention between reads and writes to a MyISAM table is allowing what are called concurrent inserts. Concurrent inserts allow more than one insert at a time to be added at the end of a table. The concurrent_insert option to mysqld defaults to 1, meaning that inserts are allowed at the same time as reads if there are no data gaps caused by UPDATE or DELETE statements. A value of 2 indicates that inserts are allowed regardless of data gaps, and a value of 0 means concurrent inserts are not allowed. There are three ﬁles on disk that represent a MyISAM table. Those three ﬁles have the table name and an extension, where the extension is either frm for the table format ﬁle, MYD for the data ﬁle, or MYI for the index ﬁle. A nice feature of MyISAM tables is that the three ﬁles compromising a table are the entire table. This means they can be copied without any problem from the server to a backup location for a raw backup or even directly to another server for use on a new server. There are two stipulations. The ﬁrst problem is that the tables should not be written to when the ﬁles are copied off or there is a possibility of corruption. This can be accomplished with a read lock or by shutting down mysqld. The second problem is that the target server must be of the same endian format as the source server. This simply means that the servers use the same byte order. There are two endian formats — little endian and big endian. As an example, you cannot copy MyISAM tables from an x86 or x86_64 server to a SPARC server, because they do not have the same endian format. However, you could copy from an x86-based Linux server to a Windows-based server, because they use the same endian format. Feature summary: ■ Non-transactional ■ No foreign key support ■ FULLTEXT indexes for text matching ■ No data cache ■ Index caches — can be speciﬁed by name ■ Implements both HASH and BTREE indexes (BTREE by default; see Chapter 6 for more information about indexes) ■ Table-level locking ■ Very fast read activity, suitable for data warehouses ■ Compressed data (with myisampack) ■ Online backup with mysqlhotcopy (see Chapter 13) ■ Maximum of 64 indexes per table 379
Part III Core MySQL Administration MyISAM conﬁguration options A number of my.cnf conﬁguration options are used for MyISAM tables. Table 11-2 lists the common conﬁguration options for MyISAM tables. TABLE 11-2 MyISAM Conﬁguration Options Conﬁguration Option Description key_buffer_size Determines the size of the memory cache used for storing MyISAM indexes. MyISAM depends on the operating system to cache MyISAM data. The default is 8 Mb, and the maximum is 4 Gb. concurrent_insert Determines the behavior of concurrent inserts. Concurrent inserts in tables with no data gaps are enabled (set to 1) by default. A setting of 0 disables concurrent inserts and a setting of 2 allows concurrent inserts for tables with data gaps. delay_key_write Delays updating indexes for MyISAM tables until tables are closed. This will provide a boost in performance but tables will be corrupted if mysqld crashes. The default is ON, which means that MyISAM tables that have the DELAY_KEY_WRITE option deﬁned (in a CREATE TABLE or ALTER TABLE statement) will delay index updates. Other values are OFF, disabling delayed index writes entirely, and ALL, which will make all MyISAM tables delay index writes. max_write_lock_count Determines how many writes to a table take precedence over reads. This could resolve issues with read starvation if there are constant writes to a table. This works with storage engines that use table-level locking so it applies to both MyISAM and MEMORY tables. The default is 4294967295 (which is the maximum), to give high precedence to writes. See Chapter 4 for more information on the HIGH_PRIORITY and LOW_PRIORITY options to queries, and Chapter 9 for an explanation of locking precedence. preload_buffer_size Determines the size of the buffer used for index preloading of the key cache. The default size is 32 Kb. MyISAM utilities Three utility programs are designed for working with MyISAM tables: ■ myisamchk — Used to analyze, optimize, and repair MyISAM tables. ■ myisampack — Used to create compressed, read-only MyISAM tables. ■ myisam_ftdump — Used to display information about fulltext ﬁelds in MyISAM tables. 380
Storage Engines 11 Each of these programs provides for speciﬁc uses. Each program must be run locally on the server where the MyISAM tables are located. myisamchk The myisamchk program has four different modes of operation. It can be used to analyze, optimize, check, and repair MyISAM tables. The default mode of operation is the check mode where it checks for possible corruption. Though you can specify a specify table, the myisamchk program also works with wildcard conditions. This makes it easy to have it check all tables in a database. The following command will check all the MyISAM tables in the mysql database: $ myisamchk /var/lib/mysql/mysql*.MYI Checking MyISAM file: columns_priv.MYI Data records: 0 Deleted blocks: 0 - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 --------- Checking MyISAM file: db.MYI Data records: 0 Deleted blocks: 2 - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 The output is too long to include all of it here, but you can see what is happening. The utility actually checks the index ﬁles of the MyISAM tables. If it had returned that one of the tables needed repair you could just run: $ myisamchk –r /var/lib/mysql/mysql/table_name.MYI When running myisamchk you must manually block all access to the tables being checked. Otherwise, corruption could occur. The easiest and best way to accomplish this is simply to shut down mysqld. If you need to check tables while mysqld is running, con- sider using the CHECK TABLE command as described in Chapter 4. myisampack Using myisampack to create compressed read-only versions of tables can provide for a good performance increase for data that is no longer being updated but still needs to be accessed. As with myisamchk, it is best to stop the database server before running, although ensuring the 381
Part III Core MySQL Administration tables are not written to (for example, doing a FLUSH TABLES WITH READ LOCK) is another way to avoid corruption. Though the Archive storage engine has better compression than read-only MyISAM tables, the Archive storage engine can support only one index. A compressed MyISAM table is read-only, and cannot have new rows inserted like an Archive table can. The basic running of myisampack is very simple. In the data directory of the database the table is in, run: shell> myisampack table_name.MYI Remember to specify the MyISAM index ﬁle (.MYI) of the table you are compressing. Once the compression is done you have to run the myisamchk program to rebuild indexes. For optimal performance you can also sort the index block and analyze the table to help the opti- mizer work better: shell> myisamchk --rq --sort-index –analyze table_name_MYI If the compression process was done while the server is online, release any read locks on the table and issue a FLUSH TABLES command to force mysqld to recognize and begin using the new table. If mysqld was shut down, restart it. Additional information is available on the various options in the MySQL Manual at http:// dev.mysql.com/refman/6.0/en/myisampack.html. myisam_ftdump The myisam_ftdump program will provide information about the FULLTEXT indexes in MyISAM tables. When running myisam_ftdump you must specify which index you want the program to ana- lyze. You can determine this by looking at the output of the SHOW CREATE TABLE command: mysql> SHOW CREATE TABLE film_text\G *************************** 1. row *************************** Table: film_text Create Table: CREATE TABLE `film_text` ( `film_id` smallint(6) NOT NULL, `title` varchar(255) NOT NULL, `description` text, PRIMARY KEY (`film_id`), FULLTEXT KEY `idx_title_description` (`title`,`description`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) Notice that FULLTEXT KEY is listed second, after the PRIMARY KEY. To specify the appropriate index you provide a number. The numbering begins at 0 so this text index is number 1. Now to 382
Storage Engines 11 run myisam_ftdump, change to the directory where the table ﬁles are (for example, /var/lib/mysql/sakila) and run: shell> myisam_ftdump film_text 1 Total rows: 1000 Total words: 9602 Unique words: 1080 Longest word: 13 chars (administrator) Median length: 7 Average global weight: 5.904181 Most common word: 158 times, weight: 1.673185 (boat) If you do not run myisam_ftdump in the directory where the table ﬁles are, you will receive an error such as: got error 2 Additional information about myisam_ftdump is available from the MySQL Reference Manual at http://dev.mysql.com/doc/refman/6.0/en/myisam-ftdump.html. Merge storage engine The Merge storage engine is actually a sort of wrapper table that wraps around MyISAM tables with the same schemas. All the underlying tables can be queried at once by querying the Merge table. This is one way to implement partitioning; see Chapter 15 for more information on par- titioning and Merge tables. Using Merge tables is one solution for typical reporting needs where you have massive tables of data. Merge tables will use the same buffers and conﬁguration options as for the underlying MyISAM tables so conﬁguration options will not be covered here. Please refer to section ‘‘MyISAM Con- ﬁguration Options’’ in this chapter for the various options available. See Chapter 15 for actual examples of CREATE TABLE statements to create Merge tables. When you create a Merge table, two ﬁles are always created in the ﬁle system. There is one ﬁle containing the table format that has a ﬁlename of the table followed by a sufﬁx of .frm. The second ﬁle also has a ﬁlename of the table but ends with a sufﬁx of .MRG. This ﬁle contains the names of the underlying MyISAM tables. After creating the Merge table, you can query the underlying individual tables or the Merge table. When a SELECT is executed against the Merge table it begins with the ﬁrst table speciﬁed in the Merge table deﬁnition. Where INSERT statements occur depends on setting of the INSERT_METHOD clause, as discussed in Chapter 15. The REPLACE statement does not work when executed against a Merge table. The beneﬁts of using Merge tables are better manageability of tables and better performance. When your table sizes become large your maintenance and repair operations will take a long time. Using a Merge table with smaller underlying tables not only speeds up these operations 383
Part III Core MySQL Administration because of the smaller table size, but it will also allow you to rotate out the table from use by modifying the Merge table deﬁnition to exclude it while maintenance is occurring. The performance beneﬁts actually come in several ways. With Merge tables it is perfectly feasi- ble to use compressed MyISAM tables, which leads to less use of disk space and faster searches of the tables. You can also put the individual MyISAM tables on different hard drives to help increase performance. Also, you can query individual MyISAM tables as well as the Merge table. This can sometimes provide a tremendous performance boost. InnoDB storage engine The most widely used transactional storage engine is the InnoDB storage engine. InnoDB brought support for foreign keys to mysqld. Feature summary: ■ Transactional support provided by MVCC (Multi Version Concurrency Control) ■ Row-level locking ■ Foreign key support ■ Indexing using clustered B-tree indexes ■ Conﬁgurable buffer caching of both indexes and data ■ Online non-blocking backup through separate commercial backup program InnoDB provides some scalability on up to eight CPU cores and 64 gigabytes of RAM. It sup- ports a high level of concurrent writes and is heavily used in typical online transactional envi- ronments. Tablespace conﬁguration variables With the InnoDB storage engine you have control over the format and the location of the tablespace. A tablespace is a logical group of one or more data ﬁles in a database. Table 11-3 lists the variables used to conﬁgure the tablespace. The full path to each shared tablespace is formed by adding innodb_data_home_dir to each path speciﬁed in the innodb_data_file_path. The ﬁle sizes are speciﬁed in kilobytes, megabytes, or gigabytes by appending K or M or G to the size value, otherwise numbers are assumed to be in bytes. The centralized data ﬁles must add up to 10 Mb or more. A raw disk partition can be used as a shared tablespace. By default if innodb_data_file_path is not deﬁned, a 10 Mb ibdata1 ﬁle is created in the data directory (datadir). The maximum size of an InnoDB shared tablespace depends on the operating system. 384
Storage Engines 11 TABLE 11-3 Tablespace Conﬁguration Variables Tablespace Conﬁguration Variable Description Innodb_data_file_path Determines both the path to individual centralized data ﬁles (shared tablespace) and the size of the ﬁles. Innodb_data_home_dir The common part of the directory path for all InnoDB data ﬁles. If you do not explicitly set this value it will default to the MySQL data directory. You can specify the value as an empty string, in which case you must use absolute ﬁle paths in the innodb_data_file_path variable. Innodb_file_per_table If innodb_file_per_table is enabled, then new InnoDB tables will be using their own .ibd ﬁle for both data and indexes rather than in the shared tablespace. There is still a common tablespace used for metadata. The default is to store data and indexes in the shared tablespace. You cannot move InnoDB table ﬁles around as you can MyISAM tables. When the innodb_file_per_table option is set, the .ibd ﬁle contains the data and indexes for an InnoDB table; however, the shared tablespace still contains metadata. Copying the .ibd ﬁle to another server will not result in actually copying the table. Performance conﬁguration variables Several variables directly affect the performance of your InnoDB tables. Table 11-4 lists these variables. A larger buffer conﬁgured by innodb_buffer_pool_size means there is less I/O needed to access data in tables. This is because the InnoDB storage engine stores your frequently used data in memory. On a dedicated database server primarily using InnoDB tables, this should be a signiﬁcant percentage of the total memory available to mysqld. Be very careful with this setting because if it is conﬁgured to use too much memory it will cause swapping by the operating system, which is very bad for mysqld performance. In the worst-case scenario, using too much memory will cause mysqld to crash. See Chapter 10 for more details on tuning this parameter. SHOW ENGINE InnoDB STATUS The SHOW ENGINE InnoDB STATUS command can be used to provide detailed information about the workings of the InnoDB storage engine. 385
Part III Core MySQL Administration TABLE 11-4 InnoDB Performance Conﬁguration Variables Performance Conﬁguration Variable Description innodb_buffer_pool_size Determines the size of the buffer that the InnoDB storage engine uses to cache both data and indexes. innodb_flush_log_at_ Conﬁgures how frequently the log buffer is ﬂushed to trx_commit disk. The three valid values are 0, 1, and 2. The default value of this variable is 1. This default setting is required for ACID-compliance. See Chapter 10 for more details on this parameter. innodb_log_file_size Determines the size, in bytes, of each of the InnoDB log ﬁles. The default size is 5 megabytes. A larger log ﬁle means there is less disk I/O. Unfortunately, larger log ﬁles also mean that recovery is slower in the case of a server crash. In our experience, reasonable values for this range between 128 megabytes and 256 megabytes. The following is sample output from a production server. The output has been modiﬁed to take up less space but will give you a good idea of the information available: mysql> SHOW ENGINE INNODB STATUS\G *************************** 1. row *************************** Status: ===================================== 081124 14:47:30 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 59 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 56930532, signal count 48711739 Mutex spin waits 0, rounds 2643139276, OS waits 43490665 RW-shared spins 22064383, OS waits 6936948; RW-excl spins 21037008, OS waits 1461843 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 081124 12:08:15 Transaction: TRANSACTION 1 2114794386, ACTIVE 0 sec, process no 30716, OS thread id 1349732704 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 4 lock struct(s), heap size 368, undo log entries 1 386