Backend category tree slow - manage categories

[TeunTuinboon]

Recently i have imported a good number of categories. But now whenever i go to the manage categories in the backend panel, it loads the page extremely slow.

It takes about 20-30 seconds to load the manage categories page, and about the same time when clicking on a desired category for it to load it's data or adding a new category.

Tried so far:

• Reindex all the data
• Made sure the server is well configured(memory,caching,ini,msql etc.)
• Tried the compiler
• Used the magento repair database tool to see if it gave any errors(none)
• Tried the same setup on a highend localhost server (without any plugins/extensions)
• Logs are clean

But the problem persists.

Some stats:
- About 250.000 different products
- About 1.2 million product-category relations
- About 200.000 different categories
- On a reliable and fast VPS server with a decent host (no cheap out of the box solution)
- Running on Nginx
- Flat catalog/products both enabled

Here's an output of magento's profiler on the manage categories page:

I'm not a pro so the only thing what gets my attention is the time and cnt values of mage, tree.phtml and catalog_Category_tree_ismovable. But i'm not sure what to do with this info.

If anyone knows an idea, can point me in the right direction or anything else helpful i would be very grateful!

If any information is missing from this post i'll be glad to add it if asked for.

[JLHC]

When you go to any of those pages, how's the CPU and RAM usage of the VPS? Are they maxed out or are they within acceptable range? 

What database do you use? MySQL? Have you tried some high performance replacement like Percona or MariaDB instead? 

[TeunTuinboon]

Hi,

I have been told by my hosting company that not even 20% is being utilized from our available CPU and RAM.

It is running on MySQL, i haven't tried any other yet. Is it possible i'd notice a huge difference when switching to another?

The reason i doubt it would make a big difference (and correct me if the following doesn't make magento sense) is because the product tables in our database are much larger combined than the category tables. The manage products page loads absolutely fine.

Also, when clicking on a product in the manage products page, you can then click on the tab categories where you can see in which category the product is listed. This tree does load fast (i guess because it loads less category information e.g. attributes and what not than the slow tree)

If you have experience with these kind of performance issues (strictly on the categories) with this amount of rows and you say it will make a big difference i will ofcourse try it out. As far as i've read MariaDB is pretty easy to implement.