0

I just migrated a store with around 5.000 products, i always found the store a bit slow (around 4/5s per page), this was a year back 2s. so it slowly declined.

I now installed a complete new server with loads of ram, ssd disks and so on. this did not improve a single. this. This site is the only site running on this server.

When i run the top command, i see the following

p 2 days, 20:39,  2 users,  load atotop - 11:52:44 utop - 11:53:09 up 2 days, 20:40,  2 users,  load average: 1.80, 1.91, 1.99
Tasks: 242 total,   4 running, 237 sleeping,   0 stopped,   1 zombie
Cpu(s): 55.3%us,  5.1%sy,  0.0%ni, 37.9%id,  1.6%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  32695208k total, 31576368k used,  1118840k free,   359384k buffers
Swap: 16777084k total,     8492k used, 16768592k free, 29262460k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
10276 mysql     20   0 1332m  66m 6984 S 233.7  0.2  49:16.61 mysqld

This seems to be the real issue of the lack of speed. even with things like redis no improvement off all, so finding cache solutions is the wrong way i suspect.

it definitely needs to be database health kind of thing. i mean i have store running on poorer servers with 2 to 8 times more products without issues.

I have no clue where to look for anymore.

7
  • Check your site mysql query for each page and you need debug all case. Also need to use memcache or apc and check table keys
    – Amit Bera
    Mar 19, 2015 at 11:04
  • Thankyou @AmitBera but using caching while there is a fundamental issue isent the way to go right?... how would you debug each query on the page, is there a tool for or a magento function? Mar 19, 2015 at 11:13
  • What i did is, i logged all queries by modifying the file var/Varian/Db/Adapter/Pdo/Mysql.php and noticed that all queries where url_rewrite is takes more than 2/3 sec. Mar 19, 2015 at 11:40
  • yes....you need check here.I suggest donot use category url to product url
    – Amit Bera
    Mar 19, 2015 at 11:44
  • @AmitBera what do you mean with "i suggest donnot use category rul to product url" Mar 19, 2015 at 11:54

1 Answer 1

1

From what you're written, I think it's plausible to suspect database performance issue, which can be caused by a variety of things. Since you said you upgraded hardware with no improvement, I suspect (with very limited information given), there's a lot of data or processes that is clogging your store. Here are some things to look at.

  1. Check out your cron jobs in table cron_schedule. Are they running properly? Are there any that are being missed? Are there any that are never finishing? What are some of the longest running cron jobs?

  2. Check the URL rewrites in the table core_url_rewrite (assuming you're on CE). How many records are there? Are there millions of records? Red alert if you have hundreds of thousands of records for 5000 products.

  3. Check other tables that have a large number of records.

  4. How long does your full re-indexing take?

These are some of the places you can start looking. Update us after checking these.

6
  • catalog_product_entity_text - catalog_product_entity_varchar are both around 90MB normally core_url_reqrite was 150MB+ but ran a reindex for like 2 hours and was still not finished. So complete reindex would take ages Further what do you want to know more 32GB Memory, running php on fast cgi, no other sites running at this moment. Mar 19, 2015 at 15:46
  • Re-indexing not finished after two hours (on live server?)? That does not sound right. Let it finish first. During off-peak, run each of the indices separately, and make sure it's the URL rewrite re-reindexing is taking long.
    – laketuna
    Mar 19, 2015 at 15:51
  • i was planning on running a complete reindex this night Mar 19, 2015 at 15:54
  • Yeah do that in the command line. If you have CE 1.9+, it will show you how long each took when you go --reindexall.
    – laketuna
    Mar 19, 2015 at 15:57
  • The strangest thing is, and also a reason i think why it takes so long is that mysqld is continuously 100%+ while there are only 2 or 3 concurrent visitors on the site. Mar 19, 2015 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.