Super helpful, was trying to get my head around what buffer pool is and how i could optimise the performance. Exactly what i needed to proceed. Thanks a ton!!!
Hope there will a followup video addressing other innodb_x config options. Preferably starting with brothers and sisters of the buffer_pool_size. There are some rules of thumb, but it is not overly trivial to get them right.
Wow! A stunner. Thanks a million. May I ask, is this possible via PHPMYADMIN? If yes, please, how? While I have your attention, is there a way to enlarge the max size of mysql database Tables? Again, in PHPMYADMIN interface.
To configure this value to can update it in the my.cnf file, like I show in the video. Looks like IMH recommends SSHing into your server to update it: www.inmotionhosting.com/support/server/databases/edit-mysql-my-cnf/
MySQL returning same select query result from buffer, where there is update query parallelly executing from client application, how to prevent this, so i can get updated result from mysql?
Is the update and the select queries happening in a single transaction, or two separate ones? If the same, then just ensure you runt he update before the select. If the latter, you should ensure that the update transaction completed before you try to select the new data. If you are using PlanetScale (or something similar) and are updating on the primary and then selecting on a replica, there can be replica lag. In this case, you'd either have to live with it, or make both requests to your primary.
@@PlanetScale I have multiple client connected with same DB table where counter field is getting updated first and then select query is being executed to get updated value, each client needs updated value but randomly on client PC I receiving old field value instead of updated one and when i fire query again in few seconds immediately its giving me updated value. How i can prevent this?
What is the tradeoff when increasing the values of innodb_buffer_pool_size and innodb_buffer_pool_chunk_size? Will it affect the server performance for other operations, such as creating indexes when a new record has been inserted?
Configuring mysql to use multiple puffer pol instances (via innodb_buffer_pool_instances and innodb_buffer_pool_chunk_size) causes mysql to create multiple pools. This can help reduce contention in situations where you have a lot of concurrent connections / queries executing, as the pages get divided up amongst the available buffer pools. The docs have some additional info that might help! dev.mysql.com/doc/refman/8.0/en/innodb-multiple-buffer-pools.html
Another key performance setting if you do not use replication and can afford to lose 1 sec of transactions in case of a server crash is to disable log_bin and set innodb_flush_log_at_trx_commit = 2
As PlanetScale we use Vitess (vitess.io/) to handle query routing, high availability, etc. We recently made a video about what happens here so check this one out if interested! ua-cam.com/video/9FYedahsJxE/v-deo.html
It’s very workload dependent, you are only reading so you’re showing kind of best case scenario. If you also have mutations it would evict parts of cache so the result wouldn’t be as great. Needless to say except that detail it is very very relevant. Moreover I had opportunity to monitor a hardware MySQL server which were backing up a highly loaded web app before AWS migration and an RDS after one. The MySQL is so underestimated, with right config it was delivering sub ms for majority of queries. Later we had to add redis caching (to compensate for AWS RDS disadvantages) and even redis was for the very list not better than the hardware MySQL. I’d say for majority of cases MySQL was beating up redis. But RDS Aurora wasn’t even close. When we analyzed the most of it was coming from network delays. The back side of cloud services.
It definitely is workload dependent! For example, if you have a large data set but the vast majority of it is never queried, you might only need to size the buffer pool such that the hot pages can fit in memory, and then you'll be fine.
What would you like to know? The amount infrastructure required for even our smallest possible database is quite costly. This video explains it well: ua-cam.com/video/9FYedahsJxE/v-deo.html
@@HollyGuevaraPS this is no explanation, other smaller companies also continue to have significant expenses with infrstructure and continue to be free tiers. It is a good thing that other companies similar to planetscale continue to offer super free tiers and it was easy to go there,
@@TheMrBazil Which companies? There was a lot of convo about this on Twitter recently and couple founders at other database companies said themselves that their free tier isn't a significant expense to them due to their architecture. I do wish we could offer a much cheaper tier, and it is something we're still discussing internally.
It's still dirt cheap for the value you get. I really don't understand why everyone feels entitled to get something for nothing these days. I'd rather have a viable DB provider than one that's burning money left and right.
Don't get me wrong, it's a great video. But i'm still a little bit disappointed because there is so many parameters to control the buffer cache and i was expecting more. Please remember that a "good" hit-ratio does not mean that your application is tuned.
Absolutely! This was a very simple benchmark. Real-world workloads are going to be much more complex, and you may need to do some deeper analysis to decide how to tune your buffer pool. If you use PlanetScale, we spin your DB up with it pre-tuned.
Thank you so much for this video. Really high quliaty and direct to the point. Learned a lot!
Glad you got a lot out of it.
Super helpful, was trying to get my head around what buffer pool is and how i could optimise the performance. Exactly what i needed to proceed. Thanks a ton!!!
Glad to have helped
Hope there will a followup video addressing other innodb_x config options. Preferably starting with brothers and sisters of the buffer_pool_size. There are some rules of thumb, but it is not overly trivial to get them right.
Cool idea 👊
I hope you do more videos explaining some other useful configurations.
Thanks Ben!
That's a great idea!
Wow! A stunner.
Thanks a million.
May I ask, is this possible via PHPMYADMIN? If yes, please, how?
While I have your attention, is there a way to enlarge the max size of mysql database Tables? Again, in PHPMYADMIN interface.
To configure this value to can update it in the my.cnf file, like I show in the video. Looks like IMH recommends SSHing into your server to update it: www.inmotionhosting.com/support/server/databases/edit-mysql-my-cnf/
Hii thank you for ur content, can you please share the python stress program or github url ?
MySQL returning same select query result from buffer, where there is update query parallelly executing from client application, how to prevent this, so i can get updated result from mysql?
Is the update and the select queries happening in a single transaction, or two separate ones? If the same, then just ensure you runt he update before the select. If the latter, you should ensure that the update transaction completed before you try to select the new data.
If you are using PlanetScale (or something similar) and are updating on the primary and then selecting on a replica, there can be replica lag. In this case, you'd either have to live with it, or make both requests to your primary.
@@PlanetScale I have multiple client connected with same DB table where counter field is getting updated first and then select query is being executed to get updated value, each client needs updated value but randomly on client PC I receiving old field value instead of updated one and when i fire query again in few seconds immediately its giving me updated value. How i can prevent this?
Does PlanetScale automatically configure the option based on your plan, or do you need to adjust it manually?
Correct! One of the many advantages of using a managed service :)
What is the tradeoff when increasing the values of innodb_buffer_pool_size and innodb_buffer_pool_chunk_size? Will it affect the server performance for other operations, such as creating indexes when a new record has been inserted?
Configuring mysql to use multiple puffer pol instances (via innodb_buffer_pool_instances and innodb_buffer_pool_chunk_size) causes mysql to create multiple pools. This can help reduce contention in situations where you have a lot of concurrent connections / queries executing, as the pages get divided up amongst the available buffer pools. The docs have some additional info that might help!
dev.mysql.com/doc/refman/8.0/en/innodb-multiple-buffer-pools.html
thanks in advance for your answer 💯
Another key performance setting if you do not use replication and can afford to lose 1 sec of transactions in case of a server crash is to disable log_bin and set innodb_flush_log_at_trx_commit = 2
Great insight. Maybe this should be another video!
I hope you can make video about mysql router
As PlanetScale we use Vitess (vitess.io/) to handle query routing, high availability, etc. We recently made a video about what happens here so check this one out if interested! ua-cam.com/video/9FYedahsJxE/v-deo.html
@@PlanetScale thank you
It’s very workload dependent, you are only reading so you’re showing kind of best case scenario.
If you also have mutations it would evict parts of cache so the result wouldn’t be as great.
Needless to say except that detail it is very very relevant.
Moreover I had opportunity to monitor a hardware MySQL server which were backing up a highly loaded web app before AWS migration and an RDS after one. The MySQL is so underestimated, with right config it was delivering sub ms for majority of queries. Later we had to add redis caching (to compensate for AWS RDS disadvantages) and even redis was for the very list not better than the hardware MySQL. I’d say for majority of cases MySQL was beating up redis. But RDS Aurora wasn’t even close. When we analyzed the most of it was coming from network delays. The back side of cloud services.
It definitely is workload dependent! For example, if you have a large data set but the vast majority of it is never queried, you might only need to size the buffer pool such that the hot pages can fit in memory, and then you'll be fine.
This is a great video
Thank you
Better make a video of why you removed the free tier plan
They already explained in the blog post
What would you like to know? The amount infrastructure required for even our smallest possible database is quite costly. This video explains it well: ua-cam.com/video/9FYedahsJxE/v-deo.html
@@HollyGuevaraPS this is no explanation, other smaller companies also continue to have significant expenses with infrstructure and continue to be free tiers. It is a good thing that other companies similar to planetscale continue to offer super free tiers and it was easy to go there,
@@TheMrBazil Which companies? There was a lot of convo about this on Twitter recently and couple founders at other database companies said themselves that their free tier isn't a significant expense to them due to their architecture. I do wish we could offer a much cheaper tier, and it is something we're still discussing internally.
It's still dirt cheap for the value you get. I really don't understand why everyone feels entitled to get something for nothing these days. I'd rather have a viable DB provider than one that's burning money left and right.
Don't get me wrong, it's a great video. But i'm still a little bit disappointed because there is so many parameters to control the buffer cache and i was expecting more. Please remember that a "good" hit-ratio does not mean that your application is tuned.
Absolutely! This was a very simple benchmark. Real-world workloads are going to be much more complex, and you may need to do some deeper analysis to decide how to tune your buffer pool.
If you use PlanetScale, we spin your DB up with it pre-tuned.
Niiiice!