tag:blogger.com,1999:blog-16959946.post4328180081057332290..comments2023-12-09T16:44:47.897+01:00Comments on The Data Charmer: Normalization and smokingGiuseppe Maxiahttp://www.blogger.com/profile/15801583338057324813noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-16959946.post-85654185430945137152009-03-16T08:28:00.000+01:002009-03-16T08:28:00.000+01:00We now have about 56 million tables on WordPress.c...We now have about 56 million tables on WordPress.com, adding 80k daily :)Unknownhttps://www.blogger.com/profile/13844194282502821497noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-67691146788283018702009-03-13T10:40:00.000+01:002009-03-13T10:40:00.000+01:00That was my impression too. The data was distribut...That was my impression too. The data was distributed in a set of 10 tables per user. Instead of using a "userid" and rows in 10 unique tables for all the users. So imagine 4000 users x 10 tables each, with the same data structure instead of 10 tables to handle them all.<BR/><BR/>@gmax + Salle, I have to admit that you handled the question very professionally and without any hint of making fun of the user.<BR/>Ok many people involved in db development, have an idea of how to structure a db; others just had a bad start. <BR/><BR/>CheersDay after day we burn it all awayhttps://www.blogger.com/profile/13670100743216334772noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-18469098598022274232009-03-12T23:06:00.000+01:002009-03-12T23:06:00.000+01:00@Mayavi,Size of table is not important. The reason...@Mayavi,<BR/>Size of table is not important. The reason for creating separate tables is just a common beginner mistake.<BR/><BR/>@Ronald,<BR/>True, you can improve performance by fiddling with file limits, but 40,000 tables, which could become 80,000 in one more year, is not a sane proposition.<BR/><BR/>GiuseppeGiuseppe Maxiahttps://www.blogger.com/profile/15801583338057324813noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-17887540045339827362009-03-12T22:11:00.000+01:002009-03-12T22:11:00.000+01:00Alas, there are a number of projects that undertak...Alas, there are a number of projects that undertake this process. WordPress is one of note.<BR/><BR/>There are some performance tips you can use when your schema has a large number of tables.<BR/><BR/>Setting a large table_cache (monitor with Open_tables and Opened_tables), setting a large open_files_limit, realizing the OS may complain and then you have to look at ulimit for the mysql user.<BR/><BR/>Ensuring your tables are in different schemas will address the first issues of OS inode issues with simply too many files in a directly, however with too many schemas you may see issues simply at the OS level.<BR/><BR/>The advice in this post is an ideal approach however in this industry, we have to accept that optimizing design is not always possible and then we look for any improvements we can. <BR/><BR/><BR/><BR/>Check out our "MySQL Essentials" training course at <A HREF="http://42sql.com" REL="nofollow">42SQL Education</A>.Ronald Bradfordhttps://www.blogger.com/profile/16170615042077930093noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-12799176590287154422009-03-12T20:27:00.000+01:002009-03-12T20:27:00.000+01:00true .... and nice analogy .. what do people think...true .... and nice analogy .. <BR/><BR/>what do people think when they come up with these kinda granularity of data with separate table for each user ? <BR/><BR/>Did he mention what was the size of his each table ? and what was the reason behind creating separate tables for each user ? <BR/><BR/>Ranjeet Walunj<BR/><A HREF="http://mayavi.info/" REL="nofollow">mayavi.info</A>Mayavihttps://www.blogger.com/profile/05921452388865853449noreply@blogger.com