Performance Issue with Big Dataset

Matias,

Wait wait please! In my case, if I comment the lines to query for hidden and public (as suggested by Sack), the time almost doubles instead of being reduced.

this 2 lines does not cover all conditions a query will be created. As you can see 9 comments above (changing the sort order), your query still used the publish and hidden conditions (screen shot) even when you commented the 2 lines already.

It also depends on other factors (like the section settings discussed above).

You have to analyze always the slow query to find out.

Also, if your setup needs the "publish" function, you don't comment it out as well, since it will deliver wrong results.

Commenting this lines out will always increase the query speed (sometimes very little, sometimes dramatically), it always depends on other factors.

Therefore the implementation of an option in the settings (which will not only affect the 2 lines, but ALL queries involving the condition) is the right thing to do. Then user can decide, if they need the feature or not.

Wait wait please! In my case, if I comment the lines to query for hidden and public (as suggested by Sack), the time almost doubles instead of being reduced.

so I will remove the debug for a while.

If you want to use the debug, but the public should not see it, then simply set a user group for it. You can set it in the debug PLUGIN settings.

Btw, i also noticed earlier a high memory consumption (18MB) and slow time in your search module. You might want to look into it as well.

Good luck for your launch ... :D

So what is your final speed result Matias?

So what is your final speed result Matias?

If he uses the proposed settings (Section settings to public and hidden and published removed) the SQL query time for the list is down to around ~250ms

The preparation of the possible ID's still takes quite some memory (18MB) and also around 500ms.

I believe there might be some room for improvement from Cobalt's side.

There is another area to look into - the search module -- it takes 350ms and consumes another 18MB.

But that is sounds great anyway for 300k articles DB. Isn't it?

But that is sounds great anyway for 300k articles DB. Isn't it?

Agree ... :D

But you should not stop here, there is still a lot to do to accommodate bigger data sets. But you can be proud of where you are already ...

And most important, this topic /use case made you finally approve the additional section settings. Hope you apply them everywhere they are used.... :D

Added to next update.

Hey guys! Hey Sergey!

How have you been?

Unfortunately, my site is struggling again to show results among a large dataset of records. The dataset hasn´t changed that much from the last time we spoke (310K records), but since now we're on production, I think that the number of visits is starting to affect the performance. For example, sometimes it takes up to 1 minute to produce search results when filters are used. Category listings may take up to 35 seconds.

The site is now on production on www.clickbeauty.com. Visits are still low from my point of view (less than 10 sessions per day).

I had asked the client to purchase a good VPS with 2 cores and 4GM of memory, but I'm starting to think that a dedicated server would have been more suitable. However, I'm a bit doubtful if a dedicated server is going to be enough and even worse what would happen if the site really increase the number of visitors (I would need a super server but I don't think the client will be willing to pay for this at a such an early phase of the project).

I've given lots of thoughts on how to increase performance, but all I can think is that we need some kind of changes on the data model. For example, with 300K records on the Record table, I have more than 1M records on the Record_Values table and that's where some filter queries are executed.

I've debugged the system using Joomla 3 debug info and the largest query is considerable reduced for this kind of dataset.

So... after all, is there anything we can do to make Cobalt more in compliance with large data sets? I thinks this case can give us a tremendous amount of thoughtful ideas because I'm a real fan of Cobalt, but I'm reaching a point where I feel that the data model is not quite enough for this. You already know that I've been happily and compulsory using it for more than 4 years in most of my projects.

Please let me know if you need access to the server to analyze this.

Thanks, Matias

Lets do this. You pack you site with Akeeba Backup. And give it to me. Upload somewhere I can download it from. Then I'll do experent. I'll create web server on AWS start with small and then increasing. I'll run apache benchmarks to see perfomance on concurrant access.

So, my first step will be to find out is compute power and memmory can improve perfomance.

Next step, I'll try to enable SQL caching and configure memmory volume as cache destination for most quick access.

And next step I'll analyze queries, and try to optimize them or DB. I believe there is yet a room for little perfomance improvement. 1,000,000 should not be a problem I think.

Also send me your public SSH key so I could add it to server and you could also access it.

Hey @Mat sorry for asking unrelated question, but I see a nice live search on your "write review" section, is that custom made or are you using a specific module in there ?

Rgds

Hi Sergey,

Perfect man, I really like your plan!

I need to get the approval from the client because the data is licensed from a 3rd party data supplier.

I'll start doing the backup and I'll try to send you the package by tomorrow, if not today.

Keeping you posted. I'm really excited about this!

Thanks, Matias

Hey Jimie,

Nice to meet you, thanks for your question!

The "Write a review" is kind of a custom feature but built with Cobalt, let me explain:

We create a type called "Review" and a section for it. The type is related to businesses (another content type) using the "Custom type comments" for types, ie, Reviews are comments for businesses.

As you can see, the feature is available for public users, although users need to login in order to submit. We control this by using custom development on the type's form and on a custom field (which is also responsible for resolve the ajax requests used to list the businesses).

If the user is not logged in, we first send him/her to the registration/login page and after he or she logs in, we redirect the user to the review form: We retrieve all the fields input from the session (so that user doesn't have to complete everything again) and finally he or she can submit the review. This was achieved using CB Auto Actions from CommunityBuilder (although it could have been done developing a Joomla custom plugin) together with a jQuery cookie plugin. There are also more custom rules, like for example not letting other businesses submit the review and reviews can also be answered by businesses (in case they want to thank or defend theirselves from the review).

What I mean is that we've used all best practices to extend this feature without doing any hacking to Joomla or Cobalt, we just developed this feature in a way that is compliant with all the available features offered as it is. However, we didn't make it "modular", which I think was because our lack of time during the project (I know, I know...nothing weird, right? LOL), though it shouldn't be pretty hard to do so.

Let me know if you have any question, I'm always eager to talk about these things!

Thanks, Matias

I will not sare this data and I can sign agreement if it is needed. But I relly harly see any other option than that for effective optimization process.

Hi Sergey!

Thanks for your response. Don't worry, I've managed to have an agreement with my client. She asked me to remove most customizations and randomize business titles so that I can share this with you. I've already done this, re-test it and still is working bad, so I think we can use what I have for optimizing Cobalt :)

Where should I send the link to download? I've seen that I no longer have the possibility to hide some part of the text in this editor, right?

Thanks again, Matias

You can create private comment.

Hi Sergey,

Please let me know whether you have received the package and if you need anything from my side.

Thanks! Matias

Hello, have you enabled notifications? I have noticed that many entries in the notifications table cobalt slowed down. I had about 8000 entries and the page took about 2 seconds to load. Then I simply deleted all entries with phpMyAdmin, then the loadtime was about 800ms.

matcorrao Please let me know whether you have received the package and if you need anything from my side.

I am downloading it right now. Will let you know beginning of the next week.

Matias, Cobalt in your pack is very old. Can I update it? Or will you be able to update it or you have customizations that you will lose?

Hi Sergey,

Go ahead and update it, no problem. No significant customizations will be lost, so don't worry.

Thanks! Matias

Lord_Helmchen Hello, have you enabled notifications? I have noticed that many entries in the notifications table cobalt slowed down. I had about 8000 entries and the page took about 2 seconds to load. Then I simply deleted all entries with phpMyAdmin, then the loadtime was about 800ms.

Hey, thanks for your contribution! Notifications are enabled, but this problems comes since the time we had no notifications at all. Besides that, it's good to know that this doing that helps improving Cobalt, thanks for the tip.

Best! Matias