Optimization help with 4 gig ram server
Optimization help with 4 gig ram
Total processors: 2
For mysql
System Information
System Item Details Status
Server Load 0.04 (2 CPUs)
Memory Used 20.03% (809,672 of 4,042,836)
Swap Used 0% (0 of 524,284)
Disk Information
Device Mount Point Usage
/tmp /var/tmp 3% (69,064 of 2,309,072)
/usr/tmpDSK /tmp 3% (69,064 of 2,309,072)
/dev/vda1 / 26% (15,816,052 of 61,927,356) [mysqld]
## connection settings
default_storage_engine=MyISAM
max_connect_errors=10
max_connections=250
wait_timeout=60
connect_timeout=20
interactive_timeout=60
thread_concurrency=4
## cache settings
query_cache_size=128M
query_cache_limit=6M
query_cache_type=1
table_open_cache=2024
table_definition_cache =1600
thread_cache_size=128
## buffer sizes
key_buffer=320M
sort_buffer_size=256k
read_buffer_size=1M
join_buffer_size=4M
read_rnd_buffer_size = 2M
aria_pagecache_buffer_size = 16m
aria_sort_buffer_size = 16m
## tmpdir / temp table sizes
tmp_table_size=256M
max_heap_table_size=256M
## misc. settings
datadir=/var/lib/mysql
skip-external-locking
server-id = 1
open-files-limit = 16384
max_allowed_packet=268435456
## innodb settings
innodb_data_file_path = ibdata1:10M:autoextend
innodb_thread_concurrency = 2
innodb_buffer_pool_size = 96M
innodb_log_buffer_size = 4M
innodb_file_per_table = 1
## slow query logging
long_query_time =0.1
slow_query_log =1
log_slow_queries=/var/lib/mysql/slow-queries.log
#log-long-format
open_files_limit=10000
[mysqld_safe]
open-files-limit = 16384
[mysqldump]
quick
max_allowed_packet=32M
>> MySQLTuner 1.3.0 - Major Hayden
>> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major
>> Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.40-MariaDB-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 310M (Tables: 402)
[--] Data in InnoDB tables: 1M (Tables: 79)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 1M (Tables: 2)
[!!] Total fragmented tables: 27
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 2h 49m 5s (83K q [8.258 qps], 3K conn, TX: 783M, RX: 23M)
[--] Reads / Writes: 89% / 11%
[--] Total buffers: 812.0M global + 7.5M per thread (250 max threads)
[OK] Maximum possible memory usage: 2.6G (68% of installed RAM)
[OK] Slow queries: 0% (1/83K)
[OK] Highest usage of available connections: 2% (7/250)
[OK] Key buffer size / total MyISAM indexes: 320.0M/21.5M
[OK] Key buffer hit rate: 99.5% (413K cached / 2K reads)
[OK] Query cache efficiency: 40.0% (44K cached / 111K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3K sorts)
[!!] Joins performed without indexes: 652
[!!] Temporary tables created on disk: 34% (1K on disk / 4K total)
[OK] Thread cache hit rate: 99% (7 created / 3K connections)
[OK] Table cache hit rate: 97% (556 open / 570 opened)
[OK] Open file limit used: 5% (885/16K)
[OK] Table locks acquired immediately: 99% (46K immediate / 46K locks)
[OK] InnoDB buffer pool / data size: 96.0M/1.7M
[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:
join_buffer_size (> 4.0M, or always use indexes with joins)-
Hello :) It's a good idea to let MySQL run for at least 24 hours before running a tuner to ensure more accurate results. Thank you. 0
Please sign in to leave a comment.
Comments
1 comment