Advanced MySQL concept questions

[bipper]

I am looking at creating a 30K hit a day web pages that is intensely database driven. I have looked into MySQL clusters, and was wondering if anyone here had any resources and/or experence based advice on working with these technologies.

I am also wondering if pulling a large amount of data from one tables vs many tables matters for speed. Normally, DB wise, I am a table whore as I am a strict OOP minded fellow. Does MySQL handle this approach just as fast, or should I tweak down the number of tables I make? Any other speed affiliated tips?

I think that is all for now I have been whoring up the MySQL page for about a long while, and have yet to see a decent responce to the above questions.


your friendly neighborhood GOE.

[Dr Unne]

Pretty sure a single flat non-normalized table will nearly always be faster (in terms of accessing the DB) than doing a bunch of joins. But it's also quite a bit harder to actually do anything with the data. It's the old tradeoff between computer speed and ease of use.

If I'm worried about speed, I tend to fetch as much data as possible out of the SQL server all in one big dump and then loop over it in whatever script is running the frontend, rather than doing filtering / data manipulation in the queries themselves. I may be entirely wrong as to whether that increases speed or not. It certainly makes programming eaiser though.

It's hard to answer without knowing more about what you're doing. Have you ever used EXPLAIN? http://dev.mysql.com/doc/refman/5.0/en/explain.html It'll tell you if you're using indexes properly. Indexes can help you a lot if you use them properly.

[bipper]

Ah I am still in conception at the moment. Moving on from design. I have chosen a massively multi table approach. For instance, page color and themeing is all take from the page database, while other tables will hold the cms content, aother for personalised content, another for user information etc. There are 14 tables in all thus far - 5 which are regularly queried and linked in groups of 2 - 3. Fairly straight forward. I have to admit, I have never used the EXPLAIN function, though I am very compitant in DB design (I think). Now to check my previous tables with this newfound tool!

My biggest challenge, I think, is going to be the cluster. *gets pumped*

[Samuraid]

30k hits a day isn't that much. I wouldn't expect you would need a cluster for that, just a fair amount of RAM (2GB+) and reliable and fast HDDs. (RAID should give you the performance and redundancy you need)

At most, you should have a single dedicated web server and a single dedicated MySQL server, but 30k hits shouldn't warrant much more than that.

Anyway, as Unne said, EXPLAIN works wonders, especially when working with indexing, joins, and lots of relational constraints. It can mean as much as a 1000% speed increase (we had such an increase at EoFF by using explain to tell us what to index).

You should also consider which storage engine to use as it can affect performance. You can use MyISAM as your storage engine and get bleeding fast performance and Fulltext indexing with absolutely no relational features or transactions; or you can use InnoDB and get slightly less performance but with all the relations aspects, constraints, and transaction support a good database should have.

Anyway, that's my 2 cents for now.

[bipper]

Just for some light on the matter; the page is intrabase mission critical information. Thus the Cluster. Redundancy not performance .

BTW, thank you both very much for your replies!

[Samuraid]

Just for some light on the matter; the page is intrabase mission critical information. Thus the Cluster. Redundancy not performance .

BTW, thank you both very much for your replies!

Ah, that's a whole different ballgame. Cluster away then.

I haven't ever set up a MySQL cluster so I cannot offer any advice on that, unfortunately.

[Dr Unne]

I have successfully pretended to know what I'm talking about. Excellent. I definitely know what a intrathinger mission critical whatsit is though. I made two of those last week. Took me hours.

[bipper]

I have successfully pretended to know what I'm talking about. Excellent. I definitely know what a intrathinger mission critical whatsit is though. I made two of those last week. Took me hours.

hah An intranet database driven page. I was kinda rushed - sorry bout ha unne When I type on the laptop they supply me, the track pad causes me issues sometimes I will translate: I am retarded.

Beter:

Bip

[Dr Unne]

I was actually making fun of my own lack of mysql knowledge. Darn me and my pretending to know what I'm talking about skills.

[Samuraid]

I just saw the "mission critical" part, and figured that was as good a reason as any for redundancy. I wasn't sure what the "intrabase" referred to either.