Re: ROugh idea of speed

Steve Vernon [mailto:steve@extremewattage.co.uk] wrote: > > Hiya, > Just wondering what is the rough idea of speed of a > server like this is > holding a database with millions of records. I know its > difficult, depends > on the data stored etc. > > Its basically storing an index int and about 5 or so char > field (50 > long). In total I want to store 500 million records. Accessed > using PHP. > a.. 2x Intel Pentium III 1260 CPU or higher > b.. 1 GB RAM > c.. 60 GB hard drive > d.. 20 GB traffic/month > e.. RedHat LInux 7.2 > Ive read that its better to store the data in different > databases on the > same server? > > Can someone please give me a rough idea of the speed and how many > servers needed, my client wants to know how much it will cost > to host the > site. > > Anyone have any experience with holding a lot in MySQL? > Any idea of > speed would be great. > One of my clients is running a special interest portal, with roughly 200-300 parallel users in peak times, and about 1000 SQL queries/sec (of which about 90% are SELECTs). For stability and scalability reasons, we have an LVS (http://linuxvirtualserver.org/) cluster for http, and 2 mysql servers (Athlon 1800+, 512 MB, SCSI-RAID) with active/active replication, having a loadaverage of about 0.10 at peak times. Our database is about 1 Gig now, storing authentication- and user data (including user-uploadable pictures, which make 600 MB by now). We had many problems with mysql running on the same machines apache was running (freezes and deadlocks with 1 httpd consuming 400+ MB and loadaverage 100+), for yet unknown reasons (kernel <= 2.4.16 comes to mind...), which were all solved moving to the above configuration. I am really confident with the current setup, which is running for half a year now. But keep in mind that - most of the times - you get far more performance by optimizing your database (and queries) than by upgrading hardware (the --log-slow-queries and --log-long-format options to mysqld are your friend). Thomas