Why is incrementing a variable slow in mysql/php?

Comments

• nishantk Member

  November 2024

  Thanks ChatGPT

  The slowness you're observing with SET in your code can be attributed to several factors that are intrinsic to how MySQL handles session variables and the overhead associated with the operations:
  Key Points to Consider:

  SET Statement Overhead:
      The SET statement involves creating or updating a session variable in MySQL. While lightweight compared to a query that accesses tables, it still requires MySQL to parse, execute, and manage the statement for each iteration. This adds a small but non-trivial overhead, which accumulates significantly when executed a million times.
  
  I/O Overhead Between Application and Database:
      Each SET statement in your code initiates a round trip between your PHP application and the MySQL server. Even though the data being sent is minimal, the cumulative effect of a million interactions is substantial.
  
  High CPU Usage:
      High CPU usage occurs because both PHP and MySQL are actively processing requests. PHP is managing the loop and preparing the SQL statement, while MySQL is handling the execution of each SET operation.
  
  Comparison of SET and @insert_count = $i:
      When using @insert_count = $i, you are still sending an SQL statement (SET @insert_count = $i), and the MySQL server still incurs the overhead of parsing and executing it. This is why the performance is nearly identical.
  
  Why ->query() is Slower:
      The ->query() method typically involves additional parsing, buffering, and management compared to ->exec(). While ->exec() executes a query without expecting a result set, ->query() prepares to fetch results, which adds more overhead.
  

  Solutions to Improve Performance:

  Avoid Repeated SQL Execution for Simple Increment Operations:
      Instead of using MySQL to manage the increment operation, manage it directly in PHP:
  
  $insert_count = 0;
  while ($i++ < 1000000) {
      $insert_count++;
  }
  
  This eliminates the overhead of SQL execution and round trips entirely.
  

  Batch Updates:

  If you must use MySQL to track the variable, reduce the frequency of SET operations by batching updates. For example:
  
      $batch_size = 1000;
      for ($i = 1; $i <= 1000000; $i++) {
          if ($i % $batch_size == 0) {
              $_DB->exec("SET @insert_count = @insert_count + $batch_size");
          }
      }
  
  Persistent Connection:
      Ensure that you're using a persistent database connection, as repeatedly opening and closing connections could exacerbate the performance issue.
  
  Profiling and Optimizing MySQL Configuration:
      Check if the MySQL server is tuned for optimal performance. Key parameters like query_cache_size and max_connections could impact execution speed.
  
  Alternative Approaches:
      If @insert_count is only required within the PHP script and not shared across sessions, there’s no need to involve MySQL at all—keep it purely in PHP.
  

  Final Takeaway:

  The slowness isn't because SET is inherently slow—it’s relatively efficient for what it does. However, executing it repeatedly in a loop introduces avoidable overhead that becomes significant at scale. Moving the logic to PHP or optimizing your interaction with MySQL should address the issue effectively.
• Adam1 Member

  November 2024 edited November 2024

  @nishantk said: The slowness isn't because SET is inherently slow—it’s relatively efficient for what it does. However, executing it repeatedly in a loop introduces avoidable overhead that becomes significant at scale. Moving the logic to PHP or optimizing your interaction with MySQL should address the issue effectively.

  but this is what im asking - why is it so slow?

  incrementing (or even setting it to the same value as Ive found) mysql @var = 15s
  incrementing in PHP: 5ms

  afaik global variables dont cause writing to logs etc and im not seeing disk activity, just cpu.

  And it really is slow, I have actual queries that update rows in a table, that run faster than this (prepared). i.e. insert/update 1m rows < 15s
• Adam1 Member

  November 2024

  Looks like its simply the php->mysql connection overhead. Damn, will see if i can improve that.
• jobayer Member

  November 2024 edited November 2024

  @Adam1 said:
  incrementing (or even setting it to the same value as Ive found) mysql @var = 15s
  incrementing in PHP: 5ms

  And it really is slow, I have actual queries that update rows in a table, that run faster than this (prepared). i.e. insert/update 1m rows < 15s

  For php there is no connection overhead and php need less cpu to run these task as it keeps your data in strings (ram not in disk) but mysql needs more due to handling a lot of background things and keep loop data in disk. That's why php loop is faster than db. To optimize your DB connection you need to
  1. use persistent connection
  2. use local server and disk
  3. High frequency cpu and nvme disk (good io)
  4. and most importantly do not close db connection after each and every query. Close it only after finishing your all queries/tasks.
  5. Also you can disable mysql name resolving and increase your inno db log and buffer size (for innodb storage engine). On our testing we found a great improvement after optimizing mysql config as per server specifications.You can use mysql tuner it's not perfect. but do the job.
• egoror Member

  November 2024

  Try the same with increment in a stored procedure?
• vovler Member

  November 2024 edited November 2024

  If you want performance and have it saved somewhere just do it into a .txt file or something.
  MySQL will perform worse for a multitude of reasons.
  If you really want SQL and both SQL and the PHP are on the same machine, try SQLite3 but do check how to improve its performance with PRAGMAs like WAL file, etc.
  Really want performance and don't care about losing your data? You can create the SQLite3 db in memory.

  You can also increase the performance of PHP, if that counter is like a visitor counter that will trigger on a visit or page view and not just something that you run once from the command line, you can try setting up swoole or workerman which use a different execution model than the most popular PHP-FPM, that should improve the performance by a lot but you may need to change a few things in your application for it to work with it, for example no exit() or die()
• Adam1 Member

  November 2024

  Thanks guys,

  in this case i just moved the counter to PHP, I just liked the idea of counting within my PDO class using a Mysql variable, but just surprised how it ended up using a noticeable amount of CPU time.
• Levi Member

  November 2024

  @Adam1 said:
  Thanks guys,

  in this case i just moved the counter to PHP, I just liked the idea of counting within my PDO class using a Mysql variable, but just surprised how it ended up using a noticeable amount of CPU time.

  You may also try to scoop soup with fork. It somehow works, but not efficient. Congrats on finding the most performant solution.
• ailice Member

  November 2024

  AFAIK, in MySQL there LOCK flag when UPDATE data happen (it got worse when you use SET statement so it does double query check) so it would wait last UPDATE query to totally complete to process any next query.