I have just purchased a new dedicated server and currently using the defaults for MySQL and Apache. I'm looking to try and optimize the setup as best I can.
Here is the spec of the machine:
Intel Xeon E3 1225v2 3.25Ghz
4 Cores / 4 Threads
32GB RAM
CentOS 6.5 - 64-Bit
The spec is similar to my previous machine (i7-2600), except that had only 8GB RAM and this one has 32GB of RAM, but the server load seems to be generally higher - I did do some tweaks on the previous machine, and have made the same changes on this server.
Here is the contents of /etc/my.cnf
Even though I've made these changes (only recently - so cannot take complete notice of mysqltuner.pl for the results, it is showing:
[OK] Maximum possible memory usage: 2.1G (6% of installed RAM)
I would expect the server should be set to be allowed to use far more than a maxmimum of 2.1GB on a 32GB machine.
I have run /usr/bin/mysqltuner.pl and this is what is showing (for info only, as it has only been running for 45 minutes since I made the changes).
Thanks in advance,
Daniel
Here is the spec of the machine:
Intel Xeon E3 1225v2 3.25Ghz
4 Cores / 4 Threads
32GB RAM
CentOS 6.5 - 64-Bit
The spec is similar to my previous machine (i7-2600), except that had only 8GB RAM and this one has 32GB of RAM, but the server load seems to be generally higher - I did do some tweaks on the previous machine, and have made the same changes on this server.
Here is the contents of /etc/my.cnf
Code:
Code:
[mysqld]
innodb_file_per_table=1
local-infile=0
open_files_limit=10000
myisam_use_mmap=1
connect_timeout = 2
join_buffer_size=2M
read_rnd_buffer_size=4M
query_cache_type = 1
query_cache_size = 100M
query_cache_limit = 30M
max_heap_table_size = 100M
tmp_table_size = 100M
thread_cache_size = 50
table_open_cache = 2000
slow_query_log=0
slow_query_log_file=mysql-slow.log
long_query_time=0.1
key_buffer_size = 500M
concurrent_insert=2[OK] Maximum possible memory usage: 2.1G (6% of installed RAM)
I would expect the server should be set to be allowed to use far more than a maxmimum of 2.1GB on a 32GB machine.
I have run /usr/bin/mysqltuner.pl and this is what is showing (for info only, as it has only been running for 45 minutes since I made the changes).
Code:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.36-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 21M (Tables: 185)
[--] Data in InnoDB tables: 1M (Tables: 113)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[OK] Total fragmented tables: 0
-------- Performance Metrics -------------------------------------------------
[--] Up for: 46m 16s (9K q [3.418 qps], 2K conn, TX: 21M, RX: 1M)
[--] Reads / Writes: 92% / 8%
[--] Total buffers: 844.0M global + 8.4M per thread (151 max threads)
[OK] Maximum possible memory usage: 2.1G (6% of installed RAM)
[!!] Slow queries: 14% (1K/9K)
[OK] Highest usage of available connections: 3% (5/151)
[OK] Key buffer size / total MyISAM indexes: 500.0M/8.4M
[!!] Key buffer hit rate: 21.6% (19K cached / 14K reads)
[!!] Query cache efficiency: 18.2% (430 cached / 2K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (3 temp sorts / 1K sorts)
[OK] Temporary tables created on disk: 2% (9 on disk / 309 total)
[OK] Thread cache hit rate: 99% (5 created / 2K connections)
[!!] Table cache hit rate: 16% (134 open / 808 opened)
[OK] Open file limit used: 0% (7/10K)
[OK] Table locks acquired immediately: 100% (2K immediate / 2K locks)
[OK] InnoDB data size / buffer pool: 1.8M/128.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_limit (> 30M, or use smaller result sets)
table_cache (> 2000)Thanks in advance,
Daniel
Last edited:
