Re: Mysql memory usage increasing until it hits the RAM limit and starts using swap
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.