Advice Ajustando meu servidor

1

como muitos outros Eu aprendo muitas informações de você no passado e agora eu consigo rodar servidor de forma independente, agora sou novo em sql tuning e andei pesquisando pela web um tempo agora e tuning parece ser uma tarefa difícil Eu apreciaria algum conselho / ajuda. Alguém pode comentar sobre os resultados abaixo são bons, ruins ou piores.

Eu baixei e lancei o mysqltuner.pl e o resultado é:

    -------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 5h 10m 1s (488K q [4.654 qps], 9K conn, TX: 2B, RX: 114M)
[--] Reads / Writes: 85% / 15%
[--] Total buffers: 522.0M global + 1.1M per thread (151 max threads)
[OK] Maximum possible memory usage: 691.9M (8% of installed RAM)
[OK] Slow queries: 0% (1/488K)
[OK] Highest usage of available connections: 5% (9/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/729.0K
[OK] Key buffer hit rate: 100.0% (465K cached / 30 reads)
[OK] Query cache efficiency: 70.9% (305K cached / 431K selects)
[!!] Query cache prunes per day: 38311
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 35K sorts)
[!!] Joins performed without indexes: 1369
[!!] Temporary tables created on disk: 58% (6K on disk / 11K total)
[OK] Thread cache hit rate: 99% (9 created / 9K connections)
[OK] Table cache hit rate: 33% (2K open / 5K opened)
[OK] Open file limit used: 3% (160/5K)
[OK] Table locks acquired immediately: 100% (222K immediate / 222K locks)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB BufferPool Size :450.0M
[--] InnoDB BufferPool Inst :1
[OK] InnoDB buffer pool / data size: 450.0M/310.3M
[OK] InnoDB buffer pool instances: 1
[OK] InnoDB log waits: 0

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_size (> 16M)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)

my.cnf como segue

mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

query_cache_type = 1
query_cache_size= 16777216
query_cache_limit=1048576

innodb_buffer_pool_instances =1
innodb_buffer_pool_size = 450M

tmp_table_size      = 32M
max_heap_table_size = 32M
join_buffer_size=256K

skip-host-cache
skip-name-resolve
explicit_defaults_for_timestamp=TRUE

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

e abaixo das informações do servidor e da memória

processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 0.9.1
stepping        : 3
cpu MHz         : 2000.074
cache size      : 32 KB
fpu             : yes
fpu_exception   : yes
cpuid level     : 4
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good unfair_spinlock pni hypervisor
bogomips        : 4000.14
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 1
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 0.9.1
stepping        : 3
cpu MHz         : 2000.074
cache size      : 32 KB
fpu             : yes
fpu_exception   : yes
cpuid level     : 4
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good unfair_spinlock pni hypervisor
bogomips        : 4000.14
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 2
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 0.9.1
stepping        : 3
cpu MHz         : 2000.074
cache size      : 32 KB
fpu             : yes
fpu_exception   : yes
cpuid level     : 4
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good unfair_spinlock pni hypervisor
bogomips        : 4000.14
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 3
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 0.9.1
stepping        : 3
cpu MHz         : 2000.074
cache size      : 32 KB
fpu             : yes
fpu_exception   : yes
cpuid level     : 4
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good unfair_spinlock pni hypervisor
bogomips        : 4000.14
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 4
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 0.9.1
stepping        : 3
cpu MHz         : 2000.074
cache size      : 32 KB
fpu             : yes
fpu_exception   : yes
cpuid level     : 4
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good unfair_spinlock pni hypervisor
bogomips        : 4000.14
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 5
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 0.9.1
stepping        : 3
cpu MHz         : 2000.074
cache size      : 32 KB
fpu             : yes
fpu_exception   : yes
cpuid level     : 4
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good unfair_spinlock pni hypervisor
bogomips        : 4000.14
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

e memória como segue:

 free
             total       used       free     shared    buffers     cached
Mem:       8060476    7057992    1002484          0     305180    4631680
-/+ buffers/cache:    2121132    5939344
Swap:      1048568     383800     664768

show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
6 rows in set (0.00 sec)

 SHOW STATUS LIKE 'qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1597    |
| Qcache_free_memory      | 4456848 |
| Qcache_hits             | 313793  |
| Qcache_inserts          | 120859  |
| Qcache_lowmem_prunes    | 48666   |
| Qcache_not_cached       | 6298    |
| Qcache_queries_in_cache | 3869    |
| Qcache_total_blocks     | 10227   |
+-------------------------+---------+
8 rows in set (0.00 sec)

Estou usando o Mysql 5.6.12 Agradeço antecipadamente por sua ajuda STef

Percona conf

# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
# Configuration name server-8 generated for mail.com at 2015-07-09 23:48:22

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve
sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now                 = 1
innodb                         = FORCE
innodb-strict-mode             = 1

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 10240

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 6G

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log
    
por DiPolo 09.07.2015 / 11:24

0 respostas