I insert data into DB via web application.
The insert has been delayed since one day.In the past,
with the same amount of data.
The processing, which took about 30 seconds to 1 minute, now takes more than 10 minutes.
(Processing takes time, but succeeds.)
On the other hand, for the same web application that dumps the same DB and builds it simply in a local virtual environment,
The memory and other specifications are lower than the actual ones, but the insert process will be completed in about 1 minute.
In such cases, what can be considered to be the cause?
My.cnf settings? Hard drive or other I/O issue? Network problems?
I would appreciate it if you could tell me how to investigate and how to solve it.
As you can see in the question, if you can dump the DB and rebuild it in a different environment to see the speed increase,
DB fragmentation is likely occurring.
In this case, running OPTIMIZE TABLE table name will improve the event.
#mariadb is not a specialist, so this is the answer after a desk survey.
The following is a supplement.
In general, EXPLAIN is used to check the execution plan for slow execution of SQL statements.
I think you can see the processing time for each part of the SQL statement (for example, one of the conditional expressions in the WHERE clause), so I'm going to
There is a way to check.
(In this case, the SQL statement will be reviewed.)
Also, if INSERT is slow, the index update may take a long time.
You can check whether you have a large number of indexes, a large number of records, etc.
(In this case, the table design will be reviewed.)
© 2024 OneMinuteCode. All rights reserved.