Server Setup ... do i need a better server
 

I'm currently developing a highend site in which will have a huge user database. So far i've purchased ...

Intel Dual Xeon 2.0 GHz Packages
1 GB RAM - (2) 73GB Hard Drives
10 Mbps unlimited Monthly Transfer

this will be used as my main Fileserver/Frontend machine and then i was thinking about getting a ...

Intel Celeron 2.0 GHz Packages
1 GB RAM - (2) 80GB Hard Drives
1000 GB Monthly Transfer

server to do just handle the mysql database. since it's just going to be database request I don't think it'll need more than 1000GB or will it? my project is going to be a highend photo database site my coder is designing it so that the database will only handle user info and small amounts of data. Do i need a more powerful server for the database.

Dude, you've got it all wrong :)

Static pages (since you're going to host the mysql database on another machine, i assume the frontent machine will only be used for static content) can be served at 10mbps from a simple celeron machine.

Lots of database queries on the other hand could use a Dual Xeon to speed things up a lot. A celeron will start lagging as soon as you're going to send alot of queries to the mysqld.

My opinion: Either do it the other way around (Dual Xeon for db, Celly for static), or buy another Dual Xeon. Some extra firepower at the front won't hurt either.

Make sure you have alot of ram on the mysql server.

well the thing is since it's going to be a photo gallery sitting, kind of like sony's imagination station site where users share there digital photo's via online albums. the main server the Dual Xeon will handle the main brunt of all the hits. Displaying hundreds hopefully thousands of users albums/images. also by it being Unlimited bw i won't run into any problems with going over. so with that said should I just get another Dell Xeon?

What he said. :thumbsup

how much ram is enough and how much is overkill? my missapplebottom.com site is a Dual Xeon 2.0 ghz server with 1.5gig of ram yet I still have mysql problems and i'm running that with a 1000 connection my.cnf . later found out that the coder i bought the script from sucked and didn't close all connections properly. it's being recoded now but anyway even with good coding do you think 1.5gig of ram on a photo gallery site be enough for just or do i need more.

the database will retrieve directory and profile info one users but php scripts will be in each folder to generate the photo display instead of having the database store every photo name and location. of course i'll be expanding as the site grows but i want to make sure i'm doing everything correctly

If (and only if) the mysql querying will be held to an abslut minimum (i read you startpost again, and saw it will only contain just a tiny bit of userinfo), you might be able to pull it off all with the one Dual Xeon you have. But remember, as soon as you start querying the db just a tad more, make sure you move it to it's own box.

If you're going to use mysql alot, there is one golden rule: You can't have too much ram.

1000GB for MySQL traffic is pure overkill. Thats around 3Mbps, unless you are storing tons of data, and I mean so much data that it doesnt even make sense to store it in MySQL anymore, you wont be hitting anywhere near that traffic.

A site getting 1-2 million hits a day, with each hit generating SQL traffic does about 1 Mbps. That's mostly hahahahahas of course, but even with mostly read traffic for you, unless you already have a ton of traffic for this project, you wont be hitting anywhere near that.

As for server setup, cgi takes cpu, mysql takes cpu, html, static, images, take no cpu. You can run 50Mbps of static traffic easily from 1 gig of ram. Definitely allocate the more powerful server for cgi/mysql work. And use the Celeron if you get it for loading images, although again, unless you are already working with a lot of traffic its overkill.

If you have any other questions hit me up on icq at 36837470 I'll be glad to help you out in any way I can.

Actually, its more like you can't have too much CPU. You dont need alot of ram unless you cache, and even then depending on caching at a certain threshold it's not even as efficient anymore.

Good point, CPU is almost as important as RAM. Don't save on one of those two, otherwise you'll end up with a lagging site and lagging mysql database.

I'm with him.

mysql means a lot o ram, 2GB of ram for mysql is basic configuration, the procesor can be even celeron 2.0

1000 gig for the sql server is overkill. Just invest in a dual setup and 2 gigs of ram at least and drop the BW.

You shouldn't need ANY bandwidth for the SQL server. It's just
LAN stuff, not really expensive to transfer data 2 feet. If the data
had to cross the country for every query then of course you'd
need bandwidth, but on the LAN... I don't see why any ISP
should charge for LAN bandwidth.....

btw make sure you lock down the SQL server to only accept
connections from the web server.

Also look into pre-generated static pages. There is no reason to
run a million database queries per day if they can be done with
five queries late at night.

-Ben

Also btw..... MySQL works.... it's great..... but technically it's under
featured for dealing with large amounts of data. Doesn't handle
indexes and the important stuff in the same way true commercial
database servers do. This is probably what people are talking
about with needing a lot of RAM.

-Ben

1000bw comes with the package =( guess i can use that for backup or something. I can increase the ram to 2.

pretty much all pages will be static, including the user albums which will probably update once since they'll be able to create multiple galleries

I would put the money into the extra RAM on the XEON use it for the mysql. Then use celeron for the static pages with 1GB RAM. Mysql is just more resource intensive than static pages.

Here is a good MySQL config file [ /etc/my.cnf ] for a 1GB ram machine. Note thread_concurrency should be 2 unless you have a Dual CPU machine.

[mysqld]
skip-locking
skip-innodb
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
max_connections = 500
interactive_timeout = 100
wait_timeout = 100
connect_timeout = 10
thread_cache_size = 80
key_buffer = 150M
join_buffer = 1M
max_allowed_packet = 16M
table_cache = 512
record_buffer = 1M
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 768K
max_connect_errors = 10
thread_concurrency = 2
myisam_sort_buffer_size = 64M

[safe_mysqld]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
hahahahaha_buffer = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
hahahahaha_buffer = 16M

[mysqlhotcopy]
interactive-timeout

what does the hahaha, damn filters