| Bug #64181 | unzip_LRU length management might need to be updated for SSD | ||
|---|---|---|---|
| Submitted: | 31 Jan 2012 14:41 | Modified: | 4 Feb 2012 17:17 |
| Reporter: | Mark Callaghan | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB Plugin storage engine | Severity: | S5 (Performance) |
| Version: | 5.1.52, 5.1.61 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | innodb, unzip_LRU | ||
[7 Feb 2013 15:07]
Calvin Sun
Yasufumi did some experiments and have the following conclusion: This is raw result of the test (100WH 32sessions bp3GB compagesize 8K; ioDrive2 tpcc(only read trx)) When reduce the value, in shot term, once throughput seems increased, but the throughput reduced soon. Though the unzip page hit rate is increased, the zip page mishit cost might be a little more expensive in total even FusionIO SSD is fast. And the current hard-coded value "50" seems nearly best value at least for this test.... I think no need to add the option for this value for now not to confuse users. * option innodb_lru_io_to_unzip_target added to change the BUF_LRU_IO_TO_UNZIP_FACTOR temporally. * innodb_lru_io_to_unzip_target is changed dynamically. innodb_lru_io_to_unzip_target=50 (current hardcoded) 10, 0(0):0.0, 0(0):0.0, 3675(0):0.2, 0(0):0.0, 3676(0):0.2 20, 0(0):0.0, 0(0):0.0, 3651(0):0.2, 0(0):0.0, 3655(0):0.2 30, 0(0):0.0, 0(0):0.0, 3742(0):0.2, 0(0):0.0, 3738(0):0.2 40, 0(0):0.0, 0(0):0.0, 3779(0):0.2, 0(0):0.0, 3778(0):0.2 50, 0(0):0.0, 0(0):0.0, 3727(0):0.2, 0(0):0.0, 3733(0):0.2 60, 0(0):0.0, 0(0):0.0, 3746(0):0.2, 0(0):0.0, 3743(0):0.2 70, 0(0):0.0, 0(0):0.0, 3762(0):0.2, 0(0):0.0, 3762(0):0.2 innodb_lru_io_to_unzip_target=40 80, 0(0):0.0, 0(0):0.0, 3561(0):0.2, 0(0):0.0, 3560(0):0.2 90, 0(0):0.0, 0(0):0.0, 3475(0):0.2, 0(0):0.0, 3473(0):0.2 100, 0(0):0.0, 0(0):0.0, 3569(0):0.2, 0(0):0.0, 3571(0):0.2 110, 0(0):0.0, 0(0):0.0, 3636(0):0.2, 0(0):0.0, 3636(0):0.2 120, 0(0):0.0, 0(0):0.0, 3631(0):0.2, 0(0):0.0, 3634(0):0.2 130, 0(0):0.0, 0(0):0.0, 3664(0):0.2, 0(0):0.0, 3658(0):0.2 innodb_lru_io_to_unzip_target=30 140, 0(0):0.0, 0(0):0.0, 3711(0):0.2, 0(0):0.0, 3718(0):0.2 150, 0(0):0.0, 0(0):0.0, 3508(0):0.2, 0(0):0.0, 3502(0):0.2 160, 0(0):0.0, 0(0):0.0, 3363(0):0.2, 0(0):0.0, 3365(0):0.2 170, 0(0):0.0, 0(0):0.0, 3504(0):0.2, 0(0):0.0, 3507(0):0.2 180, 0(0):0.0, 0(0):0.0, 3474(0):0.2, 0(0):0.0, 3475(0):0.2 190, 0(0):0.0, 0(0):0.0, 3550(0):0.2, 0(0):0.0, 3548(0):0.2 200, 0(0):0.0, 0(0):0.0, 3545(0):0.2, 0(0):0.0, 3541(0):0.2 210, 0(0):0.0, 0(0):0.0, 3598(0):0.2, 0(0):0.0, 3600(0):0.2 220, 0(0):0.0, 0(0):0.0, 3552(0):0.2, 0(0):0.0, 3554(0):0.2 innodb_lru_io_to_unzip_target=20 230, 0(0):0.0, 0(0):0.0, 3528(0):0.2, 0(0):0.0, 3528(0):0.2 240, 0(0):0.0, 0(0):0.0, 3337(0):0.2, 0(0):0.0, 3334(0):0.2 250, 0(0):0.0, 0(0):0.0, 3421(0):0.2, 0(0):0.0, 3421(0):0.2 260, 0(0):0.0, 0(0):0.0, 3523(0):0.2, 0(0):0.0, 3525(0):0.2 270, 0(0):0.0, 0(0):0.0, 3604(0):0.2, 0(0):0.0, 3604(0):0.2 280, 0(0):0.0, 0(0):0.0, 3536(0):0.2, 0(0):0.0, 3532(0):0.2 290, 0(0):0.0, 0(0):0.0, 3446(0):0.2, 0(0):0.0, 3448(0):0.2 300, 0(0):0.0, 0(0):0.0, 3378(0):0.2, 0(0):0.0, 3378(0):0.2 310, 0(0):0.0, 0(0):0.0, 3453(0):0.2, 0(0):0.0, 3452(0):0.2 innodb_lru_io_to_unzip_target=50 320, 0(0):0.0, 0(0):0.0, 3451(0):0.2, 0(0):0.0, 3451(0):0.2 330, 0(0):0.0, 0(0):0.0, 3500(0):0.2, 0(0):0.0, 3498(0):0.2 340, 0(0):0.0, 0(0):0.0, 3500(0):0.2, 0(0):0.0, 3504(0):0.2 350, 0(0):0.0, 0(0):0.0, 3639(0):0.2, 0(0):0.0, 3643(0):0.2 360, 0(0):0.0, 0(0):0.0, 3642(0):0.2, 0(0):0.0, 3635(0):0.2 370, 0(0):0.0, 0(0):0.0, 3618(0):0.2, 0(0):0.0, 3623(0):0.2 380, 0(0):0.0, 0(0):0.0, 3481(0):0.2, 0(0):0.0, 3479(0):0.2 390, 0(0):0.0, 0(0):0.0, 3575(0):0.2, 0(0):0.0, 3574(0):0.2 400, 0(0):0.0, 0(0):0.0, 3601(0):0.2, 0(0):0.0, 3601(0):0.2 410, 0(0):0.0, 0(0):0.0, 3616(0):0.2, 0(0):0.0, 3615(0):0.2 420, 0(0):0.0, 0(0):0.0, 3625(0):0.2, 0(0):0.0, 3627(0):0.2 innodb_lru_io_to_unzip_target=100 430, 0(0):0.0, 0(0):0.0, 3681(0):0.2, 0(0):0.0, 3679(0):0.2 440, 0(0):0.0, 0(0):0.0, 3726(0):0.2, 0(0):0.0, 3728(0):0.2 450, 0(0):0.0, 0(0):0.0, 3728(0):0.2, 0(0):0.0, 3725(0):0.2 460, 0(0):0.0, 0(0):0.0, 3723(0):0.2, 0(0):0.0, 3726(0):0.2 470, 0(0):0.0, 0(0):0.0, 3697(0):0.2, 0(0):0.0, 3698(0):0.2 480, 0(0):0.0, 0(0):0.0, 3696(0):0.2, 0(0):0.0, 3693(0):0.2 490, 0(0):0.0, 0(0):0.0, 3698(0):0.2, 0(0):0.0, 3698(0):0.2 500, 0(0):0.0, 0(0):0.0, 3644(0):0.2, 0(0):0.0, 3643(0):0.2 510, 0(0):0.0, 0(0):0.0, 3648(0):0.2, 0(0):0.0, 3651(0):0.2 520, 0(0):0.0, 0(0):0.0, 3609(0):0.2, 0(0):0.0, 3605(0):0.2 530, 0(0):0.0, 0(0):0.0, 3624(0):0.2, 0(0):0.0, 3628(0):0.2 540, 0(0):0.0, 0(0):0.0, 3817(0):0.2, 0(0):0.0, 3818(0):0.2 550, 0(0):0.0, 0(0):0.0, 3734(0):0.2, 0(0):0.0, 3734(0):0.2 560, 0(0):0.0, 0(0):0.0, 3756(0):0.2, 0(0):0.0, 3751(0):0.2 570, 0(0):0.0, 0(0):0.0, 3738(0):0.2, 0(0):0.0, 3745(0):0.2 innodb_lru_io_to_unzip_target=50 580, 0(0):0.0, 0(0):0.0, 3801(0):0.2, 0(0):0.0, 3793(0):0.2 590, 0(0):0.0, 0(0):0.0, 3763(0):0.2, 0(0):0.0, 3769(0):0.2 600, 0(0):0.0, 0(0):0.0, 3760(0):0.2, 0(0):0.0, 3760(0):0.2 610, 0(0):0.0, 0(0):0.0, 3804(0):0.2, 0(0):0.0, 3802(0):0.2 620, 0(0):0.0, 0(0):0.0, 3782(0):0.2, 0(0):0.0, 3780(0):0.2 630, 0(0):0.0, 0(0):0.0, 3724(0):0.2, 0(0):0.0, 3728(0):0.2 640, 0(0):0.0, 0(0):0.0, 3777(0):0.2, 0(0):0.0, 3778(0):0.2 650, 0(0):0.0, 0(0):0.0, 3753(0):0.2, 0(0):0.0, 3748(0):0.2 660, 0(0):0.0, 0(0):0.0, 3560(0):0.2, 0(0):0.0, 3562(0):0.2 innodb_lru_io_to_unzip_target=40 670, 0(0):0.0, 0(0):0.0, 3566(0):0.2, 0(0):0.0, 3567(0):0.2 680, 0(0):0.0, 0(0):0.0, 3633(0):0.2, 0(0):0.0, 3631(0):0.2 690, 0(0):0.0, 0(0):0.0, 3657(0):0.2, 0(0):0.0, 3657(0):0.2 700, 0(0):0.0, 0(0):0.0, 3650(0):0.2, 0(0):0.0, 3653(0):0.2 710, 0(0):0.0, 0(0):0.0, 3714(0):0.2, 0(0):0.0, 3714(0):0.2 innodb_lru_io_to_unzip_target=50 720, 0(0):0.0, 0(0):0.0, 3641(0):0.2, 0(0):0.0, 3639(0):0.2 730, 0(0):0.0, 0(0):0.0, 3618(0):0.2, 0(0):0.0, 3615(0):0.2 740, 0(0):0.0, 0(0):0.0, 3701(0):0.2, 0(0):0.0, 3702(0):0.2 750, 0(0):0.0, 0(0):0.0, 3704(0):0.2, 0(0):0.0, 3712(0):0.2 innodb_lru_io_to_unzip_target=20 760, 0(0):0.0, 0(0):0.0, 3652(0):0.2, 0(0):0.0, 3645(0):0.2 770, 0(0):0.0, 0(0):0.0, 3367(0):0.2, 0(0):0.0, 3365(0):0.2 780, 0(0):0.0, 0(0):0.0, 3453(0):0.2, 0(0):0.0, 3455(0):0.2 790, 0(0):0.0, 0(0):0.0, 3606(0):0.2, 0(0):0.0, 3607(0):0.2 800, 0(0):0.0, 0(0):0.0, 3602(0):0.2, 0(0):0.0, 3601(0):0.2 810, 0(0):0.0, 0(0):0.0, 3656(0):0.2, 0(0):0.0, 3659(0):0.2 820, 0(0):0.0, 0(0):0.0, 3428(0):0.2, 0(0):0.0, 3427(0):0.2 830, 0(0):0.0, 0(0):0.0, 3396(0):0.2, 0(0):0.0, 3399(0):0.2 840, 0(0):0.0, 0(0):0.0, 3458(0):0.2, 0(0):0.0, 3453(0):0.2 850, 0(0):0.0, 0(0):0.0, 3423(0):0.2, 0(0):0.0, 3422(0):0.2 860, 0(0):0.0, 0(0):0.0, 3437(0):0.2, 0(0):0.0, 3440(0):0.2 870, 0(0):0.0, 0(0):0.0, 3526(0):0.2, 0(0):0.0, 3524(0):0.2 innodb_lru_io_to_unzip_target=2 880, 0(0):0.0, 0(0):0.0, 3870(0):0.2, 0(0):0.0, 3874(0):0.2 890, 0(0):0.0, 0(0):0.0, 4069(0):0.2, 0(0):0.0, 4066(0):0.2 900, 0(0):0.0, 0(0):0.0, 3650(0):0.2, 0(0):0.0, 3651(0):0.2 910, 0(0):0.0, 0(0):0.0, 3250(0):0.2, 0(0):0.0, 3250(0):0.2 920, 0(0):0.0, 0(0):0.0, 3236(0):0.2, 0(0):0.0, 3237(0):0.2 930, 0(0):0.0, 0(0):0.0, 3300(0):0.2, 0(0):0.0, 3297(0):0.2 940, 0(0):0.0, 0(0):0.0, 3386(0):0.2, 0(0):0.0, 3387(0):0.2 950, 0(0):0.0, 0(0):0.0, 3382(0):0.2, 0(0):0.0, 3386(0):0.2 960, 0(0):0.0, 0(0):0.0, 3351(0):0.2, 0(0):0.0, 3351(0):0.2 innodb_lru_io_to_unzip_target=10 980, 0(0):0.0, 0(0):0.0, 3254(0):0.2, 0(0):0.0, 3255(0):0.2 990, 0(0):0.0, 0(0):0.0, 3199(0):0.2, 0(0):0.0, 3197(0):0.2 1000, 0(0):0.0, 0(0):0.0, 3316(0):0.2, 0(0):0.0, 3321(0):0.2 1010, 0(0):0.0, 0(0):0.0, 3381(0):0.2, 0(0):0.0, 3380(0):0.2 1020, 0(0):0.0, 0(0):0.0, 3574(0):0.2, 0(0):0.0, 3577(0):0.2 1030, 0(0):0.0, 0(0):0.0, 3381(0):0.2, 0(0):0.0, 3377(0):0.2 1040, 0(0):0.0, 0(0):0.0, 3297(0):0.2, 0(0):0.0, 3294(0):0.2 1050, 0(0):0.0, 0(0):0.0, 3362(0):0.2, 0(0):0.0, 3362(0):0.2 1060, 0(0):0.0, 0(0):0.0, 3362(0):0.2, 0(0):0.0, 3369(0):0.2 1070, 0(0):0.0, 0(0):0.0, 3325(0):0.2, 0(0):0.0, 3318(0):0.2 1080, 0(0):0.0, 0(0):0.0, 3301(0):0.2, 0(0):0.0, 3305(0):0.2
Description: This function determines when to evict from the unzip_LRU and determines the length of the unzip_LRU. The two important rules are: 1) if unzip_LRU len < (LRU len) / 10 then evict from LRU --> so unzip_LRU will be 10% of LRU by this rule 2) if (unzip_avg / io_avg) > 50 then evict from LRU --> this lets unzip_LRU become larger than 10% of LRU In production our compressed servers usually have unzip_LRU as 10% of the LRU so rule 1 dominates. Both of these rules use constants and hardwired constants are an opportunity for tuning (and complexity if new my.cnf variables are added). But I wonder whether the constant 50 is a good choice for slow storage (spinning disk) and fast storage (ssd, flash). Determines if the unzip_LRU list should be used for evicting a victim instead of the general LRU list. @return TRUE if should use unzip_LRU */ UNIV_INLINE ibool buf_LRU_evict_from_unzip_LRU(void) /*==============================*/ { ulint io_avg; ulint unzip_avg; ulint unzip_len; ulint lru_len; ut_ad(buf_pool_mutex_own()); /* If the unzip_LRU list is empty, we can only use the LRU. */ if (UT_LIST_GET_LEN(buf_pool->unzip_LRU) == 0) { return(FALSE); } /* If unzip_LRU is at most 10% of the size of the LRU list, then use the LRU. This slack allows us to keep hot decompressed pages in the buffer pool. */ unzip_len = ut_max(UT_LIST_GET_LEN(buf_pool->unzip_LRU), 1); lru_len = ut_max(UT_LIST_GET_LEN(buf_pool->LRU), 1); if (((100 * unzip_len) / lru_len) <= srv_unzip_LRU_pct) return FALSE; /* If eviction hasn't started yet, we assume by default that a workload is disk bound. */ if (buf_pool->freed_page_clock == 0) { return(TRUE); } /* Calculate the average over past intervals, and add the values of the current interval. */ io_avg = buf_LRU_stat_sum.io / BUF_LRU_STAT_N_INTERVAL + buf_LRU_stat_cur.io; unzip_avg = buf_LRU_stat_sum.unzip / BUF_LRU_STAT_N_INTERVAL + buf_LRU_stat_cur.unzip; /* Decide based on our formula. If the load is I/O bound (unzip_avg is smaller than the weighted io_avg), evict an uncompressed frame from unzip_LRU. Otherwise we assume that the load is CPU bound and evict from the regular LRU. */ return(unzip_avg <= io_avg * BUF_LRU_IO_TO_UNZIP_FACTOR); } How to repeat: read the source Suggested fix: Can something adaptive be done here to replace "50" so that this works for slow and fast storage? By "works" I mean there is an assumption about the cost of IO and decompression that can't be true for both fast and slow storage.