0

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.

  • sql01 is the master database server. All writes go to this server. It is 5.0.95 running on CentOS 5.11
  • sql02 is a slave to sql01, and does not have log-slave-updates enabled. It is 5.1.73 running on CentOS 6.6. It handles heavy load in a production environment without issues.
  • sql03 is a slave to sql01, and has log-slave-updates enabled. It is 5.0.95 running on CentOS 5.11
  • sql04 is a slave to sql03. It is 5.1.73 running on CentOS 6.6. It is intended to perform a similar function to sql02 however, 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                |       |
+----------------------------+----------------------+------+-----+---------------------+-------+
0

The important parts of the query look like

SELECT  p...., a...., r....
    FROM  tblPriceForAllotment p
    JOIN  tblAllotment a ON p.AllotRecID = a.RecID
    JOIN  tblProduct r   ON r.ProdCode   = p.ProdCode
    WHERE  p.ProdCode in(... long list ...)
      AND  p.PriceDate>='2015-10-09'
      AND  p.PriceDate<='2015-10-17'
    ORDER BY   ProdCode;

Please use JOIN...ON syntax.

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:

  1. A SELECT gets started; that locks the table for read (because of MyISAM)
  2. An update comes through replication and wants an exclusive lock.
  3. Meanwhile more SELECTs come in, but they are now stuck behind the INSERT.
  4. And more INSERTs come in.

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.)

  • Hi Rick, thank you for your response. Regarding your explanation in the second half of your answer, it sounds like that would create an indefinite lock? The SELECT queries typically do not take very long to complete, especially not on the "known good" sql02 server. Is it possible that the increased speed of sql04 has increased the likelihood of this indefinite deadlock occurring? Is there any way I can check for a deadlock once the server "freezes" during my testing? – David Hulick Mar 16 '15 at 15:14
  • Do the EXPLAIN SELECT ... on sql04. Which order is the PRIMARY KEY in? "Indefinite" -- well arbitrarily long; yet 6K seconds is seems unlikely. InnoDB should help in several ways (locking, PK clustered with data, etc). PROCESSLIST is probably best way to see the lock (for MyISAM). InnoDB has SHOW ENGINE INNODB STATUS. – Rick James Mar 16 '15 at 20:19
0

Yes Relay slaves are possible. But let's try to speed up the queries first.

The PROCESSLIST smells like you are using MyISAM, and that switching to InnoDB might help a lot. Please show us the rest of select p.ApproxPrice1, p.ApproxPrice2, p.ApproxPrice3, p.ApproxPrice4, p.PromoMessage, p.NightsToSta, together with EXPLAIN EXTENDED SELECT... and SHOW CREATE TABLE for each table involved in that 2-hour query. Perhaps it is missing an index or could be rewritten to run much faster. Or maybe we need to discuss Data Warehousing techniques.

Also, put 5.6 on your road map. 5.0 is about to be labeled "antique". The longer you wait to upgrade, the harder it is.

Suggest you do the following as the first step: Build another slave (not relay) and ALTER TABLE .. ENGINE=InnoDB on all the tables on that one slave. Cautiously put it in production and see it it is overloaded like the existing ones. If it is, then we really need to look at that naughty SELECT.

Tips on converting to InnoDB

  • Thanks for the reply! I have edited in the requested information into my original question. One thing that is concerning is that this only seems to be an issue on sql04...any of the slaves that are slaves directly to the master don't seem to have this issue. – David Hulick Mar 13 '15 at 18:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.