RE: good database design
I agree totaly to what Sujay Koduri writes : http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html My 2 cents.. Before you actually start worrying about the performance tuning of database parameters or hardware required for the DB, you should make sure that you have designed the database properly by taking care of all aspects like normalisation, denormalisation (??). If you don't take care of these logical design aspects in the early stages properly, these things will prove you very costly in the long run. Th easy and recommended way to do it is .Draw an E-R diagram .Do any normalization. .Identify proper datatypes for the table creation. .Identify and add proper indexes. .And now actually you should start worrying abt the DB Tuning and harware requirements. sujay -Original Message- From: Ian Sales (DBA) [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 2:17 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: good database design Tim Hayes wrote: I disagree completely. I prefer to have regard to the statement of requirement, which in this case is a concern over performance. If following conventional design rules creates performance issues, then performance related issues come first when considering design. - personally, I would consider integrity, and then reliability, above performance. But then 80% of any performance hit is in the application code. Design a database that gives you confidence in the data it stores first and foremost. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: good database design
This is what I am also saying. The effects of a bad logical DB design will effect you the most only in the long term. In the earlier stages you always trust your own design and always look for additional h/w resources to improve the performance. But in the long term you will realize that there is something other than adding h/w you have to do. That's when we actually realise the mistakes we have done in logical design phase. suhay -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 3:13 PM To: mysql@lists.mysql.com Subject: Re: good database design > This is an interesting subject area. > > In a data warehousing environment, one tends to adopt table structures such > as snowflake layouts which lead to improved performance. > > Createing a perfect normalised database design may well lead to performance > issues. If this is the case, go bug the database vendors :-) ... they should give us systems that work properly ... >The more joins you have, by far the worse the performance. You may That's a pretty bold statement... > need to consider horizontal or vertical table splits. You may need to > consider replicating certain data in child tables to avoid joins. > > I am not saying you do not need to carry out data analysis and gain a > full and first hand understanding of the data structures. It is just > that when it > comes to online performance, sometimes you have to break the rules. But still: logical first, performance later... If at all. I once joined a team that had a running Oracle database and an application on top of it. We were having performance problems and there was the "2 seconds of max response time" requirement in the contract. We tweaked Oracle (not particularly the fastest beast on the block), we threw hardware at it. Both options worked... for a while. Next, we denormalized, I believe, 2 joins. Yes, it worked on fetching data - the response time was alright. But it complicated our application, the database design and the stored procedures using it... Not a particular pleasant experience. Then again... years later, I realized that the design should have been different (better logical structure) and these problems would have been avoided... Pity we couldn't do that part again... Learned a lot though. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
> This is an interesting subject area. > > In a data warehousing environment, one tends to adopt table structures such > as snowflake layouts which lead to improved performance. > > Createing a perfect normalised database design may well lead to performance > issues. If this is the case, go bug the database vendors :-) ... they should give us systems that work properly ... >The more joins you have, by far the worse the performance. You may That's a pretty bold statement... > need to consider horizontal or vertical table splits. You may need to > consider replicating certain data in child tables to avoid joins. > > I am not saying you do not need to carry out data analysis and gain a full > and first hand understanding of the data structures. It is just that when it > comes to online performance, sometimes you have to break the rules. But still: logical first, performance later... If at all. I once joined a team that had a running Oracle database and an application on top of it. We were having performance problems and there was the "2 seconds of max response time" requirement in the contract. We tweaked Oracle (not particularly the fastest beast on the block), we threw hardware at it. Both options worked... for a while. Next, we denormalized, I believe, 2 joins. Yes, it worked on fetching data - the response time was alright. But it complicated our application, the database design and the stored procedures using it... Not a particular pleasant experience. Then again... years later, I realized that the design should have been different (better logical structure) and these problems would have been avoided... Pity we couldn't do that part again... Learned a lot though. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
Hi, > > Given that the OP did not state that there were any issues with an > > existing website, logical requirements come first. Period. No discussion ;) > > Logical requirements may come first, but may be overruled later by > requirements caused by performance issues or system limitations. Which is what I said :-) > If your logic designed a large type of primary key, you may run into > problems with InnoDB tables. The PK is stored with the data and other > indexes refer to the PK (and not directly to the data as is the case > with MyISAM). So a large PK will increase the table size (data + > indexes) and may thus lead to performance issues when the database does > not fit in memory anymore, or when the buffers,etc. hit the memory > limits on your system. > > A very complex model may lead to queries with more than 31 JOINs, which > is not possible with MySQL without modifying the source and recompiling > it (and even then the limit seems to be 63). Obviously, the MySQL guys should be bugged about this... > > In any case, if this is a read/write application, I would still say that > > logical > > requirements should go first. If this is a read only application, do > > whatever > > you want. > > Logic may come first in the time line, but may be overruled by other > requirements. Finding people who celebrate their birthday today (or this > week) may become a very slow task if you only use a logical data field. > Denormalisation by using extra fields for particular tasks is a > completely logical solution in this case. > > > If this is the customers own server and everything is logical correct but > > there > > are "some" performance problems, I'd say: throw more hardware at it. > > Obviously, this makes sense --after-- tweaks to the database engine caching > > etc etc... Hardware is cheap(ish). If you can control it, do so. > > "Throwing" hardware at it is not always a good solution. You know better > than that. The customer better not find out that the application could > very well run on the original server with a few "tweaks" as you call > them, and that he appears to have lost a lot of money for new hardware > and all the time needed to get the new server running in the > configuration that you suggested... Did you read my paragraph about throwing hardware? No offence, but I stated several times that the logical data requirements should come first, in design. After that, tweak the server, after that, if possible, throw more hardware at it. Now, if this doesn't cut it, you might get into denormalization or other things that make your application run faster... With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
Martijn Tonies wrote: Given that the OP did not state that there were any issues with an existing website, logical requirements come first. Period. No discussion ;) Logical requirements may come first, but may be overruled later by requirements caused by performance issues or system limitations. If your logic designed a large type of primary key, you may run into problems with InnoDB tables. The PK is stored with the data and other indexes refer to the PK (and not directly to the data as is the case with MyISAM). So a large PK will increase the table size (data + indexes) and may thus lead to performance issues when the database does not fit in memory anymore, or when the buffers,etc. hit the memory limits on your system. A very complex model may lead to queries with more than 31 JOINs, which is not possible with MySQL without modifying the source and recompiling it (and even then the limit seems to be 63). In any case, if this is a read/write application, I would still say that logical requirements should go first. If this is a read only application, do whatever you want. Logic may come first in the time line, but may be overruled by other requirements. Finding people who celebrate their birthday today (or this week) may become a very slow task if you only use a logical data field. Denormalisation by using extra fields for particular tasks is a completely logical solution in this case. If this is the customers own server and everything is logical correct but there are "some" performance problems, I'd say: throw more hardware at it. Obviously, this makes sense --after-- tweaks to the database engine caching etc etc... Hardware is cheap(ish). If you can control it, do so. "Throwing" hardware at it is not always a good solution. You know better than that. The customer better not find out that the application could very well run on the original server with a few "tweaks" as you call them, and that he appears to have lost a lot of money for new hardware and all the time needed to get the new server running in the configuration that you suggested... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: good database design
This is an interesting subject area. In a data warehousing environment, one tends to adopt table structures such as snowflake layouts which lead to improved performance. Createing a perfect normalised database design may well lead to performance issues. The more joins you have, by far the worse the performance. You may need to consider horizontal or vertical table splits. You may need to consider replicating certain data in child tables to avoid joins. I am not saying you do not need to carry out data analysis and gain a full and first hand understanding of the data structures. It is just that when it comes to online performance, sometimes you have to break the rules. -Original Message- From: Sujay Koduri [mailto:[EMAIL PROTECTED] Sent: 22 September 2005 09:58 To: Ian Sales (DBA); [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: good database design My 2 cents.. Before you actually start worrying about the performance tuning of database parameters or hardware required for the DB, you should make sure that you have designed the database properly by taking care of all aspects like normalisation, denormalisation (??). If you don't take care of these logical design aspects in the early stages properly, these things will prove you very costly in the long run. Th easy and recommended way to do it is .Draw an E-R diagram .Do any normalization. .Identify proper datatypes for the table creation. .Identify and add proper indexes. .And now actually you should start worrying abt the DB Tuning and harware requirements. sujay -Original Message- From: Ian Sales (DBA) [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 2:17 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: good database design Tim Hayes wrote: >I disagree completely. > >I prefer to have regard to the statement of requirement, which in this >case is a concern over performance. If following conventional design >rules creates performance issues, then performance related issues come >first when considering design. > > > - personally, I would consider integrity, and then reliability, above performance. But then 80% of any performance hit is in the application code. Design a database that gives you confidence in the data it stores first and foremost. - ian -- +---+ | Ian Sales Database Administrator | | | | "All your database are belong to us" | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: good database design
My 2 cents.. Before you actually start worrying about the performance tuning of database parameters or hardware required for the DB, you should make sure that you have designed the database properly by taking care of all aspects like normalisation, denormalisation (??). If you don't take care of these logical design aspects in the early stages properly, these things will prove you very costly in the long run. Th easy and recommended way to do it is .Draw an E-R diagram .Do any normalization. .Identify proper datatypes for the table creation. .Identify and add proper indexes. .And now actually you should start worrying abt the DB Tuning and harware requirements. sujay -Original Message- From: Ian Sales (DBA) [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 2:17 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: good database design Tim Hayes wrote: >I disagree completely. > >I prefer to have regard to the statement of requirement, which in this >case is a concern over performance. If following conventional design >rules creates performance issues, then performance related issues come >first when considering design. > > > - personally, I would consider integrity, and then reliability, above performance. But then 80% of any performance hit is in the application code. Design a database that gives you confidence in the data it stores first and foremost. - ian -- +---+ | Ian Sales Database Administrator | | | | "All your database are belong to us" | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
Tim Hayes wrote: I disagree completely. I prefer to have regard to the statement of requirement, which in this case is a concern over performance. If following conventional design rules creates performance issues, then performance related issues come first when considering design. - personally, I would consider integrity, and then reliability, above performance. But then 80% of any performance hit is in the application code. Design a database that gives you confidence in the data it stores first and foremost. - ian -- +---+ | Ian Sales Database Administrator | | | | "All your database are belong to us" | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
sorry for wrong reply:( And Did you do an analysis to come to this conclusion? Is it really MySQL that's hogging your CPU? Did you analyse what queries were bringing the server down? The senteces above are my big problem. How can I be sure about the quesries making my server down Please, any link, any info, any word is important forme. I can't find the right start point. Thanks OKAN - Original Message - From: "Martijn Tonies" <[EMAIL PROTECTED]> To: Sent: Thursday, September 22, 2005 11:25 AM Subject: Re: good database design Hi, Please reply to the list and not to me personally only. I want to explain my condition. I have a web site that habe 110onlne users at same time. But cpu usage is 2.00/2.00 (p4 3.0ghzHT) I think my database design is horrible because of this high cpu load. Did you do an analysis to come to this conclusion? Is it really MySQL that's hogging your CPU? Did you analyse what queries were bringing the server down? SO I want to learn something about good database design. Can you suggest any thing to me? Read a book, any book, that describes the normal forms. After that, analyse your queries and their plans and see if any indices are needed. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
Hello Tim, > I disagree completely. > > I prefer to have regard to the statement of requirement, which in this case > is a concern over performance. If following conventional design rules > creates performance issues, then performance related issues come first when > considering design. Given that the OP did not state that there were any issues with an existing website, logical requirements come first. Period. No discussion ;) > In times long since gone by (I am showing my age here) client side message > response times were written into contracts. Design had to take into account > performance issues. With very high loaded web-sites as in this case, a > little time spent on lateral thinking can make a big difference and save > costs in the long run, and keeps customers happy. It also depends heavily on the tasks of the application. In any case, if this is a read/write application, I would still say that logical requirements should go first. If this is a read only application, do whatever you want. Given that - usually - data is pretty much the most important thing inside an application, it should be logically correct. Both you and me know that any denormalization or other "performance tweaks" can result into inconsistent data and should be avoided like the plague if possible. If this is the customers own server and everything is logical correct but there are "some" performance problems, I'd say: throw more hardware at it. Obviously, this makes sense --after-- tweaks to the database engine caching etc etc... Hardware is cheap(ish). If you can control it, do so. Denormalization is dangerous. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
Hi, Please reply to the list and not to me personally only. > I want to explain my condition. I have a web site that habe 110onlne users > at same time. But cpu usage is 2.00/2.00 (p4 3.0ghzHT) > I think my database design is horrible because of this high cpu load. Did you do an analysis to come to this conclusion? Is it really MySQL that's hogging your CPU? Did you analyse what queries were bringing the server down? > SO I want to learn something about good database design. Can you suggest any > thing to me? Read a book, any book, that describes the normal forms. After that, analyse your queries and their plans and see if any indices are needed. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: good database design
I disagree completely. I prefer to have regard to the statement of requirement, which in this case is a concern over performance. If following conventional design rules creates performance issues, then performance related issues come first when considering design. In times long since gone by (I am showing my age here) client side message response times were written into contracts. Design had to take into account performance issues. With very high loaded web-sites as in this case, a little time spent on lateral thinking can make a big difference and save costs in the long run, and keeps customers happy. Tim Hayes MYdbPAL - www.it-map.com -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: 22 September 2005 09:02 To: mysql@lists.mysql.com Subject: Re: good database design > I need links about good database design information for high loaded web > sites... A database design should start with the logical data-related requirements, not with performance related issues. IMO, of course. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
> I need links about good database design information for high loaded web > sites... A database design should start with the logical data-related requirements, not with performance related issues. IMO, of course. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
