Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Sounds te me to you should really look into your queries.. you should enable slow query log and look for long running queries without indexes and qeuries what combines JOINS with GROUP BY/ORDER BY these queries can be heavy CPU/RAM bound –  Raymond Nijland Dec 24 '13 at 10:55

1 Answer 1

up vote 4 down vote accepted

There is something you are not taking into account.

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

share|improve this answer
    
+1 for increased insight into the strange world that is mySQL :) –  Zane Dec 23 '13 at 18:17
    
Should query_cache_type = OFF complete the 3rd recommendation? –  Gabriel Santos Dec 23 '13 at 19:06
1  
Set query_cache_size = 0 –  RolandoMySQLDBA Dec 23 '13 at 19:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.