Due to repeated connections to the database, a significant load on the MySQL server arises, these operations account for 64% of the total load, inevitably you have to look towards a permanent connection, tell me how best to do it? PDO or Myqli ? Now the site is set up on PDO . Read on the Internet that with constant connections is not recommended PDO . I will also gladly accept additional tips.

Here is the database load statistics: enter image description here

  • more data is needed, what is the traffic to the site, what do repeated connections mean? - Jean-Claude
  • How was 64% load measured? And this is 64% of what? CPU, time according to the profiler, something else? Just mysql architecturally knows how to easily open-close connections and how your question looks like - absolutely no details are indicated - it’s not very likely that you have tens of thousands of rps. - Small
  • The site has an average of 700 visitors. The statistics on the database says: Total connection time - 350 seconds. The total request processing time is 193 seconds. That’s 65% coming out - Igor Salamov
  • one
    Where exactly is it written? Are you sure that this is the time spent on the connection setup protocol, and not, for example, the total lifetime of the connections? - Small
  • one
    One thing, however, in this picture you can notice: you have connections even more than the number of requests. Accordingly, you install them unnecessarily. - Small

1 answer 1

First, the link , just read everything there - 10 minutes, but many questions will disappear by themselves.

  1. I do not understand, where did this stupid legend come from that it is necessary to “close” the connection? Maybe it was in php version 4 and below, on the obsolete expansion of mysql, but in PDO it is definitely not the case. That's just really, everyone who writes about it - go and read it carefully again.

Upon successful connection to the database, the created PDO object will be returned to the script. The connection remains active throughout the lifetime of the object. To close a connection, you must destroy the object by removing all references to it (this can be achieved by assigning NULL to all variables pointing to the object). If you do not do this explicitly, PHP will automatically close the connection when the script ends.

  1. How comrades write correctly in the comments - in an amicable way, for the execution of the script, the connection should be created only once. How do you implement it - no difference: you can use a global variable, you can use a static variable of a function, you can use a static class method (with a static class variable). Everyone who writes simply “static methods is bad” is, of course, brilliant, to repeat after the majority, but I really don’t see any reasonable reason why, for example, people create a new connection for each object. And it is absolutely not necessary for each request to open a separate connection.

  2. Since you have a PDO, use its maximum capabilities:

    but. Use prepared queries and never paste variables manually into the query string.

    b. where exactly it is necessary to execute several requests in a loop - do PDO :: prepare before the loop, write to a variable, then in the loop, execute PDOStatement :: execute and PDOStatement :: fetchAll. But still, in most cases, I recommend to think - is it possible to pull out all the necessary data, nevertheless, in one request.

    at. read constants PDO :: FETCH_ *. most often needed:

    • PDO :: FETCH_ASSOC - almost always (it is recommended to write it in connection options as the default extraction mode)

    • PDO :: FETCH_COLUMN - when you need to get an array in which all the values ​​are collected by only one of the columns in the database

    • PDO :: FETCH_KEY_PAIR - key - the first field of the sample, value - the second field of the sample (for example, array [client_id => client_name]).

    • PDO :: FETCH_UNIQUE | PDO :: FETCH_ASSOC - when you need to get an array, the key of which will be the first field from the sample, and the value - the data array.

    • PDO :: FETCH_GROUP | PDO :: FETCH_ASSOC is a most wonderful combination, the sample is grouped and decomposed into separate “sub-arrays” in the first field.

PS well, it does not relate directly to PDO, do not forget about the keys and preliminary preparation of statistical data (judging by the fact that you have only three updates per day - this should reduce the time of samples by several times).