First Task: Indexer
We need someone to assess and take a look at our Magento category structure and figure out a way to optimize the indexing process. In other words, we need a way to speed it up immensely and quickly.
Right now it takes over 12 hours alone on "Category Products" (Indexed category/products association process 6) in Magento 184.108.40.206 installation. 3+ hours for URL Rewrites index. All other indexes takes minutes or seconds.
We have a design flaw in which we have more categories than products. (20,000 vs 10,000). Which obviously is the culprit in the long indexing. Need to find solution to either indexing time (preferred) or re-structure with minimal down time.
We also currently have 8 "Store Views" that are being used as "White Label" for our affiliates. They all share same products, etc. Which also contributes to long indexing times. It is necessary for us to add additional store views very soon. We think correct SEO URL settings could possibly alleviate the indexing time for this.
Our server stats:
Quad Core Xeon® X3440 Processor
4 x [url removed, login to view] GHz Hyperthreaded Cores
8 MB L3 Cache, 1333 MHz FSB
8 GB 1333MHz DDR3 Memory
Recently we had some database errors and indexer for "Category Products" errors out as well, which need to be fixed. We're getting a lot of errors when running [url removed, login to view] -- reindex in SSH command line. Including timeout issues, etc. I tried re-indexing and changing time out settings but because this index takes a long time to complete it times out we're assuming. (Attached error log)
Second task: Product Updates
We're also getting possibly related error when mass updating product attribute via Magento admin. We only need to mass update "visibility" attribute for products in accordance to store view (Not visible individually vs "Catalog, Search"). We have tried altering database to reflect this (multiple moethods) but it does not show up in the front end and will technically "break" our website, causing us to restore our database.
We were able to update up to 1400 product attribute at a time via Magento admin, but it would take 2 hours or so. If it gets interrupted, the site will break. We need to know if it's possbile to do this safely that will not cause [url removed, login to view] to time out and ultimately break the visibility of our products in the frontend. So if it's possible to update via SSH command line to safely run without time out, or (though not preferred due to experiences) to do it via database but make sure it completes and shows up properly on frontend. We have over 10,000 products and we need to be able to do this in mass without it breaking.
Please note it's a requirement to try these methods on a test server remotely or locally, as our site is live right now, we want to avoid unncessary downtimes.
Bidder must be OK and Legal to work with adult content and have great communication. We're currently in PST (Pacific time UTC-08:00), and need constant updates and communication with the freelancer.