Setting BULK_INSERT_BUFFER_SIZE for loading data faster on MyISAM

How fast you can load data to MyISAM ? You can't answer this question with some number but are there techniques to load fast ? Absolutely ! It's always we need to consider simple things to load data faster, The following are quick wins to load data faster on MyISAM 

  • if doing bulk inset, consider disable the indexes before loading the data
                   alter table table1 disable keys;

  • The BULK_INSERT_BUFFER_SIZE variable limits the size of the cache tree in bytes per thread. If data is being added to a non-empty table, tuning the bulk_insert_buffer_size variable can make data insertion faster. Normally  it shows the improvement when the data to be inserted is more than 10k rows. But it is hard to say what is the right value, so, trail and try with incremental buffer size values.Setting it to 0 disables this optimization. The default value is 8MB. The maximum value is 4GB. The bulk_insert_buffer_size can be set both on system and session levels.
  • Consider setting optimally  MYISAM_SORT_BUFFER_SIZE and KEY_BUFFER_SIZE

Things to worry about while considering optimal MySQL performance

  • Optimal of structure of table is very important, Especially the columns must have the right data types and also columns are so well built to handle the data flow inside MySQL. Typically tables with large UPDATES must be conservative about adding columns and tables with large SELECTS will have more columns (hopping the tables for data is expensive)
  • Optimal indexing is good but over indexing is long-term problem to solve 
  • What is your locking strategy ? Shared access guarantees the smooth operations of MySQL and use exclusive only when you have critical transactions under top priority. InnoDB storage engine handles most locking issues without involvement from you, allowing for better concurrency in the database and reducing the amount of experimentation and tuning for your code.
  • Optimally use available memory for caching schema and SQL, Oversized memory handling causes excessive paging. 
  • Distribute data across multiple disks to optimize disk seek time 
  • The modern disks with higher throughput rate can do magic and  is easier to optimize than seeks because you can read in parallel from multiple disks.
  • When you have most often accessed data optimally placed in memory you can get maximum results from CPU investments, It's often main memory bandwidth becomes a bottleneck when the CPU needs more data than can fit in the CPU cache

Which my.cnf file is being used ?

To find which my.cnf used you can use -

 /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

[[email protected] yum.repos.d]# /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
2017-08-17 12:22:50 0 [Note] /usr/sbin/mysqld (mysqld 5.6.37) starting as process 5418 ...
2017-08-17 12:22:50 5418 [Note] Plugin 'FEDERATED' is disabled.
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 
2017-08-17 12:22:50 5418 [Note] Binlog end
2017-08-17 12:22:50 5418 [Note] Shutting down plugin 'MyISAM'
2017-08-17 12:22:50 5418 [Note] Shutting down plugin 'CSV'
[[email protected] yum.repos.d]# 

The Commandments for Optimal MySQL Performance

What we have to keep in mind for an Optimal MySQL Performance ? There are many, I am just writing down what I seriously consider for an Optimal and Scalable MySQL Performance, As you usual the comments are most welcome, Thank you !! 
  1. Go generous while buying physical memory, InnoDB works great when files are accessed from memory rather than from disk. 
  2. Lean MySQL Schema and SQL works great. Trust me, The fat is not Optimal everywhere !!
  3. Carefully choose your Datatype, The smaller ones are better. 
  4. You need Indexes to search records faster but Index overuse / abuse will have negative impact to performance, Remove unused indexes.  
  5. Occasionally MySQL go wrong in choosing Optimal Indexes, Here help MySQL using hints like "USE INDEX".
  6. Think seriously about indexing columns in WHERE, GROUP BY, and ORDER BY queries. 
  7. Less Rows Examined is great !!!
  8. Reduce disk reads, Compress TEXT, BLOB 
  9. Make incremental Schema changes sometimes small changes cause severe Performance improvements / degradation. 
  10. Lookout for duplicate records they seriously cause Performance Bottlenecks.
  11. Archive the old records, They make Sort / Search especially very expensive.
  12. Profile your MySQL regularly to define the trending chart of Performance, Nothing goes wrong overnight, Performance degradation happens in several cases over a period of time so think proactive, That's cool :) 
  13. The fat multi-core processors hyper-threading enabled works great for high performance MySQL operations  
  14. Watch out for excessive pagination queries.
  15. Go conservative about MySQL Configs (sometimes less is more).
  16. Be cautious with MySQL configuration parameters innodb_thread_concurrency and thread_concurrency variables, Don't use them if you don't clearly understand how they work !!
  17. High max_connections excessively consumes the RAM. 
  18. Use query cache only if you have repetitive queries, Using query cache on data that changes often will give you a performance hit. 
  19. Remember to warm your MySQL Database in every restart, This get schema, SQL back to memory. 
  20. Fetch only the data you need in SELECT queries 
  21. Consider persistent connection against multiple connections 
  22. Sort_buffer_size  eat memory very fast because it is actually consumed per connection so please be very cautious while increasing it.
  23. Use large redo logs
  24. Measure cost of query with Response Time, Use MySQL slow query log to diagnose query performance.
  25. I use SHOW PROCESSLIST while load increases, This works good for me during diagnostics / forensics of slow queries.
  26. Use Performance Schema wisely, This is an great MySQL performance orchestration platform.    
  27. Avoid using Triggers as much as possible. 
  28. Use ARCHIVE Storage engine for audit purposes. 
  29. You need Backups but do that on a secondary Replication Instance, This actually is a quick win to boost the Performance of your MySQL Primary Master Instance. 
  30. Split reads across several instances / disk, This helps distributing load efficiently 

Life of MySQL DBA

What is it like being a MySQL DBA? It is interesting and challenging!  A MySQL DBA is accountable equally for technology and business operations. Everyone agrees the accountability for technology operations but few asked my how on business operations? I am explaining here in this post how MySQL DBAs can equally create  business value?  A serious MySQL DBA is never the career for someone who is looking for 40 hours / week job, It is a very demanding profession! MySQL DBA is accountable for entire MySQL Infrastructure Operations  , It includes but not limited to responsibilities like Architecture, Engineering, MySQL Operations Monitoring / Trending,  Performance Health Check / Diagnostics / Forensics, Tuning , Scalability, High Availability, Disaster Recovery and Upgrades. Recently a college graduate who want to pursue the career of MySQL DBA approached me asking what is it like the life of an MySQL DBA? So I thought let's write a post about the life of an MySQL DBA!!
What will be the profile of  MySQL DBA?
A true MySQL DBA is an courageous problem solver, At times he/she is expected to remain calm and address the problem than getting sensitive/emotional (many times I was called for emergency support during personal vacations, I am ok with it because it's the nature of my Job!! ) because anything happen to MySQL Infrastructure you will be blamed (even when it's not your mistake) so be cool and never ever get into panic/anxiety situation (practice meditation & yoga, It will support you at personal level during turbulence) with any level of odd experience in your MySQL Operations, Expect the worst and be prepared for it ! Remember Murphy's law "whatever can go wrong, will go wrong ! " . Now on technical competency front every MySQL DBA is expected to know MySQL Architecture, MySQL Operations Management (Monitoring & Trending), MySQL Troubleshooting, MySQL Performance, MySQL High Availability and Disaster Recovery. Thankfully MySQL ecosystem is rich when it comes to knowledge sharing and support.  Percona's investments in MySQL research and innovation made MySQL DBA ecosystem more stronger and healthy ! The following are few books (must have) in every MySQL DBA library:
  1. High Performance MySQL (Peter Zaitsev, Vadim Tkachenko and Baron Schwartz )
  2. MySQL Troubleshooting (Sveta Smirnova)
  3. Expert MySQL (Charles Bell)
 What makes you The Expert MySQL DBA ? Practice, Practice & More Practice over multiple MySQL (MySQL GA, Percona Server, WebScaleSQL and MariaDB) infrastructure and continuous learning is key! MySQL DBAs need to automate their routine tasks to find more time for learning, community contributions  and attending MySQL events (You can learn a lot from these events from peers & experts !). Be open to learn from every MySQL flavour (MySQL GA, Percona Server, WebScaleSQL and MariaDB) available  this get you to be an unbiased MySQL advocate. Take the full ownership of your MySQL Infrastructure like Performance, Scalability, High Availability, Disaster Recovery and Security because strategically/economically it never make sense for hiring multiple MySQL DBAs for addressing different areas of MySQL Operations! Last but not the least never limit your scope to just MySQL because most of MySQL implementations are on Linux, Good understanding of Linux Operations comes handy many times in your career so please do some investments for learning Linux!!!
Where do I start my career as MySQL DBA?
Once you have basic knowledge in MySQL DBA Operations, Start actively networking with MySQL DBA Managers and Database Architects. The initial days please don't expect challenging opportunities, There is nobody in this planet who risk their mission critical MySQL database with junior DBA(s) ! The early days of your career sometimes will be limited to assisting Principal / Sr. MySQL DBAs but this will give you access to experience/knowledge of someone who is working with MySQL production infrastructure for many years. The early days of your career as Jr. MySQL DBA is very important because it equally provide to guidance and practice governance in MySQL Operations Management successfully. You must never get bored doing repetitive tasks initial years !!
What does MySQL consultant career path look like?
The value of MySQL consultant grows higher with more years of experience and volume/transaction of MySQL database he/she managed. If you have entrepreneurial interests the independent MySQL contractor (I am an independent MySQL contractor for many years) is very promising, exciting and rewarding!
How MySQL DBAs can equally create  business value
Faster, Scalable & Highly Available MySQL infrastructure is very important for successfully running planet-scale Internet/Mobility Application or SaaS. Think about "Cost-To-MySQL-Outage" for an online commerce or advertisement network business? A seasoned  MySQL DBA should proactively plan Optimal, Scalable, Highly Available and Secured MySQL Operations to address customer experience & revenue!
So have you decided to be a MySQL DBA?     
Good luck !! Start your learning with an positive mind and spend all your productive hours in reading the research & experience of many professional MySQL consultants (there are many available in public domain already!)