In order to speed up the queries we have to convert MyISAM tables to InnoDB. This will give you a huge speed improvement and take a lot of load of your CPU.
Depending on your server resources and tables, this can take a long time to do, especially if you have subscriber contacts tables exceeding 1 million rows or more. Some systems may even become unstable while the converting happens. We haven’t felt such a high impact as we run all our Oempro Databases on SSD drives in Raid.
To get started, copy the code below and upload it to you server.
IMPORTANT: Make a full backup of your DB first. I don’t take any responsibility if you loose any of your data. Use this script at your own risk.
Enter your DB connection details. Around line 32, we chose to run 20 tables at a time. Chose to your liking. On slow servers I recommend 5-10 tables at a time, with more powerful servers, you can use 20-unlimited. It should be easy to spot on how to change it in the code.
Once you have converted all the tables you can add a cron job to run once a day. This is necessary to convert the new created tables, as they still get created in MyISAM.
Also have a look at you innodb_buffer_pool_size. Make sure you have a high enough value in there.
发表于 2014-8-13 23:18:28
发表于 2014-8-13 23:27:47
楼主
