Results 1 to 2 of 2

Thread: mysql variables

  1. #1
    Join Date
    Jul 2004
    Posts
    4

    Unanswered: mysql variables

    Can anyone tell me what variables I should have set in the my.ini for a site with 5000 visitors a day and every page that accesses the db?

    It seems that once we get to 100 online users the CPU is maxing out.

    We have a windows 2000 server with a xeon 2.4gig cpu and 2 gig ddr ram with raid 5 on sata drives.

    Sounds good to me, but it seems the db takes all the cpu time.

    So far I have managed to optimise it slightly but not nearly enough , here are the current settings:

    buffer =256M
    thread_cache = 500
    timeout=15
    table_cache = 128
    sort_buffer = 4M
    tmp_table_size=32M
    back_log=100

    Thanks!

  2. #2
    Join Date
    Jul 2004
    Location
    Dundee, Scotland
    Posts
    107
    I think the config file settings are okay. 5,000 hits per day / 1,440 minutes in each day works out at 3.47 hit per minute. You'd have to ask if the 'eating up the CPU' issues couldn't simply be resolved by looking at the queries that are being run and the table design / use of indexes.



    credits: I copied the following from a post by Shawn Green (Database Administrator, Unimin Corporation - Spruce Pine) on the mysql mailling list, dated 22/07/2004 18:27

    From: http://dev.mysql.com/doc/mysql/en/Se...variables.html

    Key_reads
    The number of physical reads of a key block from disk. If Key_reads is
    big, then your key_buffer_size value is probably too small. The cache miss
    rate can be calculated as Key_reads/Key_read_requests.

    Select_full_join
    The number of joins that do not use indexes. If this value is not 0, you
    should carefully check the indexes of your tables. This variable was added
    in MySQL 3.23.25.

    Select_full_range_join
    The number of joins that used a range search on a reference table. This
    variable was added in MySQL 3.23.25.

    Select_range
    The number of joins that used ranges on the first table. (It's normally
    not critical even if this is big.) This variable was added in MySQL
    3.23.25.

    Select_range_check
    The number of joins without keys that check for key usage after each row.
    (If this is not 0, you should carefully check the indexes of your tables.)
    This variable was added in MySQL 3.23.25.

    Select_scan
    The number of joins that did a full scan of the first table. This variable
    was added in MySQL 3.23.25.

    Sort_scan
    The number of sorts that were done by scanning the table. This variable
    was added in MySQL 3.23.25.

    I would still check the slow query log to see if there are any common
    queries that could use an index or two. I would also look at changing some
    simple indexes into compound indexes (indices?) as another means of
    speeding things up.

Posting Permissions

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