MySQL with high CPU and memory usage

database-tuninginnodbMySQLmysql-5.5performance

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?

Best Answer

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?

Related Solutions

Mysql – innodb_flush_method=O_DIRECT vs O_DSYNC performance impact on ext3 with LVM disk partition

Back on January 21, 2009, Peter Zaitsev stated the following on mysqlperformanceblog.com

As the call for action – I would surely like someone to see if EXT3 can be fixed in this regard as it is by far the most common file system for Linux. It is also worth to investigate if something can be done on MySQL side – may be opening binlog with O_DSYNC flag if sync_binlog=1 instead of using fsync will help ? Or may be binlog pre-allocation would be good solution.

As of yet, I know of no one having touched this issue. O_DSYNC as a default is not an appealing prospect but does accommodate faster writes that are not really verified. That why there is so much hype around O_DIRECT.

I can tell you do not have the InnoDB Plugin installed. With the InnoDB Plugin, several variables should exist.

You should upgrade InnoDB in one of two ways:

Upgrade to MySQL 5.5 (All InnoDB Plugin enhancements are part of MySQL 5.5)
Upgrade the InnoDB Plugin

Once you have done so, you can enhance InnoDB to

access more CPUs and Cores
increase read and write I/O threads
scale the I/O capacity (this is especially needed for different storage media)

Here are my past posts on the settings you can change for this:

Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?
Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian

MySQL database drop insanely slow

I hate the checking permissions issue.

You may have to disable key checks before the DROP DATABASE

SET unique_checks = 0;
SET foreign_key_checks = 0;
SET GLOBAL innodb_stats_on_metadata = 0;
DROP DATABASE db_madeintouch;
SET GLOBAL innodb_stats_on_metadata = 1;
SET foreign_key_checks = 1;
SET unique_checks = 1;

UPDATE 2013-04-15 18:04 EDT

I just noticed you have innodb_file_per_table OFF. What gives ?

You currently have all the InnoDB data and the corresponding index sitting in a single file.
Any CREATE TABLE statement must make data dictionary updates and look for space (small but annoying in this instance)
Internal Fragmentation of ibdata1
Dropping a table means scanning the table and its indexes for availability to lock. With data and index pages possibly fragmented, this takes spindles, seek time, and latency.
See Pictorial Representation of ibdata1 to see everything that goes into ibdata1

Recommendation : Remove all Data and Index Pages from ibdata1

This will give ibdata1 a breather to handle just data dictionary and MVCC management. In addition, ibdata1 will stay rather lean and mean and can be read more quickly.

You will need to perform the InnoDB Infrastructure Cleanup. I wrote out all the steps back on October 29, 2010 in StackOverflow.

UPDATE 2013-04-22 08:10 EDT

Three suggestions

SUGGESTION 1 : I just noticed something else. You are using an ancient version of MySQL (5.0.45). You should think about upgrading to MySQL 5.6.11 as it performs significantly faster that MySQL 5.5 and way faster than MySQL 5.0.

SUGGESTION 2 : You should also go ahead and implement the InnoDB Infrastructure Cleanup.

SUGGESTION 3 : You should also check the disk itself. If the data is sitting on a RAID10 set, one of the disks may have an issues. Check the disk controller's battery as well because it can slow down disk caching and affect read performance.