I move magento 1.8 to new server with MySQL 5.7.
Yes, I know, 5.7 is not supported, but I see that many people run on it with no problem.
Shop have about 100 categories, and 12000 simple products.
Reindex is not possible to do from admin, so I need to do that from terminal. And usually reindex take about 1h.
On other servers where I'm running MySQL 5.5 and 5.6, reindex is no problem.
But on this with 5.7 if I run it from terminal, after 15 seconds, in TOP all cores are 100%, and Load Average more then 6. And whole server stuck. I need to cancel reindex.
Is there any tweak to do with 5.7 to run Magento 1.8 properly?
I know that I need to update it, but i can't :/
With the number of products mentioned, if you have multiple stores, and lots of different Catalog Price Rules, all of this multiplies the amount of data that has to be taken out of the EAV tables and put into flat tables. Depending on what type of hardware you are hosting on, you may want to consider upgrading if you are hitting high load averages, consider hosting your MySQL database in another server with more resources available.
With all that said, there are some ways not involving hardware upgrades, if you are indexing via the shell, using php shell/indexer.php without any options or simply info will give you a list of options to use, instead of using reindexall you can index each indexer separately to help identify the failing ones and not reindex indexes that have already been indexed with using reindexall.
Disable any core modules you do not need or used, RSS especially:
Magento does not ignore simple products that are set to "Not visible" and creates URL Rewrites for them, this module excludes them from the URL Rewrite indexer:
Also, it helps to keep your MySQL database as lean as possible removing any non-required data like logging, and dataflow import tables, as well as quote entries from guest customers who added something to cart and never checked out:
If you are on a multi-core server since PHP processes are single threaded only one CPU core is used during indexing, a free (although old) module does exist for utilizing the additional CPU cores, however you may have better results with the paid for solutions.
You can also use some common tools like mytop, htop, mysqltuner.pl or mysql-primer to monitor MySQL resources and get recommendations on MySQL changes (if you are able to edit my.cnf/ini) Some further details on these: https://magento.stackexchange.com/questions/13957/full-page-cache-on-ce-1-8-an-fpc-magento-module-va...
Hope this helps, cheers!
Also, if you are curious of the internal workings and reasons why indexing is needed for Magento: http://magento.stackexchange.com/a/90121/69
thank you for detailed post, I appreciate it very much!
It is very clear and I'm working on your suggestions.
It is a little bit difficult because I'm not programer, but what is one of the problem with slow reindex.
Firstly, your explanation about rewrites.
I noticed that we have too many SKUs with same url_key. So magento every time add random number after that url_key.
I emptied core_url_rewrite and run reindex, and for 17000 skus (only about 4000 active) I got about 200K rows in that table.
After every next reindex add new 115K rows in table.
Maybe reindex working slow because that?
I wan't to change script for product update (I'm updating it with magmi), and set url_key to be unique. So in my case now is
sku = dasd32424
description = brand-shoes
url_key = brand-shoes.
Like I said, we have more different "brand-shoes" articles, and I'm just guessing that magento add some random number after that "url_key", and every time that number is different.
So at this moment probably I need to export all SKU-s and description, and make third column "url_key" to be description+sku. It is important that link have also description.
But I don't know to make sql query to get all sku-s and description.
Also I have two different stores. 1 and 11, for our language and english.
Uh too long post, sorry
probably you are talking about this bug
not sure what you mean by "Load Average more then 6" ?
this is very low load. i think you have 1 or 2 cpu and weak server.