Monday, June 2, 2014

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.



Wednesday, April 2, 2014

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

Friday, November 22, 2013

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.


Thursday, October 17, 2013

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.


Wednesday, April 24, 2013

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....


Monday, December 10, 2012

MySQL 5.6

Replication

Note 1760
Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.

With the new 5.6, while doing a change master to, I received the message
Query OK, 0 rows affected, 2 warnings (0.04) sec

A quick show warnings revealed the above note which is a ER_INSECURE_CHANGE_MASTER.

It took some digging, but yes, it's possible to store replication information in a table (mysql.slave_master_info):

set global master_info_repository = 'TABLE';

This can be set in the option file as well, so it's persistent between start ups.    As soon as you do this the master.info file in the datadir is removed, and the mysql.slave_master_info is populated.  You don't have to go clean up anything.  This also can be done for relay_log_info_repository as well.

What's the best way? 

From Linux a strings slave_master_info.ibd will show the user and password and server.  Since master.info is stored in datadir and the slave_master_info.ibd table is stored unencrypted in datadir/mysql, I don't see how it's more secure.

When you backup the server using mysqldump, you can carry the replication information with the backup using the TABLE mode.  So that might be more convenient for some folks.

If you have two instances running, using the same datadir and are slaves, it also may make it more convenient, so you don't have to make each one have a custom filename  using the master-info-file option.
 
Nice feature, but not any more secure from what I can see.  Anyone?

ADDENDUM:
When you do a show slave status\G  you will see a row such as this if it stores the master information in a table.  If it is in a file it will provide the full path and file name. 

 Master_Info_File: mysql.slave_master_info


Tuesday, April 24, 2012

XSLT Hints (hard lessons)

XSLT really relies on XPATH.
To learn XSLT you must learn XPATH.
The most common problem with an xslt document is an XPATH issue.
The most common XPATH issue is a namespace issue.
Try changing your node references to include *: for namespace.
Another common problem is the xslt engine.  Free engines are crippled to the standard.