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 Usage

Discussion in 'Workarounds and Optimization' started by MaRiOsGR66, Jun 24, 2014.

  1. MaRiOsGR66

    MaRiOsGR66 Well-Known Member

    Joined:
    Feb 18, 2011
    Messages:
    92
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    in the last days, 2 of my cpanel servers face the same problem,
    websites stop loading, while the load of the server is not high (1-5)
    but when I stop mysql (/etc/init.d/stop) the load in half second,
    the next time this happened I did run watch 'mysqladmin proc'
    and I see endless screens of requests that do not seem to get completed
    or endless screens of sleeped ones.


    Code:
    +-------+------------------+-----------+-----------------------------+----------------+------+--------------------+-----------------------------------------------------
    -------------------------------------------------+
    | Id    | User             | Host      | db                          | Command        | Time | State              | Info
                                                     |
    +-------+------------------+-----------+-----------------------------+----------------+------+--------------------+-----------------------------------------------------
    | 14    | eximstats        | localhost | eximstats                   | Query          | 264  | Opening tables     | INSERT  INTO sends (mailtime,msgid,processed,domain,email,user,size,host,ip,auth,localsender,spamsco |
    | 38733 | user123     | localhost | ortho983_anorthosis413      | Query          | 590  | closing tables     | SELECT value FROM xcart_languages WHERE code='el' AND name='txt_err_msg_code_79' LIMIT 1             |
    | 38886 | user123     | localhost | ortho983_anorthosis413      | Query          | 29   | Opening tables     | SELECT value FROM xcart_languages WHERE code='el' AND name='txt_err_msg_code_79' LIMIT 1             |
    | 38893 | user123     | localhost | ortho983_anorthosis413      | Query          | 5    | Opening tables     | SELECT value FROM xcart_languages WHERE code='el' AND name='txt_err_msg_code_79' LIMIT 1             |
    | 38909 | user333 | localhost | aftonomi_jomsite            | Query          | 820  | Opening tables     | SELECT m.id, m.title, m.module, m.position, m.content, m.showtitle, m.params, mm.menuidFROM e8eqw_m |
    | 38923 | user12121   | localhost | kotesspa_base               | Query          | 863  | Opening tables     | select CollectionVersionBlocks.isOriginal, BlockTypes.pkgID, CollectionVersionBlocks.cbOverrideAreaP |
    | 38924 | user333 | localhost | aftonomi_jomsite            | Query          | 40   | Opening tables     | SELECT COUNT(*) FROM e8eqw_k2_comments WHERE itemID=2805 AND published=1                             |
    | 38927 | user12121   | localhost | kotesspa_base               | Query          | 143  | closing tables     | select csrID from CollectionVersionBlockStyles where cID = '1' and cvID = '130' and arHandle = 'Head |
    | 38932 | user333 | localhost | aftonomi_jomsite            | Query          | 149  | Opening tables     | SELECT COUNT(*) FROM e8eqw_k2_comments WHERE itemID=2805 AND published=1                             |
    | 38935 | user12121   | localhost | kotesspa_base               | Query          | 879  | Opening tables     | select CollectionVersionBlocks.isOriginal, BlockTypes.pkgID, CollectionVersionBlocks.cbOverrideAreaP |
    | 38936 | user12121   | localhost | kotesspa_base               | Query          | 879  | closing tables     | select CollectionVersionBlocks.isOriginal, BlockTypes.pkgID, CollectionVersionBlocks.cbOverrideAreaP |
    | 38937 | user333 | localhost | aftonomi_jomsite            | Query          | 457  | Opening tables     | UPDATE `e8eqw_session`SET `data` = '__default|a:7:{s:15:\"session.counter\";i:1;s:19:\"session.time |
    | 38944 | user333 | localhost | aftonomi_jomsite            | Query          | 76   | Opening tables     | SELECT COUNT(*) FROM e8eqw_k2_comments WHERE itemID=2805 AND published=1                             |
    | 38947 | user123 | localhost | ortho983_anorthosis413      | Query          | 6    | Opening tables     | SHOW FIELDS FROM xcart_stats_shop
    | 38949 | user333 | localhost | aftonomi_jomsite            | Query          | 104  | Opening tables     | SELECT * FROM e8eqw_k2_items WHERE id != 3972 AND catid=18 AND ordering > 2225 AND published=1 AND ( |

    I did put in both my.cnf files these:
    interactive_timeout=60
    wait_timeout=60

    but still the same problem occurs.

    also mysql doesn't have high ram/cpu usage during the problems..
    what could be the reason? am I attacked?
     
  2. simonas

    simonas Well-Known Member

    Joined:
    Apr 21, 2013
    Messages:
    141
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Lithuania
    cPanel Access Level:
    Root Administrator
    Re: serious MySQL problem

    Hello,

    Please check error_log , in you website folder and check server error logs.
     
  3. SS-Maddy

    SS-Maddy Well-Known Member

    Joined:
    Mar 28, 2009
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Re: serious MySQL problem

    The mysql server needs to be optimized. You can optimize it by changing the parameters in /etc/my.cnf
     
  4. MaRiOsGR66

    MaRiOsGR66 Well-Known Member

    Joined:
    Feb 18, 2011
    Messages:
    92
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Re: serious MySQL problem

    I've allready done that , with MySQLTuner ( did run it 2 times, 1 every 24 hours)
    and added all the suggestions to /etc/my.cnf
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Re: serious MySQL problem

    Hello :)

    Could you let us know the full contents of your /etc/my.cnf file? I have moved this thread to the "Optimization" forum where you are likely to receive more user feedback.

    Thank you.
     
  6. MaRiOsGR66

    MaRiOsGR66 Well-Known Member

    Joined:
    Feb 18, 2011
    Messages:
    92
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hello,

    yes it is:


    Code:
    root@[~]# cat /etc/my.cnf
    [mysqld]
    max_connections = 800
    innodb_file_per_table=1
    open_files_limit=50000
    interactive_timeout=30
    wait_timeout=30
    
    # debug
    long_query_time=0.3
    log-slow-queries=/var/log/mysql-slow.log
    
    
    #last additions 24-06-2014
    query_cache_type = 1
    query_cache_size = 64M
    query_cache_limit = 1M
    join_buffer_size = 512K
    thread_cache_size = 4
    table_open_cache = 1024
    innodb_buffer_pool_size = 1G
    innodb_log_buffer_size = 2M
    max_heap_table_size = 32M
    
     
Loading...

Share This Page