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.

swap file question

Discussion in 'Workarounds and Optimization' started by upsforum, Apr 22, 2015.

  1. upsforum

    upsforum Well-Known Member

    Joined:
    Jul 27, 2005
    Messages:
    446
    Likes Received:
    0
    Trophy Points:
    16
    I have a dedicated server with this spec tech:

    CPU 8 Core
    RAM 16 GB
    SWAP FILE 1 GB

    every day I see with htop that Swap fiel is fulled but much RAM is free, I verified that if restart mysql solves the problem, which can be the cause?
     

    Attached Files:

  2. madmanmachines

    madmanmachines Well-Known Member

    Joined:
    Nov 28, 2014
    Messages:
    94
    Likes Received:
    3
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    Hi,

    I would assume this is MySQL 5.6+ and/or performance_schema is enabled? Is there a difference with performance_schema disabled? What is the output of MySQL Tuner?

    Thanks,
     
  3. upsforum

    upsforum Well-Known Member

    Joined:
    Jul 27, 2005
    Messages:
    446
    Likes Received:
    0
    Trophy Points:
    16
    Code:
    >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.42-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 2G (Tables: 6884)
    [--] Data in InnoDB tables: 1G (Tables: 8742)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 100)
    [!!] Total fragmented tables: 579
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 5h 3m 53s (2M q [129.180 qps], 70K conn, TX: 16B, RX: 377M)
    [--] Reads / Writes: 62% / 38%
    [--] Total buffers: 2.6G global + 6.6M per thread (250 max threads)
    [OK] Maximum possible memory usage: 4.2G (27% of installed RAM)
    [OK] Slow queries: 0% (56/2M)
    [OK] Highest usage of available connections: 10% (27/250)
    [OK] Key buffer size / total MyISAM indexes: 200.0M/583.1M
    [OK] Key buffer hit rate: 99.8% (32M cached / 55K reads)
    [OK] Query cache efficiency: 77.3% (1M cached / 1M selects)
    [!!] Query cache prunes per day: 135876
    [OK] Sorts requiring temporary tables: 0% (221 temp sorts / 71K sorts)
    [!!] Joins performed without indexes: 1486
    [!!] Temporary tables created on disk: 32% (122K on disk / 374K total)
    [OK] Thread cache hit rate: 99% (69 created / 70K connections)
    [OK] Table cache hit rate: 99% (16K open / 16K opened)
    [OK] Open file limit used: 22% (14K/63K)
    [OK] Table locks acquired immediately: 99% (640K immediate / 640K locks)
    [OK] InnoDB buffer pool / data size: 2.0G/1.4G
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        query_cache_size (> 128M)
        join_buffer_size (> 4.0M, or always use indexes with joins)
    
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello,

    You will likely get more accurate results if you let MySQL run for at least 24 hours before running the tuner. I've moved this thread to our "Optimization" forum.

    Thank you.
     
  5. upsforum

    upsforum Well-Known Member

    Joined:
    Jul 27, 2005
    Messages:
    446
    Likes Received:
    0
    Trophy Points:
    16
    mysql> SHOW ENGINES;
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | Engine | Support | Comment | Transactions | XA | Savepoints |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | MyISAM | DEFAULT | MyISAM storage engine | NO | NO | NO |
    | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
    | CSV | YES | CSV storage engine | NO | NO | NO |
    | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
    | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
    | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
    | ARCHIVE | YES | Archive storage engine | NO | NO | NO |
    | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
    | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

    mysql> SHOW VARIABLES LIKE 'perf%';
    +---------------------------------------------------+---------+
    | Variable_name | Value |
    +---------------------------------------------------+---------+
    | performance_schema | OFF |
    | performance_schema_events_waits_history_long_size | 10000 |
    | performance_schema_events_waits_history_size | 10 |
    | performance_schema_max_cond_classes | 80 |
    | performance_schema_max_cond_instances | 1000 |
    | performance_schema_max_file_classes | 50 |
    | performance_schema_max_file_handles | 32768 |
    | performance_schema_max_file_instances | 10000 |
    | performance_schema_max_mutex_classes | 200 |
    | performance_schema_max_mutex_instances | 1000000 |
    | performance_schema_max_rwlock_classes | 30 |
    | performance_schema_max_rwlock_instances | 1000000 |
    | performance_schema_max_table_handles | 100000 |
    | performance_schema_max_table_instances | 50000 |
    | performance_schema_max_thread_classes | 50 |
    | performance_schema_max_thread_instances | 1000 |
    +---------------------------------------------------+---------+
    16 rows in set (0.00 sec)

    now I enabled performance_schema,
     
    #5 upsforum, Jul 28, 2015
    Last edited by a moderator: Jul 28, 2015
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  7. upsforum

    upsforum Well-Known Member

    Joined:
    Jul 27, 2005
    Messages:
    446
    Likes Received:
    0
    Trophy Points:
    16
    performance_schema is on

    Code:
    mysql> SHOW VARIABLES LIKE 'perf%';
    +---------------------------------------------------+---------+
    | Variable_name                                     | Value   |
    +---------------------------------------------------+---------+
    | performance_schema                                | ON      |
    | performance_schema_events_waits_history_long_size | 10000   |
    | performance_schema_events_waits_history_size      | 10      |
    | performance_schema_max_cond_classes               | 80      |
    | performance_schema_max_cond_instances             | 1000    |
    | performance_schema_max_file_classes               | 50      |
    | performance_schema_max_file_handles               | 32768   |
    | performance_schema_max_file_instances             | 10000   |
    | performance_schema_max_mutex_classes              | 200     |
    | performance_schema_max_mutex_instances            | 1000000 |
    | performance_schema_max_rwlock_classes             | 30      |
    | performance_schema_max_rwlock_instances           | 1000000 |
    | performance_schema_max_table_handles              | 100000  |
    | performance_schema_max_table_instances            | 50000   |
    | performance_schema_max_thread_classes             | 50      |
    | performance_schema_max_thread_instances           | 1000    |
    +---------------------------------------------------+---------+
    16 rows in set (0.00 sec)
    
    this is tuner

    Code:
     >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.45-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 2G (Tables: 6391)
    [--] Data in InnoDB tables: 1G (Tables: 8282)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 104)
    [!!] Total fragmented tables: 493
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 21h 35m 1s (25M q [102.035 qps], 375K conn, TX: 113B, RX: 5B)
    [--] Reads / Writes: 75% / 25%
    [--] Total buffers: 3.9G global + 44.1M per thread (250 max threads)
    [!!] Maximum possible memory usage: 14.7G (94% of installed RAM)
    [OK] Slow queries: 0% (795/25M)
    [OK] Highest usage of available connections: 46% (116/250)
    [OK] Key buffer size / total MyISAM indexes: 1.0G/697.9M
    [OK] Key buffer hit rate: 100.0% (7B cached / 296K reads)
    [OK] Query cache efficiency: 86.5% (19M cached / 22M selects)
    [!!] Query cache prunes per day: 215494
    [OK] Sorts requiring temporary tables: 0% (172 temp sorts / 827K sorts)
    [!!] Joins performed without indexes: 33811
    [!!] Temporary tables created on disk: 27% (220K on disk / 790K total)
    [OK] Thread cache hit rate: 99% (1K created / 375K connections)
    [OK] Table cache hit rate: 91% (30K open / 33K opened)
    [OK] Open file limit used: 30% (19K/63K)
    [OK] Table locks acquired immediately: 99% (5M immediate / 5M locks)
    [OK] InnoDB buffer pool / data size: 2.0G/1.5G
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        Increasing the query_cache size over 128M may reduce performance
        Adjust your join queries to always utilize indexes
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (> 384M) [see warning above]
        join_buffer_size (> 8.0M, or always use indexes with joins)
     
  8. upsforum

    upsforum Well-Known Member

    Joined:
    Jul 27, 2005
    Messages:
    446
    Likes Received:
    0
    Trophy Points:
    16
    I solved the problem without restart mysql but with # swapoff -a ... and # swapon -a
     
  9. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    I am happy to see you were able to address the issue. Thank you for updating us with the outcome.
     
Loading...

Share This Page