How to Optimize MySQL

[joe94500]

Hello. This is my first post. I recently upgraded to my first vps. I notice my server is a bit slow.
my.config is as follows:

[mysqld]

default-storage-engine=MyISAM
interactive_timeout=300
key_cache_block_size=4096
max_heap_table_size=32M
max_join_size=1000000000
max_allowed_packet=32M

open_files_limit=4096
query_cache_size=32M
thread_cache_size=100
tmp_table_size=32M


wait_timeout=7800
max_user_connections=5000

myisam_recover_options=FORCE
innodb_file_per_table=1

innodb_flush_log_at_trx_commit=0
innodb_purge_threads=1
innodb_support_xa=0

innodb_thread_concurrency=8
pid-file=/var/lib/mysql/mysqld.pid

How can it be configured for maximum performance?
Thank you.

 

[cPanelMichael]

Hello

Please review the following thread in order to review the information needed to provide you with optimization assistance:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Thank you.

 

[thinkbot]

run mysqltuner.pl

 

[joe94500]

run mysqltuner.pl

Hello, thanks cPanelMichael and thinkbot for answering my question and pointing me in the right direction. I
I have a table with more than a million rows. When trying to go to one of the bottom rows, I get this error:
Error writing file '/tmp/MYOV5w52' (Errcode: 28).

server summary

Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        30G   21G  7.5G  74% /
tmpfs           939M     0  939M   0% /dev/shm
/usr/tmpDSK     3.0G  176M  2.6G   7% /tmp


my.cnf (This is current mycnf)

[mysqld]
tmpdir=/tmp
innodb_file_per_table=1
open_files_limit=50000
myisam_use_mmap=1

max_connections = 200
max_user_connections = 75

join_buffer_size=2M
sort_buffer_size=2M

table_open_cache = 8000
table_definition_cache = 6000
max_allowed_packet = 1G

thread_cache_size = 384

query_cache_type = 1
query_cache_size = 100M
query_cache_limit = 1M

max_heap_table_size = 50M
tmp_table_size = 300M

key_buffer_size = 2500M

innodb_buffer_pool_size = 3G
innodb_log_buffer_size = 32M
innodb_old_blocks_time=1000
innodb_concurrency_tickets=5000
innodb_autoextend_increment=64M
innodb_open_files=2000
innodb_stats_on_metadata=0

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1

mysqltuner result

- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.35-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 6G (Tables: 293)
[--] Data in InnoDB tables: 224K (Tables: 14)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 45

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 37m 24s (909 q [0.405 qps], 127 conn, TX: 608K, RX: 75K)
[--] Reads / Writes: 68% / 32%
[--] Total buffers: 5.6G global + 4.6M per thread (200 max threads)
[!!] Maximum possible memory usage: 6.5G (356% of installed RAM)
[OK] Slow queries: 4% (39/909)
[OK] Highest usage of available connections: 6% (13/200)
[OK] Key buffer size / total MyISAM indexes: 2.4G/2.4G
[!!] Key buffer hit rate: 85.3% (685 cached / 101 reads)
[OK] Query cache efficiency: 47.4% (225 cached / 475 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 12 sorts)
[OK] Temporary tables created on disk: 11% (2 on disk / 17 total)
[OK] Thread cache hit rate: 89% (13 created / 127 connections)
[OK] Table cache hit rate: 98% (379 open / 386 opened)
[OK] Open file limit used: 1% (666/50K)
[!!] Table locks acquired immediately: 93%
[OK] InnoDB data size / buffer pool: 224.0K/3.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Optimize queries and/or use InnoDB to reduce lock wait

What is the best way to proceed to optimize the mysql? NB: I don't understand plenty of this stuff.