SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Crazed
    Join Date
    Mar 2001
    Location
    TO, Canada
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy MySQL Optimisation

    Damn I need all of your help

    I currently have:

    Dual Xeon 1.8 Ghz
    2 GB DDR RAM
    3x18.3 GB SCSI 10K RPM 8 MB cache [in RAID1]

    My sites are heavily MySQL based, including writing [I have a site that counts unique IPs other sites get using images].

    MySQL is taking up 50% of my CPU and 75% of my damn RAM.

    My current config:
    PHP Code:
    [mysqld]
    set-variable max_connections=500
    port
    3306
    socket
    = /tmp/mysql.sock
    skip
    -locking
    set
    -variable max_connections=400
    set
    -variable key_buffer=36M
    set
    -variable join_buffer=6M
    set
    -variable record_buffer=6M
    set
    -variable sort_buffer=10M
    set
    -variable table_cache=1536
    set
    -variable myisam_sort_buffer_size=40M
    set
    -variable thread_cache_size=530
    set
    -variable connect_timeout=30
    set
    -variable wait_timeout=30
    set
    -variable interactive_timeout=28800

    [isamchk]
    set-variablekey_buffer=128M
    set
    -variablesort_buffer=128M
    set
    -variableread_buffer=2M
    set
    -variablewrite_buffer=2M

    [myisamchk]
    set-variablekey_buffer=256M
    set
    -variablesort_buffer=256M
    set
    -variableread_buffer=8M
    set
    -variablewrite_buffer=8M 
    What changes can I do to keep it running butter smooth but eliminating the damn stress on my server [I have hit sustained periods of 100% CPU]

    Thanks ::chinese:
    iBegin.com - everything local
    Tech Soapbox - my blog

  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i don't think there are many MySQL config people around here. i wish more people would ask like you though, because i love to tweak it. and MySQL's default configuration isn't that good...

    if you're doing a lot of INSERTs, DELETEs, or UPDATEs that modify indexed columns, make sure you don't have unnecessary indexes on those tables.

    also, i totally recommend upgrading to MySQL 4.0 if you're using 3.23. it should reduce load, possibly by a lot.

    try this config file (my.cnf):

    Code:
    [mysqld]
    port = 3306
    socket = /tmp/mysql.sock
    skip-locking
    skip-innodb
    set-variable = max_connections=500
    set-variable = key_buffer=32M
    set-variable = join_buffer=1M
    set-variable = record_buffer=1M
    set-variable = sort_buffer=2M
    set-variable = table_cache=1536
    set-variable = myisam_sort_buffer_size=128M
    set-variable = thread_cache_size=96
    set-variable = connect_timeout=30
    set-variable = wait_timeout=300
    
    [mysqld_safe]
    open-files-limit=8192
    
    [mysql]
    no-auto-rehash
    
    [mysqldump]
    quick
    
    [myisamchk]
    set-variable = key_buffer=192M
    set-variable = sort_buffer=192M
    set-variable = read_buffer=16M
    set-variable = write_buffer=16M
    
    [mysqlcheck]
    set-variable = key_buffer=192M
    set-variable = sort_buffer=192M
    set-variable = read_buffer=16M
    set-variable = write_buffer=16M
    if you're using or ever need to use InnoDB tables, remove the skip-innodb line.

    and if you're using MySQL 4.0, add

    Code:
    query_cache_size = 16M
    under the [mysqld] section.

    let me know if that helps.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  3. #3
    Crazed
    Join Date
    Mar 2001
    Location
    TO, Canada
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ill try it

    We basically have a LOT of SELECTs, and a quite fair amount of INSERTs and UPDATEs too.

    I tried to design the tables best I can .. but sometimes the internal config can't be helped.

    Oh .. and its not really 'relational' at all .. very few JOINs are used.

    As for MySQL 4.0 ... Im just updating things as cPanel updates things ... is there a reason why MySQL 4 is not used much?

    Thanks again ... Ill get right on it
    Last edited by AhmedF; Feb 22, 2003 at 16:20.
    iBegin.com - everything local
    Tech Soapbox - my blog

  4. #4
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by AhmedF
    We basically have a LOT of SELECTs, and a quite fair amount of INSERTs and UPDATEs too.
    i see. your record_buffer and sort_buffer were pretty big which will use more memory and cause more page faults (i think that's the term ) to allocate so much for each query. and the thread_cache_size was very big which can lead to a lot of old sleeping processes/threads on the system.

    BTW, how many connections do you usually have? average queries per second?


    I tried to design the tables best I can
    good.


    As for MySQL 4.0 ... Im just updating things as cPanel updates things ... is there a reason why MySQL 4 is not used much?
    oh cPanel he he. does it not allow you to upgrade MySQL yourself? well, if you can't go to 4.0, make sure you're at least using the latest 3.23.x version.

    4.0 isn't used much yet because it's not "officially" stable (4.0.10 is classified as Gamma). but it is actually very stable. if i had my own server, i'd have been using it for the last 6-12 months. Yahoo! has been using it on their very busy servers for almost a year, if not more.

    just judging from the cycle of 3.23, i'd expect 4.0 to be officially stable very soon now -- like within a month or 2. maybe it could even happen with 4.0.11 since it seems to be taking awhile to be released... when that happens, i assume hosts and cPanel and stuff should start using it because it's "safe" (!) all of a sudden.


    Thanks again
    no prob.
    Last edited by DR_LaRRY_PEpPeR; Feb 22, 2003 at 17:20.

  5. #5
    Crazed
    Join Date
    Mar 2001
    Location
    TO, Canada
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    Any good articles that you would recommend on more 'hardcore' optimisations? I Saw the thread in the PHP forum .. some good info.

    You confused me a bit with the buffers. Is there any place that explicity tells you what they do?

    I hate the MySQL manual :P

    [btw .. haha ... ur avatar's little GIF animation ]


    I actually have my friend do the server admining .. he should be able to upgrade it to 4.0 NP. And I'll get the connections info out of him.

    Lastly .. clear out your PM inbox!
    Last edited by AhmedF; Feb 22, 2003 at 16:48.
    iBegin.com - everything local
    Tech Soapbox - my blog

  6. #6
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by AhmedF
    Any good articles that you would recommend on more 'hardcore' optimisations? I Saw the thread in the PHP forum .. some good info.

    You confused me a bit with the buffers. Is there any place that explicity tells you what they do?
    unfortunately i don't know any good articles. i've just kinda picked things up over the last year -- from the MySQL tweaking threads in vBulletin.com's MySQL and Server Configuration forums.

    the MySQL page for variables explains (sorta) what the different ones do.

    one thing to remember for memory usage is:

    max MySQL mem usage = key_buffer + query_cache_size + (max_connections * (join_buffer + record_buffer + sort_buffer + tmp_table_size))

    so if you make the buffers (except key_buffer) larger, you can't support as many connections, etc.


    [btw .. haha ... ur avatar's little GIF animation ]
    that was stuck in their by the creator -- forum member/Advisor platinum.


    I actually have my friend do the server admining .. he should be able to upgrade it to 4.0 NP.
    cool!


    And I'll get the connections info out of him.
    not needed, i was just wondering what kind of activity it had.


    Lastly .. clear out your PM inbox!
    sorry about that. i'll try to delete some stuff later.

  7. #7
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't know if you've read this: http://www.webmasterbase.com/article/402 - may be some help there to use indexes.

    The other thing you may be able to do is modify what PHP's doing to MySQL - is there a way to reduce the number of queries overall? For example Vincent (a frequent visitor to SPFs) makes good use of mysql_data_seek() in Eclipse. If you're performing the same query more than once on the same page, mysql_data_seek() would allow you to perform one query only and fetch the results from it as many times as you like. Your code has to keep track of the row number but that may help.


Bookmarks

Posting Permissions

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