MySQL Performance Tuning: Part 1. Configuration (Covers MySQL 5.7)
Вставка
- Опубліковано 20 лип 2024
- I've prepared this mysql config file tuning tutorial for you - it has pretty much all you need to know about configuring MySQL for best performance.
On the agenda:
1. Essentials of MySQL Configuration
2. Differences between:
- MySQL versions (5.1, 5.5, 5.6, 5.7) and
- Distros (Community MySQL, Percona Server, MariaDB, WebScaleSQL)
3. 17 Key Settings for High Performance MySQL Server
And a bonus section: how to look at MySQL status variables.
Enjoy!
P.S. Some links from the video:
Article by Laurynas Biveinis about WebScaleSQL:
www.percona.com/blog/2014/05/...
My my.cnf template download: www.speedemy.com/17
On innodb_io_capacity: www.speedemy.com/17-key-mysql-...
More on MySQL handler counters: dev.mysql.com/doc/refman/5.7/e...
On using Threads_running: www.speedemy.com/mysql-trouble...
Cacti: www.cacti.net
Zabbix: www.zabbix.org
Percona Monitoring Plugins: www.percona.com/software/mysq...
Percona Toolkit: www.percona.com/software/mysq...
Innotop manual: innotop.googlecode.com/svn/htm...
Innotop source: github.com/innotop/innotop
If I forgot to include some link, please be kind and report that in the comments so I update this.
![SQL Tutorial for Beginners [Full Course]](http://i.ytimg.com/vi/7S_tz1z_5bA/mqdefault.jpg)
I've been looking for this for a long time. Answered my biggest questions that I've had for years.
This was amazing!!! Thank you for your hard work and deep understanding!
Thank you for your resources, they are great and you are a very good tutor!
This is simply awesome. Please keep up the great work
excellent video and all of things you need to optimize mysql database mentioned. thank you so much man.
That was a great tutorial ! Hopefully, that part 2 will be out soon.
Hi, Jeff. Actually, slow query logging series (3 videos) serves as Part 2 of MySQL Performance Tuning, even though I should have stated it clearly:
1. ua-cam.com/video/noFn2sgQiNw/v-deo.html
2. ua-cam.com/video/INovio_FuTU/v-deo.html
3. ua-cam.com/video/f6fiy_dVYxw/v-deo.html
Thanks !!
pin this comment
Could've probably been the best MySQL tuning video here.
definitely one of the most complete MySQL videos out there. Thank you Sir, really appreciated
Thank you so much!
This video was actually INCREDIBLY helpful! I couldn't thank you enough for sharing your knowledge!
Thanks, Deckard. I really appreciate it.
Do you have the cnf.file by any chance?
superb , nailed it!!
hope I'll find more videos from speedemy
Very helpful and informative. Excellent video tutorial for MySQL DBAs
Very useful and clear. Thanks for this great video and ressources.
really awesome...great explanation and very useful, thanks for uploading
It was really awesome...
waiting for the next Tutorials
Incredibly solid information!
one of the greatest videos I've ever seen !!!
Luckily found this video. It helps me a lot.
Great explained and great speaker! Kudos!
thank you friend. better than most of the resources - more clear, informative, true.
I really appreciate your feedback.
Gracias!!, Excelente Video, solo con la primer variable, la respuesta del servidor cambió notablemente.
That was a great tutorial !
I am looking this video since long time
helpful and can get many handy things which is not included in the official ref .
Priceless... Thank you
Great video!
Thank you!
Great tutorial very useful
its simply excellent
This video is very helpful to me
Super clearly.thanks
Very Nice infromation .. Thank you So much Sir ..
Thank you sir. :)
seems the author of this video is very busy or changed the line.
Thank you so much for this highly knowledgeable video ever on MySQL
Thanks Sir, worth to watch
Thanks that was great :)
Excellent
Thank you much for sharing your knowledged. I find it very valuable content. Please let me ask. Minute 43.52 shows commands pager grep seq and show engine innodb status\G select sleep (60); the results on my end are not the same. Can you explain are they both sepparate commands or are all in one line.
Its very useful.. Thank you Sir...
Thank you, Shailesh! I'm really happy about it.
Puikus video!
Thank you so much
Everyone is highly appreciating this video.
I don't even know what is said there! 😞
Great Video!!
There are few other settings which we are tweaking on the server like sort_buffer_size, max_heap_table_size, max_connections. Are they not important?
We changed them on recommendation by mysql tuner script.
Hi, Ashish. Thanks! All settings are important, it's just that some are importanter than others ;) Re the ones you mentioned:
- sort_buffer_size - only increase per query (you can test it with specific query and you'll see if it helps)
- max_heap_table_size (and tmp_table_size) - would be fairly high on the list too. watch Created_tmp_disk_tables status variable to understand if it could help to increase it, but note that sometimes tmp tables are created on disk becasuse tmp tables don't support dynamic columns (text/blob)
- max_connections - not really related to performance. mysql can have high number of idle connections at virtually no cost (tens of thousands). but if you need to change it, definitely do that.
Hope that helps.
So glad that I found this video, thanks for sharing
BTW, I've downloaded the ebook and seems that it is got encrypted or something else when I open it.
What should I do to fix this ?
Sorry, my bad. I can read the ebook now. Thanks
innodb_buffer_pool_size really give a big difference if configure properly. My program keep having timeout issues when not set correctly. When I changed from default 80MB to 2GB, the program never have SQL timeout issue.
fantastic, can you do a similar video for mysql8 :)
Thanks, 👌
Hey nice tutorial. Great help.
I recently saw mysql 8.0 is available. Whats your recommendation on using that?
MySQL 8.0 will be a great release. It's not GA yet. Here's a few things I'm personally looking forward to (but there will be more, I'm sure) - www.speedemy.com/new-in-mysql-8-0-dr/
Please publish more videos :)
I read at Percona's blog high innodb_io_capacity values can be bad for SSD. Also Letsencrypt server on 24 M.2 enterprise SSD still has innodb_io_capacity set to like 1000 to avoid frequent flushing. They say SSD will wear much faster with values set to 50-75% of actual IOPS.
So maybe we should be careful using high innodb_io_capacity without good reason.
Hi, can the same config be used for mysql 8 ?
your website is down. can you share alternative link.
Is there Part 2 of this video?
can you share your PPT slides
please
Guys, Anyone have the my.cnf files. It seems the website is no longer active. Please, share the file with me.
Great Content.
Thank you. But the links are not working.
Should it be query_cache_type =OFF or query_cache_type=0
Whichever you prefer. Both map to the same constant internally.
i like the ppt in this video,can u share it ?
Sure, it's available here: www.slideshare.net/inner/mysql-performance-tuning-part-1-mysql-configuration-includes-mysql-57
thank you!!!!!!!
where is part 2????
Consider this a part 2: ua-cam.com/video/noFn2sgQiNw/v-deo.html
Hello? Guys.
Anyone?