AWS RDS is showing very high wait/synch/mutex/sql/ values and EXPLAIN statements in performance insights

I’m running a CRON script which checks the database for work and executes anything that needs to be done. It does this across ~500 customers per minute, but we are using AWS RDS with a 16 vCPU machine which, until recently, has been plentiful to keep it happy (normally plugging along under 20%).

This weekend we updated customers to the latest version of the code and implemented some tooling, and since then we’ve started seeing these huge waits: enter image description here

Further I’m seeing that about half of our busiest queries are EXPLAIN statements, somewhere illustrated here: enter image description here

Nowhere in our code base is an "EXPLAIN" performed (though we are using AWS RDS performance insights, ProxySQL and New Relic for monitoring). I did notice that in the past week our number of DB connections was previously baselined around 10 and is now closer to 90. enter image description here

Any ideas on where I should be digging to find the cause of these waits and explain statements? And if they could justify the large number of open connections?

Are the following statements True or False? Briefly explain your answer

a. Best-first search is a special case of Uniform Cost Search.
b. A heuristic that always evaluates to h(s)=1 for non-goal search node s is always admissible (given that cost value for each node is positive integer)
c. Hill-climbing can be called Greedy Global Search.
d. Local Search uses less memory than Global Search.

Thank you for helping me <3

How do you combine multiple update statements for the same row using MySQL trigger

Background:

Each time a column is modified, I need to update the associated column (which has the same name) in a second table. This is my first attempt at using a trigger.

Code:

Here’s a simplified example of what I’m trying to do, which does its job fine, but inefficiently:

DROP TRIGGER IF EXISTS update_second_table; DELIMITER // CREATE TRIGGER update_second_table   BEFORE UPDATE ON first_table    FOR EACH ROW BEGIN   /* putting IF statements on one line so it's easier to see what's happening */   IF NOT(OLD.firstname <=> NEW.firstname)   THEN UPDATE second_table SET firstname  = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;   IF NOT(OLD.middlename <=> NEW.middlename) THEN UPDATE second_table SET middlename = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;   IF NOT(OLD.lastname <=> NEW.lastname)     THEN UPDATE second_table SET lastname   = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;   IF NOT(OLD.nickname <=> NEW.nickname)     THEN UPDATE second_table SET nickname   = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;   IF NOT(OLD.dob <=> NEW.dob)               THEN UPDATE second_table SET dob        = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;   IF NOT(OLD.email <=> NEW.email)           THEN UPDATE second_table SET email      = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;   IF NOT(OLD.address <=> NEW.address)       THEN UPDATE second_table SET address    = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;   IF NOT(OLD.city <=> NEW.city)             THEN UPDATE second_table SET city       = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;   IF NOT(OLD.state <=> NEW.state)           THEN UPDATE second_table SET state      = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;   IF NOT(OLD.zip <=> NEW.zip)               THEN UPDATE second_table SET zip        = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF;   IF NOT(OLD.phone <=> NEW.phone)           THEN UPDATE second_table SET phone      = CURRENT_TIMESTAMP WHERE id = OLD.id; END IF; END; // DELIMITER; 

The problem:

As you can see, depending on how many columns are updated in `first_table`, there can be as many as 11 update statements on the same row in `second_table`.

The question:

Is there any way to combine the update statements into one?

What can I read about how we tie the stochastic characteristics of task resolution into statements about a game system’s aesthetics? [closed]

I like making RPG systems. One thing I’ve noticed is that different kinds of task resolution systems make the game significantly different.

Background

For example, games like D&D 3.X and Shadowrun 4E have a very details-oriented approach to task resolution. A typical die roll in combat might be something like 1d20+1+1+4+3+(7+2+3)*1.5+20-2 v.s. 10+8+min(4,1)+5+3+2+5, where each number comes from a different source and things like "I enjoyed breakfast greatly! +3 to hit" and "My shoes are freshly polished for +1 max dex mod to AC" matter greatly.

There are a limited number of modifiers and choosing the right combination for any given character is immensely important to the character’s success in the game.

Other games, like FATE 2.0 or Amber Diceless, have a different approach. There a typical task looks like 5+4dF vs 3+4dF±2. All of the things that are tracked carefully in the first examples are abstracted away into a single modifier. This modifier generally does not exceed 50% of the base skill amount, and is generally regarded as less important than having a higher base skill amount. (In Amber diceless the ‘rolls’ are even more extreme: 1±1 v 3±1 is an example of a task’s mechanical description there).

I am comfortable talking about this kind of difference between RPGs in general. We can talk about levels of abstraction, we can talk about focus, we can describe a system as ‘high-level’ or ‘detail-oriented’ or whatever.

The problem

What I am less comfortable with is the manner in which the stochastic character of a system’s task resolution comes off to participants of RPGs run in it.

For example, I can tell you that the absence of dice in Amber significantly changes the feel of the game versus a similar setting modeled and run in FATE 2.0.
I’m much less articulate as to what the actual differences are, though. I’m aware of some popular pieces on randomness in RPGs, like the ‘goblin dice’ thing, but none of them really talk about the full space of stochastic design available to us as game designers. We can talk about how 2d6 is ‘less swingy’ than 1d13, but how using one or the other more commonly for some hypothetical ruleset would influence our aesthetic perception of that ruleset is not immediately clear.

I’m looking for a published overview of ways that different features of a task resolution system (in terms of stochastic analysis) are relevant to the ‘feel’ (i.e. the perception of aesthetic qualities) of the overall game system from a game-design perspective. In particular, I’m interested in the impact of the magnitude of the stochastic variance of the resolution system on the system, as well as the impact of greater or lesser volatility, and of polynomialization of the distribution (i.e. how binomial, trinomial, etc distribution for a game’s randomizer affects the game’s overall aesthetic).

Basically, I’m looking to read published work addressing the question: How do we tie the stochastic characteristics of task resolution into a statement about the experience of using a particular role-playing game system?

What makes a good answer?

Answers will recommend further reading on the topic to support the claims made in their shorter overview. IJRP preferred. I’m looking for an overview, not a full discussion– it’s sufficient to provide references to appropriate academic literature and to explain how, and that, that literature answers the question. Also, since comments indicate that people are seeking primarily for online sources, let it be explicitly mentioned that offline sources like books are no less good for their being offline (RPGs may be young, but they most certainly predate widespread internet use).

Beyond unauthorized data access, what security considerations should I have regarding a user-facing language based on SQL SELECT statements?

I’m considering making a new language based on SQL SELECT statements to allow users to export CSV data in the manner they please. I’m confident in being able to interface this with a permissions system by inspecting the resulting AST from parsing before turning it into a SELECT statement to execute, so I’m not really concerned about this leading to unauthorized data access.

This language would be pretty much a 1-to-1 mapping of SQL SELECT statements, except for a few changes regarding joins and a few other things.

Users are relatively few and can be easily traced and contacted. It’s not the public at large.

The underlying DB would be MariaDB.

What should I be concerned about from this idea? If it’s a bad idea, why?

I thought about the possibility of making a query that doesn’t terminate by using WITH RECURSIVE, so I’m not going to support that syntax, and I made the following question at the DBA SE to see what other ways a SELECT statement could be non-terminating (I thought of a few more while writing that question):

What are all the ways that a SELECT statement could be made to not terminate or take a very long time?

Besides that, is there anything more? Any particular risk? Is it possible to make some type of resource bomb with it, to consume all memory for example?

Access to this language could be put under a permission so only very privileged users could use it, but I wonder if that’s needed.

Checking if two statements can be reached in one control flow

Assume I have a graph representing the control flow and the call graph of a given program. I also have a first and a second statement. I now want to figure out if it is possible to execute both statements (in order) within the same program execution.

Control Flow Graph: I have a graph with all the statements of the program and edges connecting the statements determining the control flow of the program intra function (i.e., within a function).

Call Graph: I also have edges connecting any function call with the start of the function control flow of the called function.

The literature I found concerning control flow covers only intra function flow analysis and the only correct approach I can come up with is a depth first (or breadth first) search starting from the first statement. This, however, hardly feels correct as it is quite cumbersome and I would expect a better solution.

I’ve been asked to change this statements into predicate form by using standard form but I’m stuck

I studied Artificial intelligence a long time ago and I’ve had no interest in it. I studied Artificial intelligence a long time ago and I’ve had no interest in it. But recently one of my friends asked me to change the following states into predicate form by using a standard form. Can someone help me?

• Marin is married to Sylvia who has a daughter Joy. • Harry is the son of Amos who is married to Joy. • The parent of one’s parent is his/her grandparent.
• If a person is married to another person who has a child then the first person is also a parent of the child.
• If a person is married to another person then the latter person is married to the former.

Change these sentences to predicate form, by using the standard form of logic and the following predicates: parent(X, Y), grandparent(X, Y), and married_to (X, Y).

I know it may look simple for some of you. But I recently went to another field about 2 years ago. Thank you.

Why is using prepared statements in PHP considered best practice?

Let me first start by stating that I am by no means a webdeveloper, so please do point out if I’m going in the wrong somewhere in my story.

I think most people agree with the idea that using prepared statements effectively stops injections if executed properly. With that said, in order to write prepared statements in PHP, you need to establish a connection with the database in your php file. In other words, if the webserver ever becomes compromised, the account used to establish a connection with the database becomes compromised as well as its basically there inside a php file, allowing your attacker to basically create dumps out of your database. If I were to design an application, I would separate the website and the logic, through some API server or something similar, in order to make sure that the database account isn’t compromised as well.

Why is it that nobody points out what in my eyes looks like an obvious security flaw in PHP? Or is the chance of this being exploited so small that people aren’t even considering the chances that it might happen?

How to write suitable three address code for switch-case statements?

I want to translate a java switch-case statement to intermediate representation of the three address code form. Three address code or TAC is a form of intermediate representation where each instruction contains at most three addresses and one operator. An address is a name such as x (stored in the symbol table), compiler generated temporary such as $ t_{1}$ or a constant such as 3.14 or ‘s’. A name could refer to a variable, label, etc. If you have an arithmetic statement

(3 * w) + y

then the corresponding TAC would be

$ t_{1}$ = 3 * w

$ t_{2}$ = $ t_{1}$ + y

Now, when it comes to switch statements, my textbook (the Dragon Book for Compilers), gives a translation of this form:

TAC for switch-case statement

for a switch-case statement of this type:

switch E

case V1: S1

case Vn-1: Sn-1

default: Sn

Assuming the first translation is used, at the code generation stage (when we convert 3AC to machine code), the code generator will interpret the instructions as a sequence of conditional and unconditional jumps with intervening labeled statement blocks. It would translate them into the machine code (say x86) version of the same. When these instructions are executed by the processor, it handles each jump sequentially to determine the correct labeled block to be executed. But, I have also read that the machine code translation of a switch-case statement includes a jump table that allows the processor to execute the entire switch statement in one go. So, then which version is used?

I wanted to post this on stackoverflow but I do not have enough reputation to post an image.