Results 1 to 11 of 11

Thread: Dedicated MySQL

  1. #1
    Join Date
    May 2004
    Posts
    208

    Dedicated MySQL

    Hi,

    I need to get a managed dedicated server mainly as a mysql server, and it will have very little http load. I am expecting about 100K queries per day, about 10-20% of which are updates... I am aware of the fact that this is rather a high-level requirement, but don't know how to provide more information. I can just say that all queries will be optimized with proper indexes, etc.

    So, can you guys please recommend:

    1. What kind of hardware should I be getting, at least at a rather broad level?

    2. I am not a mysql guru, hence have limited knowledge of db administration for mysql. Can you guys please recommend any hosting company that provides managed dedicated server mainly for mysql usage?

    Thanks...

  2. #2
    Join Date
    Aug 2003
    Location
    Montréal
    Posts
    953
    What front end applications will use the MySQL? A forum? Known scripts? How do you estimate your number of queries per day? I think you can't have an exact answer without giving more details.

    A Dual Xeon server will probably handle the load without any problems but you will need to define the amount of RAM you need, if IDE disks are fine or if you need SCSI, if you want RAID, etc. I am not 100% sure but if the MySQL load is well distributed during the day a P4 could even work. It depends what you will use the server for and of other factors like the complexity of the requests knowing that a single request can kill a server by itself.

    I think almost all dedicated server companies that are stated on these forums will be able to provide you with a server for your MySQL needs. As for the management look for the management packages, sometimes optional, offered by the provider of your choice.

    Do you have a defined budget? You can try to ask for quotes using HostQuote or HostVoice.

    Regards,
    :: Martin Leclair
    :: Linkedin Profile

  3. #3
    Join Date
    May 2004
    Posts
    208
    Hi,

    The front-end application will be using custom scripts (PHP), and this application will be hosted on a SEPARATE machine... The box will be solely for mysql and it will run some basic administration tasks every once in a while (like 10 times a day, for 10 minutes)...

    As for the # of queries/day, as I mentioned, I am expecting about 20K pagehits/day and hence around 100K mysql queries per day, and 30% of will be inserts regarding session management.

    I know that you can write a single query which can take down the whole machine, but as I said, I will optimize all the queries, etc. And, hence asking for a high-level estimate.

    Don't you think Dual Xeon is a bit too much for this?

    Thanks...

  4. #4
    Join Date
    May 2004
    Location
    Blue Springs, Missouri
    Posts
    366
    qcache = my best friend



    you should look into it....

  5. #5
    Join Date
    Aug 2003
    Location
    Montréal
    Posts
    953
    As Omaha said, if most of the queries are selects to show your web site content you can use a cache system to help for performance. A P4 could be enough .. if it's a new web site and you do not have any visitors right now, a P4 is probably the best to start with and you can upgrade later if needed.

    Regards,
    :: Martin Leclair
    :: Linkedin Profile

  6. #6
    Join Date
    Apr 2003
    Location
    Los Angeles, CA
    Posts
    800
    Let me see... 100,000 queries per day, let's say the majority over 6 hours, that's only ~5 queries per second, no? Unless your database is huge (a few GB) I can't see how this is a problem...

    Looking at some sample stats I just pulled:

    Uptime: 14,167,235 (~164 days)
    Queries: 59,184,268 (~4 queries/second)
    of these Inserts/Updates/Deletes: 7,507,600 (~13%)
    Qcache hits: 22,394,066 (~35%)

    This causes no load on a Celeron server:

    # ps aux|grep mysql
    mysql 25542 0.2 6.6 99532 67432 ? S Jan14 652:56

    That's about 4 minutes of CPU time per day. No problem running other things on the same machine (qmail, spamassassin, clamav, apache, asterisk).

    It's late here, so unless I screwed up this calculation badly, why are people worried about 100K queries per day? No high-end machine required...
    Pings <1 ms, Unlimited Transfer, Lowest Price: http://localhost/

  7. #7
    Join Date
    May 2004
    Location
    Blue Springs, Missouri
    Posts
    366
    exactly.

    just get a p4 or athlon64 w/ 1GB+ RAM and use qcache and you'll be fine.


    on a 2600+ w/ 2GB RAM of mine we do about 25 queries/sec but 60% of those are pulled from the qcache. this causes no significant load on the server

  8. #8
    Join Date
    Jun 2005
    Posts
    73
    just my 2 cents

    P4 , 1GB RAM, fastest disks you can find I dont feel you will need much more than that if you are just running the db on the server

    As far as the admin side of things, there are a few things you can do, but understanding what they are you may be able to admin the db yourself.

    Might want to check out these:
    http://www.databasejournal.com/featu...le.php/3367871

    On a side notewhen changing the cache and buffer sizes always test afterwards.

  9. #9
    Join Date
    Jun 2003
    Location
    USA
    Posts
    31
    http://www.mysql.com/products/hosting/

    You don't need the webspace, but the database(s) are hosted on a dedicated MySQL cluster. This solution has worked out great for a few clients.
    HostXM - Going the extra mile since 2001
    http://www.hostxm.com

  10. #10
    Join Date
    May 2004
    Posts
    208
    Ahha! This is exactly what I was looking for.

    Do you know whether they impose rules as to # of concurrent sessions (users)/queries per hour and/or day?

    Also, do you know who/which hosting company is managing this hosting solution? Are they any reliable?

    Thanks...
    Last edited by dcabbar; 06-28-2005 at 04:46 PM.

  11. #11
    100k queries per day like calculated above is very light use. One of our servers is pushing 1.5M/hr (36M/day) without much load at all.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •