upsforum

Well-Known Member
Jul 27, 2005
473
0
166
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?
 

Attachments

madmanmachines

Well-Known Member
Nov 28, 2014
94
4
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,
 

upsforum

Well-Known Member
Jul 27, 2005
473
0
166
Code:
>>  MySQLTuner 1.3.0 - Major Hayden <[email protected]>
>>  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)
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,218
463
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.
 

upsforum

Well-Known Member
Jul 27, 2005
473
0
166
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,
 
Last edited by a moderator:

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,218
463
Were you able to run the tuner again after letting MySQL run for at least 24 hours?

Thank you.
 

upsforum

Well-Known Member
Jul 27, 2005
473
0
166
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 <[email protected]>
>>  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)
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,218
463
I solved the problem without restart mysql but with # swapoff -a ... and # swapon -a
I am happy to see you were able to address the issue. Thank you for updating us with the outcome.