PDO insert request slowing down the site

Question

On my site, I have encoded a function that shows how many unique visitors and how many page views I get per day.

The problem is that sometimes the insert request is executed forever, and there is no DELAYED INSERT function in InnoDB.

Edit: It uses InnoDB, what I mean with a long load time is about 6 seconds instead of 0.1-0.5 seconds. As soon as I delete the registration, the site is much faster.

The $b array contains information about the browser, but according to XHProf, its a PDO request that takes so long.

Insert Code:

 $values = array( 'time' => time(), 'ip' => $_SERVER['REMOTE_ADDR'], 'page' => rtrim((isset($_GET['q']) ? $_GET['q'] : 'index'), '/'), 'browser' => $b[11][0] . ' ' . $b[11][1], 'os' => $uos, 'referred' => (isset($_SERVER['HTTP_REFERER']) && !preg_match('|^' . Config::getValue('site', 'url') . '|', $_SERVER['HTTP_REFERER']) ? $_SERVER['HTTP_REFERER'] : ''), ); $this->table->insert($values); 

and insert function code:

 public function insert($table, $data) { ksort($data); $fieldNames = implode('`, `', array_keys($data)); $fieldValues = ':' . implode(', :', array_keys($data)); $sth = $this->prepare('INSERT INTO ' . $table . '(`' . $fieldNames . '`) VALUES (' . $fieldValues . ');'); foreach ($data as $key => $value) { $sth->bindValue(':' . $key, $value); } $sth->execute(); } 

Answer 1

Minor optimization. As you do your binding, you can get rid of the foreach statement and pass the array directly for execution. PDO maps the array key to the names of the binding names that you created in the preparation statement.

 $sth->execute($data); 

However, you should look at your slow query log in mysql to see what happens. Although you are using Innodb, there are scenarios where Innodb is blocking a table. Perhaps that is why inserts sometimes take a lot of time. You can reduce the slow query threshold to 3 seconds to capture requests. The magazine will tell you how much time was spent at each stage (i.e. blocked).

Answer 2

"On my site, I encoded a function that shows how many unique visitors and how many page views I get per day."

Is a live query being executed for every page request? This could very well be a performance issue. It will not kill anyone if you ask for these numbers every hour or so and cache the value. Subsequent pageviews can then use the cached value instead of the live one.

"The problem is that sometimes the insert request is taken forever, and there is no DELAYED INSERT function in InnoDB."

Maybe this is not INSERT itself, which slows you down, and your MySQL instance is just busy all the time calculating page views. There is a chance that this problem will disappear after you start serving the numbers in the form of cache pages.

Answer 3

As deceze said, he may need more details. But I think this might get stuck in "Config :: getValue (" site "," url ")". Btw, is your mysql remote?

Answer 4

Is there a reason you use prepare instead of calling pdo -> exec() ?

A search around shows that labels for people mark prepare as much slower and more intensive than using exec() , especially if you don't expect any values ​​back (also see the document for Prepare - it is designed to optimize calling the same thing at the same time request).

Instead of binding your values, you can do something like this -

 function insert($table, $data) { ksort($data); $fieldNames = implode("`, `", array_keys($data)); $fieldValues = implode("', '", array_values($data)); $this->exec("INSERT INTO $table (`{$fieldNames}`) VALUES ('{$fieldValues}')"); }