Re: Mysql memory usage increasing until it hits the RAM limit and starts using swap
Posted by: Ravi Malghan
Date: December 11, 2018 07:38PM

Peter: thanks for those links.

1. I reviewed the first bug report and checked my VM. THP is already disabled on my system
cat /proc/sys/vm/nr_hugepages
0
2. For Bug 2. The mysql documentation says "As of MySQL 5.7.13, MySQL distributions no longer include a tcmalloc library". If I read user responses here, the problem went away by starting mysqld_safe with --malloc-lib=tcmalloc option?
3. Any ideas on how much RAM to allocate for this VM? I know that's a loaded question since it depends on the queries and tables. But trying to figure out how to come up with how much RAM I should ask for and provide justification.
4. I have two big tables that are not partitioned. I don't think partitioning the tables is going to help unless coupled with changes to the queries that query the table. Am I correct? Or since the table is not partitioned, each insert into the table could take longer since all of the data is in a single file on the drive.
5. My hardware is a 24CPU (2.5GHZ). I am guessing by faster hardware you mean the processor speed and not necessarily number of CPUs.

Thanks
Ravi
Peter Brawley Wrote:
-------------------------------------------------------
> Commonly reported with 5.7. A fix to 5.7.13 for
> this bug did not stop such reports, eg ...
>
> https://bugs.mysql.com/bug.php?id=84003 ...
>
> where a user said "The problem in 5.7 is this:
> once MySQL consumes memory it *never* releases it
> back to the OS!". In that case doubling RAM seemed
> to fix it, but this bug page ...
>
> https://bugs.mysql.com/bug.php?id=83047
>
> ... was created before 5.7.13 and revived in
> 2017
, a year later, and it reports the same
> problem, so keep reading, it was necessary to
> change the memory allocator!

>
> I ran your numbers through a mem analysis script.
> It said ...
>
>
> Queries needing full table scans/sec  2.1714
> Find & optimise those queries
> 
> InnoDB log file thrashing             0.0179
> Best is near 0.5; grow innodb_log_file_size if
> 
> well under 0.5, shrink if much larger
> 
> Est. best InnoDB log file size        736MB
> Average hour of writes rounded up to 32MB boundary
> 
> MyISAM key buffer size                32MB
> Pre-8.0 MyISAM system tables need about 40MB
> 
> MyISAM cache miss ratio               311.88   If
> > 10, grow key_buffer_size
> 
> MyISAM key read rate                  0.03
> Should be high when the buffer is full
> 
> Temp tables created/sec               86.41    >
> 20 is too much, find the queries responsible
> and
> and optimise them
> 
> Queries/sec                           2265.52
> Very busy system, you may need better hardware
> 
> Inserts/sec                           7496.68
> 
> Next table row requests/sec           30993
> Some queries need optimising esp. with covering
> indexes
> 
> Max MySQL RAM use                     114GB
> Must be less than RAM available to MySQL
>
>
> ie it's a very busy server, you're using 90% of
> available RAM, you need more RAM, some queries
> need optimising, and you may need faster hardware.

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.