0

MYSQL take too much time for insertion record
I have 32 GB RAM Dedicated Server and its hardly use CPU upto 15% and Memory 20% even 5 crons simultaneously executing. The issue is that, there is PHP script has simple 200 lines of code with some basic calculation and total 3 queries to select and insert with 12 column (4 has an integer, 8 has varchar datatype)

It executes once per day and insert records around 280000 to 300000 records, it takes on average 5-6 hours to execute.

Questions:

1) Why it takes 5-6 hours to insert just 3 lack of records?

2) Why it's not used much resources, RAM and CPU?

3) Is that any configuration to limit mysql execution?

Server Details: Total 4 processors each have Intel(R) Xeon(R) CPU E3-1220 v3 @ 3.10GHz Cache 8192 KB 32 GB RAM

Please help me to figure out the issue

jatinsureliya
  • 23
  • 7
  • 2
    For big imports use `LOAD DATA INFILE`. Much and much faster. – Daan Mar 06 '17 at 12:11
  • Why? How can we possibly tell without seeing your code or the type of data that is being inserted. It could be disk speed, network speed, sun spots... – DavidG Mar 06 '17 at 12:11
  • 1
    Thanks Daan, but i used other table for calculation and insert based on than, not importing anything from other files – jatinsureliya Mar 06 '17 at 12:13
  • Just write everything in a `.csv` file in your script. When you're done creating the `.csv` then do the `LOAD DATA INFILE`. – Daan Mar 06 '17 at 12:14
  • @DavidG Code has just 200 lines which has some simple subtraction and addition with selected data, and then insert in another table which has 12 column and each has maximum 200 char limit – jatinsureliya Mar 06 '17 at 12:20
  • Yes, but we have no idea where your bottleneck is. Is it slow because of the calculation? Or maybe the insertion? Is it network speed slowing things down? Do you have slow disks on the server? There's too many things for us to guess at. – DavidG Mar 06 '17 at 12:22
  • @DavidG Thanks, i will update question with all that details. – jatinsureliya Mar 06 '17 at 12:28
  • @Ryan Vincent autocommit is enabled, checked using mysql> select @@autocommit; – jatinsureliya Mar 06 '17 at 13:02

1 Answers1

0

First create INDEXon YOUR table .Then try to Execute this query

SELECT COLUMN1,COLUMN2..
FROM YOUR.

How much time it took for execution?Note down that timing and Note down Execution timing for same query without creating index on both table. You will definitely get more timing. So indirectly it indicates that Data you want to insert is more dependent on how fast it was fetched. So once Fetching is fast obviously insertion is faster than previous one.

Hope this will helps.

Sagar Gangwal
  • 7,544
  • 3
  • 24
  • 38