MySQL Forums
Forum List  »  InnoDB

mysql 8.0.27 went crash -- continued
Posted by: y y
Date: April 23, 2022 07:23PM

Sorry. Because It occur "This is a moderated forum. Your message will remain hidden until it has been approved by a moderator or administrator",when I reply the https://forums.mysql.com/read.php?22,704031,704031#msg-704031.

So I make a new topic to post the analysis of my mysql db.

I run the analysis follow https://www.artfulsoftware.com/infotree/tip.php?id=1339&r=1 。 And the results are as follows:

Database load:

engine	data	indexes	total
InnoDB	84.08 GB	58.25 GB	142.33 GB
CSV	0.00 GB	0.00 GB	0.00 GB
TOTALS	84.08 GB	58.25 GB	142.33 GB

Performance metrics:

item	rate	suggestions
Slow queries/sec	    0	      Enable slow query log
Unindexed queries/sec	    0	      Find & optimise unindexed queries
Full table scans/sec	    240.63    Find & optimise queries that do full table scans
Total InnoDB load / buffer pool size  14.23	Increasing innodb_buffer_pool_size may improve performance
InnoDB buffer pool inefficiency	      0.21	Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
InnoDB pages read and buffer pool    0.24	As above
InnoDB log thrashing	             0.01	Best near 0.5; if well under 0.5, grow innodb_log_file_size, shrink if much larger
Est. best InnoDB log file size	     32MB	Average hour of writes rounded up to 32MB boundary
Mins between InnoDB log rotation     4.91	Grow log size if < 30 min, shrink if > 60 min**
Query cache hits/sec		                If high, grow query_cache_size up to 50
Query cache spills/sec		                If < 5, try turning query cache off
MyISAM buffer hit ratio		                If < 10, grow key_buffer_size
MyISAM key read rate	             0	        Should be high when the buffer is full
MyISAM key write ratio		                Much higher than 1 suggests a disk write bottleneck
MyISAM query cache hits/inserts		        If < 5, consider disabling the cache
Temp tables created/sec	            0.02	Grow tmp_table_size, max_heap_size. Use Heap tables in queries
Database changes/connection		        Use db.table syntax
Processes/connection	            0.41	If high, grow thread_cache_size
Queries/sec	                   1266.14	Over 1K is busy: you may need better hardware
Inserts/sec		                        Over 10K is busy, you may need better hardware
Table scan next row seeks/sec	   138037.52	If high, there are queries that need optimising, esp. with covering indexes
InnoDB buffer reads/sec	           1175.37	Over 1K is busy: you may need better hardware
Files opened/sec	           0	       If > 5, increase table_open_cache
Created tmp disk tables/sec	   0.02	       If > 1, check tmp_table_size, max_heap_table_size, Text/Blob query retrievals
Created_tmp_disk_tables/Queries     0	       If > 4%, check tmp_table_size, max_heap_table_size, Text/Blob query retrievals
Max MySQL RAM use	           107.64	Must not be greater than RAM available to MySQL




Another:

innodb iops	buffpoolsize	bufferpoolpctfee	bytesread/s	read/s	bytes/read	byteswritts	writes/S	bytes/write
1658	10.0GB	0	947	16298.2	17	6851	470.1	15

Would you please to help me to point out some suggestions for dealing with the crash of mysql? Thank you.

Options: ReplyQuote


Subject
Views
Written By
Posted
mysql 8.0.27 went crash -- continued
94
y y
April 23, 2022 07:23PM


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.