Help for - Optimize Mysql

Asked

Viewed 684 times

2

I’m having trouble optimizing my VPS Mysql that I use.

I have a plan on Ramnode with the following specs:

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

  • 4GB of Ram

  • 135 GB SSD Raid 10

I am having problems of slowness in one of the applications that I have hosted, sometimes gives up error max user connections.

Below is the test performed on Mysqltunner:

Mysqltuner 1.3.0 - Major Hayden

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 Index: 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 use Leds

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)

Follow my 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

If you can help me I appreciate :)

mysql server vps

  • Can’t the error be in your application? Follow the recommendations section in the file you posted, but I believe the problem is in the applications.

  • Exactly, I also think the problem is in the application. Run a show full processlistwhile slow and try to identify a query that is taking too long. Also use the explainMysql to understand and optimize the results. If you find any suspicions add to the question or ask another more specific.

1 answer

2

Browser other questions tagged mysql server vps

You are not signed in. Login or sign up in order to post.