MySQL Performance Tuning: Part 1. Configuration (Covers MySQL 5.7)
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.
Пікірлер: 77
This was amazing!!! Thank you for your hard work and deep understanding!
This is simply awesome. Please keep up the great work
Thank you for your resources, they are great and you are a very good tutor!
I've been looking for this for a long time. Answered my biggest questions that I've had for years.
excellent video and all of things you need to optimize mysql database mentioned. thank you so much man.
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
@Speedemy
7 жыл бұрын
Thank you so much!
Very helpful and informative. Excellent video tutorial for MySQL DBAs
This video was actually INCREDIBLY helpful! I couldn't thank you enough for sharing your knowledge!
@Speedemy
7 жыл бұрын
Thanks, Deckard. I really appreciate it.
@vitalyRodero
2 жыл бұрын
Do you have the cnf.file by any chance?
Very useful and clear. Thanks for this great video and ressources.
That was a great tutorial ! Hopefully, that part 2 will be out soon.
@Speedemy
7 жыл бұрын
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. kzread.info/dash/bejne/oKN60JSsl7TPfto.html 2. kzread.info/dash/bejne/e4Kj2Muoj6nbhLg.html 3. kzread.info/dash/bejne/mGqay9uYlLm_qNo.html
@jeffherdzina6716
7 жыл бұрын
Thanks !!
@Darival92
7 жыл бұрын
pin this comment
really awesome...great explanation and very useful, thanks for uploading
superb , nailed it!! hope I'll find more videos from speedemy
Incredibly solid information!
thank you friend. better than most of the resources - more clear, informative, true.
@Speedemy
7 жыл бұрын
I really appreciate your feedback.
one of the greatest videos I've ever seen !!!
It was really awesome... waiting for the next Tutorials
Great explained and great speaker! Kudos!
Luckily found this video. It helps me a lot.
its simply excellent
Priceless... Thank you
I am looking this video since long time
Gracias!!, Excelente Video, solo con la primer variable, la respuesta del servidor cambió notablemente.
That was a great tutorial !
Great video! Thank you!
helpful and can get many handy things which is not included in the official ref .
Super clearly.thanks
Great tutorial very useful
This video is very helpful to me
Thanks Sir, worth to watch
Very Nice infromation .. Thank you So much Sir ..
Thank you sir. :)
Its very useful.. Thank you Sir...
@Speedemy
8 жыл бұрын
Thank you, Shailesh! I'm really happy about it.
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
Excellent
Thanks that was great :)
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.
Thank you so much
Puikus video!
Everyone is highly appreciating this video. I don't even know what is said there! 😞
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 ?
@edonur246
6 жыл бұрын
Sorry, my bad. I can read the ebook now. Thanks
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.
@Speedemy
7 жыл бұрын
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.
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.
Please publish more videos :)
Hey nice tutorial. Great help. I recently saw mysql 8.0 is available. Whats your recommendation on using that?
@AurimasMikalauskasPlus
7 жыл бұрын
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/
Hi, can the same config be used for mysql 8 ?
Is there Part 2 of this video?
fantastic, can you do a similar video for mysql8 :)
your website is down. can you share alternative link.
can you share your PPT slides please
Great Content. Thank you. But the links are not working.
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.
Guys, Anyone have the my.cnf files. It seems the website is no longer active. Please, share the file with me.
Should it be query_cache_type =OFF or query_cache_type=0
@Speedemy
7 жыл бұрын
Whichever you prefer. Both map to the same constant internally.
i like the ppt in this video,can u share it ?
@Speedemy
8 жыл бұрын
Sure, it's available here: www.slideshare.net/inner/mysql-performance-tuning-part-1-mysql-configuration-includes-mysql-57
@user-yc6xj1jd5y
8 жыл бұрын
thank you!!!!!!!
where is part 2????
@Speedemy
5 жыл бұрын
Consider this a part 2: kzread.info/dash/bejne/oKN60JSsl7TPfto.html
Hello? Guys. Anyone?