alexander Posted July 11, 2011 Report Share Posted July 11, 2011 So recently, i've been toying a bit with MySQL, and it is amazing how a proper setup, and a proper server configuration can affect the performance of MySQL. So to give some ideas about how i am going about and setting this up and how it affected throughput, here is a brief tear-down. Server: Every time its a VM, 4 physical cores (no ht atm), 8GB ram, 80GB hard drive capacity.Setup 1 (one i'm trying to show is not very optimal): RHEL 5.6 stockMySQL 5.0 (latest release, whatever it is, 92 i think) installed from rpmoriginally stock config Setup 2 (one i'm working with): CentOS 5.63 partitions, boot (ext3), / (ext3) and /var/lib/mysql (XFS)/var/lib/mysql is mounted with noexec,noatime,nodiratime,nobarriervm.swappiness = 0vm.dirty* = 0MySQL 5.5 (rpm, same deal)google perf toolstcmalloc_minimal preloaded pre mysqld startnot-so-stock config includes: # InnoDB options #Do not extend files too frequently innodb_autoextend_increment = 20 # Use one file per table innodb_file_per_table # Buffer pool size innodb_buffer_pool_size = 5G innodb_additional_mem_pool_size = 32M # Transaction log location and sizes innodb_log_files_in_group = 4 innodb_log_file_size = 256M innodb_log_buffer_size = 4M # Percentage of unwritten dirty pages not to exceed innodb_max_dirty_pages_pct = 80 # Transaction commit policy innodb_flush_log_at_trx_commit = 2 # Timeout to wait for a lock before rolling back a transaction innodb_lock_wait_timeout = 50 # Flush method innodb_flush_method = O_DIRECT # Number of concurrent threads to run innodb_thread_concurrency = 8 # Autoinc lock mode innodb_autoinc_lock_mode = 1 # Enable fast innodb shutdown innodb_fast_shutdown = 0 # Dont delay insert, update and delete operations when purge # operations are lagging innodb_max_purge_lag = 0 So, what did i try.I used mysqlslap to test mysql io performance, so i ran it with a concurrency of 1, for 100000 queries write-centric, the results of the first run were vastly different. It took over 40 sec on the stock setup, so i fixed that by changing the setup to resemble what i am using in 5.5, here are the results:Example of command line: mysqlslap --concurrency=1 --iterations=1 --engine=innodb --auto-generate-sql --auto-generate-sql-load-type=write --number-of-queries=100000 -u [user] -h [ip] --password=[password]stock: Running for engine innodb Average number of seconds to run all queries: 26.058 seconds Minimum number of seconds to run all queries: 26.058 seconds Maximum number of seconds to run all queries: 26.058 seconds Number of clients running queries: 1 Average number of queries per client: 100000 mine:Benchmark Running for engine innodb Average number of seconds to run all queries: 24.931 seconds Minimum number of seconds to run all queries: 24.931 seconds Maximum number of seconds to run all queries: 24.931 seconds Number of clients running queries: 1 Average number of queries per client: 100000 So i increased concurency to see how they bare:stock:Benchmark Running for engine innodb Average number of seconds to run all queries: 9.176 seconds Minimum number of seconds to run all queries: 9.176 seconds Maximum number of seconds to run all queries: 9.176 seconds Number of clients running queries: 10 Average number of queries per client: 10000 Benchmark Running for engine innodb Average number of seconds to run all queries: 9.449 seconds Minimum number of seconds to run all queries: 9.449 seconds Maximum number of seconds to run all queries: 9.449 seconds Number of clients running queries: 100 Average number of queries per client: 1000 Benchmark Running for engine innodb Average number of seconds to run all queries: 91.794 seconds Minimum number of seconds to run all queries: 91.794 seconds Maximum number of seconds to run all queries: 91.794 seconds Number of clients running queries: 1000 Average number of queries per client: 100 mysqlslap: Cannot drop database 'mysqlslap' ERROR : MySQL server has gone awayit doesn't crash, but queries start timing out, so you get a "server unresponsive thing here" the mysql server doesn't actually crash not-so-stock:Benchmark Running for engine innodb Average number of seconds to run all queries: 5.613 seconds Minimum number of seconds to run all queries: 5.613 seconds Maximum number of seconds to run all queries: 5.613 seconds Number of clients running queries: 10 Average number of queries per client: 10000 Benchmark Running for engine innodb Average number of seconds to run all queries: 5.250 seconds Minimum number of seconds to run all queries: 5.250 seconds Maximum number of seconds to run all queries: 5.250 seconds Number of clients running queries: 100 Average number of queries per client: 1000 Benchmark Running for engine innodb Average number of seconds to run all queries: 6.030 seconds Minimum number of seconds to run all queries: 6.030 seconds Maximum number of seconds to run all queries: 6.030 seconds Number of clients running queries: 1000 Average number of queries per client: 100 no conclusions yet, though you can see, that server setup and configuration does matter and can gain significant performance on the same hardware. Now, i'm going to write a script with a battery of tests and attempt to brake both setups, and we'll see how both of these servers bare the weight, write though, as you can see, there is a significant difference in performance already :) I also want to monitor io, threads, and stuff like that while its all running... For now, this was the first part of a teaser... Quote Link to comment Share on other sites More sharing options...
Robinsoneion Posted March 26, 2012 Report Share Posted March 26, 2012 MySQL Community Server is a freely downloadable version of the database's most popular open source which is supported by an active community of developers and enthusiasts free.... :rolleyes: Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.