http://pinterest.com/riitoo/all-things-digital/
Monday, June 11, 2012
Saturday, June 9, 2012
Should you move from MyISAM to Innodb ?
There is significant portion of customers which are still using MyISAM when they come to us, so one of the big questions is when it is feasible to move to Innodb and when staying on MyISAM is preferred ?
I generally prefer to see Innodb as the main storage engine because it makes life much simpler in the end for most users – you do not get to deal with recovering tables on the crash or partially executed statements. Table locks is no more problem, hot backups are easy, though there are some important things which we have to consider on case by case basics before recommending the move.
Is MyISAM used as default or as a choice ? This is the most important question to ask upfront. Sometimes MyISAM is there just because it is default, in other cases this is deliberate choice with system being optimized to deal with MyISAM limits, for example there is a dedicated slave available for all long reporting queries. In case MyISAM was chosen not just happened to be it is important to build the good argument to suggest Innodb.
Application Readiness Application should be ready to work with Innodb, for example be ready to deal with deadlocks which can happen with Innodb even if you do not use transactions, but which are not existent with MyISAM. QA has to be performed as part of the move.
Performance Innodb has a lot to offer in terms of performance – Performance benefits and drawbacks. On the benefits side we usually see clustering by primary key, caching data, higher concurrency, background flushes while on the drawbacks side we see significantly large table size (especially if data size is close to memory size), generally slower writes, slower blob handling, concurrency issues, problems dealing with very large number of tables, slow data load and ALTER TABLE and others. Another big one is COUNT(*) without where clause which is often the show stopper for them move until it is worked around.
Operations What is good for MyISAM kills Innodb, such as copying binary tables between the servers. It is important the team understands Innodb and knows how to handle it, or be able to learn it. It is also important to adjust processes as required to work with Innodb. For example binary copy of one of the databases from the Slave to the dev envinronment works great for MyISAM but does not work with Innodb. Backup tools like “mysqlhotcopy” does not work etc. Note Performance also affects Operations aspects a lot – for example using mysqldump as a backup may well work for MyISAM but will start taking way too much time to do restore for Innodb. On large scale installations mysqldump does not work anyway but it may still work for you when you’re running MyISAM but instantly break upon upgrading to Innodb.
Features The MyISAM features which forbid moving to Innodb are typically Full Text Search and RTREE indexes/GIS with Full Text being much more common. There are workarounds for both of them, including dedicated MyISAM slave or shadow table but it is important to consider them.
How about Mixing Storage Engines ? Sure you can mix storage engines but I suggest you doing is wisely. It complicates operations tasks (backups, balancing, performance analyzes) as well as it exercises not so common paths in the MySQL server – in particular Optimizer may have harder time because costs between storage engines may not be well balanced or replication of mixed table types which is quite complicated.
I prefer to pick one storage engine (typically Innodb) and when use other tables when it really gives substantial gains. I would not switch table to MyISAM because it gives 5% performance improvement but I can perfectly use MyISAM (or Archive) for logging.
Innodb Needs Tuning As a final note about MyISAM to Innodb migration I should mention about Innodb tuning. Innodb needs tuning. Really. MyISAM for many applications can work well with defaults. I’ve seen hundreds of GB databases ran with MyISAM with default settings and it worked reasonably. Innodb needs resources and it will not work well with defaults a lot. Tuning MyISAM from defaults rarely gives more than 2-3 times gain while it can be as much as 10-50 times for Innodb tables in particular for write intensive workloads. Check here for details.
Note: As Few people questioned me, I indeed forgot to clarify the scope here – I’m mainly speaking about OLTP/ Traditional web applications. for Analytics things are a lot different.
This post is referred from mysql performance blog article
I generally prefer to see Innodb as the main storage engine because it makes life much simpler in the end for most users – you do not get to deal with recovering tables on the crash or partially executed statements. Table locks is no more problem, hot backups are easy, though there are some important things which we have to consider on case by case basics before recommending the move.
Is MyISAM used as default or as a choice ? This is the most important question to ask upfront. Sometimes MyISAM is there just because it is default, in other cases this is deliberate choice with system being optimized to deal with MyISAM limits, for example there is a dedicated slave available for all long reporting queries. In case MyISAM was chosen not just happened to be it is important to build the good argument to suggest Innodb.
Application Readiness Application should be ready to work with Innodb, for example be ready to deal with deadlocks which can happen with Innodb even if you do not use transactions, but which are not existent with MyISAM. QA has to be performed as part of the move.
Performance Innodb has a lot to offer in terms of performance – Performance benefits and drawbacks. On the benefits side we usually see clustering by primary key, caching data, higher concurrency, background flushes while on the drawbacks side we see significantly large table size (especially if data size is close to memory size), generally slower writes, slower blob handling, concurrency issues, problems dealing with very large number of tables, slow data load and ALTER TABLE and others. Another big one is COUNT(*) without where clause which is often the show stopper for them move until it is worked around.
Operations What is good for MyISAM kills Innodb, such as copying binary tables between the servers. It is important the team understands Innodb and knows how to handle it, or be able to learn it. It is also important to adjust processes as required to work with Innodb. For example binary copy of one of the databases from the Slave to the dev envinronment works great for MyISAM but does not work with Innodb. Backup tools like “mysqlhotcopy” does not work etc. Note Performance also affects Operations aspects a lot – for example using mysqldump as a backup may well work for MyISAM but will start taking way too much time to do restore for Innodb. On large scale installations mysqldump does not work anyway but it may still work for you when you’re running MyISAM but instantly break upon upgrading to Innodb.
Features The MyISAM features which forbid moving to Innodb are typically Full Text Search and RTREE indexes/GIS with Full Text being much more common. There are workarounds for both of them, including dedicated MyISAM slave or shadow table but it is important to consider them.
How about Mixing Storage Engines ? Sure you can mix storage engines but I suggest you doing is wisely. It complicates operations tasks (backups, balancing, performance analyzes) as well as it exercises not so common paths in the MySQL server – in particular Optimizer may have harder time because costs between storage engines may not be well balanced or replication of mixed table types which is quite complicated.
I prefer to pick one storage engine (typically Innodb) and when use other tables when it really gives substantial gains. I would not switch table to MyISAM because it gives 5% performance improvement but I can perfectly use MyISAM (or Archive) for logging.
Innodb Needs Tuning As a final note about MyISAM to Innodb migration I should mention about Innodb tuning. Innodb needs tuning. Really. MyISAM for many applications can work well with defaults. I’ve seen hundreds of GB databases ran with MyISAM with default settings and it worked reasonably. Innodb needs resources and it will not work well with defaults a lot. Tuning MyISAM from defaults rarely gives more than 2-3 times gain while it can be as much as 10-50 times for Innodb tables in particular for write intensive workloads. Check here for details.
Note: As Few people questioned me, I indeed forgot to clarify the scope here – I’m mainly speaking about OLTP/ Traditional web applications. for Analytics things are a lot different.
Thursday, June 7, 2012
Mobile Application Management (MAM) has Put MDM in its Place
This vendor-written tech primer has been edited by Network World to eliminate product promotion, but readers should note it will likely favor the submitter's approach.
The debate about the best tool set for enterprise mobility continues to rage between themobile application management (MAM) and mobile device management (MDM) camps.
MDM zealots ridicule MAM as a glorified app catalog and a security sieve, while MAM proponents say MDM's rigid and centralized control of all policies, user access, hardware/software and content sabotages the revolutionary benefits of enterprise mobility.
IN PICTURES: 10 mobile device management apps to take charge of BYOD
We firmly believe that MAM is a superior approach for the management of a vast majority of mobile enterprise applications, that it has leapfrogged MDM as a systems management best practice for mobility, and -- importantly -- incorporated its most useful security capabilities. Read more here...
The debate about the best tool set for enterprise mobility continues to rage between themobile application management (MAM) and mobile device management (MDM) camps.
MDM zealots ridicule MAM as a glorified app catalog and a security sieve, while MAM proponents say MDM's rigid and centralized control of all policies, user access, hardware/software and content sabotages the revolutionary benefits of enterprise mobility.
IN PICTURES: 10 mobile device management apps to take charge of BYOD
We firmly believe that MAM is a superior approach for the management of a vast majority of mobile enterprise applications, that it has leapfrogged MDM as a systems management best practice for mobility, and -- importantly -- incorporated its most useful security capabilities. Read more here...
10 Mobile Device Management Apps to Take Charge of BYOD
Managing devices in a BYOD environment is no mean feat, and the right mobile device management (MDM) product can be a key component in making it work. Here are 10 leading MDM products on the market today.
Top 10 emerging infrastructure trends
Gartner kicked off its Infrastructure & Operations Management Summit here today with a "top 10" list of the most significant emerging trends that will impact data centers and information technology used by businesses and government from now into the next four or five years.
The list was presented by Gartner Chief of Research Dave Cappuccio in a keynote address, in which he explained there's often a cascading effect as one trend such as employee mobile devices and cloud use affects others such as helpdesk operations. Read more...
The list was presented by Gartner Chief of Research Dave Cappuccio in a keynote address, in which he explained there's often a cascading effect as one trend such as employee mobile devices and cloud use affects others such as helpdesk operations. Read more...
Cloud-based mobile device management (MDM) getting hot
The MDM "leaders" are said to be MobileIron, AirWatch, Fiberlink, Zenprise and Good Technology. The "challengers" are said to be SAP and Symantec. The "visionaries" are BoxTone and IBM, while the cast of "niche players," the category that usually makes vendors squirm, included McAfee, Sophos, Trend Micro, Tangoe, OpenPeak, Silverback, LANDesk, Smith Micro Software and MyMobileSecurity. There are plenty of other MDM vendors that didn't make it this far to even get on the Gartner Magic Quadrant. Read more...
Subscribe to:
Posts (Atom)