Server and MySql load issue

Question

I am using a code that fetch 100 items from a website and insert it into the database if the item exists it updates that record. It runs as a cron job every minute. It runs upto 50000 products.

The script works good and products are imported successfully. The problem now is the server and MySql load. My server support said that it takes almost 50% of the resources and they need to disable that script.

I tried google to check for a solution and find that Load file is better choice than mysql_query insert and updates. So I am planning to write all queries in a txt file on the server and once completed import it to db using load file.

Is it a good method? or it is also taking the same server load? I want to know the expert's decision before proceeding. Please help. Thanks

Answer 1

It would be helpful to know all the variables at play in your scenario. Obviously you do not want to disclose any sensitive information about your setup, but without knowing more parameters of your situation it is hard to give an appropriate answer. Any time the issue of MySQL performance comes up you have to evaluate everything you are doing, not just the script that you wrote to run on a cron every minute (this in itself at first glance seems like it could be optimized or re-factored to run a different way). Some things that would be good to know are: - What type of hosting environment are you in, ie shared, cloud etc...? - What kind of traffic does your site get every day, hour, min etc...? - How many web servers are you running? - How many DB servers do you have running? How many masters and slaves? - Do you have load balancing? - Are you using any type of caching like Memcache?

It would also be helpful to know why you are doing that many updates every minute? If you have no other choice then you may want to consider dedicating resources to just that procedure and running a cron once a day in the middle of the night to sync your DBs up.

This all may be overkill, you will be able to gain a lot of performance if your DB tables are normalized correctly and your indexes are set correctly. A very general rule of thumb is make sure the columns in your DB that are being queried in your "WHERE" clause have indexes on them.

Hopefully this helps.