PostgreSQL vs MySQL on VirtualBox

After several days of testing, here are the results.

Platform in Short
Host
Ubuntu Linux 16.04 LTS (System 76)  Intel(R) Core(TM) i7-4710MQ CPU @ 2.50GHz
16GB RAM
Samsung 850 SSD 500GB (formatted ext4)

Virtual Guest
3 CPUs on VirtualBox 5.2.04
CentOS 7.2.1511
4096 MB
Fixed Size Virtual Drive formatted xfs
 
Benchmarking Software:  www.hammerdb.com v2.21 running "remotely" on the Host

DBMSs:  PostgreSQL 9.2 and MySQL community edition 5.75  

Motivation
Primarily I wanted to explore benchmarking methods to compare DBMSs.  Secondarily, it is a very good practice to benchmark your database throughout the lifecycle of your system or product.  Sometimes a DBA does not have access to the app layer for security reasons, thus a benchmark that runs outside of the app layers provides a work-around.  It also is immune to application changes.  Benchmarking without regard to the application helps detect performance regressions in other layers other than the application.

Results
There are many disclaimers here.  One of which, this is on a Virtual Box.  Not bare metal.  A second one is there are multiple innodb options to experiment with.  Also, HammerDB is relying on stored procedures/functions to create the workload.  It may be that PG is superior here.  (PG does cache query plans in stored functions, but MySQL also has a stored program cache:  https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_stored_program_cache)

For HammerDB I use 10 warehouses 3 virtual users.  At 3, there is enough CPU to allocate processes (in PostgreSQL's case) and threads (in MySQL's case) to separate cores/"chips".

With PostgreSQL 9.2, I set checkpoint_segments=35 to make the warnings go away and set shared_buffers=2016MB.  We we get Orders Per Minute (OPM) of three runs:

Postgresql 9.2 OPM
21,148
21,868
21,165

(We have vm.swappiness at 10 BTW, but all of the database fits in RAM and there's low memory pressure.)


MySQL 5.75 with Performance Schema on
9,427
9,361
8,955

The difference is so stark at this point, I'm questioning my chops.  Query cache is off (query_cache_size        = 0, query_cache_type        = 0).  Innodb_buffer_pool is 1300M.  That's smaller than shared_buffers used in Postgres but they are not the same thing.  The database size is 2,613 MB on Postgres, and 961 MB on MySQL.    Remember, MySQL also benefits from Linux's filesystem cache just like Postgres, so we shouldn't have this big of a difference even if innodb_buffer_pool is not completely perfect. (I later set the innodb_buffer_pool to 2,613MB just for grins, and of course there was no significant difference).  I wonder why the database size is so much smaller on MySQL than PG, but that should help MySQL perform better at the I/O level.  And on MySQL we never see CPU pegged (each CPU barely ever goes above 50%), so any compression isn't being bottlenecked on CPU.


Let's get MySQL up to speed to Postgres
MySQL has lots more knobs that can alter it's performance.  But let's first turn off the Performance Schema (PS).


11,369 OPM
Better. PS does not have a counterpart in PG, so it's a fairer comparison, but it's still nowhere near PG in performance for this test.

innodb_flush_method = IO_DIRECT
11,064 OPM
Not much of a difference, the decrease could be due to some of the non-deterministic nature of the whole stack.  So I wouldn't say this setting and result made a difference (more runs required), and it doesn't get us near PG.


numactl interleave=all
11,179 OPM See https://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/ for details.  But with 3 virtual CPUs, it all depends on the hypervisor's biases.  Still, not a significant difference.  

set global innodb_spin_wait_delay=0;
11,793 OPM
I was seeing huge spin lock wait counts with show engine innodb status.  So this setting seemed to bump up performance just a nudge.  I will point out that top didn't show CPU getting pegged.  There was no definitive bottleneck.



set global innodb_rand_read_ahead=ON;
11,453 OPM
HammerDB uses a random seed for its operations, so for a long-shot I turned this on.   The setting is there because the developers believe some workloads will benefit, but in this case I would say it's a wash.  



set global innodb_io_capacity=1000;
10,916 OPM
I turned off the innodb random read ahead and then was really grasping for straws.  This setting on my SSD system made performance nudge down.


many trials and days later...
 
I got a big jump by setting the paravirtualization interface to KVM on VirtualBox.  Setting query cache on, as expected, caused a degradation.   

After many more trials, the final settings I landed on are below, giving 15,651 OPM

I changed the number of CPUs on VBox from 3 to 2 which sped things up too.
 
innodb_file_per_table        = 1
performance_schema           = OFF
innodb_flush_method          = O_DSYNC #faster than O_DIRECT
query_cache_size             = 0
query_cache_type             = 0
innodb_buffer_pool_instances = 1
innodb_log_file_size         = 5142M
innodb_doublewrite           = OFF
innodb_spin_wait_delay       = 0
innodb_write_io_threads      = 6
innodb_flush_neighbors       = 0
What stands out in all trials is a high number of context switches at around 6,500/sec.  I also see spin rounds per wait of over 8,000 in the last run.  i/o wait is low and disk is not saturated.  The binary log is off. 
 

Conclusion
This benchmark workload  is based on http://www.tpc.org/tpcc/ with the same tables and use-cases.  It is not a typical workload of a web application, where you will see select * from products where category=... for listing products for sale, and much use of limit and offset for paging through a list of items.  Web apps are really a workload unique unto themselves versus OLAP and OLTP.

This workload is more like 2 applications opening up a waterhose of CRUD transactions into the database.

PostgreSQL is clearly the winner here even after careful benchmarking and applied best practices.

Hopefully someone can assist and point out an obvious tweak.  MySQL is thread per connection and PG is process per connection.  And this is all virtualized.  The keeper of HammerDB pointed out past tests between the two DBs were not significantly different.  But I suspect more virtual users were used and on real hardware.

I'm a big fan of both platforms.  And I know one workload is not a definitive comparison of overall performance.  It could very well be that PG is fit perfectly for this test but degrades on all other workloads in comparison to MySQL. 

The next step would be to dive into the internals to find the bottleneck....

I hope this helps somebody.

  
  

Programming: Programmer Memory

As one writes code, the mind changes state over time adding code artifacts to the brain's short-term and long-term memory.  Having every line of code memorized would be perfect for a programmer's brain  to work on code.    Only at first blush.  Maybe even better is to have the entire call graph memorized.  Well, even better than having all code memorized and the call graph memorized, is to have all possible states the computer will move through memorized as well.  So on and so forth.  One could imagine a graph or set of graphs indicating the amount of programmer memory (PM) on a project.

It's this programmer memory that benefits projects.  A project with low PM will take longer to implement and probably be more bug-ridden.   There's probably a real positive correlation between a programmer's experience on the project and PM.  So programmer experience on the project is very important, and cannot be discounted.

Frameworks that get reused across projects can also increase PM over time as long as the frameworks stay relatively stable.  So programmer experience with a framework is better than no experience.

How can a programming code (I do not like the term language) or set of coding tools decrease the value of PM?

MySQL 5.5 Maximum Sequence in Binary Logs

MySQL Binary Logs use a sequence number as the extension.

If you have binary logging turned on, the first binary log file will be mysql-bin.000001

What happens if you flush logs after mysql-bin.999999?

We could fire up the source code to take a look, or we could try it out.

I renamed the last binary log file to mysql-bin.999997 and changed the mysql-bin.index file to only list the one.    After a couple of flush logs we get the following.

This not like the odometer on your car. 

The filename is generated in sql/log.c  and it will throw an error when

if (max_found == MAX_LOG_UNIQUE_FN_EXT)

where  MAX_LOG_UNIQUE_FN_EXT is 0x7FFFFFFF or  2147483647, roughly 2.1 billion flushes. 

Let's try it out.

The error message definitely needs to be updated.  But the server didn't crash.  And that's a good thing.

The error log produced the following.
 140602 20:27:52 [ERROR] Log filename extension number exhausted: 2147483647. Please fix this by archiving old logs and updating the index files.
140602 20:27:52 [ERROR] Can't generate a unique log-filename mysqlbin.(1-999)

Conclusion
1.  There is a maximum sequence number in binary logs, and that is 2147483647.
2.  There's an error message update in the source code that could be more useful.
3.  Monitor your error logs!
4.  If mysql-bin.000001 doesn't exist, it seems to me that it's okay to roll over the odometer just like a good car.

New Variables in MariaDB 5.5 not in MySQL 5.5

By doing a diff between  MySQL 5.5 and MariaDB 5.5 with show global variables, the following are new variables in MariaDB 5.5.35 not found in MySQL 5.5

Documentation for these variables can be found on MariaDB's knowledge base:  Server System Variables

aria_block_size
aria_checkpoint_interval
aria_checkpoint_log_activity
aria_force_start_after_recovery_failures
aria_group_commit
aria_group_commit_interval
aria_log_file_size
aria_log_purge_type
aria_max_sort_file_size
aria_page_checksum
aria_pagecache_age_threshold
aria_pagecache_buffer_size
aria_pagecache_division_limit
aria_recover
aria_repair_threads
aria_sort_buffer_size
aria_stats_method
aria_sync_log_dir
aria_used_for_temp_tables
binlog_annotate_row_events
binlog_checksum
binlog_optimize_thread_scheduling
deadlock_search_depth_long
deadlock_search_depth_short
deadlock_timeout_long
deadlock_timeout_short
debug_no_thread_alarm
expensive_subquery_limit
extra_max_connections
extra_port
ignore_db_dirs
innodb_adaptive_flushing_method
innodb_adaptive_hash_index_partitions
innodb_blocking_buffer_pool_restore
innodb_buffer_pool_populate
innodb_buffer_pool_restore_at_startup
innodb_buffer_pool_shm_checksum
innodb_buffer_pool_shm_key
innodb_checkpoint_age_target
innodb_corrupt_table_action
innodb_dict_size_limit
innodb_doublewrite_file
innodb_fake_changes
innodb_fast_checksum
innodb_flush_neighbor_pages
innodb_ibuf_accel_rate
innodb_ibuf_active_contract
innodb_ibuf_max_size
innodb_import_table_from_xtrabackup
innodb_kill_idle_transaction
innodb_lazy_drop_table
innodb_locking_fake_changes
innodb_log_block_size
innodb_max_bitmap_file_size
innodb_max_changed_pages
innodb_merge_sort_block_size
innodb_page_size
innodb_print_all_deadlocks
innodb_read_ahead
innodb_recovery_stats
innodb_recovery_update_relay_log
innodb_show_locks_held
innodb_show_verbose_locks
innodb_stats_auto_update
innodb_stats_update_need_lock
innodb_thread_concurrency_timer_based
innodb_track_changed_pages
innodb_use_atomic_writes
innodb_use_fallocate
innodb_use_global_flush_log_at_trx_commit
innodb_use_stacktrace
innodb_use_sys_stats_table
join_buffer_space_limit
join_cache_level
key_cache_segments
log_slow_filter
log_slow_rate_limit
log_slow_verbosity
master_verify_checksum
mrr_buffer_size
myisam_block_size
old_mode
plugin_maturity
progress_report_time
query_cache_strip_comments
replicate_annotate_row_events
replicate_do_db
replicate_do_table
replicate_events_marked_for_skip
replicate_ignore_db
replicate_ignore_table
replicate_wild_do_table
replicate_wild_ignore_table
rowid_merge_buff_size
slave_sql_verify_checksum
thread_pool_idle_timeout
thread_pool_max_threads
thread_pool_oversubscribe
thread_pool_size
thread_pool_stall_limit
userstat

What Time did MySQL Start?

Time MySQL Server Started

From a mysql client just execute the following to see the date and time the server was started.

select now() - interval (variable_value/60/60/24) day -- \

from information_schema.global_status -- \

where variable_name='Uptime';

From the shell, this will provide the same information.

mysql -uroot -p -A -s  -e"select now() - interval (information_schema.global_status.VARIABLE_VALUE/60/60/24) day -- \"from information_schema.global_status where variable_name='Uptime';"

Sometime you may run into having to restart the mysqld under pressure. And it will not come up.  

One cause is the my.cnf file is modified some time after startup with a bad setting. You could use the results from the commands above and compare that to

stat /etc/my.cnf

to see if any changes were made to my.cnf that might prevent the server from re-starting.

A little-mentioned feature on the interwebs regarding MySQL is the InnoDB monitors.

It has been around for a long time.

http://dev.mysql.com/doc/refman/5.6/en/innodb-monitors.html

You have to create varying tables to enable the monitoring.  You only want to do this when your system can withstand a performance degradation.

When you are trying to improve performance or want a look under the hood, the monitors are valuable.

Monitors provide rich information about InnoDB such as File I/O, Locks, and Cardinality of indexes.  They can help to provide a shape of the overall activity on your InnoDB tables.

The amount of information can be overwhelming when you are in a scenario where performance is degrading rapidly and you are on-call to remedy the degradation of service.

The most common issue is lock contention where more than 2 clients attempt to update and read the same row.  This is sometimes unavoidable, like having a conversation where more than two people are involved.  The natural remedy is to have lock waits timeout at half the time the user expects a result (1.5 seconds), which is configurable in my.cnf (or my.ini on windows).  Then the application must retry the transaction if it faults.  Unfortunately retry-code is not part of many of the standard database libraries and must be incorporated by the developers.  In short time, the frameworks could handle for this case.

You can also replay the binary log on a server with these monitors enabled to dig deeper into a past performance degradation.

The monitors are powerful diagnostics built-in to MySQL.

MySQL Connector/J: Failover and Blacklists

MySQL Connector/J provides loadbalancing and failover support.  While testing failover I wanted to know what was happening under the hood. Specifically I wanted to know when a host was blacklisted and which host.

If you set loadBalanceStrategy to random in your connection properties, which has been the recommended strategy over round-robin, you can modify the RandomBalanceStrategy.java file to log when a host is blacklisted.

Using guidance found on Todd Farmer's blog (http://mysqlblog.fivefarmers.com/) and a bit of twiddling it wasn't difficult.

In my setup, I have jboss-as-7.1.1. running on my local windows machine with my test app I developed using one of the JBoss quickstarts.

The connector (connector-java-5.1.21) is deployed and I have the source on C:\mysql-connector-java-5.1.21.

In RandomBalanceStrategy.java, right after the class declaration, I added the second line shown below.

public class RandomBalanceStrategy implements BalanceStrategy {
    private static Logger logger = Logger.getLogger("cyfd.connectorj.plugins");

Next, I added the second line shown below.

proxy.addToGlobalBlacklist( hostPortSpec );
logger.log(Level.SEVERE, "RandomBalanceStrategy-"+"Blacklisted host: " + 
   hostPortSpec + " att: " + attempts + " ret:" + numRetries  );

Save the file and compile it: (from windows cmd prompt at C:\mysql-connector-java-5.1.21\src\com\mysql\jdbc>; and, this is all one line)

javac -verbose -classpath "C:\jboss-as-7.1.1.Final\modules\com\mysql\main\
mysql-connector-java-5.1.24-bin.jar" -sourcepath . RandomBalanceStrategy.java

With jboss shutdown, update the jar file with the new class (again, this is all one line but executed from under the src folder which is C:\mysql-connector-java-5.1.21\src on my machine):

jar uf C:\jboss-as-7.1.1.Final\modules\com\mysql\main\mysql-connector-java-
5.1.24-bin.jar com/mysql/jdbc/RandomBalanceStrategy.class

Start jboss and when a host is blacklisted, you'll get a message similar to this.
08:25:11,005 SEVERE [cyfd.connectorj.plugins] (http--127.0.0.1-8080-80) RandomBalanceStrategy Blacklisted host: mysqlA:3306 att: 0 ret:100

So far the results of failover and load balance testing work perfectly.   As far as I can tell, a web client will get an error page--sometimes--but they can refresh the page and all transactions happen on the other server.

More testing to do....