| Performance of a database with a lot of tables [message #696] |
Fri, 09 February 2007 03:58  |
 Kynlem
Messages: 11
Registered: February 2007
Location: Ukraine |
Junior Member |
|
|
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/
|
|
|
| Re: Performance of a database with a lot of tables [message #699 is a reply to message #696 ] |
Fri, 09 February 2007 08:08  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
| Re: Performance of a database with a lot of tables [message #700 is a reply to message #696 ] |
Fri, 09 February 2007 08:52 |
Kynlem
Messages: 11
Registered: February 2007
Location: Ukraine |
Junior Member |
|
|
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?
|
|
|
| Re: Performance of a database with a lot of tables [message #701 is a reply to message #700 ] |
Fri, 09 February 2007 10:05 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
| Re: Performance of a database with a lot of tables [message #704 is a reply to message #701 ] |
Fri, 09 February 2007 13:20 |
Kynlem
Messages: 11
Registered: February 2007
Location: Ukraine |
Junior Member |
|
|
| 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?
[Updated on: Fri, 09 February 2007 13:20]
|
|
|
| Re: Performance of a database with a lot of tables [message #707 is a reply to message #704 ] |
Fri, 09 February 2007 16:54 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
|
|
|
| Re: Performance of a database with a lot of tables [message #715 is a reply to message #714 ] |
Sun, 11 February 2007 11:52 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
|
|
| Re: Performance of a database with a lot of tables [message #730 is a reply to message #696 ] |
Fri, 16 February 2007 00:33 |
Kynlem
Messages: 11
Registered: February 2007
Location: Ukraine |
Junior Member |
|
|
| 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?
[Updated on: Fri, 16 February 2007 00:34]
|
|
|
|
|
| Re: Performance of a database with a lot of tables [message #809 is a reply to message #696 ] |
Mon, 26 February 2007 02:37 |
Kynlem
Messages: 11
Registered: February 2007
Location: Ukraine |
Junior Member |
|
|
Thanks for the replies.
Continuing talking about using views, would it produce a lot of overhead in comparison with adding the proper condition to queries in the application, like:
select post_id,
post_title
from posts
where post_id = 5
and user_id = 7
?
Are there any side effects I should consider when using views?
[Updated on: Mon, 26 February 2007 02:40]
|
|
|
|
| Re: Performance of a database with a lot of tables [message #812 is a reply to message #696 ] |
Mon, 26 February 2007 10:39 |
Kynlem
Messages: 11
Registered: February 2007
Location: Ukraine |
Junior Member |
|
|
|
Is there a way to make MySQL set user_id automatically when I perform an insert to username_tablename (it is a view on tablename)?
|
|
|
Members
Search
Help
Register
Login
Home
