Hello
I am having issues with MYSQL and CPU+RAM usage
my server is
Intel(R) Xeon(R) CPU E3-1231 v3 @ 3.40GHz
4 cores/8 threads
32 GB ram
2x2TB raid0
my mysql configurations are:
And Mysqtuner.pl reports are :
what should i do now to minimize server + RAM usage. because i am using FASTCGI with custom configurations. otherwise on SUPHP, the server load goes from 4% to 60% instantly.
I am having issues with MYSQL and CPU+RAM usage
my server is
Intel(R) Xeon(R) CPU E3-1231 v3 @ 3.40GHz
4 cores/8 threads
32 GB ram
2x2TB raid0
my mysql configurations are:
Code:
[mysqld]
local-infile=0
default-storage-engine=InnoDB
max_connections = 300
# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
sort_buffer_size = 1M
join_buffer_size = 2M
# CACHES AND LIMITS #
query_cache_limit = 10M
query_cache_size = 1024M
query_cache_type = 1
tmp-table-size = 512M
max-heap-table-size = 512M
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 8192
table-open-cache = 10240
# INNODB CONFIGURATION #
innodb_buffer_pool_size = 9G
innodb_log_file_size = 512M
innodb_log_buffer_size = 7M
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
innodb_buffer_pool_instances = 9
innodb_file_per_table=1
[mysqldump]
quick
max_allowed_packet = 16MAnd Mysqtuner.pl reports are :
Code:
[email protected] [~]# ./mysqltuner.pl
>> MySQLTuner 1.6.7 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Modified by George Liu (eva2000) at http://vbtechsupport.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.29
[OK] Operating on 64-bit architecture
-------- System Linux Recommendations ---------------------------------------
Look for related Linux system recommandations
[--] There is 43 listening port(s) on this server.
[!!] There is too many listening ports: 43 > 10
[!!] There is Apache like server running on 80 or 443 port.
[OK] No Application server runing on 8080 or 8443 port.
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 11M (Tables: 34)
[--] Data in InnoDB tables: 8G (Tables: 7229)
[--] Data in CSV tables: 0B (Tables: 4)
[--] Data in MEMORY tables: 0B (Tables: 12)
[!!] Total fragmented tables: 547
-------- Security Recommendations -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User '[email protected]' has user name as password.
[!!] User '[email protected]%' hasn't specific host restriction.
[!!] User '[email protected]%' hasn't specific host restriction.
[!!] There is no basic password file list!
-------- CVE Security Recommendations ---------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 0h 21m 25s (66M q [380.327 qps], 1M conn, TX: 491G, RX: 9G)
[--] Reads / Writes: 94% / 6%
[--] Binary logging is disabled
[--] Total buffers: 10.5G global + 2.6M per thread (300 max threads)
[OK] Maximum reached memory usage: 10.7G (34.25% of installed RAM)
[OK] Maximum possible memory usage: 11.3G (36.31% of installed RAM)
[OK] Slow queries: 0% (43/66M)
[OK] Highest usage of available connections: 16% (49/300)
[OK] Aborted connections: 0.00% (11/1560518)
[!!] Query cache should be disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (281 temp sorts / 3M sorts)
[!!] Joins performed without indexes: 9648
[!!] Temporary tables created on disk: 52% (1M on disk / 2M total)
[OK] Thread cache hit rate: 99% (49 created / 1M connections)
[OK] Table cache hit rate: 98% (8K open / 8K opened)
[OK] Open file limit used: 0% (131/65K)
[OK] Table locks acquired immediately: 99% (18M immediate / 18M locks)
-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.
-------- Performance schema --------------------------------------------------
[--] Performance schema is enabled.
-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 24.3% (8M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/3.5M
[OK] Read Key buffer hit rate: 99.9% (2M cached / 1K reads)
[OK] Write Key buffer hit rate: 97.8% (195K cached / 4K writes)
-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.
-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 9.0G/8.4G
[OK] InnoDB buffer pool instances: 9
[!!] InnoDB Used buffer: 38.74% (228513 used/ 589815 total)
[OK] InnoDB Read buffer efficiency: 100.00% (9858875877 hits/ 9859028204 total)
[!!] InnoDB Write Log efficiency: 82.70% (4580780 hits/ 5538873 total)
[!!] InnoDB log waits: 0.00% (2 waits / 958093 writes)
-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.
-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
-------- Recommendations -----------------------------------------------------
General recommendations:
Consider dedicating a server for your database installation with less services running on !
Consider dedicating a server for Web server in production !
Run OPTIMIZE TABLE to defragment tables for better performance
Set up a Secure Password for [email protected] ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Restrict Host for [email protected]% to [email protected]
Adjust your join queries to always utilize indexes. Please note this
calculation is made by adding Select_full_join + Select_range_check
status values and triggered when the total >250
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
query_cache_type (=0)
join_buffer_size (> 1.0M, or always use indexes with joins)
innodb_log_buffer_size (>= 6M)what should i do now to minimize server + RAM usage. because i am using FASTCGI with custom configurations. otherwise on SUPHP, the server load goes from 4% to 60% instantly.
