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.

  • 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 '15 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? – Kay Int Veen Mar 19 '15 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. – Kay Int Veen Mar 19 '15 at 11:40
  • yes....you need check here.I suggest donot use category url to product url – Amit Bera Mar 19 '15 at 11:44
  • @AmitBera what do you mean with "i suggest donnot use category rul to product url" – Kay Int Veen Mar 19 '15 at 11:54
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.

  • 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. – Kay Int Veen Mar 19 '15 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. – musicliftsme Mar 19 '15 at 15:51
  • i was planning on running a complete reindex this night – Kay Int Veen Mar 19 '15 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. – musicliftsme Mar 19 '15 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. – Kay Int Veen Mar 19 '15 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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