Opened 3 years ago

Closed 3 years ago

#11642 closed Bug/Something is broken (fixed)

increase innodb_buffer_pool_size on rose?

Reported by: Steve Revilak Owned by: Jamie McClelland
Priority: Medium Component: Tech
Keywords: rose, mysql Cc: info@…
Sensitive: no

Description

This morning, I was upgrading some sites that I help maintain on rose. One of the upgrades went bad, forcing me to restore from a backup. I found myself patiently waiting for the restore to complete, and decided to spend the time looking at resource usage.

Here's a clip of iostat output. It's the second set of output in "iostat -x 10 2".

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          80.14    0.00   10.48    0.00    9.13    0.25

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
vda               0.00    37.40    9.60   17.60    77.60   208.80    21.06     9.02  163.76   36.83  233.00  36.76 100.00
dm-0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
dm-1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
dm-2              0.00     0.00    0.00    8.00     0.00    32.00     8.00     1.00   52.30    0.00   52.30 120.70  96.56
dm-3              0.00     0.00    0.00   20.40     0.00    80.80     7.92     2.20   94.75    0.00   94.75  35.88  73.20
dm-4              0.00     0.00    8.80    2.60    74.40     9.60    14.74     1.39   62.53   36.09  152.00  87.65  99.92
dm-5              0.00     0.00    0.80   22.00     3.20    88.00     8.00     3.24  131.19   44.00  134.36  15.93  36.32

dm-4 is /var/lib/mysql, which leads me to believe there's a lot of database related I/O happening. On rose, mysql's innodb_buffer_pool_size is set to 128m. Rose appears to have 8GB of ram, and I'm wondering how folks would feel about increasing innodb_buffer_pool_size to 256m (or even 512M). This would be done in the hope of relieving some of the load on dm-4.

I'm using "show processlist" to keep tabs on my restore. I see "create table FOO" taking 10-20+ seconds to complete, and single row insert statements taking ~3 seconds to complete.

mysql> show full processlist;
+---------+-----------------+-----------+-----------------+---------+------+--------+--------------------------------------------------------------------------------------------------------------------+
| Id      | User            | Host      | db              | Command | Time | State  | Info                                                                                                               |
+---------+-----------------+-----------+-----------------+---------+------+--------+--------------------------------------------------------------------------------------------------------------------+
| 7092694 | masspirate_drup | localhost | masspirate_drup | Query   |    3 | update | INSERT INTO `civicrm_activity_contact` (`id`, `activity_id`, `contact_id`, `record_type_id`) VALUES (2862,162,3,2) |
| 7093799 | masspirate_drup | localhost | masspirate_drup | Query   |    0 | NULL   | show full processlist                                                                                              |
+---------+-----------------+-----------+-----------------+---------+------+--------+--------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Across the entire system uptime, dm-4's utilization is kind of high

0 rose:/var/lib# iostat -x /dev/dm-4
Linux 3.2.0-4-amd64 (rose)      04/09/2016      _x86_64_        (4 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          32.47    0.00    3.59   11.12    1.37   51.45

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
dm-4              0.00     0.00    3.87   51.40   105.85   196.82    10.95     1.23   22.20   27.08   21.84  10.40  57.49

So, thoughts on increasing innodb_buffer_pool_size on rose? Or, other ideas for dealing with dm-4 saturation?

Change History (4)

comment:1 Changed 3 years ago by JaimeV

Owner: set to Jamie McClelland
Status: newassigned

Maybe we should also see how this compares on other mosh's. I can try to check on that. Meanwhile assigning to jamie for final word on rose.

comment:2 Changed 3 years ago by Jamie McClelland

Yes - I think that's a great idea. I think 512MB is fine for Rose and I think 256MB should be our default for all moshes. Steve: can you please kick this off one rose by adding /etc/mysql/conf.d/innodb-buffer-pool-size.cnf with:

[mysqld]
innodb_buffer_pool_size = 512MB

Then, I think Jaime or I could create a puppet conf that will add this file with 512MB by default (unless it is overridden in the server's .pp file - which we will do for rose).

Last edited 3 years ago by Jamie McClelland (previous) (diff)

comment:3 Changed 3 years ago by Steve Revilak

I've made the change Jamie suggested:

0 rose:/etc/mysql/conf.d# cat /etc/mysql/conf.d/innodb-buffer-pool-size.cnf 
[mysqld]
innodb_buffer_pool_size=512M
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 536870912 |
+-------------------------+-----------+
1 row in set (0.00 sec)

mysql> select 536870912 / (1024 * 1024);
+---------------------------+
| 536870912 / (1024 * 1024) |
+---------------------------+
|                  512.0000 |
+---------------------------+

comment:4 Changed 3 years ago by Jamie McClelland

Resolution: fixed
Status: assignedclosed

This has now been pushed to puppet and will go out on the next signing. Note: this puppet change will cause mysql to be restarted - which will cause a few minutes of downtime on each server.

jamie

Please login to add comments to this ticket.

Note: See TracTickets for help on using tickets.