We have been rapidly adding MySQL servers as slaves to our master database server to help alleviate a massive increase in search (read-only) traffic to our web service. This past week, I was tasked with adding a tenth slave server to support a new "node" of web application servers. Out-of-control scaling aside, I did some research and found that using a "Relay Slave" is a good option for alleviating load on the Master server when adding more slaves.
Our setup is roughly as follows.
sql01is the master database server. All writes go to this server.
It is5.0.95running onCentOS 5.11sql02is a slave tosql01, and does not havelog-slave-updates
enabled. It is5.1.73running onCentOS 6.6. It handles heavy load
in a production environment without issues.sql03is a slave tosql01, and haslog-slave-updatesenabled. It
is5.0.95running onCentOS 5.11sql04is a slave tosql03. It is5.1.73running onCentOS. It is intended to perform a similar function to
6.6sql02however, it has issues when presented with load.
There are more servers, but I believe that is all that is needed for this explanation.
If I do not place any load on sql04, it stays up-to-date with its master without issue. As soon as I place a search load on it, I notice one thread gets stuck sending data, and other threads eventually lock. Here is the SHOW PROCESSLIST from 'sql04`
mysql> SHOW PROCESSLIST;
+------+-------------+--------------------+------------+---------+-------+----------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-------------+--------------------+------------+---------+-------+----------------------------------+------------------------------------------------------------------------------------------------------+
| 3 | system user | | NULL | Connect | 66988 | Waiting for master to send event | NULL |
| 4 | system user | | xxxxxxx_db | Connect | 6126 | Locked | replace into tblPriceForAllotment (ProdCode,AllotCode,AllotRecID,PriceDate,ApproxPrice1,ApproxPrice2 |
| 1358 | root | 192.168.0.21:43254 | xxxxxxx_db | Sleep | 27 | | NULL |
| 1357 | root | 192.168.0.21:43255 | xxxxxxx_db | Query | 6122 | Locked | select p.ApproxPrice1, p.ApproxPrice2, p.ApproxPrice3, p.ApproxPrice4, p.PromoMessage, p.NightsToSta |
| 1359 | root | 192.168.0.21:43257 | xxxxxxx_db | Sleep | 27 | | NULL |
| 1360 | root | 192.168.0.22:40467 | xxxxxxx_db | Sleep | 7 | | NULL |
| 1361 | root | 192.168.0.22:40466 | xxxxxxx_db | Sleep | 27 | | NULL |
| 1362 | root | 192.168.0.22:40468 | xxxxxxx_db | Sleep | 27 | | NULL |
| 1363 | root | 192.168.0.21:43258 | xxxxxxx_db | Sleep | 12 | | NULL |
| 1364 | root | 192.168.0.22:40469 | xxxxxxx_db | Query | 6122 | Sending data | select p.ApproxPrice1, p.ApproxPrice2, p.ApproxPrice3, p.ApproxPrice4, p.PromoMessage, p.NightsToSta |
| 1365 | root | 192.168.0.21:43259 | xxxxxxx_db | Sleep | 27 | | NULL |
| 1366 | root | 192.168.0.22:40470 | xxxxxxx_db | Query | 6122 | Locked | select p.ApproxPrice1, p.ApproxPrice2, p.ApproxPrice3, p.ApproxPrice4, p.PromoMessage, p.NightsToSta |
| 1367 | root | 192.168.0.21:43260 | xxxxxxx_db | Sleep | 27 | | NULL |
| 1368 | root | 192.168.0.22:40471 | xxxxxxx_db | Sleep | 27 | | NULL |
| 1369 | root | 192.168.0.21:43261 | xxxxxxx_db | Sleep | 27 | | NULL |
| 1370 | root | 192.168.0.22:40472 | xxxxxxx_db | Sleep | 27 | | NULL |
| 1371 | root | 192.168.0.21:43262 | xxxxxxx_db | Sleep | 27 | | NULL |
| 1372 | root | 192.168.0.22:40473 | xxxxxxx_db | Query | 6122 | Locked | select p.ApproxPrice1, p.ApproxPrice2, p.ApproxPrice3, p.ApproxPrice4, p.PromoMessage, p.NightsToSta |
| 1373 | root | 192.168.0.21:43263 | xxxxxxx_db | Sleep | 27 | | NULL |
| 1374 | root | 192.168.0.22:40474 | xxxxxxx_db | Query | 6122 | Locked | select p.ApproxPrice1, p.ApproxPrice2, p.ApproxPrice3, p.ApproxPrice4, p.PromoMessage, p.NightsToSta |
| 1375 | root | 192.168.0.21:43264 | xxxxxxx_db | Sleep | 27 | | NULL |
| 1376 | root | 192.168.0.22:40475 | xxxxxxx_db | Query | 6122 | Locked | select p.ApproxPrice1, p.ApproxPrice2, p.ApproxPrice3, p.ApproxPrice4, p.PromoMessage, p.NightsToSta |
| 1384 | root | 192.168.0.22:40476 | xxxxxxx_db | Sleep | 27 | | NULL |
| 1385 | root | 192.168.0.22:40477 | xxxxxxx_db | Sleep | 27 | | NULL |
| 1399 | root | 192.168.0.22:40478 | xxxxxxx_db | Sleep | 7 | | NULL |
| 1406 | root | 192.168.0.22:40479 | xxxxxxx_db | Sleep | 7 | | NULL |
| 1407 | root | 192.168.0.21:43265 | xxxxxxx_db | Sleep | 27 | | NULL |
| 1408 | root | 192.168.0.22:40480 | xxxxxxx_db | Sleep | 7 | | NULL |
| 1527 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
+------+-------------+--------------------+------------+---------+-------+----------------------------------+------------------------------------------------------------------------------------------------------+
29 rows in set (0.00 sec)
The configuration file for sql04 is functionally identical to sql02. The table, tblPriceForAllotment is MyISAM, and I suspect switching to InnoDB will solve this issue, but it is MyISAM on the "known-good" sql02 server. Another notable difference is that sql04 has a gigabit connection to its corresponding web servers, which all other database servers have a 100mb connection. During times of load on sql04, I have observed over 300mbps of traffic outbound.
Please let me know if I need to post configurations for any of the servers.
Thank you!
Edit:
- Here is the full query that was running: http://pastebin.com/U3MSRbvf
(Due to the length, could not paste it here.) - Here is the EXPLAIN SELECTED.
Here:
+----+-------------+-------+--------+---------------+----------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+---------------+----------+---------+-------------------------+------+----------+-------------+
| 1 | SIMPLE | p | range | PRIMARY | PRIMARY | 13 | NULL | 5860 | 100.00 | Using where |
| 1 | SIMPLE | r | eq_ref | PRIMARY | PRIMARY | 10 | tourbot_db.p.ProdCode | 1 | 100.00 | |
| 1 | SIMPLE | a | eq_ref | idxRecID | idxRecID | 4 | tourbot_db.p.AllotRecID | 1 | 100.00 | |
+----+-------------+-------+--------+---------------+----------+---------+-------------------------+------+----------+-------------+
tblPriceForAllotment
+------------------+------------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+------------+-------+
| ProdCode | char(10) | NO | PRI | | |
| AllotCode | char(10) | NO | | | |
| AllotRecID | int(10) unsigned | NO | | NULL | |
| PriceDate | date | NO | PRI | 0000-00-00 | |
| WebPriority | char(1) | YES | | NULL | |
| Description | char(50) | YES | | NULL | |
| PackageFlag | tinyint(1) | YES | | NULL | |
| MaxOcc | int(2) | YES | | NULL | |
| VendorID | char(10) | YES | | NULL | |
| ApproxPrice1 | decimal(8,2) | YES | | NULL | |
| ApproxPrice2 | decimal(8,2) | YES | | NULL | |
| ApproxPrice3 | decimal(8,2) | YES | | NULL | |
| ApproxPrice4 | decimal(8,2) | YES | | NULL | |
| ApproxChildPrice | decimal(8,2) | YES | | NULL | |
| PromoMessage | char(200) | YES | | NULL | |
| NightsToStay | char(1) | YES | | NULL | |
| NightsFree | char(1) | YES | | NULL | |
+------------------+------------------+------+-----+------------+-------+
tblAllotment
+---------------------------+---------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+---------------------+------+-----+------------+----------------+
| RecID | int(10) unsigned | NO | UNI | NULL | auto_increment |
| ProdCode | char(10) | NO | PRI | | |
| AllotDate | date | NO | PRI | 0000-00-00 | |
| Allotment | int(10) unsigned | YES | | NULL | |
| IsBookable | tinyint(1) unsigned | YES | | NULL | |
| CloseOut | tinyint(1) unsigned | YES | | NULL | |
| MaxStay | int(2) | YES | | NULL | |
| MinStay | int(2) | YES | | NULL | |
| MinFromArrival | tinyint(1) | YES | | NULL | |
| alert | char(2) | YES | | NULL | |
| ApproxQtyUsed | int(10) | YES | | NULL | |
| ApproxQtyUpdated | datetime | YES | | NULL | |
| ApproxPrice1 | decimal(8,2) | YES | | NULL | |
| ApproxPrice2 | decimal(8,2) | YES | | NULL | |
| ApproxPrice3 | decimal(8,2) | YES | | NULL | |
| ApproxPrice4 | decimal(8,2) | YES | | NULL | |
| PromoMessage | char(100) | YES | | NULL | |
| NightsToStay | char(1) | YES | | NULL | |
| NightsFree | char(1) | YES | | NULL | |
| notes | text | NO | | NULL | |
| ExtraNetSell1 | decimal(8,2) | NO | | NULL | |
| ExtraNetSell2 | decimal(8,2) | YES | | NULL | |
| ExtraNetSell3 | decimal(8,2) | YES | | NULL | |
| ExtraNetSell4 | decimal(8,2) | YES | | NULL | |
| ExtraNetCost1 | decimal(8,2) | YES | | NULL | |
| ExtraNetCost2 | decimal(8,2) | YES | | NULL | |
| ExtraNetCost3 | decimal(8,2) | YES | | NULL | |
| ExtraNetCost4 | decimal(8,2) | YES | | NULL | |
| ExtraNetCutOff | tinyint(2) | YES | | NULL | |
| ContractCutOff | tinyint(2) | YES | | NULL | |
| CloseOutUpdatedDate | date | NO | | NULL | |
| NoDDBeds | tinyint(1) | NO | | NULL | |
| PriceUpdatedDate | datetime | NO | | NULL | |
| OldPrice1 | decimal(8,2) | NO | | NULL | |
| room_hold | tinyint(4) | YES | | NULL | |
| OldPrice2 | decimal(8,2) | NO | | NULL | |
| OldPrice3 | decimal(8,2) | NO | | NULL | |
| OldPrice4 | decimal(8,2) | NO | | NULL | |
| StopFreeSell | tinyint(1) | NO | | NULL | |
| NoDDBedsUpdatedDate | date | NO | | NULL | |
| StopFreeSellUpdatedDate | date | NO | | NULL | |
| CutOff | int(3) | YES | | NULL | |
| ChildAge | int(1) | YES | | NULL | |
| AdditionalSentRequestFlag | tinyint(1) | NO | | 0 | |
| LastAllotRequestSentDate | datetime | YES | | NULL | |
+---------------------------+---------------------+------+-----+------------+----------------+
tblProduct:
+----------------------------+----------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+----------------------+------+-----+---------------------+-------+
| ProdCode | char(10) | NO | PRI | | |
| Description | char(80) | YES | | NULL | |
| Misc1Flag | tinyint(1) unsigned | YES | | NULL | |
| Misc2Flag | tinyint(1) unsigned | YES | | NULL | |
| PULocFlag | tinyint(1) unsigned | YES | | NULL | |
| PUTimeFlag | tinyint(1) unsigned | YES | | NULL | |
| FlightItinFlag | tinyint(1) unsigned | YES | | NULL | |
| PackageFlag | tinyint(1) unsigned | YES | | NULL | |
| AllotmentFlag | tinyint(1) unsigned | YES | | NULL | |
| Flight1 | char(50) | YES | | NULL | |
| Flight2 | char(50) | YES | | NULL | |
| Flight3 | char(50) | YES | | NULL | |
| Flight4 | char(50) | YES | | NULL | |
| Vendor | char(10) | YES | | NULL | |
| IsProdCom | tinyint(1) unsigned | YES | | NULL | |
| Multiple | smallint(5) unsigned | YES | | NULL | |
| PriceOverrideFlag | tinyint(1) unsigned | YES | | NULL | |
| DisplayOccFlag | tinyint(1) unsigned | NO | | 0 | |
| ReportAllotUsageFlag | tinyint(1) unsigned | YES | | NULL | |
| ConfType | int(10) unsigned | YES | | NULL | |
| DisplayNightsFlag | tinyint(1) unsigned | NO | | 0 | |
| DefaultNights | smallint(3) unsigned | NO | | 0 | |
| Instr1 | char(100) | YES | | NULL | |
| Instr2 | char(100) | YES | | NULL | |
| Instr3 | char(100) | YES | | NULL | |
| Instr4 | char(100) | YES | | NULL | |
| associated | tinyint(1) | YES | | NULL | |
| html_file | char(50) | YES | | NULL | |
| display_category | char(30) | YES | | NULL | |
| web_access | char(10) | YES | | ALLOW ALL | |
| WebPriority | char(1) | YES | | 3 | |
| AllotCode | char(10) | YES | | NULL | |
| BrochureData | int(1) | YES | | 1 | |
| Dept | char(5) | YES | | FIT | |
| days_prior1 | int(3) | YES | | NULL | |
| type1 | char(10) | YES | | NULL | |
| amount1 | decimal(8,2) | YES | | NULL | |
| days_prior2 | int(3) | YES | | NULL | |
| type2 | char(10) | YES | | NULL | |
| amount2 | decimal(8,2) | YES | | NULL | |
| days_prior3 | int(3) | YES | | NULL | |
| type3 | char(10) | YES | | NULL | |
| amount3 | decimal(8,2) | YES | | NULL | |
| web_restricted | char(1) | YES | | 0 | |
| isHotel | tinyint(1) | YES | | NULL | |
| usesLanguage | tinyint(1) | YES | | NULL | |
| MaxOcc | int(1) | YES | | NULL | |
| NeedsPriceUpdate | tinyint(1) | YES | | NULL | |
| EliteBrochureData | tinyint(1) | NO | | NULL | |
| ProductDescription | text | NO | | NULL | |
| ProductCharge | decimal(8,2) | YES | | NULL | |
| FreeSellProgram | tinyint(1) | NO | | NULL | |
| FreeSellNotes | text | NO | | NULL | |
| CalendarPriority | char(2) | NO | | NULL | |
| DisneyHotelCode | char(3) | NO | | NULL | |
| DisneyRoomType | char(2) | YES | | NULL | |
| ReportDisneyFlag | tinyint(1) | NO | | NULL | |
| LastCalendarUpdate | date | NO | | NULL | |
| DisneyAdultTicketCode | char(5) | YES | | NULL | |
| DisneyChildTicketCode | char(5) | YES | | NULL | |
| LatestDisneyCalendar | text | YES | | NULL | |
| LatestDisneyCalendarUpdate | datetime | YES | | NULL | |
| MealPlanID | int(11) | YES | | NULL | |
| RoomTypeID | int(11) | YES | | NULL | |
| ReportDisneyTicketFlag | tinyint(3) | YES | | NULL | |
| DisneyPackageCode | varchar(25) | YES | | NULL | |
| PermRoomCode | tinyint(1) | YES | | NULL | |
| SpecialRoomFlag | tinyint(1) | YES | | 0 | |
| GroupPricingType | char(8) | YES | | NULL | |
| webStartDate | date | YES | | 0000-00-00 | |
| webEndDate | date | YES | | 0000-00-00 | |
| extranet | tinyint(1) | YES | | 0 | |
| ResortFee | varchar(10) | YES | | NULL | |
| ResortFeeType | int(2) | YES | | NULL | |
| IsActiveProduct | tinyint(1) unsigned | YES | | 1 | |
| Instr5 | char(100) | YES | | NULL | |
| NonRefundable | tinyint(1) unsigned | YES | | NULL | |
| RoomTypeModified | timestamp | NO | | 0000-00-00 00:00:00 | |
| RoomTypeUpdateFlag | tinyint(1) | YES | | 0 | |
| MaxChildAge | int(1) | YES | | NULL | |
| TransferType | int(1) | NO | | 0 | |
| NotifyEmails | varchar(255) | YES | | NULL | |
| MinNights | int(1) | YES | | NULL | |
| NotifyStartDate | date | YES | | NULL | |
| NotifyEndDate | date | YES | | NULL | |
| DisneyPackageCode2 | varchar(25) | YES | | NULL | |
| DisneyCodeCurrentYear | varchar(4) | YES | | NULL | |
| DisneyCodeNextYear | varchar(4) | YES | | NULL | |
| DisneyAdultTicketCode2 | char(5) | YES | | NULL | |
| DisneyChildTicketCode2 | char(5) | YES | | NULL | |
| DisneyCodeStart1 | date | YES | | NULL | |
| DisneyCodeStart2 | date | YES | | NULL | |
| DisneyCodeEnd1 | date | YES | | NULL | |
| DisneyCodeEnd2 | date | YES | | NULL | |
| IsRoundTrip | int(1) | NO | | 0 | |
| isHiltonProduct | int(2) | YES | | NULL | |
| DynamicProduct | int(2) | YES | | NULL | |
+----------------------------+----------------------+------+-----+---------------------+-------+
Best Answer
The important parts of the query look like
Please use
JOIN...ONsyntax.Since (ProdCode, PriceDate) is already the PRIMARY KEY, there is no need for a different index on p.
I see ProdCode in Allotment, but no JOIN on it. Why?
The EXPLAIN shows that it should be efficient.
Does that long list of ProdCodes change a lot?
Here's what may be happening:
etc. With MyISAM, all prior SELECTs must finish before an INSERT can start. Meanwhile, all subsequent SELECTs must wait for the INSERT to finish. (Another argument for InnoDB.)