1

I'm trying to migrate a legacy PHP/Fat Free project from php 7.1 to 7.4 and I found that some queries take too long (like 10x more time) to finish. Particularly some inserts. I'm running the same project in my localhost with xampp (7.1.32 and 7.4.6) and using the exact same MariaDB server (v10.4.8) with the exact same database always.

The code is something like that:

foreach($ridiculouslyLongArray as $row) //I'm talking about some millons of rows
  $this->db->exec("INSERT INTO a_table (field1, field2, fieldn) VALUES ('".$row['field1']."', '".$row['field2']."', '".$row['fieldn']."')");

//Yes, it's open to sql injection, I will fix that too

The definition of $this->db is the next:

$this->db = new DB\SQL('mysql:host=localhost;port=3306;dbname=something', 'dbuser', 'dbpassword', array(\PDO::ATTR_ERRMODE=>\PDO::ERRMODE_EXCEPTION));

and is a wrapper of PDO as far as I know.

I've tried to change the code to insert multiple rows per query but the query still taking much more time than in php 7.1.

This is my setup

->Original Project (in which the queries run fine)

  • PHP 7.1.32 (memory limit 2048mb)
  • Fat Free 3.6.4
  • MariaDB 10.4.8

->New Project (in which the queries run slow)

  • PHP 7.4.6 (memory limit 2048mb)
  • Fat Free 3.7.2
  • MariaDB 10.4.8 (same server and db that in the previous one)

Thanks for your time.

EDIT: I Just noticed that the PDO Drivers for MySQL are different between versions

for PHP 7.1:

  • mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $

for PHP 7.4:

  • mysqlnd 7.4.6

Edit 2: The query is in a transaction and it is using the same indexes and same dB engine because is the same insert over the same table in the same database on the same server. Nothing change in the code only the PHP versión.

Martin
  • 22,212
  • 11
  • 70
  • 132
Ivan Stadius
  • 17
  • 4
  • Do you have the same indexes? Do you have the same Database Engine (InnoDB or MyISAM, etc.)? – Martin Oct 16 '20 at 23:06
  • If you're dealing with inserting millions of rows in one go you're probably running into memory issues. you should be wrapping your work in a TRANSACTION. – Martin Oct 16 '20 at 23:08
  • Due to your Foreach loop you might be running into clog up for the SQL, so you should reset the query cache. To do this you can try : https://stackoverflow.com/questions/5231678/clear-mysql-query-cache-without-restarting-server perhaps? (shot in the dark) – Martin Oct 16 '20 at 23:10
  • @Martin, I forgot to mention that but the query is in a transaction and it is using same indexes and same dB engine because is the same insert over the same table in the same database on the same server. Nothing change in the code only the PHP versión. I will try the cache thing. – Ivan Stadius Oct 18 '20 at 02:15
  • you have to provide **much, much more** debugging information to have an answerable question. A question like "oh this code is wrong i'll fix it later", "oh, here i forgot to mention" simply won't do. You cannot even make your mind whether it's "some queries" or "some millions". – Your Common Sense Oct 18 '20 at 04:18
  • this is why it is **required** by the site rules to provide a [**working** example](https://stackoverflow.com/help/minimal-reproducible-example) – Your Common Sense Oct 18 '20 at 04:20
  • the only shot in the dark i can make is this wrapper connects anew every time it executes a query – Your Common Sense Oct 18 '20 at 04:21
  • Have you ensured your PHP.ini file is the same, such as setting the same memory allocation limits? – Martin Oct 18 '20 at 11:35
  • Ok, I don't know what changed. I suppose some change was applied in some configuration. But it was fixed by restarting the computer. It's strange because I restarted Apache and MariaDB several times. – Ivan Stadius Oct 19 '20 at 15:11

1 Answers1

0

This wasn't explicitly mentioned in the comments, but something else that may be causing some slowness is query logging.

By default, Fat-Free will log all DB queries. If you are running a gazillion inserts, all those inserts are being logged. If it's not already, I would recommend in production to disable query logging. Wherever your bootstrap/services file is that creates the db connection, I would add this after it:

$f3->set('db', new DB\SQL(/* config stuff */));
if(ENVIRONMENT === 'PRODUCTION') { // or whatever you use to signal it's production
    $f3->db->log(false);
}
n0nag0n
  • 1,575
  • 1
  • 17
  • 25