Thread

Hey guys,

I am using EE V1.x.  Everytime I change, add, or delete a category or custom field, my database crashes

I am on a very very strong server, but my site is very large (over 10K entries, 250K visits per day).  I assume that everytime I do one of the actions above EE has to do something across the entire database.

Can someone explain to me what exactly EE has to do when one of these actions is taken?  Is it changing every single entry in the database?  If so, does EE2 change this process in any way?

Thanks!

DM

Hi, DM -

EE shouldn’t only be touching the bare minimum to put in the change.

Can you tell us a bit more about what “database crashes” means? Is there an error?

And what version of build of EE 1 are you on? You can find that information in the footer of your control panel.

Thanks!

EE is definitely doing something very intensive, I can see SQL load spike right after making the change.

Right after I make the change my site becomes extremely slow (minutes to load pages) and then starts giving out database errors (cant connect to database).  10-20 minutes later its back to normal.

The issue gets worse as the site gets bigger.

I remember hearing from an admin a while back that EE has to change something on every single entry when a category is added or changed.  Ill try o find the thread.

Here is the thread.  http://ellislab.com/forums/viewthread/102021/

Does adding categories or editing categories run through the same process as adding a custom field?

Does EE 2 follow the same process?

Hi dmacli,

In that thread Pascal recommended turning the site off whilst you are doing this sort of thing due to the heavy processes at play here.

Have you tried that at all to see if it enables things to go more smoothly / quicker?

Thanks,

Mark

It certainly does!  Sometimes I forget though!

I guess my only remaining question is if this process is the same in EE 2.x?  Has it been changed in any way?

Hi dmacli,

I will ask internally on this one for you and update this thread when I get some information for you on that.

My guess would be that it may well still be operating in the same way due to the nature of what is going on there though.

I’ll post back as soon as I hear anything on this though.

Thanks,

Mark

Much appreciated.  I am a little concerned about how my site will scale with EE as we are adding 3000 entries a year.

Hi dmacli,

Could you possibly check with your host to see how much memory MySQL has available to it. It may well be that you are getting these problems due to it not having enough memory available to it in these instances.

Thanks,

Mark

OK Will do

12.5GB - 50% of total.

Our database is a over 4GB though because we have so many relationships between entries.  I heard that makes the database grow in size quickly.

Hi dmacli,

Sorry but when I said memory I meant the RAM available to it not the amount of space that you are allowed to save to.

12.5GB will be the space available to save data to but there will also be an amount of RAM available to the MySQL server too. You will need to check with the host on that though and they may well need to perform some tests to ensure that everything is working as intended here.

Thanks,

Mark

Ill check with my host and get back to you.

Hi Mark. My system has 24GB of available RAM and I’ve dedicated 12.5GB to just MySQL. I left 2GB available to the OS and the remaining 10GB are dedicated to Apache. We have plenty of HDD space so that’s not an issue.

Adding new categories won’t be a problem, DM, as that’s only a new row on a table. Adding new custom fields, however, is indeed a rather database intensive operation. Essentially, the table is locked to add a new column, and your visitors’ DB queries queue up while the table is locked. If you have a relatively high traffic site, the DB server will go down once that lock is cleared and the server is inundated with visitors’ queries.

So when you’re adding new custom fields, it’s recommended that you take your site offline just during the time that you’re actually adding the fields. Once they’re added, you can put your site back online.

On the topic of your relationship fields, that really sounds like a massive number of relationships. Are you using the native Relationship field to do this or is there a third-party add-on involved?