MySQL with high CPU and memory usage

Question

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?

Answer 1

There is something you are not taking into account.

• MyISAM caches index pages into the Key Cache
• InnoDB caches data and index pages in the Buffer Pool
• I wrote a post about this : What are the main differences between InnoDB and MyISAM?

The 466092032 you gave is just the number of bytes taken by indexes. The InnoDB Storage Engine is moving data and index pages (each InnoDB page is 16K) in and out of the Buffer Pool due to its small size and the amount of data you are not accounting for.

Please refer back to my post. It will recommend how to size both key_buffer_size (which should be about 84M in you case) and the innodb_buffer_pool_size.

RECOMMENDATIONS

• Increase the VPS RAM enough to fully accommodate your data.
• Lower the read and write threads since you are in a VPS
• Disable the query cache because it slows down InnoDB : See my post Is the overhead of frequent query cache invalidation ever worth it?