Hello.
On Wed 2002-10-23 at 10:47:28 -0400, [EMAIL PROTECTED] wrote:
> I'm planning on using Win2k Adv. server, IIS, MYSQL, PHP on a dual
> processor 2.4GHz processor with 1 or 2GB RAM, with either SCSI or ATA HDs.
>
> The webpage will be dynamic.
> 1)How many concurrent connections would something like this handle?
The short answer is: it depends.
The long answer:
Since you mention both, IIS and MySQL, it is not clear what you refer
to. Concurrent web connections or concurrent database connection.
Either way, "concurrent connections" is not a meaningful measure, as
performance also depends on "time needed per request", i.e. 10
concurrent connection with a response time of 0.1 seconds is better
than 50 concurrent connections with a response time of 10 seconds (the
former can answer 100 requests/sec, the latter only 5).
The performance of the setup depends on many factors, among other
things:
- CPU speed / number of CPUs
- disk space
+ speed
+ latency
- memory size
+ free mem for database buffers
+ free mem for OS file caching
- database usage
+ whole size
+ size of regularly used parts
- usage pattern (i.e. the queries)
+ read/write ratio
+ complexity of queries
- operating system
+ quality of threading support
- MySQL
+ required version
+ required table type
That are only the most common of the factors. Even if all of them were
known it would still be hard to make a reasonable guess. You provided
less than half of them, so the answer must be: it depends.
Btw, the "simplest" scaling possibility you have, if needed, is to
install two servers, one for the web server part, one for the database
server.
A data point: A dual Pentium III 1133MHZ, several 18GB IBM SCSI disks,
2GB RAM (0.5GB used, 0.5GB used for caching, rest sparely used), only
for MySQL. Linux 2.4, ext2 filesystem. Seperate app server.
Database is 15GB, of which no more than 1GB is often used. About 40
Million records. 80% simple SELECTs, 10% SELECTs without perfect
index, 10% writes. MyISAM tables on v3.23.49. Indexes and tables are
optimized a lot.
This setting is able to handle about 120 queries/s, where one web
request triggers about 8 queries, i.e. 15 request/s with a CPU
utilisation of 20% and disk throughput of 400MB/s. Experience tells
that this system on this database manages to sustain > 2GB/s, so CPU
usage will become first bottleneck at about 75 req/sec resp. 600
queries/sec.
For this application, RAID/SCSI/IDE is a non-issue for now, because
the relevant database parts are cached (by the OS) in memory, and
there is room for the database to about double the size before the
cache ratio will get worse.
The CPU requirement is unusually high due to certain shortcomings of
the 3.23 optimizer, which are already fixed in the 4.x tree (so I do
not care and plan to switch to 4.x when CPU becomes a problem).
Additionally, the query cache of 4.x will benifit my kind of
application *a lot*, so expect to get up to 4000-5000 queries
regarding the CPU, but some other bottleneck will hit me first.
When the database is 5 times the size, the situation will have changed
completely (given the same hardware), because the relevant parts
cannot be cached anymore -> the disk throughput increases -> the disks
are too slow -> the whole system bogs down. A RAID system may be able
to partially compensate for this (or simply buying new mem, which will
be cheapier by then).
> 2) Will it make a big difference if I use SCSI or ATA? Example SCSI 1000
> conncetions, ATA 100 connections. Just want a ball park figure if there is
> a difference.
It depends. ATA is about as fast as SCSI or faster for a single disk.
For several disks, SCSI has less congestion and will be faster. (Only
high usage disks count for this number, not a disk holding the system.)
There are other advantages to SCSI which have nothing to do with speed.
> 3) How much RAM do I need for 1000 conncurrent connections?
What kind of connections (see above)? MySQL's memory usage is quite
configurable. According to my experience the web server will use more
memory than the database per connection. But you want a key cache of a
reasonable size for your database.
> 4) Should I use RAID 1 or 5? Will it make a big difference if I want 1000
> concurrent connections?
If you are concerned about disk speed, RAID 1 or RAID 10 is the way to
go, if you can afford the needed system and disks. But be sure that
you need a RAID system at all (regarding the speed issue).
Btw, using a UNIX system like Solaris, FreeBSD or Linux will give a
significant speed boost for MySQL (up to 30%, IIRC).
Regards,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
