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 its 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
Do not use the
mysql_* API. Switch to
Please provide these:
SHOW CREATE TABLE SHOW VARIABLES LIKE '%buffer%';
select * from players where p_name=' -- there is no need to select
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.)