MySQL Forums
Forum List  »  InnoDB

Re: Large database run locally crashing
Posted by: Peter Brawley
Date: August 06, 2018 08:30AM

Not much time just now, so here is a raw text dump of diagnostics on your values. As you can see, your "4G" settings didn't take, indeed MySQL is configured to use only 0.3G so let's see a dump of my.cnf, and there are slow queries but the sample time is small, just 20 mins, we really need a couple of hours at least ...

MySQL version                           5.6.40          
Slow queries/sec                        unknown         Enable the slow query log
Queries not using indexes               0               Find & optimise those queries with indexes
Queries needing full table scans/sec    0.0255          Find & optimise those queries
InnoDB buffer pool inefficiency         0.0564          Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
InnoDB page reads and buffer pool       0.0569          Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
InnoDB log file thrashing               0.75            Best is near 0.5; grow innodb_log_file_size if well under 0.5, shrink if much larger
Mins between InnoDB log rotation        265011.2        Grow log size if < 30 min, shrink if > 60 min, but see here.
InnoDB buffer pool reads / sec          5.3014          If high, increase Innodb_buffer_pool_size up to 70% of RAM
Est. best InnoDB log file size          32MB            Average hour of writes rounded up to 32MB boundary
MyISAM key buffer size                  8MB             Pre-8.0 MyISAM system tables need about 40MB
MyISAM query cache spills / sec         0               If high, grow query_cache_size up to 50
MyISAM query cache hits / sec           0               If < 5, try turning query cache off
MyISAM cache miss ratio                 0               If > 10, grow key_buffer_size
MyISAM key write ratio                  0               Much higher than 1 suggests a disk write bottleneck
MyISAM key read rate                    0               Should be high when the buffer is full
MyISAM query cache hits/inserts         0               If low, consider disabling the cache
Temp tables created/sec                 0.08            If > 20, find the queries responsible and optimise them, 
Temp tables created on disk/Questions   0.7273          If > .05, try growing join_buffer_size, optimise responsible queries, 
Temp tables created on disk/sec         0.02            If high, grow tmp_table_size, max_heap_size. Use HEAP tables in queries
Database changes / connection           0.67            Use db.table syntax
Processes/connection                    0.5             If high, grow thread_cache_size
Files opened/sec                        16.3            If > 5, increase table_open_cache
Queries/sec                             0.03            Thousands is busy: you may need better hardware
Insert statements/sec                   0               
Inserts/sec                             80.92           Can be up to 20K/sec with efficient memory use, see here
Next table row requests/sec             81              If high, queries need optimising esp. with covering indexes
Max MySQL RAM use                       0.3GB           Must be less than RAM available to MySQL
Available RAM                           unknown         If less than Max MySQL memory use, more memory needed

I don't run Macs, if I read that mem result correctly you have 16G RAM, correct?

We also need the exact error messages.

Edited 1 time(s). Last edit at 08/06/2018 08:30AM by Peter Brawley.

Options: ReplyQuote

Written By
August 02, 2018 02:57PM
Re: Large database run locally crashing
August 06, 2018 08:30AM

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.