[Maria-discuss] question regarding innodb compression
Hi all, Due to space constraints we are considering the innodb compression option for some of our larger tables. We're not compressing all the tables, but only a select few to begin with. Our current planned process for this is to SET GLOBAL innodb_file_format=Barracuda; create compressed table, transfer data, and switch innodb_file_format back to Antelope. My question is around having this mix of table file formats in the same database. I've tried test queries and they were fine, but is it ok to have a mix of both Antelope and Barracuda file format tables in the db for a production environment? Thanks, Rohan
I had a mix of Antelope/Barracuda for years. As far as I can tell, this never caused problems. Regards Federico -------------------------------------------- Mer 29/10/14, Rohan M C <rohanmc@gmail.com> ha scritto: Oggetto: [Maria-discuss] question regarding innodb compression A: maria-discuss@lists.launchpad.net Data: Mercoledì 29 ottobre 2014, 08:36 Hi all, Due to space constraints we are considering the innodb compression option for some of our larger tables. We're not compressing all the tables, but only a select few to begin with. Our current planned process for this is to SET GLOBAL innodb_file_format=Barracuda; create compressed table, transfer data, and switch innodb_file_format back to Antelope. My question is around having this mix of table file formats in the same database. I've tried test queries and they were fine, but is it ok to have a mix of both Antelope and Barracuda file format tables in the db for a production environment? Thanks,Rohan -----Segue allegato----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Setting innodb_file_format Barracuda needs innodb_file_per_table. So, your (new created) Barracuda table is putted into one .ibd file. Any other your Antelope tables (in other .ibd files or ibdata1) don't be affected that operation. I have mix of Antelope/Barracuda for years in our production environment too, without any problem of that. Regards, 2014-10-29 17:35 GMT+09:00 Federico Razzoli <federico_raz@yahoo.it>:
I had a mix of Antelope/Barracuda for years. As far as I can tell, this never caused problems.
Regards Federico
-------------------------------------------- Mer 29/10/14, Rohan M C <rohanmc@gmail.com> ha scritto:
Oggetto: [Maria-discuss] question regarding innodb compression A: maria-discuss@lists.launchpad.net Data: Mercoledì 29 ottobre 2014, 08:36
Hi all, Due to space constraints we are considering the innodb compression option for some of our larger tables. We're not compressing all the tables, but only a select few to begin with. Our current planned process for this is to SET GLOBAL innodb_file_format=Barracuda; create compressed table, transfer data, and switch innodb_file_format back to Antelope. My question is around having this mix of table file formats in the same database. I've tried test queries and they were fine, but is it ok to have a mix of both Antelope and Barracuda file format tables in the db for a production environment? Thanks,Rohan -----Segue allegato-----
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Hi Frederico & Yoku, Thanks for your responses! Also, I had a question regarding the environment in which you've been using compression- are they virtualized instances or bare metal? Our db instances are m1.xlarge aws ec2 instances (4 vcpu + 15G mem) and this is somewhat an unknown for us which is why we're only moving some tables to compressed to try out- I'm just trying to read up and find out as much as I can about compression and any pitfalls as I can so that I can be better prepared- I am aware of the basic concepts that it's more cpu intensive, but most of the material I've found states that they're running on fusionio etc...so any insight/reading into running on virtualized instances would help greatly Jan, We do have innodb_file_per_table set to 1. About your question - as I've mentioned we've never used compression or barracuda before so we want to tread lightly. Also considering that Antelope is the default file format, I assumed that it was (possibly) stable/preferrable. Do you know if this is the case? Thanks, Rohan On Wed, Oct 29, 2014 at 2:14 AM, yoku ts. <yoku0825@gmail.com> wrote:
Setting innodb_file_format Barracuda needs innodb_file_per_table. So, your (new created) Barracuda table is putted into one .ibd file. Any other your Antelope tables (in other .ibd files or ibdata1) don't be affected that operation.
I have mix of Antelope/Barracuda for years in our production environment too, without any problem of that.
Regards,
2014-10-29 17:35 GMT+09:00 Federico Razzoli <federico_raz@yahoo.it>:
I had a mix of Antelope/Barracuda for years. As far as I can tell, this never caused problems.
Regards Federico
-------------------------------------------- Mer 29/10/14, Rohan M C <rohanmc@gmail.com> ha scritto:
Oggetto: [Maria-discuss] question regarding innodb compression A: maria-discuss@lists.launchpad.net Data: Mercoledì 29 ottobre 2014, 08:36
Hi all, Due to space constraints we are considering the innodb compression option for some of our larger tables. We're not compressing all the tables, but only a select few to begin with. Our current planned process for this is to SET GLOBAL innodb_file_format=Barracuda; create compressed table, transfer data, and switch innodb_file_format back to Antelope. My question is around having this mix of table file formats in the same database. I've tried test queries and they were fine, but is it ok to have a mix of both Antelope and Barracuda file format tables in the db for a production environment? Thanks,Rohan -----Segue allegato-----
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Hi Rohan, I'm sorry my response is late. Just in information, our environments are bare-metal servers in our datacenter. They have about 120~ 400GB InnoDB datas in SSD, 12~ 24 CPU cores, 32~ 192GB memory, in each server. This opinion is based on my experience, please be sure to confirm it under your environment. InnoDB Compression has two point to care, I think. They are "buffer pool mishits" and "flushing dirty pages". InnoDB tables slow down when you query the pages which are not in buffer pool, as you know. Compressed InnoDB tables are more slower than uncompressed one in this case. And the case when buffer pool doesn't have enough free page and most old page in LRU list is dirty, means the case when InnoDB need to flush dirty pages, is slower too. Compressed InnoDB table compresses/decompresses when InnoDB writes into/reads from data file in storage. So, if your InnoDB doesn't have enough buffer pool, copmpressed tables takes you heavy latency, exchanging your disk spaces. Taking care to your dirty pages and buffer pool hit ratio. And I remind you again, these are based on *my* experience, please confirm by yourself, and let me know if you have other situation :) Regards, yoku0825 2014-10-30 10:26 GMT+09:00 Rohan M C <rohanmc@gmail.com>:
Hi Frederico & Yoku, Thanks for your responses! Also, I had a question regarding the environment in which you've been using compression- are they virtualized instances or bare metal? Our db instances are m1.xlarge aws ec2 instances (4 vcpu + 15G mem) and this is somewhat an unknown for us which is why we're only moving some tables to compressed to try out- I'm just trying to read up and find out as much as I can about compression and any pitfalls as I can so that I can be better prepared- I am aware of the basic concepts that it's more cpu intensive, but most of the material I've found states that they're running on fusionio etc...so any insight/reading into running on virtualized instances would help greatly
Jan, We do have innodb_file_per_table set to 1. About your question - as I've mentioned we've never used compression or barracuda before so we want to tread lightly. Also considering that Antelope is the default file format, I assumed that it was (possibly) stable/preferrable. Do you know if this is the case?
Thanks, Rohan
On Wed, Oct 29, 2014 at 2:14 AM, yoku ts. <yoku0825@gmail.com> wrote:
Setting innodb_file_format Barracuda needs innodb_file_per_table. So, your (new created) Barracuda table is putted into one .ibd file. Any other your Antelope tables (in other .ibd files or ibdata1) don't be affected that operation.
I have mix of Antelope/Barracuda for years in our production environment too, without any problem of that.
Regards,
2014-10-29 17:35 GMT+09:00 Federico Razzoli <federico_raz@yahoo.it>:
I had a mix of Antelope/Barracuda for years. As far as I can tell, this never caused problems.
Regards Federico
-------------------------------------------- Mer 29/10/14, Rohan M C <rohanmc@gmail.com> ha scritto:
Oggetto: [Maria-discuss] question regarding innodb compression A: maria-discuss@lists.launchpad.net Data: Mercoledì 29 ottobre 2014, 08:36
Hi all, Due to space constraints we are considering the innodb compression option for some of our larger tables. We're not compressing all the tables, but only a select few to begin with. Our current planned process for this is to SET GLOBAL innodb_file_format=Barracuda; create compressed table, transfer data, and switch innodb_file_format back to Antelope. My question is around having this mix of table file formats in the same database. I've tried test queries and they were fine, but is it ok to have a mix of both Antelope and Barracuda file format tables in the db for a production environment? Thanks,Rohan -----Segue allegato-----
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (3)
-
Federico Razzoli -
Rohan M C -
yoku ts.