MySQL Lists are EOL. Please join:

List:InnoDB Storage Engine« Previous MessageNext Message »
From:Selvam Date:April 3 2017 12:32pm
Subject:Re: InnoDB memory usage
View as plain text  
Hi,

Thanks for the update, I don't think I have an option to export settings
from AWS RDS.  I can share you the important list of parameters,

Buffer pool size is configured as 75% of 4 GB RAM.
innodb_file_per_table is enabled
innodb_file_per_table O_DIRECT
innodb_log_buffer_size 8388608
key_buffer_size 16777216
thread_stack 262144
sort_buffer_size, join_buffer_size are left as default value: 128 KB or 256
KB (10.0.24 which is our production DB version while staging is 10.1.19)

query cache is disabled

Should I try to increase the key_buffer_size?


More details as of now:

INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 125, seg size 127, 96523 merges
merged operations:
 insert 77866, delete mark 193430, delete 61948
discarded operations:
 insert 0, delete mark 0, delete 0
74.43 hash searches/s, 5.87 non-hash searches/s


BUFFER POOL AND MEMORY
----------------------
Total memory allocated 3021996032; in additional pool allocated 0
Total memory allocated by read views 344
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 130802384 (46748264 + 84054120)
    Page hash           366056 (buffer pool 0 only)
    Dictionary cache    13611078 (11688592 + 1922486)
    File system         960472 (812272 + 148200)
    Lock system         7306496 (7305416 + 1080)
    Recovery system     0 (0 + 0)
Dictionary memory allocated 1922486
Buffer pool size        180216
Buffer pool size, bytes 2952658944
Free buffers            8195
Database pages          166891
Old database pages      61443
Modified db pages       0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1507557, not young 40621567
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1006260, created 58891, written 1358879
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead
0.00/s



Note: As far as I have researched, the undo space is not getting cleaned
up. Might be the purge thread not doing its work for some reason. I am not
sure though.

On Mon, Apr 3, 2017 at 4:35 PM, geetanjali mehra <mailtogeetanjali@stripped
> wrote:

> Please send contents of my.cnf
>
> On 01-Apr-2017 1:16 PM, "Selvam" <s.selvamsiva@stripped> wrote:
>
>> Hi,
>>
>> Thanks, it's from the production instance which has 4GB of RAM. All our
>> instances run on AWS rds.
>>
>> On Sat, 1 Apr 2017 at 12:58 PM, geetanjali mehra <
>> mailtogeetanjali@stripped> wrote:
>>
>>> Please mention from which server(RDS or your another production
>>> instance)  you have pasted " show engine innodb status" output?
>>>
>>>
>>> --
Regards,
Selvam

Thread
InnoDB memory usageSelvam31 Mar
Re: InnoDB memory usageSelvam3 Apr