-2

I've a bit in a problem optimizing my VPS's (Linux / Cpanel) mysql settings. I don'n know much about Mysql and I'm using this VPS for about 50 different websites. They all had below 100 visitors daily, so traffic not much my problem. My VPS working well at the moment but I'm worrying about [!!] warnings in mysqltuner.pl, do I need to worry?

=> My VPS Info:

Processor Information
**Total processors: 6**
Processor #1
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) CPU E5-2620 v3 @ 2.40GHz
Speed
2394.454 MHz
Cache
15360 KB
Processor #2
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) CPU E5-2620 v3 @ 2.40GHz
Speed
2394.454 MHz
Cache
15360 KB
Processor #3
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) CPU E5-2620 v3 @ 2.40GHz
Speed
2394.454 MHz
Cache
15360 KB
Processor #4
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) CPU E5-2620 v3 @ 2.40GHz
Speed
2394.454 MHz
Cache
15360 KB
Processor #5
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) CPU E5-2620 v3 @ 2.40GHz
Speed
2394.454 MHz
Cache
15360 KB
Processor #6
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) CPU E5-2620 v3 @ 2.40GHz
Speed
2394.454 MHz
Cache
15360 KB

Memory Information
Memory: 30789880k/**32505856k** available (5336k kernel code, 1050056k absent, 665920k reserved, 7016k data, 1288k init)

System Information
Linux xl.xlxlxl.com 2.6.32-504.12.2.el6.x86_64 #1 SMP Wed Mar 11 22:03:14 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

Current Memory Usage
             total       used       free     shared    buffers     cached
Mem:      30812400   30064332     748068        244    3883652   17129112
-/+ buffers/cache:    9051568   21760832
Swap:            0          0          0
Total:    30812400   30064332     748068

=> My.cnf:

[mysqld]
max_allowed_packet=268435456
open_files_limit=10000
query_cache_type=1
query_cache_size=768M
query_cache_limit=2048M
sort_buffer_size=4M
join_buffer_size=26M
read_rnd_buffer_size = 1M
max_heap_table_size = 1024M
tmp_table_size = 1024M
table_open_cache = 15000
innodb_log_buffer_size = 64M
max_connections=200
wait_timeout=26000
interactive_timeout=26000
innodb_buffer_pool_size=256M

=> Mysqltuner.pl:

 >>  MySQLTuner 1.4.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.6.23
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 3G (Tables: 3156)
[--] Data in InnoDB tables: 183M (Tables: 1455)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 5

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 20h 23m 59s (11M q [45.850 qps], 381K conn, TX: 85B, RX: 2B)
[--] Reads / Writes: 82% / 18%
[--] Total buffers: 2.1G global + 31.4M per thread (200 max threads)
[OK] Maximum possible memory usage: 8.2G (27% of installed RAM)
[OK] Slow queries: 0% (4/11M)
[OK] Highest usage of available connections: 7% (14/200)
[OK] Key buffer size / total MyISAM indexes: 8.0M/432.3M
[OK] Key buffer hit rate: 99.6% (493M cached / 1M reads)
[OK] Query cache efficiency: 59.6% (5M cached / 8M selects)
[!!] Query cache prunes per day: 143463
[OK] Sorts requiring temporary tables: 0% (16 temp sorts / 434K sorts)
[!!] Joins performed without indexes: 2610
[!!] Temporary tables created on disk: 83% (458K on disk / 550K total)
[OK] Thread cache hit rate: 99% (36 created / 381K connections)
[!!] Table cache hit rate: 13% (1K open / 12K opened)
[OK] Open file limit used: 1% (324/30K)
[OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
[!!] Connections aborted: 18%
[OK] InnoDB buffer pool / data size: 256.0M/183.9M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    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
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_size (> 768M) [see warning above]
    join_buffer_size (> 26.0M, or always use indexes with joins)
    table_open_cache (> 15000)
exspet
  • 1

1 Answers1

2

It's the idea of mysqltuner to recommend actions for better performance. The optimize table will care about fragmentation. Optimize on big tables takes some time though, while the table you are optimizing is locked. Generally the warning notes prefixed with [!!] are commented in the General recommendations section.

Before you tune the default parameters of mysql, you should check your sql statements and your sql connections. Joins without indexes lead to major performance penalties. Try to use slow_queries to indentify which queries to optimize.

Also the result set size is often much too big. Think of a shop system with a database.

Example

Say you have 10,000 items in the shop system, then a select * from items; call will return all 10,000 items, so your result set size is 10,000. But it's quite unlikely that you really need all 10,000 items at once.

The main purpose of SQL actually is to reduce the result set using filters, that operate most effectively on indexes. For example you are searching for all items that have a yellow color, you reduce the result set size by select * from items where color="yellow";

But if you have some hundred yellow items, that are still too many to get displayed on a web page. So you limit your results

select * from items where color="yellow" limit 1,10

for the first page of 10 items

select * from items where color="yellow" limit 11,20

for the next page and so on ... .

So I would recommend to tune your statements before you tune your database, as you will gain much more preformance if you correct table indexes and limit large result sets. That's why mysqltuner spits out these warnings.

One of the most usefull tools to debug joins and indexes is to use explain in front of your select statements:

explain select i.name, l.address
    from items i
         inner join location l on i.location = l.id
    where l.description like '%universe%';

will show you the the number of table rows with and without indexes that are used to compile your result.

ikrabbe
  • 151
  • 7