The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL optimize help on Xeon

Discussion in 'Workarounds and Optimization' started by saamxvr, Mar 2, 2011.

  1. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    Hello
    I was buy new Xeon Dedicate server , 4GB ram this is my currant MySQL sever 5.1,


    my.cnf

    When i run ./mysqltuner.pl as there recommendation have new Option :confused: Plz help me to Edit this

    Thanks!!
     
  2. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hello,

    Here is what I would possibly suggest for the revised /etc/my.cnf file:

    Code:
    [mysqld]
    max_connections=250
    max_user_connections=50
    slow_query_log
    query_cache_type=1
    query_cache_size=256M
    query_cache_limit=70M
    sort_buffer_size=4M
    read_rnd_buffer_size=512K
    tmp_table_size=256M
    max_heap_table_size=384M
    thread_cache_size=32
    key_buffer_size=128M
    table_open_cache=1184
    wait_timeout=300
    interactive_timeout=300
    You are running MySQL 5.1.54 and had the MySQL 5.0 entries for the slow query log and table_open_cache (it used to be table_cache and is now table_open_cache in MySQL 5.1), so I changed to the right ones for MySQL 5.1.

    Next, safe-show-databases is already default and deprecated, so I removed it.

    I increased max_connections from 50 to 250, since you are severely limiting the number of connections for MySQL on the machine if you only have 50 total connections. The default we use is normally 500 so I'm uncertain why it was reduced all the way down to 50. I added max_user_connections=50 so that one database cannot take up most or all of the connections at any time.

    For wait_timeout and interactive_timeout, I set these values to 300, which represents 300 seconds. If you wonder what these are, wait_timeout represents the number of seconds MySQL waits for activity on non-interactive connections, and interactive_timeout represents the number of seconds MySQL waits for activity on interactive connections.

    I made no changes to the query_cache_size because regardless of the message from mysqltuner.pl, you don't want to increase it even more from that value.

    Thanks.
     
  3. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    Thank you very much .!! i will update this setting result next 24 hours :)
     
  4. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    i Guss Temporary table size have problem right ?
     
  5. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    It isn't the temporary table size as it's already large, it's the result set size. That would be the database itself that's the issue there, though, and I really don't have a recommendation on how to correct that. You might want to enable the general logs for MySQL 5.1 for 24 hours and then check which databases show running SELECT DISTINCT queries.

    To enable the general log for MySQL 5.1, put the following into /etc/my.cnf under the [mysqld] section:

    Code:
    general_log
    Then restart MySQL:

    Code:
    /etc/init.d/mysql restart
    Give it 24 hours, then check the logs for SELECT DISTINCT queries. The log file will be at /var/lib/mysql and called hostname.log

    After 24 hours, I would remove the general_log line because that log will get very large very quickly.
     
  6. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    Hi im sorry for didn't reply . i was run my server as preview setting very well with out problem , now im facing small server loading case look my currant my.cnf

    mysqltuner result is

    Im exacting some good tuning setting :)

    Thanks lot.
     
  7. nobodyk

    nobodyk Well-Known Member

    Joined:
    Aug 1, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    Not much you can do now. Mysql only performs as well as how your queries are develop. What mysqltuner is saying is that you have too many queries without limits.

    Ex: "SELECT userid FROM users WHERE username = saamxvr"
    in the above example were trying to find the row for the data of user saamxvr.

    The above query can be written as follows:
    "SELECT userid FROM users WHERE username = saamxvr LIMIT 1"
    since we know that there could only be one row for the user saamxvr, there's no point in continuing to search the database when the row has been found, hence you add limits to your queries.
     
  8. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Did you check the general log after enabling it for the database that is running the SELECT DISTINCT queries as I indicated in my prior post?
     
  9. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    no i didn't, becoz i was went-out some country , so u need to do to me as ur prior post ?
     
  10. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    I would suggest trying to enable that general log and then checking for databases using those type of queries yep. The database or databases doing that are the ones you'd need to further change how they are handling those type of queries.
     
  11. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    OK i just add general_log in to my my.cnf , plz let me know how i browse after 24hours my log file ? plz provide me to SHH command
    Thanks
     
  12. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    The general log will be in /var/lib/mysql and called hostname.log where hostname is your machine's hostname. You can always do this command to find the log's name:

    Code:
    ls -lah /var/lib/mysql/*.log
    This should output the general log file's name, which you can then grep for SELECT DISTINCT:

    Code:
    grep -i "select distinct" /var/lib/mysql/hostname.log
     
  13. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    this is log file result

     
  14. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    So the database isn't showing up in the output? If not, you'll have to go into the log file itself and do a search for the SELECT DISTINCT string.
     
  15. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    When i use this command grep -i "select distinct" /var/lib/mysql/hostname.log , result start

    1 Query SELECT DISTINCT DAYOFMONTH(post_date) - to - 220639 Query SELECT DISTINCT DAYOFMONTH(post_date), plz tel me what i do next .
     
  16. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    I already indicated what to do. You'll have to go into the log file itself by opening it up in a text editor in root SSH, then search for SELECT DISTINCT in that log file. If you aren't familiar with how to open files in text editors in root SSH, here is a guide on using vi to open a file:

    Basic vi Commands
     
  17. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    ohh can i open with vi nano right :) wait i will reply
     
  18. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Yes, you can open it with whatever text editor you prefer and search for SELECT DISTINCT in the log file.
     
  19. saamxvr

    saamxvr Well-Known Member

    Joined:
    Oct 30, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    log file not open , that much file is big so server load goin crazy ?
     
  20. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Then copy the file to some location where you can download it and open it on your local computer after you download it using a basic text editor there. If it crashes your local computer, that would probably be less of a concern anyway. You might try just tailing the last 500 lines to see if any show SELECT DISTINCT otherwise.

    Code:
    tail -500 /var/lib/mysql/hostname.log
    If they don't, keep increasing the number of lines until you do see the query.
     
Loading...

Share This Page