here we have a VPS with 3.2GHz quadcore processor with 2GB of RAM and 4GB of SWAP which need to work with a high intensive legacy system (read: bad coded). The main problem is that the CPU (~80 - 100%) and memory usage (about 50%) are going to the sky, even in a not so intensive usage.
Current /etc/my.cnf
[mysqld]
query_cache_type = ON
slow_query_log = OFF
long_query_time = 1
innodb_buffer_pool_size = 1GB
join_buffer_size = 16MB
key_buffer_size = 128MB
table_open_cache = 100000
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
max_user_connections = 800
max_connections = 800
slow_launch_time = 1
thread_cache_size = 4
query_cache_size = 512MB
MySQL version
mysql> SELECT VERSION();
+------------+
| VERSION() |
+------------+
| 5.5.34-cll |
+------------+
1 row in set (0.00 sec)
MyISAM index length
mysql> SELECT SUM(index_length) FROM information_schema.tables WHERE engine = 'MyISAM';
+-------------------+
| SUM(index_length) |
+-------------------+
| 82627584 |
+-------------------+
1 row in set, 2 warnings (4.55 sec)
InnoDB index length
mysql> SELECT SUM(index_length) FROM information_schema.tables WHERE ENGINE = 'InnoDB';
+-------------------+
| SUM(index_length) |
+-------------------+
| 466092032 |
+-------------------+
1 row in set, 2 warnings (2.62 sec)
Any can guide-me on what is the first step to solve or ease this issue?