DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Building Scalable Real-Time Apps with AstraDB and Vaadin
Register Now

Trending

  • Microservices Decoded: Unraveling the Benefits, Challenges, and Best Practices for APIs
  • Which Is Better for IoT: Azure RTOS or FreeRTOS?
  • Building and Deploying Microservices With Spring Boot and Docker
  • Best Practices for Securing Infrastructure as Code (Iac) In the DevOps SDLC

Trending

  • Microservices Decoded: Unraveling the Benefits, Challenges, and Best Practices for APIs
  • Which Is Better for IoT: Azure RTOS or FreeRTOS?
  • Building and Deploying Microservices With Spring Boot and Docker
  • Best Practices for Securing Infrastructure as Code (Iac) In the DevOps SDLC
  1. DZone
  2. Data Engineering
  3. Databases
  4. MySQL and the SSB – Part 2 – MyISAM vs InnoDB low concurrency

MySQL and the SSB – Part 2 – MyISAM vs InnoDB low concurrency

Peter Zaitsev user avatar by
Peter Zaitsev
·
May. 23, 13 · Interview
Like (0)
Save
Tweet
Share
2.66K Views

Join the DZone community and get the full member experience.

Join For Free
this post comes from justin swanhart at the mysql performance blog.

this blog post is part two in what is now a continuing series on the star schema benchmark.

in my previous blog post i compared mysql 5.5.30 to mysql 5.6.10, both with default settings using only the innodb storage engine.  in my testing i discovered that innodb_old_blocks_time had an effect on performance of the benchmark.  there was some discussion in the comments and i promised to follow up with more ssb tests at a later date.

i also promised more low concurrency ssb tests when peter blogged about the importance of performance at low concurrency .

the ssb
the ssb tests a database’s ability to optimize queries for a star schema. a star schema presents some unique challenge to the database optimizer. the ssb benchmark consists of four sets of queries. each set is known as a “flight”. i have labeled each query as q{flight_number}.{query_number}. in general, each flight examines different time periods or different regions. the flights represent the type of investigations and drill-downs that are common in olap analysis.

each query in each flight (q1.1 for example) is tested with a cold buffer pool. then the query is tested again without restarting the database. the first test is described as the cold test, and the second as the hot test. the database software is restarted after the hot test. all os caches are dropped at this time as well.

these set of queries were tested on the ssb at scale factor: 20. this means there is approximately 12gb of data in the largest table.

you can find the individual ssb query definitions in my previous blog post .

test environment
these tests were done on a relatively fast machine with a xeon e5-2680 (8 cores, 16 threads) with fast io (ocz r4 1.6tb) and 128gb memory. for the hot test, the data fits in the buffer pool and has been loaded by the cold test already. the buffer pool and adaptive hash index are cold for the cold test. all tests were done with no concurrency. the hardware for this test was provided by adotomi . i will be blogging about raw performance of the ocz card in another post.

also, while it is labeled on the graphs, it is important to note that in all cases, lower times are better .

ssb flight #1
here you will see the start of an interesting trend. myisam is faster when the data is not cached (the cold run) but is slower in the hot (cached) run. i did some investigation during the testing and found that innodb does more io than myisam when the database is cold, but uses less cpu time when the database is hot. i am only speculating (and i can investigate further), but i believe the adaptive hash index is improving performance of innodb significantly during the hot run, as hash indexes are faster than a b-tree index. also accessing pages from the buffer pool should be faster than getting them from the os cache, which is another advantage of innodb.


image009


image001

ssb flight #2
flight #2 is similar to flight #1. myisam is faster than innodb when the database is cold, but the opposite is true when the database is hot.


image012


image003

ssb flight #3
here in some cases myisam is substantially faster than innodb both cold and hot.


image014


image005

ssb flight #4
there is one query in this flight, q4.3, which is faster using myisam than innodb. like the queries in flight #3 that are faster using myisam, q4.3 examines very little data. it seems that innodb performs better when a larger number of rows must be joined together (q4.1, q4.2) but worse when small amounts of data are examined.


image016


image007


conclusion

in some cases myisam is faster than innodb, but usually only when the buffer pool is cold. please don’t take away that you should be using myisam for everything! . myisam may be good for raw performance, but there are limitations which myisam imposes that are difficult to work with.  myisam does not maintain checksum consistency during regular operations and is not acid compliant. myisam and innodb may perform differently under concurrency, which this benchmark does not cover. i will make a follow-up post about concurrency in another blog post in this series. regardless, when the working set fits in memory, innodb almost always performs better, at least for this workload.

notes

mysql version used: 5.6.11, custom compiled to remove performance_schema

for the innodb tests, a 64gb buffer pool was used. o_direct was used so, there was no caching of data at the filesystem level. the innodb indexes were built using alter table fast index creation (merge sort).

for the myisam tests i used a 10gb key buffer. i used alter table disable keys and built the keys with sort via alter table enable keys.
my.cnf

[mysqld]
datadir=/mnt/mysql56/data
basedir=/usr/local/mysql
socket=/var/lib/mysql/mysql.sock
user=justin
innodb_buffer_pool_size=64g
innodb_log_file_size=4g
innodb_file_per_table
innodb_stats_on_metadata=off
innodb_file_format=barracuda
innodb_log_buffer_size=32m
innodb_buffer_pool_instances=16
metadata_locks_hash_instances=32
table_open_cache_instances=8
sort_buffer_size=128k
read_rnd_buffer_size=8m
join_buffer_size=8m
default_tmp_storage_engine=myisam
tmpdir=/dev/shm
innodb_undo_logs=32
innodb_old_blocks_time=0
table_open_cache=2048
table_definition_cache=16384
innodb_flush_method=o_direct
key_buffer_size=10g
# disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_stats_persistent
innodb_stats_auto_update=off
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid



MyISAM InnoDB Database MySQL Testing

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • Microservices Decoded: Unraveling the Benefits, Challenges, and Best Practices for APIs
  • Which Is Better for IoT: Azure RTOS or FreeRTOS?
  • Building and Deploying Microservices With Spring Boot and Docker
  • Best Practices for Securing Infrastructure as Code (Iac) In the DevOps SDLC

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: