I have mysql/php script running on my linux machine. Its basically migrating files content to MySql table. there are about 4400,000 Files, Account files each file`s content is places in a table in one row. It have been 14 hours and so far it have only done 300,000 Accounts.
At first it was very fast and was doing about 1000 files a second now it’s slowed down to 50 files per second and the mysql process is consuming 95% of server CPU.
Although The machine have multiple cores and I was thinking if its possible to allocate more then one core to mysql process which is consuming 95% of CPU.
Or is there any other way to make the process faster?
Thank you.
here is the script https://paste.ee/p/LZwlH#GHxpgqiUUPsVQFchdKVny2DEJQxaXH9V
Advertisement
Answer
Do not use the mysql_* API. Switch to mysqli_* or PDO.
Please provide these:
SHOW CREATE TABLE SHOW VARIABLES LIKE '%buffer%';
select * from players where p_name=' — there is no need to select *, simply SELECT 1. Do you have an index on p_name? That is very important.
It smells like index updating, but can’t be sure.
One way to speed up inserts is to ‘batch’ them — 100 rows at a time will typically run 10 times as fast as 1 at a time.
Even better might be to use LOAD DATA. You may need to load into a temp table, then massage things before doing INSERT .. SELECT .. to put the data into the real table.
Temporarily remove the INSERT from the procedure. See how fast it runs. (You have not ‘proven’ that INSERT is the villain.)