Re: Suggestions for InnoDB files
- Original Message - From: Adarsh Sharma adarsh.sha...@orkash.com Dear all, I have doubt regarding the storage structure for Innodb files : Our database server has the following paths : /dev/sda5 69G 35G 32G52% /hdd1-1 /dev/sdb1 274G 225G 36G 87% /hdd2-1 /dev/sdc5 274G 225G 36G 87% /hdd3-1 /dev/sdd5 274G 218G 43G 84% /hdd4-1 /dev/sde1 266G 184G 69G 73% /hdd5-1 Interesting, but why like this instead of simply larger disks or raidsets ? Is it better to have innodb_file_per_table on. or innodb_data_file_path = /hdd2-1/innodb_data1/ibdata1:8G;/hdd3-1/innodb_data1/ibdata2:8G;/hdd4-1/innodb_data1/ibdata3:8G;/hdd2-1/innodb_data1/ibdata4:8G; [unmanageable mess cut] Why would you use 8G datafiles instead of large, partition-filling ones? which is currently set because to increase performance to read from separate small files instead of reading from one large one because one table is expected to grow more than 300 GB some tables are near about 60-80 GB increasing day by day. I should check up on InnoDB internals wether it strips across datafiles, but from a disk point of view, many smaller files aren't likely to be faster than one large one. Make sure the disk /hdd2-1/innodb_data1 is big enough /hdd2-1/innodb_data1 is going to need be a large RAID10 set A good RAID10 is recommended for databases anyway; I suggest you go with that. What is the best configuration for them so that we doesn't hit performance issues. Performance issues are oftentimes more dependant on how you use the DB than how you set it up; but a good setup never hurt anyone, of course. Consider throwing all those disk partitions into a single RAID10 set, either through underlying hardware or using MD on Linux. Even if you already have hardware RAID under those devices and can't modify that, consider concatenating the individual devices with LVM to benefit from striping. InnoDB file-per-table should yield roughly the same performance as global datafiles, albeit with more file descriptors used. If you want to be able to reclaim space, go for file-per-table; if all space is for InnoDB anyway, monolithic storage might be slightly more convenient. I seem to recall InnoDB can use raw devices, too; I'm not sure wether there's a big performance gain, though. I that Oracle has stepped down from recommending that in recent years, stating only marginal gains. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysql binlog format
Hiya I run master - master replication for my MySQL replication setup. Ive just upgraded to Mysql 5.1 and as a result im seeing the following in my syslog. 'Statement may not be safe to log' Ive been reading up on the following. http://dev.mysql.com/doc/refman/5.1/en/replication-sbr-rbr.html http://www.slideshare.net/ligaya/mysql-51-replication (See from slide 12.) The question I would like to ask is. Is it safe to just change the binlog format? Or is a preprocedure that needs to first be exercised. With the above warning, is this something I can ignore, or is there a serious matter (silly question I know, but I felt I needed to ask it). After reading the above urls, im still stuck as to which binlog format to use. Im leaning toward mixed, but the part of its still testing is a little nerving. And then lastly, if the change is made, and something goes wrong. Is there a role back procedure, or can I just change the binlog format back to STATEMENT. If anyone can help me understand this, it would greatly be appreciated. Kind Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Suggestions for InnoDB files
From: Adarsh Sharma adarsh.sha...@orkash.com Johan De Meersman wrote: Interesting, but why like this instead of simply larger disks or raidsets ? It's the IT-Admin Issue , I can't question that and we have only disks of 300GB ( SAS ). Your admin is supposed to provide services that benefit the application you need to run on the server. You're stuck with the hardware, but not the setup. Why would you use 8G datafiles instead of large, partition-filling ones? What is your recommendations for number of ibdata files , keeping in Mind Raid10 is not used and the size of tables . Because in RAID10 : We can utilize 50 – 55 percent size of hard disk.(50-55 % of 4 hard disk total space if hard disks are 500 GB X 4 then we can utilize only 1 TB space from 2 TB. Correct. That's the price you pay for the performance and redundancy RAID10 gives you. Nothing is free in life :-) Incidentally, it's going to be exactly 50% - I'll be very interested to see where he pulls those extra 5% from. You could ostensibly go for RAID5, which will allow you to use 1.5 TB off those same four disks, at a minor loss of disk redundancy (only one may fail) and some loss of performance - but still better than no RAID at all. If you want to lose no space at all, use RAID0 (striping) to increase performance, but that offers no disk redundancy at all - single disk fails, you lose all data. As a small overview, RAID 10 gives you the benefits of striping (data for a single file is split over multiple disks) so reads and writes faster, AND of mirrorring (every block is available on multiple disks, which provides insurance data loss when a disk breaks and additionally increases the read speed even more. You won't actually quadruple the read speed, but I wouldn't be surprised to see it triple on a 4-disk RAID 10. RAID 5 uses one of your disks for redundancy purposes, so any single disk may fail and you'll still have all your data. Data is striped, so disk performance also increases, although not as much as mirrorring. This is however the most CPU-intensive form, as checksumming over all disks happens at every write. This also makes that write speed won't see as much benefit. RAID 0 has no redundancy whatsoever - if anything you could say it's worse than data over multiple disks, because if one disk fails the entire volume is lost. Because it offers striping, however, it gives performance a good boost. Software RAID is not reliable on production environment because software raid is dependent on hardware and software both thing if one thing go down then it will not work, but in hardware raid there is no role of software every thing is depend on hardware. But, We are not able to afford Hardware RAID. Maybe you shouldn't have an OS then, either; because if that fails everything is down? My word, if that's his excuse, I seriously recommend you get a better admin. Software RAID offers the same or better performance than hardware RAID, save for the real high-end RAID cards. Additionally it offers more flexibility in the setup - many combinations of RAID levels are possible, whereas the majority of controllers offer 1, 5 and 10 at most. An additional benefit that is not to be laughed at, especially if you're on a budget, is that software RAID will work regardless of the hardware involved. Hardware RAID controllers tend to have their own specific set of metadata on the disks, and if your controller breaks, you had better manage to get the exact same one, or you risk not being able to read your disks. Sofware RAID, by virtue of being software, can simply be reinstalled on another system if need be. Tell MD to scan for and assemble RAID arrays and it'll just find the appropriate partitions and match the pieces together. No more accidentally putting a disk in the wrong bay and having it break the RAIDset. (I'll admit that has become rare with controllers getting smarter over the years, but I've seen multi-terabyte arrays go useless because some idiot operator switched two disks into the wrong bays) So, yes, my recommendation remains the same: switch the system to software RAID; preferably 10, 5 or 0 if you really need all that space. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql binlog format
- Original Message - From: Brent Clark brentgclarkl...@gmail.com 'Statement may not be safe to log' Heh. Some of those statements weren't particularly safe in previous versions, either, but they didn't whine :-p Roughly, what it comes down to is that statements that contain things that may be different on master and slave at the respective time of execution might replicate differently. For some things (like date() calls) this is solved by replacing them with a constant before they get logged (now() is replaced with the actual timestamp, for example), while for other things (unORDERed selects) it may not be a simple. Row logging is another take on it: instead of logging the statement, it logs the actual changes, thereby neatly sidestepping the problem. This brings other issues, though. The question I would like to ask is. Is it safe to just change the binlog format? Or is a preprocedure that needs to first be exercised. And then lastly, if the change is made, and something goes wrong. Is there a role back procedure, or can I just change the binlog format back to STATEMENT. AFAIK, the log format is automatically detected by the slave, so there should be no problem in switching between the two (well, three) forms. The very existence of Mixed format logging is actually proof of that, in a sense :-) If something goes wrong, however, it's a reasonable assumption that your replication got messed up, so it's highly recommended to reinitialize the slave at that point, as wel as changing back to statement-based logs. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Suggestions for InnoDB files
Johan De Meersman wrote: From: Adarsh Sharma adarsh.sha...@orkash.com Johan De Meersman wrote: Interesting, but why like this instead of simply larger disks or raidsets ? It's the IT-Admin Issue , I can't question that and we have only disks of 300GB ( SAS ). Your admin is supposed to provide services that benefit the application you need to run on the server. You're stuck with the hardware, but not the setup. Why would you use 8G datafiles instead of large, partition-filling ones? What is your recommendations for number of ibdata files , keeping in Mind Raid10 is not used and the size of tables . Because in RAID10 : We can utilize 50 – 55 percent size of hard disk.(50-55 % of 4 hard disk total space if hard disks are 500 GB X 4 then we can utilize only 1 TB space from 2 TB. Correct. That's the price you pay for the performance and redundancy RAID10 gives you. Nothing is free in life :-) Incidentally, it's going to be exactly 50% - I'll be very interested to see where he pulls those extra 5% from. You could ostensibly go for RAID5, which will allow you to use 1.5 TB off those same four disks, at a minor loss of disk redundancy (only one may fail) and some loss of performance - but still better than no RAID at all. If you want to lose no space at all, use RAID0 (striping) to increase performance, but that offers no disk redundancy at all - single disk fails, you lose all data. As a small overview, RAID 10 gives you the benefits of striping (data for a single file is split over multiple disks) so reads and writes faster, AND of mirrorring (every block is available on multiple disks, which provides insurance data loss when a disk breaks and additionally increases the read speed even more. You won't actually quadruple the read speed, but I wouldn't be surprised to see it triple on a 4-disk RAID 10. RAID 5 uses one of your disks for redundancy purposes, so any single disk may fail and you'll still have all your data. Data is striped, so disk performance also increases, although not as much as mirrorring. This is however the most CPU-intensive form, as checksumming over all disks happens at every write. This also makes that write speed won't see as much benefit. RAID 0 has no redundancy whatsoever - if anything you could say it's worse than data over multiple disks, because if one disk fails the entire volume is lost. Because it offers striping, however, it gives performance a good boost. Software RAID is not reliable on production environment because software raid is dependent on hardware and software both thing if one thing go down then it will not work, but in hardware raid there is no role of software every thing is depend on hardware. But, We are not able to afford Hardware RAID. Maybe you shouldn't have an OS then, either; because if that fails everything is down? My word, if that's his excuse, I seriously recommend you get a better admin. Software RAID offers the same or better performance than hardware RAID, save for the real high-end RAID cards. Additionally it offers more flexibility in the setup - many combinations of RAID levels are possible, whereas the majority of controllers offer 1, 5 and 10 at most. An additional benefit that is not to be laughed at, especially if you're on a budget, is that software RAID will work regardless of the hardware involved. Hardware RAID controllers tend to have their own specific set of metadata on the disks, and if your controller breaks, you had better manage to get the exact same one, or you risk not being able to read your disks. Sofware RAID, by virtue of being software, can simply be reinstalled on another system if need be. Tell MD to scan for and assemble RAID arrays and it'll just find the appropriate partitions and match the pieces together. No more accidentally putting a disk in the wrong bay and having it break the RAIDset. (I'll admit that has become rare with controllers getting smarter over the years, but I've seen multi-terabyte arrays go useless because some idiot operator switched two disks into the wrong bays) So, yes, my recommendation remains the same: switch the system to software RAID; preferably 10, 5 or 0 if you really need all that space. A Heartiest Thanks from my heart for explaining all these things in a fantastic manner. I agreed with your suggestions but one thing which isn't explained from your side , as you go deeper in RAID point. Q:- What is your recommendations for number of ibdata files , would it be Make sure the disk /hdd2-1/innodb_data1 is big enough and it doesn't affect performance. I need your help while configuring RAID10 on a Server, may be next week. Best Regards, Adarsh Sharma
Re: Suggestions for InnoDB files
- Original Message - From: Adarsh Sharma adarsh.sha...@orkash.com Johan De Meersman wrote: A Heartiest Thanks from my heart for explaining all these things in a fantastic manner. I agreed with your suggestions but one thing which isn't explained from your side , as you go deeper in RAID point. Q:- What is your recommendations for number of ibdata files , would it be Make sure the disk /hdd2-1/innodb_data1 is big enough and it doesn't affect performance. Roughly, yes - file-per-table is only useful if you need to be able to reclaim the space for non-InnoDB data; and I don't think InnoDB stripes across datafiles, so just use one large file on one RAID partition. Saves on file descriptors, saves on header space. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
mysql using aio/raw device on linux
hi, Guys One Q: Can mysql binlog use raw device on Linux? Can we use asynch IO for binlog writing? sequential non-qio fsync is slowing our throughput... Thx -- Regards Zhu Chao
RE: Suggestions for InnoDB files
You should use a simpl data path and create a separate tablespace for each InnoDB file innodb_data_file_path=ibdata1:10M:autoextend innodb_file_per_table This way, ibdata1 only contains the metadata and MVCC control data for all InnoDB files and transactions Awhile back, you ran a query to get the Diskspace used from you data and sent back this: ++--+--+--+ | Storage Engine | Data Size| Index Size | Table Size | ++--+--+--+ | MyISAM | 0.010 TB | 0.001 TB | 0.011 TB | | InnoDB | 0.161 TB | 0.010 TB | 0.171 TB | | Total | 0.171 TB | 0.011 TB | 0.182 TB | ++--+--+--+ This means you only have something like 200GB of data. Your architecture has 101 files that are 8G each to house all InnoDB data. You had a 553GB table which must be spread out over at least 69 of those 8G files. You should convert over to 6 x 300GB RAID10 set which will give you 824GB of space to start. Rolando A. Edwards MySQL DBA (SCMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: Adarsh Sharma [mailto:adarsh.sha...@orkash.com] Sent: Wednesday, March 16, 2011 5:33 AM To: Johan De Meersman Cc: mysql@lists.mysql.com Subject: Re: Suggestions for InnoDB files Johan De Meersman wrote: From: Adarsh Sharma adarsh.sha...@orkash.com Johan De Meersman wrote: Interesting, but why like this instead of simply larger disks or raidsets ? It's the IT-Admin Issue , I can't question that and we have only disks of 300GB ( SAS ). Your admin is supposed to provide services that benefit the application you need to run on the server. You're stuck with the hardware, but not the setup. Why would you use 8G datafiles instead of large, partition-filling ones? What is your recommendations for number of ibdata files , keeping in Mind Raid10 is not used and the size of tables . Because in RAID10 : We can utilize 50 – 55 percent size of hard disk.(50-55 % of 4 hard disk total space if hard disks are 500 GB X 4 then we can utilize only 1 TB space from 2 TB. Correct. That's the price you pay for the performance and redundancy RAID10 gives you. Nothing is free in life :-) Incidentally, it's going to be exactly 50% - I'll be very interested to see where he pulls those extra 5% from. You could ostensibly go for RAID5, which will allow you to use 1.5 TB off those same four disks, at a minor loss of disk redundancy (only one may fail) and some loss of performance - but still better than no RAID at all. If you want to lose no space at all, use RAID0 (striping) to increase performance, but that offers no disk redundancy at all - single disk fails, you lose all data. As a small overview, RAID 10 gives you the benefits of striping (data for a single file is split over multiple disks) so reads and writes faster, AND of mirrorring (every block is available on multiple disks, which provides insurance data loss when a disk breaks and additionally increases the read speed even more. You won't actually quadruple the read speed, but I wouldn't be surprised to see it triple on a 4-disk RAID 10. RAID 5 uses one of your disks for redundancy purposes, so any single disk may fail and you'll still have all your data. Data is striped, so disk performance also increases, although not as much as mirrorring. This is however the most CPU-intensive form, as checksumming over all disks happens at every write. This also makes that write speed won't see as much benefit. RAID 0 has no redundancy whatsoever - if anything you could say it's worse than data over multiple disks, because if one disk fails the entire volume is lost. Because it offers striping, however, it gives performance a good boost. Software RAID is not reliable on production environment because software raid is dependent on hardware and software both thing if one thing go down then it will not work, but in hardware raid there is no role of software every thing is depend on hardware. But, We are not able to afford Hardware RAID. Maybe you shouldn't have an OS then, either; because if that fails everything is down? My word, if that's his excuse, I seriously recommend you get a better admin. Software RAID offers the same or better performance than hardware RAID, save for the real high-end RAID cards. Additionally it offers more flexibility in the setup - many combinations of RAID levels are possible, whereas the
Re: Backup Policy
Interestingly, this page does not say anything about MySQL Enterprise Backups. On Mar 15, 2011, at 8:48 AM, a.sm...@ukgrid.net wrote: Hi, there is a lot of info on different backup methods here: http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html For example, for incremental backups see Making Incremental Backups by Enabling the Binary Log, cheers Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=a...@apple.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL Community Server 5.5.10 has been released
Dear MySQL users, MySQL 5.5.10 is a new version of the 5.5 production release of the world's most popular open source database. MySQL 5.5.10 is recommended for use on production systems. MySQL 5.5 includes several high-impact enhancements to improve the performance and scalability of the MySQL Database, taking advantage of the latest multi-CPU and multi-core hardware and operating systems. In addition, with release 5.5, InnoDB is now the default storage engine for the MySQL Database, delivering ACID transactions, referential integrity and crash recovery by default. MySQL 5.5 also provides a number of additional enhancements including: - Significantly improved performance on Windows, with various Windows specific features and improvements - Higher availability, with new semi-synchronous replication and Replication Heart Beat - Improved usability, with Improved index and table partitioning, SIGNAL/RESIGNAL support and enhanced diagnostics, including a new PERFORMANCE_SCHEMA For a more complete look at what's new in MySQL 5.5, please see the following resources: MySQL 5.5 is GA, Interview with Tomas Ulin: http://dev.mysql.com/tech-resources/interviews/thomas-ulin-mysql-55.html Documentation: http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html Whitepaper: What's New in MySQL 5.5: http://dev.mysql.com/why-mysql/white-papers/mysql-wp-whatsnew-mysql-55.php If you are running a MySQL production level system, we would like to direct your attention to MySQL Enterprise Edition, which includes the most comprehensive set of MySQL production, backup, monitoring, modeling, development, and administration tools so businesses can achieve the highest levels of MySQL performance, security and uptime. http://mysql.com/products/enterprise/ For information on installing MySQL 5.5.10 on new servers, please see the MySQL installation documentation at http://dev.mysql.com/doc/refman/5.5/en/installing.html For upgrading from previous MySQL releases, please see the important upgrade considerations at: http://dev.mysql.com/doc/refman/5.5/en/upgrading.html MySQL Database 5.5 is available in source and binary form for a number of platforms from our download pages at: http://dev.mysql.com/downloads/mysql/ Not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc.: http://forge.mysql.com/wiki/Contributing The following section lists the changes in the MySQL source code since the previous released version of MySQL 5.5. It may also be viewed online at: http://dev.mysql.com/doc/refman/5.5/en/news-5-5-10.html D.1.2. Changes in MySQL 5.5.10 Configuration Notes: * MySQL releases are now built on all platforms using CMake rather than the GNU autotools, so autotools support has been removed. For instructions on building MySQL with CMake, see Section 2.11, Installing MySQL from Source. Third-party tools that need to extract the MySQL version number formerly found in configure.in can use the VERSION file. See Section 2.11.6, MySQL Configuration and Third-Party Tools. C API Notes: * Incompatible Change: The shared library version of the client library was increased to 18 to reflect ABI changes, and avoid compatibility problems with the client library in MySQL 5.1. Note that this is an incompatible change between 5.5.10 and earlier 5.5 versions, so client programs that use the 5.5 client library should be recompiled against the 5.5.10 client library. Functionality added or changed: * MySQL distributions now include auth_socket, a server-side authentication plugin that authenticates clients that connect from the local host through the Unix socket file. The plugin uses the SO_PEERCRED socket option to obtain information about the user running the client program (and thus can be built only on systems that support this option. For a connection to succeed, the plugin requires a match between the login name of the connecting client user and the MySQL user name presented by the client program. For more information, see Section 5.5.6.1.4, The Socket Peer-Credential Authentication Plugin. (Bug #59017, Bug #11765993) * The mysql_upgrade, mysqlbinlog, mysqlcheck, mysqlimport, mysqlshow, and mysqlslap clients now have --default-auth and --plugin-dir options for specifying which authentication plugin and plugin directory to use. (Bug #58139) * Boolean system variables can be enabled at run time by setting them to the value ON or OFF, but previously this did not work at server startup. Now at startup such variables can be enabled by setting them to ON or TRUE, or disabled
