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

0
90
lượt xem
30

Mô tả tài liệu

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)

Lưu

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

1. 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
3. 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
4. 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
5. 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
6. 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
7. 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
8. 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
14. 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
15. 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