1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Share your my.cnf if you have a 16gb ram server

Discussion in 'Databases' started by ASTRAPI, Jan 11, 2011.

  1. #1
    Hello

    Can anyone share with me the my.cnf settings from an optimized server with more than 600 users online at the same time on a 16gb ram server?

    Thank you
    ASTRAPI, Jan 11, 2011 IP
  2. mwasif

    mwasif Active Member

    Messages:
    819
    Likes Received:
    23
    Best Answers:
    1
    Trophy Points:
    70
    #2
    Why don't you post your 16GB RAM server my.cnf to get suggestions from others? Is your server dedicated MySQL server or sharing resources with web server?
    mwasif, Jan 11, 2011 IP
  3. ASTRAPI

    ASTRAPI Guest

    Messages:
    500
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Ok here it is:

    [mysqld]
    skip-name-resolve
    skip-innodb
    skip-locking
    skip-networking
    thread_concurrency=16
    safe-show-database
    tmp_table_size=256M
    max_heap_table_size=256M
    query_cache_limit=3M
    query_cache_size=128M
    query_cache_type=1
    max_user_connections=300
    max_connections=300
    interactive_timeout=30
    wait_timeout=50
    connect_timeout=30
    low_priority_updates=1
    thread_cache_size=512
    key_buffer=2048M
    join_buffer_size=256M
    max_allowed_packet=64M
    table_cache=1536
    record_buffer=1M
    sort_buffer_size=16M
    read_buffer_size=16M
    max_connect_errors=10
    myisam_sort_buffer_size=512M
    concurrent_insert=2

    [isamchk]
    key_buffer=128M
    sort_buffer=128M
    read_buffer=32M
    write_buffer=32M

    [myisamchk]
    key_buffer=128M
    sort_buffer=128M
    read_buffer=32M
    write_buffer=32M

    [mysql]
    no-auto-rehash



    And the problem i think is that every connection takes a lot of ram or cpu and sql is loading a lot :(

    The server is dedicated and web files and sql is on the same server as i think 400 users online are not a problem to handle for sure.

    Or maybe every php request is big or something related to that that i am not sure how to check :(

    Thank you
    ASTRAPI, Jan 11, 2011 IP
  4. firman01

    firman01 Well-Known Member

    Messages:
    155
    Likes Received:
    2
    Best Answers:
    1
    Trophy Points:
    115
    #4
    1. for big website that use intensive mysq, it shouldn't be on the same server.
    2. are you using innodb or myisam? try looking on mysql processlistand see what is hogging your mysql.
    if you're using myisam as table, probably if there is lots of select, there will be lots of locking *if* you have consecutive insert too.
    3. 600 online user at the sametime? 600 concurrent connection to sql? or what? (i see on your config max 300)
    firman01, Jan 11, 2011 IP
  5. ASTRAPI

    ASTRAPI Guest

    Messages:
    500
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    2)myisam
    3)600 users online on the forum.

    How a second derver help if the sql is locking as it will take the same amount of requests i think it will lock again.
    ASTRAPI, Jan 11, 2011 IP
  6. firman01

    firman01 Well-Known Member

    Messages:
    155
    Likes Received:
    2
    Best Answers:
    1
    Trophy Points:
    115
    #6
    try looking on mysql processlistand see what is hogging your mysql <-- this is important, and you can adjust from there.

    1. by default UPDATE priority are higher than SELECT, by changing SLELECT priority higher than UPDATE maybe could help the things down.
    2. use caching, like static file or memcache <-- dp forum also use memcache.
    3. for dynamic caching use varnish
    4. and start thinking about load blancing your sql with atleast 1 master 2 slave (it could be replication or cluster)
    5. use sphinx or solr/lucene to search your data, so it will not lock your myisam table when doing search <-- look at the footer of dp forum, they use sphinx.
    6. fix pagination system, instead using LIMIT M,N use LIMIT N and add some condition so mysql will use the available index instead of scanning all table for lookups.

    atleast for busy db you need like raid 10 sas hdd for the db itself with minimal 4hdd setup, and use separate hdd for OS and log.
    if all still failed, drop myisam, and use something better like innodb.

    still failed too, use mysql enterprise server or xtraDB engine from mariadb.
    firman01, Jan 11, 2011 IP
  7. ASTRAPI

    ASTRAPI Guest

    Messages:
    500
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #7
    What do you think about my.cnf?

    Also i think the problem is per thread/connection that every user use.

    Maybe php size or a query use a lot of ram as the problem is coming when i have exactly the same amount of users.

    And yes i use raid 10 4x sas 15k 16gb ram 2x quad xeon xcache and spinx.

    I want to ask also if xcache by default cache also files and not only php as maybe this is my problem....

    On a cpanel server i have nginx plugin in front of apache.

    Also can i use nginx for caching static files?

    Thank you
    Last edited: Jan 11, 2011
    ASTRAPI, Jan 11, 2011 IP
  8. firman01

    firman01 Well-Known Member

    Messages:
    155
    Likes Received:
    2
    Best Answers:
    1
    Trophy Points:
    115
    #8
    xcache only caching php opcode.
    if nginx is in front of apache, so when it request static file it will most probably got it requested from nginx.

    have you try optimizing from the forum scripts? if you're using vb http://articles.digitalpoint.com/content.php?r=7-Optimize-vBulletin-4
    have you try to use memcache too? (different than xcache)

    i rarely use myisam so i cannot comment on your config.
    you try this script to check http://mysqltuner.pl/mysqltuner.pl

    if all still fail, hire a reputable decent database guy to solve your problem that has experience with your forum software, probably you can find on this forum.

    but before that, i recommend you use mariaDB or Perconna, it's waaay better than standard MySQL.
    it has Aria and xtraDB engine.

    Performance
    Aria > myisam
    xtraDB > innoDB

    i've tried myself, running busy wp side with mariadb replication on xtraDB + sphinx, it's awesome!
    but it doesn't work magically by just changing mysql build. i had to optimize alot of wordpress code on the query part and use a caching plugin because it's very important.
    firman01, Jan 11, 2011 IP
  9. ASTRAPI

    ASTRAPI Guest

    Messages:
    500
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #9
    I don't know first if i have to check when i use Easy apache update the Memcache and memcached too.

    And then after installation do you know any easy info to configure it?

    Thank you
    ASTRAPI, Jan 11, 2011 IP
  10. jestep

    jestep Notable Member Premium Member

    Messages:
    3,486
    Likes Received:
    199
    Best Answers:
    16
    Trophy Points:
    280
    #10
    The hardware sounds good. I would seriously consider Innodb or one of percona innodb builds, Xtradb as suggested. The thought of table locking alone with this much usage just wouldn't work for me. Apart from that Innodb is so much more able to be tuned. The exception is if you need full text searching, however there are workarounds for it.

    If you stick with myisam, I would start with the key buffer and bump it up to about 6 - 8Gb. Here's a good article from the percona guys about migrating. They estimate you can get 2 - 3 times performance tuning myisam to 10 - 50 for Innodb. This does mean that Innodb isn't as good out of the box, but IMO it is far easier and more predictable to work with.

    The other question I have, is how are you connecting to the database on the application level. I would suggest switching to a singleton database connector to prevent opening and closing connections so often. If you have 400 users on the site at the same time, this would definitely be appropriate.

    As far as caching, I would start with APC. If you're finding it's not performing the way you want it to, maybe go to a reverse proxy caching mechanism as well.
    jestep, Jan 21, 2011 IP