multi-tenant-single-db performance tuning

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

multi-tenant-single-db performance tuning

Sitati Kituyi
We're using the multi-tenant-single-db plugin in our grails (2.0.3) app, which generally works well. However, as our tenant count and the amount of data per tenant has grown, our app's response times have degraded exponentially. We take this to indicate database queries being the bottleneck (even with only 2 users active, the performance is much worse than it was with an emptier database).

We've struggled to find much information on performance tuning when using this plugin, other than the useful advice in the plugin docs about setting up database indexes for the added tenant_id column.

Wondering whether anyone has advice on how to improve performance when using this plugin? Specifics on cacheing would be a huge plus - we intuitively suspect our cache performance is badly affected by "..and tenant_id = x" being appended to each query.

Regards,

Sitati

--
Sitati Kituyi
Reply | Threaded
Open this post in threaded view
|

Re: multi-tenant-single-db performance tuning

sryan
Have you tried a monitoring tool like melody plugin to determine which queries are slow.  You can then use tools like explain to see if indexes will help.  We have added a number of indexes that include the tenant id to speed up our database.  There is little difference in tuning this type of DB from any other db you just have to include the tenant id where appropriate.


Scott Ryan

From: Sitati Kituyi <[hidden email]<mailto:[hidden email]>>
Reply-To: "[hidden email]<mailto:[hidden email]>" <[hidden email]<mailto:[hidden email]>>
Date: Friday, May 9, 2014 at 5:41 AM
To: "[hidden email]<mailto:[hidden email]>" <[hidden email]<mailto:[hidden email]>>
Subject: [grails-user] multi-tenant-single-db performance tuning

We're using the multi-tenant-single-db plugin in our grails (2.0.3) app, which generally works well. However, as our tenant count and the amount of data per tenant has grown, our app's response times have degraded exponentially. We take this to indicate database queries being the bottleneck (even with only 2 users active, the performance is much worse than it was with an emptier database).

We've struggled to find much information on performance tuning when using this plugin, other than the useful advice in the plugin docs about setting up database indexes for the added tenant_id column.

Wondering whether anyone has advice on how to improve performance when using this plugin? Specifics on cacheing would be a huge plus - we intuitively suspect our cache performance is badly affected by "..and tenant_id = x" being appended to each query.

Regards,

Sitati

--
Sitati Kituyi

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: multi-tenant-single-db performance tuning

Kimble
In reply to this post by Sitati Kituyi
As Scott says you have to figure out which queries are slowing you down and run then against the query planner in your database to see how you can optimize them. The plugin is just a convenience for altering sql queries before they're executed. Everything you know and love about database optimization still apply :-) However, if adding indexes is not enough and you have to modify the actually query generated by Hibernate you're pretty much on your own.  

I wrote a blog post about this some time ago,
http://blog.developer-b.com/post/23621627413/multi-tenant-architecture-and-db-indexes
 - Kim A. Betti
Have a nice day!
Reply | Threaded
Open this post in threaded view
|

Re: multi-tenant-single-db performance tuning

Sitati Kituyi
Kim, your blog post is one of the guides we've followed to setting up indices, it's been very helpful.

Scott, your grails-multi-tenant-ehcache plugin is one of the reasons we thought the effect of multitenancy on cache behaviour was a thing to worry about. Our app does have largely predictable queries, which would easily benefit from cacheing, except that when there are multiple users active at the same time, we'd imagine the cache performance takes a massive drop because it is shared.

All of this is conjecture though, we haven't gone into measuring our cache's effectiveness, I asked here in hope that there would be obvious set of optimisations to apply before we fully dive into this. Knowing that there isn't, and that we should just treat this as any other database, is definitely useful information.

Thanks both for the advice.


On 10 May 2014 21:23, Kimble <[hidden email]> wrote:
As Scott says you have to figure out which queries are slowing you down and
run then against the query planner in your database to see how you can
optimize them. The plugin is just a convenience for altering sql queries
before they're executed. Everything you know and love about database
optimization still apply :-) However, if adding indexes is not enough and
you have to modify the actually query generated by Hibernate you're pretty
much on your own.

I wrote a blog post about this some time ago,
http://blog.developer-b.com/post/23621627413/multi-tenant-architecture-and-db-indexes



-----
 - Kim A. Betti
Have a nice day!
--
View this message in context: http://grails.1312388.n4.nabble.com/multi-tenant-single-db-performance-tuning-tp4656748p4656786.html
Sent from the Grails - user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email





--
Sitati Kituyi
Reply | Threaded
Open this post in threaded view
|

Re: multi-tenant-single-db performance tuning

lucastex
Here we're using almost 500 active tenants right now, and going good.
In the past (+- 100 tenants) we got serious performance problems, and found that the database indexes we were creating with the hibernate-hijacker weren't being created in the database.

After analysing all queries and creating indexes, we got our db in 15% CPU (before was 90%)

You might wanna check this out,

[]s,

Lucas 



On Sun, May 11, 2014 at 5:23 PM, Sitati Kituyi <[hidden email]> wrote:
Kim, your blog post is one of the guides we've followed to setting up indices, it's been very helpful.

Scott, your grails-multi-tenant-ehcache plugin is one of the reasons we thought the effect of multitenancy on cache behaviour was a thing to worry about. Our app does have largely predictable queries, which would easily benefit from cacheing, except that when there are multiple users active at the same time, we'd imagine the cache performance takes a massive drop because it is shared.

All of this is conjecture though, we haven't gone into measuring our cache's effectiveness, I asked here in hope that there would be obvious set of optimisations to apply before we fully dive into this. Knowing that there isn't, and that we should just treat this as any other database, is definitely useful information.

Thanks both for the advice.


On 10 May 2014 21:23, Kimble <[hidden email]> wrote:
As Scott says you have to figure out which queries are slowing you down and
run then against the query planner in your database to see how you can
optimize them. The plugin is just a convenience for altering sql queries
before they're executed. Everything you know and love about database
optimization still apply :-) However, if adding indexes is not enough and
you have to modify the actually query generated by Hibernate you're pretty
much on your own.

I wrote a blog post about this some time ago,
http://blog.developer-b.com/post/23621627413/multi-tenant-architecture-and-db-indexes



-----
 - Kim A. Betti
Have a nice day!
--
View this message in context: http://grails.1312388.n4.nabble.com/multi-tenant-single-db-performance-tuning-tp4656748p4656786.html
Sent from the Grails - user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email





--
Sitati Kituyi