PHP+MySQL


#1

hello,
i am trying to build a dynamic PHP+MySQL web site. I plan to make heavy load to database engine. I would like to have about 200 mp3 melodies catalogue with genre and collection classification (that’s just to give you an idea what i pretend to do).

i would very appresiate if someone of current DreamHost customers who has similar sites could post their URLs so i’ll get an idea about the performance of the PHP+MySQL engine. i also would very appresiate your recomendations on package selection.

i’ll check this forum in a few days, or you could write me directly to oleg.titov@aleph-tec.com .

Kind regards,

Oleg Titov


#2

Hi Oleg -

[quote]I would like to have about 200 mp3 melodies catalogue with
genre and collection classification (that’s just to give you an idea
what i pretend to do).

[/quote]

This isn’t really an answer to your question, but just a tip: Try to resist the urge to store the actual MP3s in the database. While it’s possible to do with BLOB column types, it can make things rather hard to code and can slow down your database access.

Instead, store the files in the file system itself and store paths to them in the database.

As an aside, what aspect of this site do you think will be so heavy? Will it be accessed by many people, or were you planning to do something specific with it that seems rather CPU intensive? If you can give us an idea of what you’re planning on, I’ll be happy to give you some ideas on optimizing database performance (which may help, no matter which host you decide on).

  • Jeff @ DreamHost
  • DH Discussion Forum Admin

#3

Dear Jeff,
thank you for your prompt answer.

[quote]This isn’t really an answer to your question, but just a tip: >Try to resist the urge to store the actual MP3s in the database. While it’s possible to do with BLOB column types, it can make things rather hard to code and can slow down your database access.

Instead, store the files in the file system itself and store paths to them in the database.
[/quote]

Your tip is already implemented :wink:

[quote]As an aside, what aspect of this site do you think will be so heavy? Will it be accessed by many people, or were you planning to do something specific with it that seems rather CPU intensive? If you can give us an idea of what you’re planning on, I’ll be happy to give you some ideas on optimizing database performance (which may help, no matter which host you decide on).

[/quote]

I have published this site at http://www.tucantucan.com and responce time is about 30 sec for main page, that is completle unacceptable for me.

I do have made several tests on local network to estimate my responce times. Even Pentium-100 was performing all tasks at blazing speed. At this point I am unable to answer is that site is CPU intensive or is it hosted on overloaded PHP+MySQL server.

I expect to have 300 visitors per day, with avarage 50-100 MySQL queries per visitor.

Jeff, I would very appreciate if you visit mentioned site to have an idea what I am up to. It would be my pleasure to provide any additional information.

Kind regards,

Oleg Titov


#4

[quote]Jeff, I would very appreciate if you visit mentioned site to have
an idea what I am up to. It would be my pleasure to provide any
additional information.

[/quote]

No problem!

I took a quick look - it does seem to be rather slow, so I can definitely see where your concern is. This would seem to indicate it’s either a problem with the script, server or database, or that any network problem relating to the site is pretty close to the host.

I have a few thoughts in mind as to what the problem may be, but the first thing you need to do is to isolate whether it’s the database access and/or script that is causing the slow-down, something to do with the HTML structure of the page, or the server/network infrastructure.

Try this:

  1. Open up the main page in a web browser. Use the “Save As…” function in your browser to save a local copy. Name it something like “speedtest.html”.

  2. Upload it to the root directory of your site.

  3. With an empty cache, visit both “http://www.tucantucan.com/” and “http://www.tucantucan.com/speedtest.html”. Make sure that the latter loads pretty much identically

  4. No matter what, the static HTML page should load at least a little faster. Using this test, you can isolate the “slow MySQL” and “slow PHP” variables in the equation. Time both and see how dramatic the difference is.

  5. Run the test a few times, with a clean/empty browser cache, and make sure that the results are consistent.

  6. Let us know what you find. :>

  • Jeff @ DreamHost
  • DH Discussion Forum Admin

#5

[quote]
I have published this site at http://www.tucantucan.com and responce time is about 30 sec for main page, that is completle unacceptable for me.[/quote]
I just visited the site and didn’t have to wait at all.


#6

I’ve been running a completely PHP & MySQL generated site at DreamHost for a while now, and I’ve had no problems with respect to page load times. I get between 100,000 - 300,000 page views per month, and have yet to come anywhere close to my 5 million “Conueries” quota per month.

Key to any database application is proper 3rd or even 4th form normalization, make sure you define your database tables and relationships well, and use indexes where you should.

Other than that, be sure to only make a single database connection per page and re-use it for all of your subsequent queries.

If you’re curious, you can check out my site at http://www.beacontender.com and see for yourself (shameless plug). :slight_smile:

–Brad Barkhouse
Be a Contender!
http://www.beacontender.com
brad.barkhouse@beacontender.com


#7

[quote]Key to any database application is proper 3rd or even 4th form
normalization, make sure you define your database tables and
relationships well, and use indexes where you should.

[/quote]

The importance of this cannot be overstated. A lot of the problems people have with excessive load are usually due to inefficient database structure, not using indexes, making DB connections willy-nilly (willy-nilly?), etc.

  • Jeff @ DreamHost
  • DH Discussion Forum Admin

#8

Atropo7,
thank you for taking time and looking at my site. The problem has sporadic nature - you can catch a “good wave” and the answer would be instant, but other 80% of time its slows down.

Thanks to very kind help from Jeff, I feel I have solved this issue!


#9

Dear Brad,
thank you for share you knowledge.

I have visited your site and I was impressed how fast it loads and operates!

Do you mind to comment on “3rd or even 4th form normalization” as I am not familiar with subject? Any links for further reading would be more than enought :slight_smile:

I have solved my principal problems with kind help from Jeff.

Thanks once again! DreamHost is a great place!