2

I just setup a new server for a large MySQL database of about 70GB. This database is regularly written by automatic processes that must write as quickly as they can their data. Before we had a server with 120GB SSD but we switched to HDD because the amount of data is getting bigger and bigger.

The problem is that when the processes are running, the CPU spikes to more than 150% and write operations become deadly slow...

The server has a 4 cores - 8 thread CPU, 32GB RAM and 2x2TB HDD with a LSI 2108 RAID controller (RAID 1). MariaDB 10.0 is the only server running on this machine. OS is Ubuntu 14.04 freshly installed. It has a slave server slightly less powerful, that's why I enabled binlogs.

I tuned InnoDB setup like that :

query_cache_type        = OFF
tmp_table_size          = 1G
max_heap_table_size     = 1G
transaction-isolation   = READ-COMMITTED
binlog_format           = row
innodb_log_file_size    = 6G
innodb_buffer_pool_size = 24G
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_open_files       = 400
innodb_io_capacity      = 300
innodb_io_capacity_max  = 400
innodb_flush_method     = O_DIRECT
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout= 240
innodb_use_fallocate = 1
innodb_random_read_ahead = 1
innodb_flush_neighbors = 0
innodb_checksum_algorithm = crc32
innodb_fast_shutdown    = 0
skip-innodb_doublewrite

While the processes are running, the slow log are full of these lines (highlighted query is random, could be insert, update or delete on any table) :

# User@Host: user_prod[user_prod] @ xxxxx [xxx.xxx.xxx.xxx]
# Thread_id: 177018  Schema: user_prod  QC_hit: No
# Query_time: 18.318539  Lock_time: 0.000026  Rows_sent: 0  Rows_examined: 1
SET timestamp=1413450644;
update `pages_objects` set `status_comments` = 'idle', `updated_at` = '2014-10-16 09:10:57' where `id` = '331667763652878';

I'm stuck and don't find any help on Google... Do you have any idea where the problem could come ? Thanks :)

Edit : A sample of processlist while my CPU is spiking to 250% (hell yeah !) :

+--------+--------------+---------------------------------+--------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+----------+
| Id     | User         | Host                            | db           | Command | Time | State          | Info                                                                                                 | Progress |
+--------+--------------+---------------------------------+--------------+---------+------+----------------+--------------------------------    ----------------------------------------------------------------------+----------+
|    378 | user_prod | server.ip:46542 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
|   2985 | user_prod | server.ip:60257 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
|   4001 | user_prod | server.ip:38046 | user_prod | Execute |    0 | preparing      | select * from `pages_users` where `user_id` = '1247143319' and `page_id` = '169449309753828' limit 1 |    0.000 |
|   6533 | user_prod | server.ip:54548 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
|   7582 | user_prod | server.ip:59995 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
|  13179 | user_prod | server.ip:33221 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
|  14624 | user_prod | server.ip:41004 | user_prod | Execute |    0 | Writing to net | select * from `pages_users` where `user_id` = '100000010909375' and `page_id` = '476930419093906' li |    0.000 |
|  54642 | user_prod | server.ip:45540 | user_prod | Execute |    0 | update         | insert into `pages_users` (`user_id`, `page_id`, `updated_at`, `created_at`) values ('1318873669', ' |    0.000 |
|  55244 | user_prod | server.ip:47407 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
|  55426 | user_prod | server.ip:47983 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
| 107408 | user_prod | server.ip:57303 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
| 204661 | user_prod | server.ip:45568 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
| 204717 | user_prod | server.ip:51573 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
| 204795 | user_prod | server.ip:52682 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
| 204844 | user_prod | server.ip:53290 | user_prod | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |
| 204972 | user_prod | server.ip:54717 | user_prod | Sleep   |   20 |                | NULL                                                                                                 |    0.000 |
| 204999 | user_prod | server.ip:55069 | user_prod | Sleep   |   13 |                | NULL                                                                                                 |    0.000 |
| 205006 | user_prod | server.ip:55159 | user_prod | Sleep   |   11 |                | NULL                                                                                                 |    0.000 |
| 205020 | user_prod | server.ip:55377 | user_prod | Sleep   |    7 |                | NULL                                                                                                 |    0.000 |
| 205026 | user_prod | server.ip:55443 | user_prod | Sleep   |    5 |                | NULL                                                                                                 |    0.000 |
| 205028 | user_prod | server.ip:55524 | user_prod | Sleep   |    3 |                | NULL                                                                                                 |    0.000 |
| 205031 | user_prod | server.ip:55569 | user_prod | Sleep   |    2 |                | NULL                                                                                                 |    0.000 |
| 205032 | user_prod | server.ip:55573 | user_prod | Sleep   |    2 |                | NULL                                                                                                 |    0.000 |
| 205034 | user_prod | localhost                       | NULL         | Query   |    0 | init           | show processlist                                                                                     |    0.000 |
+--------+--------------+---------------------------------+--------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+----------+
24 rows in set (0.00 sec)

1 Answer 1

1

I would investigate if table_open_cache limit isn't the bottleneck reason. Perhaps the threads are simply blocking, causing long execution time and high CPU.

Also, enabling the query cache can possibly help, like switching query_cache_type to ON and setting the query_cache_size to 1G.

If that helps, you could also try to play with it's amount, because different values can give different result, even "more is better" rule sometimes doesn't apply.

6
  • My table_open_cache is set to 800 and show global status like 'opened_tables'; gives : Opened_tables: 249 It doesn't seem to be that :( Oct 16, 2014 at 12:29
  • now check the processlist with 'show processlist' and see if there's really that many processes and their running time. Plus, this may sound like a dumb question, most of your variables are about innodb. are most of your databases innodb too ? may be you are running lots of old myisam database, thus they just don't see enough memory and other options.
    – drookie
    Oct 16, 2014 at 12:35
  • Don't worry, I use InnoDB tables ;) I edited the question to add a sample of processlist but it doesn't seem to help... Oct 16, 2014 at 13:02
  • and why you have query cache disabled ? I would enable it, set to at least 1 gig and see whet will happen.
    – drookie
    Oct 16, 2014 at 13:41
  • I read that query_cache is bad on write intensive setups... I'll try anyway :) Oct 16, 2014 at 14:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .