alexander Posted July 11, 2011 Report 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
Robinsoneion Posted March 26, 2012 Report 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
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.