【正文】 frequently encounter errors of the form Lost connection to MySQL server at ‘XXX’, system error: errno.解釋：在獲取鏈接時(shí)，等待握手的超時(shí)時(shí)間，只在登錄時(shí)有效，登錄成功這個(gè)參數(shù)就不管事了。主要是為了防止網(wǎng)絡(luò)不佳時(shí)應(yīng)用重連導(dǎo)致連接數(shù)漲太快，一般默認(rèn)即可。delayed_insert_timeout手冊(cè)描述：How many seconds an INSERT DELAYED handler thread should wait for INSERT statements before terminating.解釋：這是為MyISAM INSERT DELAY設(shè)計(jì)的超時(shí)參數(shù)，在INSERT DELAY中止前等待INSERT語(yǔ)句的時(shí)間。innodb_lock_wait_timeout手冊(cè)描述：The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error:ERROR 1205 (HY000): Lock wait timeout exceeded。 try restarting transactionWhen a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back. (To have the entire transaction roll back, start the server with the –innodb_rollback_on_timeout option, available as of MySQL . See also Section , “InnoDB Error Handling”.)innodb_lock_wait_timeout applies to InnoDB row locks only. A MySQL table lock does not happen inside InnoDB and this timeout does not apply to waits for table locks.InnoDB does detect transaction deadlocks in its own lock table immediately and rolls back one transaction. The lock wait timeout value does not apply to such a wait.For the builtin InnoDB, this variable can be set only at server startup. For InnoDB Plugin, it can be set at startup or changed at runtime, and has both global and session values.解釋：描述很長(zhǎng)，簡(jiǎn)而言之，就是事務(wù)遇到鎖等待時(shí)的Query超時(shí)時(shí)間。跟死鎖不一樣，InnoDB一旦檢測(cè)到死鎖立刻就會(huì)回滾代價(jià)小的那個(gè)事務(wù)，鎖等待是沒(méi)有死鎖的情況下一個(gè)事務(wù)持有另一個(gè)事務(wù)需要的鎖資源，被回滾的肯定是請(qǐng)求鎖的那個(gè)Query。innodb_rollback_on_timeout手冊(cè)描述：In MySQL , InnoDB rolls back only the last statement on a transaction timeout by default. If –innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction (the same behavior as in MySQL ). This variable was added in MySQL .解釋：這個(gè)參數(shù)關(guān)閉或不存在的話遇到超時(shí)只回滾事務(wù)最后一個(gè)Query，打開(kāi)的話事務(wù)遇到超時(shí)就回滾整個(gè)事務(wù)。interactive_timeout/wait_timeout手冊(cè)描述：The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also解釋：一個(gè)持續(xù)SLEEP狀態(tài)的線程多久被關(guān)閉。線程每次被使用都會(huì)被喚醒為acrivity狀態(tài)，執(zhí)行完Query后成為interactive狀態(tài)，重新開(kāi)始計(jì)時(shí)。wait_timeout不同在于只作用于TCP/IP和Socket鏈接的線程，意義是一樣的。net_read_timeout / net_write_timeout手冊(cè)描述：The number of seconds to wait for more data from a connection before aborting the read. Before MySQL , this timeout applies only to TCP/IP connections, not to connections made through Unix socket files, named pipes, or shared memory. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. See also slave_net_timeout.On Linux, the NO_ALARM build flag affects timeout behavior as indicated in the description of the net_retry_count system variable.解釋：這個(gè)參數(shù)只對(duì)TCP/IP鏈接有效，分別是數(shù)據(jù)庫(kù)等待接收客戶端發(fā)送網(wǎng)絡(luò)包和發(fā)送網(wǎng)絡(luò)包給客戶端的超時(shí)時(shí)間，這是在Activity狀態(tài)下的線程才有效的參數(shù)slave_net_timeout手冊(cè)描述：The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the MASTER_CONNECT_RETRY option for the CHANGE MASTER TO statement or –masterconnectretry option, and the number of reconnection attempts is limited by the –masterretrycount option. The default is 3600 seconds (one hour).解釋：這是Slave判斷主機(jī)是否掛掉的超時(shí)設(shè)置，在設(shè)定時(shí)間內(nèi)依然沒(méi)有獲取到Master的回應(yīng)就人為Master掛掉了（2）：How to decrease InnoDB shutdown timesSometimes a MySQL server running InnoDB takes a long time to shut down. The usual culprit (根本原因)is flushing dirty pages (刷新臟數(shù)據(jù))from the buffer pool. These are pages that have been modified(修正) in memory, but not on disk.If you kill the server before it finishes this process, it will just go through the recovery phase（恢復(fù)階段） on startup, which can be even slower in stock InnoDB than the shutdown process, for a variety of way to decrease the shutdown time is to preflush the dirty pages, like this:Shellmysql set global innodb_max_dirty_pages_pct = 0。Now run the following mand:Shell$ mysqladmin ext i10 | grep dirty| Innodb_buffer_pool_pages_dirty | 1823484 || Innodb_buffer_pool_pages_dirty | 1821293 || Innodb_buffer_pool_pages_dirty | 1818938 |And wait until it approaches zero. (If the server is being actively used, it won’t get to zero.)Once it’s pretty （相當(dāng)）low, you can perform（執(zhí)行） the shutdown and there’ll be a lot less unfinished work to do, so the server should shut down more quickly.Mark Callaghansays: April 15, 2009 at 10:04 amBaron – you can update this article to include a link to a Percona binary that makes this faster.It is one thing to ask InnoDB to lower the percentage of dirty buffers, it is another thing for InnoDB to do that on a busy server.If writes are slow on your server, then you really need the Percona patches or features from the v3 Google patch to make the writing use the available IO capacity on a server. The patches to use include innodb_io_capacity, more background IO threads and a few others.Writes to files in the OS buffer cache are fast. Writes may be slow when: * using innodb_flush_method=O_DIRECT * disabling SATA writeback cache * not using a HW RAID disk cache * using NFS or some other remote storage service that doesn’t buffer writes in the OS buffer cacheKevin Burtonsays: April 15, 2009 at 10:45 pmWe made this a standard part of /etc/It just sets this and waits for MySQL to finish writing and then allows the stop to happen.The main problem is that the mysql contr