0

following problem with SELECTS like this only in staging system:

SELECT * FROM `nd` 
WHERE `nd`.`nd_id` IN (1, 396, 782, 830, 831, 1929, 3786, 5059, 5713, 5714, 6698) 
AND `nd`.`nd_type` = 'App\Models\N' 
AND `nd`.`deleted_at` 
IS NULL

In my dev system the select needs 340 ms. In my staging system (other server) all CPU go up to 100% and never finish the selects. Same problem when I start this query direct in PHPMyAdmin in staging system.

How I can do? Some settings in config wrong?

Maybe the problem is: After deployment to staging the migration for set keys in ndaten table was not completed. The first key was set and migration execution stops. Repair and optimize the table already done.

Thanks for help :)

pette87
  • 1
  • 1
  • Some reason why there are differences in operation and/or performance are like CPU+RAM, data sizes (usually dev side have much lesser data), mysql config (my.ini/my.cnf), table structure (indexes) etc. – FanoFN Jul 15 '21 at 03:42
  • I imported all data from the two tables from stage to dev. Bove have the same data... – pette87 Jul 15 '21 at 03:44
  • How about the hardware? CPU+RAM? CPU and RAM usage? Is it a Windows machine? Does it shows 100% CPU usage and `mysld` is on top if you sort the CPU usage by descending in the task manager? – FanoFN Jul 15 '21 at 03:49
  • The stage OS is a Ubuntu system. 8 GB RAM and RAM under 2GB Usage and not change by execution this SELECT. The RAM are fine I think. – pette87 Jul 15 '21 at 04:15

1 Answers1

0

This composite index is likely to help both staging and production:

INDEX(nd_type, deleted_at, nd_id)

By "the migration for set keys in ndaten table", do you mean that staging and production did not have the same indexes? If so, that violates a main intent of staging. (I don't know laravel's "migration" features, so I cannot address details, but this sounds vital!)

If, after dealing with both of those, you still have problems, please provide

SHOW CREATE TABLE
the size of the relevant table(s)
the query
EXPLAIN SELECT ...
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';  -- on both
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thanks :) Solved with: Schema::table('notardaten', function (Blueprint $table) { $table->index(['nXXX_id', 'nXXX_type', 'deleted_at']); }); – pette87 Jul 31 '21 at 04:03