Otimizar MySQL

[luizdsouza3]

Pessoal bom dia.

Estou com problemas para otimizar meu MySQL do VPS que uso.

Tenho um plano na RamNode com as seguintes specs:

 

- Intel® Xeon® CPU E3-1240 V2 @ 3.40GHz (4 Cores)

- 4GB de Ram

- 135 GB SSD Raid 10

 

Estou com problemas de lentidão em uma das aplicações que eu tenho hospedada, as vezes dá até erro max user connections.

 

Segue abaixo o teste realizado no MySQLTunner:

 

>>  MySQLTuner 1.3.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.5.42-cll

[OK] Operating on 64-bit architecture

 

-------- Storage Engine Statistics -------------------------------------------

[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM

[--] Data in MyISAM tables: 136M (Tables: 300)

[--] Data in InnoDB tables: 44M (Tables: 202)

[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)

[!!] Total fragmented tables: 220

 

-------- Security Recommendations  -------------------------------------------

[OK] All database users have passwords assigned

 

-------- Performance Metrics -------------------------------------------------

[--] Up for: 1d 20h 25m 13s (3M q [23.681 qps], 251K conn, TX: 9B, RX: 605M)

[--] Reads / Writes: 57% / 43%

[--] Total buffers: 528.0M global + 3.6M per thread (400 max threads)

[OK] Maximum possible memory usage: 1.9G (48% of installed RAM)

[OK] Slow queries: 0% (17/3M)

[OK] Highest usage of available connections: 11% (47/400)

[OK] Key buffer size / total MyISAM indexes: 256.0M/23.3M

[OK] Key buffer hit rate: 100.0% (49M cached / 5K reads)

[OK] Query cache efficiency: 76.0% (1M cached / 2M selects)

[!!] Query cache prunes per day: 7322

[!!] Sorts requiring temporary tables: 69% (144K temp sorts / 208K sorts)

[!!] Joins performed without indexes: 21719

[OK] Temporary tables created on disk: 0% (4K on disk / 574K total)

[OK] Thread cache hit rate: 99% (47 created / 251K connections)

[OK] Table cache hit rate: 87% (1K open / 1K opened)

[OK] Open file limit used: 6% (1K/15K)

[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)

[OK] InnoDB buffer pool / data size: 128.0M/44.1M

[OK] InnoDB log waits: 0

-------- Recommendations -----------------------------------------------------

General recommendations:

    Run OPTIMIZE TABLE to defragment tables for better performance

    Enable the slow query log to troubleshoot bad queries

    Adjust your join queries to always utilize indexes

Variables to adjust:

    query_cache_size (> 64M)

    sort_buffer_size (> 2M)

    read_rnd_buffer_size (> 236K)

    join_buffer_size (> 128.0K, or always use indexes with joins)

 

 

Segue meu My.CNF

[mysqld]

max_connections = 400

max_user_connections=40

key_buffer_size = 256M

myisam_sort_buffer_size = 16M

read_buffer_size = 1M

table_open_cache = 2048

thread_cache_size = 128

wait_timeout = 20

connect_timeout = 10

tmp_table_size = 128M

max_heap_table_size = 64M

max_allowed_packet=268435456

net_buffer_length = 5500

max_connect_errors = 10

concurrent_insert = 2

read_rnd_buffer_size = 242144

bulk_insert_buffer_size = 2M

query_cache_limit = 2M

query_cache_size = 64M

query_cache_type = 1

query_prealloc_size = 87382

query_alloc_block_size = 21845

transaction_alloc_block_size = 2730

transaction_prealloc_size = 1364

max_write_lock_count = 2

log-error

external-locking=FALSE

open_files_limit=15000

 

default-storage-engine=MyISAM

innodb_file_per_table=1

[mysqld_safe]

 

[mysqldump]

quick

max_allowed_packet = 8M

 

[isamchk]

key_buffer = 128M

sort_buffer = 128M

read_buffer = 64M

write_buffer = 64M

 

[myisamchk]

key_buffer = 128M

sort_buffer = 128M

read_buffer = 64M

write_buffer = 64M

 

#### Per connection configuration ####

sort_buffer_size = 2M

join_buffer_size = 2M

thread_stack = 192K

log-slow-queries

Se puderem me ajudar eu agradeço :)

[Jesmarcelo]

O que está acontecendo, o load está subindo? As querys estão demorando para retornar o resultado?

[luizdsouza3]

O que está acontecendo, o load está subindo? As querys estão demorando para retornar o resultado?

 

Querys demorando e dão timeout.

Algumas nem rodam, ficam travadas sem retorno.

Load não sobe pois está sob controle.

Estou tbm com uma lentidão absurda em algumas páginas.

[Jesmarcelo]

Já verificou se o problema não está na estrutura da base? As tabelas possuem campos indexados? Um simples índice pode resolver vários problemas.

[luizdsouza3]

Já verificou se o problema não está na estrutura da base? As tabelas possuem campos indexados? Um simples índice pode resolver vários problemas.

Joins performed without indexes: 21719

Joins não tem Indexação e não faço a minima idéia de como forçar isso.

[Jesmarcelo]

Não tem que forçar, você precisa verificar quais campos precisam de indice e criar.

[luizdsouza3]

Não tem que forçar, você precisa verificar quais campos precisam de indice e criar.

Converti as tabelas da aplicação para InnoDB e já melhorou bastante a performace.

Até as Query que estavam travando já voltaram a funcionar muito mais rápido que antes.

 

Preciso realizar outros ajustes e certamente os indices vão ajudar bastante :)

[SHBR]

Em 30/03/2015 em 10:33, luizdsouza3 disse:

Pessoal bom dia.

Estou com problemas para otimizar meu MySQL do VPS que uso.

Tenho um plano na RamNode com as seguintes specs:

 

- Intel® Xeon® CPU E3-1240 V2 @ 3.40GHz (4 Cores)

- 4GB de Ram

- 135 GB SSD Raid 10

 

Pode me dizer de qual, empresa é a vps ?