GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Performance of a database with a lot of tables

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Performance of a database with a lot of tables

    Hello,

    I am working on hosting solution for a popular open source script, where users can sign up and get an own forum. We use InnoDB, and each user has an own set of tables (around 30); there are currently around 500 users registered. Despite the large count of tables, database is small, around 50 MB.

    Server is dual Xeon with 4GB of RAM and MySQL uses all memory, available on the server, so there's even nothing left for Apache.

    As the user base is growing, what steps would you suggest me to do, in order to keep good database performance and make it more efficient on RAM usage? The former is more important.

    ------------------------
    Olexandr Melnyk,
    http://omelnyk.net/

  • #2
    Olexandr,

    First - I think you configured MySQL to consume too much memory this is why it takes all memory available. check your my.cnf

    Regarding your structure - if you will get very large amount of users you will get into trouble - Innodb does not like too many tables. Thousands are OK but if it starts to be tens-hundreds of thousands you're in trouble.

    It is best to charge software so it can share tables.

    Comment


    • #3
      Hello Peter, and thanks for the prompt reply.

      Quote:

      First - I think you configured MySQL to consume too much memory this is why it takes all memory available. check your my.cnf


      There was some misunderstanding between and the server admin, MySQL is actually taking 700 MB in memory; and looks like the problem wasn't it. Sorry for taking your take with that.

      Quote:

      Regarding your structure - if you will get very large amount of users you will get into trouble - Innodb does not like too many tables. Thousands are OK but if it starts to be tens-hundreds of thousands you're in trouble.


      Around hundred users sign up for the service daily, so as I understand this can become an issue soon. Wouldn't splitting database into several databases, or over several servers help in this case?

      Quote:

      It is best to charge software so it can share tables.


      Well, the software is quite huge, so that's not really an option. I understand the approach I use is "incorrect" from the database theory standpoint, and much from the performance but it's much harder to do it using a single table set. Are there any other options, besides switching to a shared [for all users] tables model, which would increase performance?

      Comment


      • #4
        You obviously can split it to many MySQL servers, the question is rather efficiency at which you can handle it. Ie having 10.000 or 100.000 users per server can be a big difference

        Two problems you should watch out id Innodb internal table dictionary which is never purged - so all open tables stay in memory forever and estimating stats which is done on table open first time after MySQL restart.

        Comment


        • #5
          Quote:

          You obviously can split it to many MySQL servers, the question is rather efficiency at which you can handle it. Ie having 10.000 or 100.000 users per server can be a big difference


          Yeah

          Quote:

          Two problems you should watch out id Innodb internal table dictionary which is never purged - so all open tables stay in memory forever and estimating stats which is done on table open first time after MySQL restart.


          Thanks for the tips. Does the first one mean that this can become a memory issue? I've been reading your blog posts, especially about open table scalability (great posts BTW!), is there anything else but the points described there I should pay attention to?

          Comment


          • #6
            There might be. I wrote about Innodb issues with multiple tables few times.

            But anyway I listed the points here.

            yes it can become memory issue up to the point it will take more memory than you have at all - ie 1.000.000 of tables 4K each take 4GB of RAM which is serious waste.

            Comment


            • #7
              Quote:

              it can become memory issue up to the point it will take more memory than you have at all - ie 1.000.000 of tables 4K each take 4GB of RAM which is serious waste.


              An idea, I came up with recently: what about setting up own views for each user with "on update" and "on delete" handlers, which would perform like they are separate tables, but make the requested changes / fetch data from a single table using a key field? The problem that comes up is that MySQL doesn't allow to set the data change/delete handlers for views. Of course, we can use empty tables with "before" triggers instead of using views, but then it wouldn't make much sense. Is there a workaround for this or any other implementation of this approach?

              Comment


              • #8
                Good idea.

                The views should be updatable in such case - if you only restrict things by user_id. What exactly fails ?

                Comment


                • #9
                  Quote:

                  The views should be updatable in such case - if you only restrict things by user_id. What exactly fails ?

                  I don't know how to define "on delete/update/insert" and "on change" hadlers for views. Or can "before insert/change/delete" triggers be used for views as well?

                  Comment


                  • #10
                    If you're creating a view in a way MySQL will be able to find a view in original table to perform update, it should be updatable without extra steps.

                    As you mention you simply filter by user_id it should be the case, so I'm curious what exactly does not work.

                    Comment


                    • #11
                      Quote:

                      As you mention you simply filter by user_id it should be the case, so I'm curious what exactly does not work.

                      Not that it doesn't work, but I do not know how to do it technically. How can I set an additional field to a fixed value when an insert/update/delete query is being issued?

                      Comment


                      • #12
                        You update the view and it is view which sets it to the fixed value for you and finds proper row in base table.

                        Comment


                        • #13
                          Quote:

                          You update the view and it is view which sets it to the fixed value for you and finds proper row in base table.


                          Okay, great. Wasn't sure whether MySQL will do it automatically or not.

                          Talking more closely to how application works right now, would it give any benefit to split up tables into several databases?

                          Comment


                          • #14
                            Sure. I would not hold tens of thousands of tables in the same database. It is at least inconvenient and some commands would run too long.

                            Comment


                            • #15
                              It sounds to me that each of your users has a copy of the same set of tables.

                              Surely the best way to manage this is to just have one of each table and add a 'user id' field. This would reduce number of tables, and I am sure it would give you just as good performance if properly indexed.

                              Comment

                              Working...
                              X