Near identical MySQL deployments behaving very different – High CPU Usage problem

So I have five identical websites, running on five machines provisioned in the same way. The only thing that differs between these installations are the language files and the languages of the text stored in MySQL tables.

Four of them have no problems what so ever. One is struggling a LOT under the same or somewhat less load than the other four.

I cannot understand why this is.

Things I’ve done so far:

  1. Checked slow queries. All queries uses indexes and are in the realm of 0.0008 Sec execution time i.e. very fast
  2. I’ve noticed that the thing that causes most trouble for this MySQL instance is UPDATE and INSERT, so much so, I’ve turned off the UPDATE’s that were there, for this instance. Bear in mind that these UPDATE’s doesn’t cause a blip on the other servers.
  3. Tried to eliminate external factors i.e. noisy neighbours (moved host) etc.

Worth noticing is that the machines are deployed the same i.e. a vanilla Debian 10 installation with a LEMP stack, nothing out of the ordinary at all.

Still, the problem persists. I can see the load of the machine struggling to keep under 1.00. The other machines are in the 0.10 – 0.20 range all the time.

Looking at CPU for the MySQL process on this machine (with 2 CPU cores as the other machines have as well) it is quite often above 100%. The other machines are never – EVER – over 60% for the MySQL process.

So, any help is much appreciated.

Please do let me know if you need me to run a command that you need to see the output from in order to help.


EDIT Spelling and clarifications

What ways are there to securely connect different sites/branches?

I’m somewhat new to the field, but I have been tasked with coming up with the best way to connect remote customer sites to our two main offices. The data sent across from the customer to the main offices contains sensitive information and needs to be protected adequately.

From what I’ve gathered, VPN connections would be one of the traditional ways of doing this, but I’m not sure about what topology would fit best, mesh or hub-and-spoke. There are probably a lot of things you can do with cloud computing too, and if you guys have any ideas that would be great.

I’m sure there are several more ways of transferring data securely and I would appreciate every single bit of advice!

How can I identify the different terrains on the Chult map in Tomb of Annihilation?

At the back of the Tomb of Annihilation book, in Appendix B, there is a random encounters table for Wilderness Encounters on pp. 194-195. The different terrains listed in that table are as follows:

  • Beach
  • Jungle; further divided into:
    • No Undead
    • Lesser Undead
    • Greater Undead
  • Mountains
  • Rivers
  • Ruins
  • Swamp
  • Wasteland

So that’s 9 different terrains in total.

Looking at the map on p. 39, there doesn’t appear to be any legend for describing the different terrains. The only key is as follows, and only shows what the lesser and greater Undead territories look like*:

The only descriptions I can find in the books are on p. 38, which lists the different terrains under the Travel Distances and Navigation sections but does not expand on how to identify these areas on the map, and on p. 40, which is just the section Undead Territory, which only explains the undead territory as per the above key.

The description of the random encounter table, Wilderness Encounters, only says: "Roll percentile dice and check the Wilderness Encounters table for the terrain appropriate to where the characters are", without actually elaborating on how to determine that.

From the map itself, a lot of it is obvious; where the map shows a lot of trees, clearly that’s Jungle, and for hexes that intersect mountains, clearly that’s Mountain, etc. However, Ruins, Swamp and Wasteland are quite ambiguous to me with only the map to go on.

So is there any way to determine these terrains besides just eyeballing the map, and how am I supposed to identify Ruins, Swamp, or Wasteland from the map (if eyeballing really is the only way)?

* There’s also "Ruin", but I assume this is the location of a specific ruin rather than denoting that it’s a type of terrain, especially given that the black square icon turns up in different terrains (for example, the Orolunga ruin is clearly in a hex of Jungle terrain), so I am unconvinced that the black square icon for "Ruin" is related to the "Ruins" terrain.

Below I have attempted to identify each of the 9 different terrains from the map, as well as my best guess as to what they are (some are obvious, others less so, hence my question):

what happens when an Erinyes uses different ropes to entangle?

The Erinyes devil has the extraordinary ability to Entangle an opponent, it reads:

Each erinyes carries a stout rope some 50 feet long that entangles opponents of any size as an animate rope spell (caster level 16th). An erinyes can hurl its rope 30 feet with no range penalty.

However, the entry is not specific about the type and properties of the rope (stout and some 50 feet long are hardly very specific) and I could totally imagine a devil with a fondness for tying up its victims to not use a standard piece of rope. Probably it would use a 2 inch diameter rope of 50 feet or a 3 inch diameter rope of 32 feet, or maybe even a piece of rare bloodvine rope. Now, such ropes could be animated by a 16th level caster like the Erinyes, but the Animate Rope spell assumes a standard 1 inch diameter piece of hempen rope is used.. How would hit points, break DC, escape artist DC and spellcasting Concentration DC change for ropes of different diameter or for bloodvine rope when used for Animate Rope?

How to remove any words containing two adjacent characters with different cases?

I have a list of permutations of ABCabc and I want to remove any permutations with two adjacent characters with different cases (uppercase and lowercase).

For example,

  • ABCcab is kept.
  • ABCacb must be removed because Ca contains two adjacent characters with different cases.
  • AbBcaC must be removed as well.


Here is my attempt but without filtering.

Select[StringJoin /@ Permutations[Characters@"ABCabc"],....] 

Is it possible to lift things multiple times with different castings of reverse gravity?

My players came up with an interesting idea using reverse gravity recently which at the time I let them do because it made sense and I like to keep things flowing but wondered about the actual mechanics of it.

Reverse Gravity lifts everything centered on a point in a 50ft circle 100ft up. 2 of the players in the party had taken it, in player ones turn he cast it at ground level to lift a group of enemies the full 100 feet, the 2nd player then cast the spell a second time centering at the top of the previous spell and lifting the enemies another 100 feet. In player ones turn he stopped his version of the spell meaning player 2 could then drop them 200 feet. As there was nothing to grab hold of on the second casting there was no saving throw

At the time I was unsure if the initial casting of the spell would lock them in at 100 feet but decided to go with it. My players have also asked me if one of them could cast it repeatedly turn after turn. Effectively cancelling it as they cast it a second time lifting people multiples of 100 feet into the air before then dropping them.

Is this a DM decide situation, which I am fine with, or are there any rules stated anywhere for this situation?

Dynamically merge different arrays in javascript

I want to combine two arrays (ranking and matches) that has common properties:

var ranking = [{     def: "0.58",     league: "Scottish Premiership",     name: "Celtic",     off: "3.33",     grank: "3",     tform: "96.33", }, {     def: "2.52",     league: "Scottish Premiership",     name: "Dundee",     off: "1.28",     grank: "302",     tform: "27.51", }]  var matches = [{ date: "2010-04-22", league: "Scottish Premiership", home: "0.0676", away: "0.8", draw: "0.1324", goals1: "3", goals2: "1", tform1: "96.33", tform2: "27.51", team1: "Celtic", team2: "Dundee",}] 

Expected output looks like this:

[{ date: "2010-04-22", league: "Scottish Premiership", home: "0.0676", away: "0.8", draw: "0.1324", goals1: "3", goals2: "1", tform1: "96.33", tform2: "27.51", def1: "0.58", def2: "2.52", off1: "3.33", off2: "1.28", grank1: "3", grank2: "302", team1: "Celtic", team2: "Dundee",}] 

To merge the arrays, I used Lodash _.merge function

var result = _.merge(ranking, matches); 

The output it returned did merge some objects and omitted homogeneous objects.

Please I need some help and insight in achieving this task. I wouldn’t mind any javascript (client-side) solution.

How to make multiple right joins in one single command for different child tables?

On the followin image you see 3 tables: A, B , C. A: Is the table that holds the foreign keys from B and C

enter image description here

enter image description here

If I do a right join between A and B on that foreign key, it works, meaning I get the data from column fooB. Same between A and C with fooC.

But If I put both right joins under the same command it fails with null

+-------------+---------------+ | fooB        | fooC          | +-------------+---------------+ | NULL        | abc           | | NULL        | xyz           | 

*This is slightly different as the image with the commands posted below, as this comes straight from the real deployment, however cmds below still show that both columns do not show up.

I’ve tried mixin up left joins, right joins , outer joins all with same result. I have googled about this, but the way my question is formulated is awkard and the hits I get return the traditional right joins for just one table.

How do I get this to work in one command? CMDS Below:

enter image description here

Same commands as above but in text, as comments are asking for text rather than images:

MariaDB [joinPOC]> select fooB from tableA right join tableB on tableB_idtableB=tableB.idtableB; +------+ | fooB | +------+ | b1   | | b2   | | b3   | +------+ 3 rows in set (0.001 sec)  MariaDB [joinPOC]> select fooC from tableA right join tableC on tableC_idtableC=tableC.idtableC; +------+ | fooC | +------+ | c1   | | c2   | | c3   | +------+ 3 rows in set (0.001 sec)  MariaDB [joinPOC]> select fooB from tableA right join tableB on tableB_idtableB=tableB.idtableB right join tableC on tableC_idtableC=tableC.idtableC; +------+ | fooB | +------+ | b1   | | b2   | | b3   | +------+ 


-- MySQL Workbench Forward Engineering  SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';  -- ----------------------------------------------------- -- Schema joinPOC -- ----------------------------------------------------- DROP SCHEMA IF EXISTS `joinPOC` ;  -- ----------------------------------------------------- -- Schema joinPOC -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `joinPOC` ; USE `joinPOC` ;  -- ----------------------------------------------------- -- Table `joinPOC`.`tableB` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `joinPOC`.`tableB` (   `idtableB` INT NOT NULL,   `fooB` VARCHAR(10) NOT NULL,   PRIMARY KEY (`idtableB`)) ENGINE = InnoDB;   -- ----------------------------------------------------- -- Table `joinPOC`.`tableC` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `joinPOC`.`tableC` (   `idtableC` INT NOT NULL,   `fooC` VARCHAR(10) NOT NULL,   PRIMARY KEY (`idtableC`)) ENGINE = InnoDB;   -- ----------------------------------------------------- -- Table `joinPOC`.`tableA` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `joinPOC`.`tableA` (   `idtableA` INT NOT NULL,   `fooA` VARCHAR(10) NOT NULL,   `tableB_idtableB` INT NOT NULL,   `tableC_idtableC` INT NOT NULL,   PRIMARY KEY (`idtableA`),   INDEX `fk_tableA_tableB_idx` (`tableB_idtableB` ASC),   INDEX `fk_tableA_tableC1_idx` (`tableC_idtableC` ASC),   CONSTRAINT `fk_tableA_tableB`     FOREIGN KEY (`tableB_idtableB`)     REFERENCES `joinPOC`.`tableB` (`idtableB`)     ON DELETE NO ACTION     ON UPDATE NO ACTION,   CONSTRAINT `fk_tableA_tableC1`     FOREIGN KEY (`tableC_idtableC`)     REFERENCES `joinPOC`.`tableC` (`idtableC`)     ON DELETE NO ACTION     ON UPDATE NO ACTION) ENGINE = InnoDB;   SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;  -- ----------------------------------------------------- -- Data for table `joinPOC`.`tableB` -- ----------------------------------------------------- START TRANSACTION; USE `joinPOC`; INSERT INTO `joinPOC`.`tableB` (`idtableB`, `fooB`) VALUES (1, 'b1'); INSERT INTO `joinPOC`.`tableB` (`idtableB`, `fooB`) VALUES (2, 'b2'); INSERT INTO `joinPOC`.`tableB` (`idtableB`, `fooB`) VALUES (3, 'b3');  COMMIT;   -- ----------------------------------------------------- -- Data for table `joinPOC`.`tableC` -- ----------------------------------------------------- START TRANSACTION; USE `joinPOC`; INSERT INTO `joinPOC`.`tableC` (`idtableC`, `fooC`) VALUES (1, 'c1'); INSERT INTO `joinPOC`.`tableC` (`idtableC`, `fooC`) VALUES (2, 'c2'); INSERT INTO `joinPOC`.`tableC` (`idtableC`, `fooC`) VALUES (3, 'c3');  COMMIT;   -- ----------------------------------------------------- -- Data for table `joinPOC`.`tableA` -- ----------------------------------------------------- START TRANSACTION; USE `joinPOC`; INSERT INTO `joinPOC`.`tableA` (`idtableA`, `fooA`, `tableB_idtableB`, `tableC_idtableC`) VALUES (1, 'a1', 1, 1); INSERT INTO `joinPOC`.`tableA` (`idtableA`, `fooA`, `tableB_idtableB`, `tableC_idtableC`) VALUES (2, 'a2', 2, 2); INSERT INTO `joinPOC`.`tableA` (`idtableA`, `fooA`, `tableB_idtableB`, `tableC_idtableC`) VALUES (3, 'a3', 3, 3);  COMMIT; 

Desired output in a single command:

+-------------+---------------+ | fooB        | fooC          | +-------------+---------------+ | b1          | c1            | | b2          | c2            | 

Best way to code a shopping cart consisting of items with different properties

This is an interview question. Interviewer wanted a scalable solution

Goal is to find value of shopping cart. Cart could have 3 different types of items(Physical,Digital,URL).

Only Physical Items have a shipping address.

Only Digital items have a redeemable code that is used in a merchant site.

Only URL items have a URL that gives access to some online resource.

I’ve suggested having an Item class with price property which is extended by classes PhysicalItem, DigitalItem, UrlItem. Objects of these classes added to list which is iterated over to find the total order amount. Interviewer wasn’t impressed.

Is there any good design pattern/approach that is best applied here?