Help with MySQL optimization

Question

MySQL

There is a dedicated server with two VPSs on it. On one of them, the load is about 2000 hosts per day.

config is:
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
max_connections = 150
wait_timeout = 20000
interactive_timeout = 20000
skip-external-locking
key_buffer_size = 32M
query_cache_size = 128M
join_buffer_size = 8M
tmp_table_size = 64M
max_heap_table_size = 64M
thread_cache_size = 4
max_allowed_packet = 1M
table_cache = 30720
#table_open_cache = 64000
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
#default-character-set = cp1251
init_connect = "SET NAMES 'cp1251'"
long_query_time = 10
log-slow-queries = /var/log/mysqld-slow-query.log
log-queries-not-using-indexes
skip-bdb
skip-innodb Server

memory:
Mem: 2098968k total, 576912k used, 1522056k free, 0k buffers

mysql> SHOW STATUS;
+-----------------------------------+------------- +
| variable_name | value |
+-----------------------------------+------------- +
| Aborted_clients | 4 |
| Aborted_connects | 437654 |
| Binlog_cache_disk_use | 0 |
| binlog_cache_use | 0 |
| bytes_received | 148 |
| bytes_sent | 336 |
| Com_select | 1 |
| Com_show_status | 2 |
| compression | OFF |
| connections | 6605115 |
| Created_tmp_files | 981624 |
| Created_tmp_tables | 1 |
| flush_commands | 1 |
| handler_write | 132 |
| Key_blocks_used | 28995 |
| Key_read_requests | 63806241894 |
| key_reads | 76824130 |
| Key_write_requests | 127477205 |
| key_writes | 47262433 |
| last_query_cost | 0.000000 |
| Max_used_connections | 66 |
| open_files | 4915 |
| Open_tables | 2948 |
| Qcache_free_blocks | 10967 |
| Qcache_free_memory | 37494008 |
| Qcache_hits | 48773973 |
| Qcache_inserts | 31843780 |
| Qcache_lowmem_prunes | 5234025 |
| Qcache_not_cached | 7450097 |
| Qcache_queries_in_cache | 42978 |
| Qcache_total_blocks | 99926|
| Queries | 131748067 |
| Questions | 3 |
| Rpl_status | NULL |
| select_scan | 1 |
| Slave_running | OFF |
| table_locks_immediate | 64940767 |
| table_locks_waited | 1065540 |
| threads_connected | 5 |
| threads_created | 598470 |
| Threads_running | 3 |
| uptime | 2162098 |
+-----------------------------------+------------- +
248 rows in set (0.00 sec)

Run mysqltuner.pl and this is what I got

- General Statistics - [--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.95-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

- Storage Engine Statistics - [--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4G (Tables: 1957)
[--] Data in MEMORY tables: 762K (Tables: 6)
[ !!] Total fragmented tables: 105

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

- Performance Metrics - [--] Up for: 25d 0h 17m 48s (131M q [60.941 qps], 6M conn, TX: 1B , RX: 1B)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 234.0M global + 8.9M per thread (150 max threads)
[OK] Maximum possible memory usage: 1.5G (76% of installed RAM)
[OK] Slow queries: 2% (3M/131M)
[OK] Highest usage of available connections: 44% (66/150)
[OK] Key buffer size / total MyISAM indexes: 32.0M/837.3M
[OK] Key buffer hit rate: 99.9% (63B cached / 76M reads)
[OK] Query cache efficiency: 55.6% (48M cached / 87M selects)
[!!] Query cache prunes per day: 209257
[!!] Sorts requiring temporary tables: 30% (1M temp sorts / 4M sorts)
[!!] Joins performed without indexes: 137119
[OK] Temporary tables created on disk: 16% (898K on disk / 5M total)
[OK] Thread cache hit rate: 90% (598K created / 6M connections)
[OK] Table cache hit rate: 86% (2K open / 3K opened)
[OK] Open file limit used: 7% (4K/61K)
[OK] Table locks acquired immediately: 98% (64M immediate / 65M locks)
[!!] Connections aborted: 6%

- Recommendations - General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_size (> 128M)
sort_buffer_size (> 256K)
read_rnd_buffer_size (> 256K)
join_buffer_size (> 8.0M, or always use indexes with joins)

Answer 1

I don’t even know how to hint to you so that you don’t get offended. There is nothing in your question that would not become obvious if you read at least a minimal amount of MySQL administration manuals. Moreover, the text that you cited contains indications of the necessary changes. Moreover, both variables and recommended values ​​are indicated in this way. Any advice here will be "captain's", for example, "look at what queries you have going past the indexes (log-slow-queries = /var/log/mysqld-slow-query.log) and put the indexes on the required fields (Adjust your join queries to always utilize indexes).

Answer 2

Tuner asks:

1. Change OS to 64 bit
2. Optimize 105 tables
3. Put in settings:
   
   • query_cache_size = 256M
   • sort_buffer_size = 2M
   • read_rnd_buffer_size = 8M
   • join_buffer_size = 8M

He also asks to find queries where you use JOIN, and there are no indexes in the ON construction, respectively. fields.