discount coach purses Mysql optimization parameter

[ommei538554]

1. Get the current configuration parameters to optimize the configuration parameters
, we must first understand the current configuration parameters and operating conditions. Use the following command to get the current server configuration parameters:
mysqld verbose help
mysqladmin variables extended-status u root p
inside the MySQL console, run the following command to obtain the state variables Value:
mysql> SHOW STATUS;
just check if a number of state variables, you can use the following command:
mysql> SHOW STATUS LIKE '[Matching Mode]'; (you can use%?, etc.)
2.
Optimization optimization parameters based on a premise that is often in our database, use InnoDB tables instead of MyISAM tables. In the optimization of MySQL, there are two configuration parameters are the most important, that table_cache and key_buffer_size.
table_cache
table_cache specify the size of the cache table. Each time MySQL accesses a table, if there is buffer space in the table, the table is opened and placed them on so you can quickly access the contents of the table. Time by checking the status of the peak value of Open_tables and Opened_tables, can decide whether to increase the value of table_cache. If you find open_tables equal table_cache, and opened_tables growing, then you need to increase the value of the table_cache (the value of the state can use SHOW STATUS LIKE 'Open% tables' access). Note that not blindly set the table_cache great value. If set too high may result in insufficient file descriptors, resulting in unstable performance, or the connection fails.
For 1G memory machine, the recommended values ​​is 128-256.
Case 1: the case from a not particularly busy server
table_cache 512
open_tables 103
opened_tables 1273
uptime 4021421 (measured in seconds)
the case table_cache seems to set in too high. In peak time, the number of open tables is much less than table_cache.
Case 2: the case from a development server.
table_cache 64
open_tables 64
opened-tables 431
uptime 1662790 (measured in seconds)
Although open_tables have equal table_cache,golf puma, but relative to the server is running time, opened_tables The value is very low. Therefore, increasing the value of table_cache be useful.
Case 3: The case of poor performance from a server
table_cache 64
open_tables 64
opened_tables 22423
uptime 19538
table_cache set too in the case low. Although the running time of less than 6 hours, open_tables reached the maximum, opened_tables value is very high. This will need to increase the value of table_cache.
key_buffer_size
key_buffer_size buffer size specified index, which determines the speed of index handling, particularly in the index reading speed. By checking the status value Key_read_requests and Key_reads, can know key_buffer_size set is reasonable. Ratio key_reads / key_read_requests be as low as possible, at least 1:100,1:1000 better (you can use these status values ​​SHOW STATUS LIKE 'key_read%' access).
key_buffer_size works only for MyISAM tables. Even if you do not use MyISAM tables, but the internal temporary disk tables are MyISAM tables, but also the use of the value. Can check the status value created_tmp_disk_tables to know the details.
1G of memory for the machine, if you do not use MyISAM tables, the recommended value is 16M (8-64M).
Case 1: health status
key_buffer_size 402649088 (384M)
key_read_requests 597579931
key_reads - 56188
Case 2: Alert Status
key_buffer_size 16777216 (16M)
key_read_requests 597579931
key_reads - 53832731
Case 1 is lower than the proportion of 1:10000 is health; Case 2 ratio to 1:11,jessica simpson handbags 出门一个礼拜遭受、, the alarm has been sounded.
query_cache_size
starting from 4.0.1, MySQL query caching mechanism provided. Use the query cache, MySQL SELECT statements and query results will be stored in the buffer, the next SELECT statement for the same (case sensitive),jordan 1 flight low, read directly from the results buffer. According to MySQL user manual, use the query cache can be up to 238% efficiency.
by checking the status value Qcache_ *, can know whether it is reasonable query_cache_size set (the value of the state can use SHOW STATUS LIKE 'Qcache%' access). If Qcache_lowmem_prunes value is very large, it indicates that the buffer often enough, and if the value of Qcache_hits very large, then the query cache used very frequently, then the need to increase the buffer size; if Qcache_hits little value, this indicates that your Query repetition rate is very low, such use of the query but will affect the efficiency of the buffer, you can not consider the query buffer. In addition, the SELECT statement is not clear that adding SQL_NO_CACHE can use the query buffer.
the query cache related parameters as well query_cache_type, query_cache_limit, query_cache_min_res_unit. query_cache_type specify whether to use query cache can be set to 0,1,2, the variable is the SESSION level variables. query_cache_limit single query can use the specified buffer size, default is 1M. query_cache_min_res_unit was introduced after version 4.1, which specifies the smallest unit of allocation of buffer space, default is 4K. Check the status value Qcache_free_blocks, if the value is very large, it indicates that a lot of debris in the buffer, which show that the results are relatively small, at this time need to reduce the query_cache_min_res_unit.
open binary log (Binary Log)
binary log contains all statements that update data, the aim is to restore the database to use it to restore the data as much as possible to the final state. Also, if you do synchronous replication (Replication) would also need to use the binary log to send the revision.
open binary log, you need to set the parameters of log-bin. log_bin specify a log file, if you do not provide the file name, MySQL will generate its own default file name. MySQL will automatically add the file name behind the number of indexes, each time you start the service, will re-generate a new binary file.
In addition, the use of log-bin-index to specify the index file; using binlog-do-db can specify the database records; using binlog-ignore-db can specify the database does not record. Note: binlog-do-db and binlog-ignore-db time only specify a database, specify multiple databases require multiple statements. Moreover, MySQL database name will be all lowercase, in the specified database name must be all lowercase, or will not work.
used in MySQL SHOW MASTER STATUS command to see the current state of the binary log.
open slow query log (slow query log)
slow query log for tracking the query in question is very useful. It records all checked long_query_time query, if necessary, you can not use the index recorded a record. Here is an example of a slow query log:
open slow query log, you need to set the parameters log_slow_queries, long_query_times, log-queries-not-using-indexes. log_slow_queries specify a log file, if you do not provide the file name, MySQL will generate its own default file name. long_query_times slow query threshold specified, the default is 10 seconds. log-queries-not-using-indexes are the parameters introduced after 4.1.0, which indicates that records do not use the index query.
configure InnoDB
relative to MyISAM tables,christian louboutin studded shoes, the correct configuration parameters for InnoDB tables even more critical. The most important parameter is the innodb_data_file_path. It specifies the table data and index storage space, can be one or more files. Finally, a data file must be automatically extended, and only the last file to allow automatic expansion. Thus, when space is exhausted, it will automatically expand the data file automatically increase (to 8MB units) to accommodate the additional data. For example:
innodb_data_file_path = / disk1/ibdata1: 900M; / disk2/ibdata2: 50M: autoextend
two data files on a different disk. Data on the ibdata1 first, when to 900M, the data is placed in ibdata2. Once you reach 50MB, ibdata2 8MB as the unit will automatically increase.
If the disk is full, you need another disk to add a data file above. To do this,cheap mbt, you need to see the last file size, and then calculate the nearest whole number (MB). And then manually modify the size of the file,discount coach purses, and add new data files. For example: Suppose ibdata2 have 109MB of data, it can be modified as follows:
innodb_data_file_path = / disk1/ibdata1: 900M; / disk2/ibdata2: 109M; / disk3/ibdata3: 500M: autoextend
flush_time
If the system problems and often lock or reboot, the variable should be set to a nonzero value, which will cause the server to refresh the table by flush_time second cache. Using this method to write the changes on the table will reduce performance, but the table can be reduced opportunities for corruption or data loss.
generally use the default value.
Binlog_cache_size
The size of the cache to hold the SQL statements for the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has binary log enabled (log-bin option). If you often use big, multiple-statement transactions, you can increase this to get more performance. The Binlog_cache_use and Binlog_cache_disk_use status variables can be useful for tuning the size of this variable.
3. Storage engine
in MYSQL 3.23.0 version, introduced the MyISAM storage engine. It is a non-transactional storage engine, has become the default storage engine MYSQL. However, if you use the Setup Wizard to set the parameters, it will InnoDB storage engine as the default. InnoDB is a transactional storage engine.
create the table, they can specify the storage engine for the table, the syntax is as follows:
CREATE TABLE t (i INT) ENGINE = MyISAM
CREATE TABLE t (i INT) TYPE = MyISAM
If not specified , use the default storage engine. You can also use ALTER TABLE to change the table engine, the syntax is as follows:
ALTER TABLE t ENGINE = MyISAM
the same database can contain tables with different storage engines.
transactional table has the following characteristics:
? Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup plus the transaction log.
? You can combine many statements and accept them all at the same time with the COMMIT statement (if autocommit is disabled).
? You can execute ROLLBACK to ignore your changes (if autocommit is disabled).
? If an update fails, all your changes will be restored. (With non-transaction-safe tables, all changes that have taken place are permanent.)
? Transaction-safe storage engines can provide better concurrency for tables that get many updates concurrently with reads .
non-transactional table has the following advantages:
? Much faster
? Lower disk space requirements
? Less memory required to perform updates
4. MyISAM storage engine MyISAM following parameters
MySQL manual for the recommended parameters are said to adapt to most situations. How to monitor the parameters are reasonable, still have no clue.
max_connections = 200
read_buffer_size = 1M
read_rnd_buffer_size = 8M
sort_buffer_size = 1M
Read_buffer_size
Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you might want to increse this value.
Read_rnd_buffer_size
When reading rows in sorted order after a sort, the rows are read through this buffer to avoid disk seeks. Setting the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries.
Bulk_insert_buffer_size
the parameters introduced in 4.0.3. MyISAM uses a tree buffer to speed up a lot of inserts, such as INSERT ... SELECT, INSERT ... VALUES (...), VALUES (...), ..., LOAD DATA INFILE and so on. This parameter specifies the size of the buffer. The default value is 8M, set to 0, said they did not use the optimization.
If you do not use MyISAM tables, you can set it to 0.
5. InnoDB storage engine
reference to a lot of information that are not clear how to optimize the InnoDB parameters,the north face metropolis 照片太多，勤得保留，直接, and how to monitor these parameters are reasonable, and only under the MySQL user manual to set the above description.
innodb_buffer_pool_size
for InnoDB tables is, innodb_buffer_pool_size key_buffer_size equivalent to the role of the role for the same MyISAM tables. Use this parameter to specify the size of the InnoDB memory to buffer the data and indexes. MySQL database server for the individual,jordans shoes for women RAYS TE37 18寸(现货), the maximum value can be set to the 80% of physical memory.
According to MySQL manual for the 2G memory machines, the recommended value is 1G (50%).
innodb_flush_log_at_trx_commit
This value specifies the way InnoDB logs. If set to 1, when submitted for each transaction, MySQL will be the transaction log to disk. If set to 0 or 2, then the log will be written to disk about once per second. (Also do not know the difference between 0 and 2)
actual test found that the value of the speed of the inserted data is very large, is set to 2, insert 10,000 records only 2 seconds, set to 0, only 1 second, and is set to 1, you will need 229 seconds. Therefore, MySQL manual also suggested as far as possible into a single transaction into operation, thus greatly increasing the speed.
According to MySQL manual, there is the danger of missing the last part of the transaction under the premise that value can be set to 0.
innodb_log_file_size
The size of each log file in a log group. The default is 5MB. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I / O. But large log files also mean that recovery will be slower in case of a crash.
under the MySQL manual, the recommended value is 25% innodb_buffer_pool_size.
Note: to reset the value, as if trying to delete the original file.
innodb_log_buffer_size
The size of the buffer that InnoDB uses to write to the log files on disk. Sensible values ​​range from 1MB to 8MB. The default is 1MB. A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger will save disk I / O.
under the MySQL manual, the recommended value is 8M.
innodb_additional_mem_pool_size
This parameter specifies the InnoDB to store data dictionary and other internal data structure of memory pool size. The default value is 1M. Usually not too much, as long as enough on the line, should the complexity of the table structure a relationship. If not enough, MySQL is written in the error log a warning message.
According to MySQL manual for the 2G memory machines, the recommended value is 20M.
SHOW INNODB STATUS
shows the status of InnoDB storage engine