TheMattReid

A couple of question I get a lot from MySQL customers is “how will this hardware upgrade improve my transactions per second (TPS)” and “what level of TPS will MySQL perform on this hardware if I’m running ACID settings?” Running sysbench against MySQL with different values for per-thread and global memory buffer sizes, ACID settings, and other settings gives me concrete values to bring to the customer to show the impact that more RAM, faster CPUs, faster disks, or cnf changes have on the server. Here are some examples for a common question: “If I’m using full ACID settings vs non-ACID settings what performance am I going to get from this server?”

Let’s find out by running sysbench with the following settings (most are self explanatory – if not the man page can explain them):

• sysbench –test=oltp –db-driver=mysql –oltp-table-size=1000000 –mysql-engine-trx=yes –oltp-test-mode=complex –oltp-read-only=off –oltp-dist-type=special –max-requests=0 –num-threads=8 –max-time=120 –init-rng=on run

MySQL Settings:

In the first test MySQL is set to the following ACID related settings. This will give us results for TPS performance without full ACID compliance – very common settings on a server that is handling blogs, ad serving, general business websites, and other roles where full ACID is not required and performance is valued over the benefits of full ACID. These are important settings when we look at the difference in performance when we change to full ACID in the second test.

• innodb_flush_log_at_trx_commit = 0
• sync_binlog=0
• transaction-isolation=REPEATABLE-READ

System configuration and InnoDB buffer pool size:

• XEON E5345 Series 2.33ghz 8-core, 16GB RAM, Local SATA 7.2K disks
• innodb_buffer_pool_size = 10G

Full result set from sysbench:

Summary OLTP test statistics:

• queries performed:
• transactions:                        172426 (1436.83 per sec.)
• read/write requests:                 3276664 (27304.51 per sec.)
• other operations:                    344882 (2873.91 per sec.)

Non-ACID results:

We can simplify the results by looking at the following TPS results for this non-ACID test:

• transactions:                        172426 (1436.83 per sec.)

Full ACID results:

Let’s go ahead and run the test again with different ACID settings. This will give us the TPS results for full ACID compliance:

• innodb_flush_log_at_trx_commit = 1
• sync_binlog=1
• transaction-isolation=REPEATABLE-READ

We get the following results for TPS:

• transactions:                     3197   (26.58 per sec.)
• read/write requests:                 60743  (505.04 per sec.)
• other operations:                    6394   (53.16 per sec.)

Final Results:

So as you can see the difference between full ACID settings and not (on the same server with only those values on the cnf being changed) results in a huge difference in performance on this standard database server. We can now hand this data to the customer and they will know what impact the settings will have on their application’s performance and what to expect when running full ACID vs non-ACID.

More info on using sysbench here: http://sysbench.sourceforge.net

For all of those linux users out there that have moved over to, or tried out, Solaris10 or OpenSolaris because they heard the tales of how MySQL is faster on Solaris… or perhaps you wanted to learn how to use Sol10 for the great features of Zones or the ZFS filesystem? Regardless of why you’re on it you are probably wondering why Linux has colored output of filenames and directories but Solaris does not. The question of ‘why?’ isn’t important, but how to enable colors is. It’s very simple, and here’s how I fixed it. This is a result of digging through multiple semi-related links on Google.

1. Download all packages from SunFreeware.com
   • dependency: libintl-3.4.0-sol10-x86-local
   • dependency: libiconv-1.13.1-sol10-x86-local
   • dependency: gmp-4.2.1-sol10-x86-local
   • dependency: gcc-3.4.6-sol10-x86-local or libgcc-3.4.6-sol10-x86-local depending on your system needs
   • coreutils-8.4-sol10-x86-local
2. Install ‘coreutils’ dependency packages using the command “pkgadd -d [package_name]
3. Install ‘coreutils’ packages using the command “pkgadd -d coreutils-8.4-sol10-x86-local
4. Enable color aliases in your rc file: “alias ls=’/usr/local/bin/ls –color=auto’”

Some notes from my recent installation of MySQL 5.4.2-beta on a PPC G5 box running OSX 10.5.8. Hopefully these notes will save someone a bit of time when they begin the “I want to make my mac a development box” process. I’m not going to cover installing/configuring Apache and PHP since the web is full of those articles already.  So, first things first,  I downloaded the pkg file  – not the tarball – from the MySQL downloads site and installed the following files in the following order:

1. mysql-5.4.2-beta-osx10.5-powerpc-64bit.pkg
2. MySQLStartupItem.pkg
3. MySQL.prefPane

CNF File Settings

Here are some interesting settings that you may want to be aware of with 5.4 and OSX

• Do not set innodb_flush_method=fdatasync – it’s not a valid option, and the default after install is actually blank.
• Do not set “nice = value” in the [mysqld_safe] section as this is not recognized and give the error ‘nice: illegal option — -‘
• Set the variable lower_case_table_names=2 because the HFS+ file system is case insensitive – isn’t that special.
• The variable ‘log_slow_queries’ will work but you should use ‘slow_query_log’ or ‘slow_query_log_file’ instead since it’s getting removed in *gasp* MySQL 7.0 so we do have a bit of time before that becomes something to be concerned about but you will see it in the error log if you don’t correct it now.

Preference Pane service controller

In order to get the Preference Pane controller to work (if you are getting the following error in /var/log/system.log – “Could not setup Mach task special port 9: (os/kern) no access”) you need to correct the permissions for the installation directory as such
(root@g5)-(/usr/local)-
shell> chown -R _mysql:wheel /usr/local/mysql*

This will still generate the ‘(os/kern) no access‘ message in the system.log but the server will be started/stopped by the pane controller and that’s what we really care about. There may be some more notes along the way as I’m going to be testing 5.4 quite a bit for various application related matters.


install_driver(mysql) failed: Can't load '/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.so' for module DBD::mysql: /usr/lib64/libmysqlclient.so.15: version `libmysqlclient_15' not found (required by /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.so) at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230.
at (eval line 3


After a lot of mucking around I noticed that the MySQL-shared-community RPM was installed, not the MySQL-shared-compat RPM like my usual installs. So…


rpm -Uvh MySQL-shared-compat-5.1.33-0.rhel5.x86_64.rpm


Quick post here for a problem that other people might run into and wonder how to fix. Let’s say you have the following error:

install_driver(mysql) failed: Can't load '/usr/lib64/perl5/vendor_perl/5.8.5/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.14: cannot open shared object file: No such file or directory at /usr/lib64/perl5/5.8.5/x86_64-linux-thread-multi/DynaLoader.pm line 230.
at line 3
Compilation failed in require at line 3.
Perhaps a required shared library or dll isn't installed where expected
at ./kontroll-client-5.0.x_linux-x86-2.0.1.pl line 193


Well, you probably need to symlink “libmysqlclient.so.14″ to the existing one that is most likely of a newer version.

> find / -name "libmysqlclient*"
/usr/lib64/libmysqlclient.so.15
/usr/lib64/libmysqlclient_r.so
/usr/lib64/libmysqlclient.so.15.0.0
/usr/lib64/libmysqlclient.so


So we just symlink it to fix the issue. It’s that simple.

> ln -s /usr/lib64/libmysqlclient.so.15 /usr/lib64/libmysqlclient.so.14


I saw something interesting today when helping out someone on the #mysql IRC channel. It was a cnf file that was designed to destroy a server. Before I get into the why-not, here are the goods:

...snip...
read_buffer = 128M
join_buffer = 128M
key_buffer = 512M
max_allowed_packet = 200M
thread_stack = 192K
thread_concurrency = 8
thread_cache_size = 64
query_cache_limit = 256M
query_cache_size = 256M
table_cache = 8192
query_cache_type = 1
sort_buffer = 128M
record_buffer = 128M
myisam_sort_buffer_size = 128M
thread_cache = 64
max_user_connections = 500
wait_timeout = 200
max_connections = 4096
tmp_table_size = 1000M
max_heap_table_size = 1000M
...snip...


Now, you may ask why these settings are bad. I will tell you. First, an equation for calculating per-thread buffer memory usage.

total per-thread buffer usage = (read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections


So in this case we have the following memory usage in per-thread buffers. Any buffers or settings not specifically mentioned above take the MySQL defaults.

1537GB = ( 128M + 256K + 128M + 192K + 128M + 32K) * 4096


Yes, you read that correctly. This server is setup to allow 1537GB of ram usage on the per-thread buffer level. This server only has 4GB of ram. You tell me what would happen if 4096 connections were made and all of them, or even just a few of them, tried to max out their available buffer space. So folks, remember to always check your per-thread variables and make sure you’re not over allocating your memory resources.

I just read about the release of Honey Monitor for MySQL. I read through the features. I looked at the screenshots. Then I read about the licensing. Then I read about the fact that it only runs on windows XP or Vista. So, my questions for the ages…

1. Why, in this age of multiple OSes battling each other for market share, would you write an application that is OS specific? Every app should run in a vendor neutral web browser unless there’s a really really good reason. Monitoring software, are you kidding? Monitoring software must be vendor neutral and web-based. I will never use anything that ties me to windows or linux or osx, it has to be cross platform. End of story.
2. Why, when MySQL is so heavily used on Linux and Solaris, would you write an application for MySQL that can only be run on Windows? Every MySQL DBA that I know uses either OSX or Linux or OpenSolaris for a desktop. Making a MySQL related application use windows seems like a slap in the face to me.
3. Why, in the age of Open Source, would you create a product with so many different tiers of licensing? Call me an obnoxious open-source advocate, but I firmly believe that ALL software should be free. If you use MySQL and you use Linux – you best be contributing back with 100% free code. To do otherwise, in my eyes, is selfish. It also drives away possible users and testers that might have helped make your product a success.

All of that ranting aside, I think Honey Monitor looks like a great product. The features are impressive and the screenshots look great. The problem is that I’ll never get to use anything other than the free version – and then not even that because it’s for windows and I am not using windows. However, if you don’t care about the points I just mentioned, go ahead and try out the app and let me know what you think. I would actually be interested to know.

Monolith: MySQL server monitoring. I only have servers that run Redhat/Debian/Ubuntu Linux x86 and x86_64. I don’t have machines to test MySQL monitoring for the following OSes. Perhaps you would like to be a test candidate for the new version of Monolith? If so, let me know and you’ll be on the list, as well as get beta testing credit.

OSX Server: PPC and Intel

AIX

FreeBSD / OpenBSD / NetBSD

Windows Server

Solaris: Intel & Sparc

Linux servers that are NOT Intel based.

What this involves: testing the client script, perl modules, snmp stats, and other functions that may require a custom client script for that architecture.

Progress on the Open Source enterprise grade MySQL monitoring system;  the schema for Monolith version 2 has been designed. Due to the many suggestions for features and the interest it has received I’ve put this on the front burner. That said, here is some more info on the next steps I’ll be taking.

• Monitored servers will use a command line agent (called remotely) to pull information from both MySQL and the OS.
• Historical information will include all values from global status and global variables, as well as CPU/Memory/IO/Disk usage.
• Standard graphing functions – the ones in the list from the previous post – will gather information from various view tables that contain historical data that is collected from the agent script. User defined graphing will allow you to look at historical values over time for any of the various global status or global variable settings (integer based ones anyway).
• The cnf file for each server will be stored in the monitoring database and available for viewing for historical purposes. 

What would you like to see in a free enterprise-grade monitoring system for your daily MySQL needs?I’m rewriting Monolith – MySQL DBA Console from the ground up. This will be version 2 and I would like to get some input from the global MySQL community.So far I am going with the following; comment with any improvements/additions.

• Variable interval polling of server statistics
• Over 50 different alerts (see list below)
• Graphing of various server statistics (see list below)
• Tuning recommendations with cnf file changes to apply to server
• Change control documents for recommended performance/security tuning
• Threshold based alerting with multiple alert groups: info,warn,critical
• Sorting/ordering of servers via groups. ie: client -> dev,stage,prod
• RSS feeds for each alert group
• XML export with user defined fields for external applications (API of sorts)
• Slave server alerts, IO thread / SQL thread, seconds behind master (with threshold)
• Various general stats: version, system ram size, # of schema, default table type
• Ability to view current cnf file from server
• Ability to view current global variables as a list
• Email / pager reporting for alerts (choose warn/crit alerts to report on)

Alerts - can be enabled or disabled for reporting on a per server basis

• number of connection failures (threshold)
• number of connections errors (threshold)
• binary logging not enabled
• sync_binlog not set
• max_used_connections too high compared to max_connections (ratio over 85% utilization)
• query cache size too small: improper utilization
• query cache size too large: improper utilization
• memory usage over 85% of system ram
• table scans excessive
• tmp table to disk ratio too high
• innodb buffer too small
• innodb buffer too large (resources can be used elsewhere)
• key buffer too large (resources can be used elsewhere)
• key buffer too small
• sort buffer too small
• sort buffer too large (resources can be used elsewhere)
• join buffer too small
• join buffer too large (resources can be used elsewhere)
• open_files_limit too small compared to open_files usage ratio
• table locks too high
• table cache too small
• table cache too large (resources can be used elsewhere)
• thread cache too small
• thread cache too large (resources can be used elsewhere)
• binlog size too small
• excessive disk tmp table usage
• flush time not zero value
• indexes not being utilized properly
• innodb doublewrite buffer enabled
• innodb flush method not properly set
• innodb transaction isolation level not properly set for ACID
• innodb log wait time too high
• conncurrent insert not set correctly
• query cache not enabled
• table locks too high
• thread cache not enabled
• concurrent queries too high (threshold)
• binary logs not set to auto-purge
• binary logging not enabled
• slave not active if server is set to slave
• slave sql thread stopped
• slave io thread stopped
• slave behind master too long (threshold)
• slave not set as read-only
• slave relay logs not automatically purged
• account has global privs
• account set to old_password
• accounts able to be added with empty password
• account has grant option
• root can login remotely
• root has no password
• blank username detected
• test database exists
• usage user exists
• general query log enabled
• CPU usage over threshold
• Load Average over threshold

Graphs - default

• Query cache utilization, in/out hit ratio
• Temp tables usage
• Temp files usage
• Open tables usage
• Thread cached / created
• Connections
• Query rate
• Data size, Index size, overall size
• Uptime
• CPU usage
• I/O usage
• Load average
• Data transfer in/out
• Memory allocation
• Swap usage
• Slave delay (if configured as a slave)

As mentioned before, this is a OSS application that has no license or subscription fees and will be updated on a regular basis. It’s a mix of PHP for the web side and Perl for the system polling and reporting processes. The installer will be new, web based, and a simple process, much improved from the current method.Please add your comments now, as I am drawing up plans this week for version 2.