Monday, September 28, 2009

Oracle Versus MySQL Classes

I'm always being asked what is the difference between Oracle classes and MySQL classes. What are the similarities and what are some of the differences? So I thought I'd jot down a few notes.

Course similarities:
Oracle Classes
 MySQL Classes
Oracle Database 11g: Introduction to SQL
 MySQL for Beginners
Oracle Database 11g: Introduction for Exp. SQL Users
 MySQL for Developers
Oracle Database 11g: SQL Fundamentals II
 MySQL Developer Techniques
Oracle Database 11g: PL/SQL Developer
 MySQL Stored Procedures Techniques
Oracle Database 11g: Administrator Workshop I
 MySQL for DBAs
Oracle Database 11g: Performance Tuning
 MySQL Performance Tuning
Oracle Database 11g: RAC Administration
 MySQL Cluster

Notes on some differences:
Oracle RAC (MySQL Cluster), PL/SQL (MySQL Stored Procedures), Streams (MySQL HA) have a lot more depth and complexity than MySQL equivalents.
MySQL does not have a class for MySQL Enterprise Monitor (Oracle Grid).
MySQL has PHP class, while Oracle has Java, Fusion Middleware, APEX and Forms developer classes
Posted by at 4 comments:
Labels:

Tuesday, September 8, 2009

MySQL Continues to be a "Tipping Point" in the IT Industry




















MySQL continues to be a tipping point for the IT industry.  In March of 2008 and January 2009 I talked about MySQL being a very important factor in the IT industry.  Today, MySQL  continues to play an important role in the direction of the IT industry.  Some facts about MySQL in a down economy:
  • While technical conferences are being canceled or running at 50% attendance of previous years, the MySQL conference ran at 100% attendance.
  • MySQL continues to enjoy significant revenue growth.
  • The number of MySQL database servers continue to increase at a constant pace.
  • The passion of Dolphins remains very high.
So it will be interesting to watch what happens to MySQL in the next year.  Who knows there may be a future appendix to "The MySQL Story".    "Everybody, please fasten your seat belts."

Posted by at 1 comment:

Friday, September 4, 2009

Configuring INFORMATION_SCHEMA Plugin Tables for InnoDB Plugin

There are seven new INFORMATION_SCHEMA tables (plugins) available with the InnoDB plugin.  They need to be loaded to be used.

New InnoDB Plugins contain information on:
  • Compressed InnoDB tables
  • Compressed InnoDB buffer pool
  • Current InnoDB transactions
  • Transaction Locks
  • Blocking transactions
These INFORMATION_SCHEMA plugins are available by setting the plugin-load parmeter:

plugin-load=innodb=ha_innodb_plugin.dll;innodb_trx=ha_innodb_plugin.dll;innodb_locks=ha_innodb_plugin.dll;innodb_cmp=ha_innodb_plugin.dll;innodb_cmp_reset=ha_innodb_plugin.dll;innodb_cmpmem=ha_innodb_plugin.dll;innodb_cmpmem_reset=ha_innodb_plugin.dll;

mysql> SELECT table_name, engine, table_type FROM information_schema.tables
             WHERE table_name LIKE 'innodb%'
 
table_nameenginetable_type
INNODB_CMP_RESETMEMORYSYSTEM VIEW
INNODB_TRXMEMORYSYSTEM VIEW
INNODB_CMPMEM_RESETMEMORYSYSTEM VIEW
INNODB_CMPMEMORYSYSTEM VIEW
INNODB_CMPMEMMEMORYSYSTEM VIEW
INNODB_LOCKSMEMORYSYSTEM VIEW


Before loading plugin tables: 

mysql>  SHOW PLUGINS;
NameStatusTypeLibraryLicense
binlogACTIVESTORAGE ENGINEGPL
ARCHIVEACTIVESTORAGE ENGINEGPL
BLACKHOLEACTIVESTORAGE ENGINEGPL
CSVACTIVESTORAGE ENGINEGPL
FEDERATEDDISABLEDSTORAGE ENGINEGPL
MEMORYACTIVESTORAGE ENGINEGPL
MyISAMACTIVESTORAGE ENGINEGPL
MRG_MYISAMACTIVESTORAGE ENGINEGPL
partitionACTIVESTORAGE ENGINEGPL
InnoDBACTIVESTORAGE ENGINEha_innodb_plugin.dllGPL

After loading plugin tables: 

mysql>  SHOW PLUGINS;

NameStatusTypeLibraryLicense
binlogACTIVESTORAGE ENGINEGPL
ARCHIVEACTIVESTORAGE ENGINEGPL
BLACKHOLEACTIVESTORAGE ENGINEGPL
CSVACTIVESTORAGE ENGINEGPL
FEDERATEDDISABLEDSTORAGE ENGINEGPL
MEMORYACTIVESTORAGE ENGINEGPL
MyISAMACTIVESTORAGE ENGINEGPL
MRG_MYISAMACTIVESTORAGE ENGINEGPL
partitionACTIVESTORAGE ENGINEGPL
InnoDBACTIVESTORAGE ENGINEha_innodb_plugin.dllGPL
INNODB_TRXACTIVEINFORMATION SCHEMAha_innodb_plugin.dllGPL
INNODB_LOCKSACTIVEINFORMATION SCHEMAha_innodb_plugin.dllGPL
INNODB_CMPACTIVEINFORMATION SCHEMAha_innodb_plugin.dllGPL
INNODB_CMP_RESETACTIVEINFORMATION SCHEMAha_innodb_plugin.dllGPL
INNODB_CMPMEMACTIVEINFORMATION SCHEMAha_innodb_plugin.dllGPL
INNODB_CMPMEM_RESETACTIVEINFORMATION SCHEMAha_innodb_plugin.dllGPL

InnoDB parameters example:
[mysqld]
basedir="C:/mysql/5.1.38/"
datadir="C:/mysql/Data/"

#*** INNODB Specific options ***
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.dll;innodb_trx=ha_innodb_plugin.dll;innodb_locks=ha_innodb_plugin.dll;innodb_cmp=ha_innodb_plugin.dll;innodb_cmp_reset=ha_innodb_plugin.dll;innodb_cmpmem=ha_innodb_plugin.dll;innodb_cmpmem_reset=ha_innodb_plugin.dll

plugin_dir="C:/mysql/5.1.38/lib/plugin"

default-storage-engine=INNODB
innodb_data_home_dir="C:/mysql/innodb"

innodb_data_file_path=ibdata1:1G;ibdata2:1G:autoextend
innodb_autoextend_increment=100
innodb_log_group_home_dir="C:/mysql/innologs"

innodb_buffer_pool_size=120M
innodb_log_file_size=100M
Posted by at No comments:
Labels:

InnoDB Plugin Parameters

 Here is the set of new parameters available with the InnoDB Plugin 1.0.4


NameScopeDynamicDefault
innodb_adaptive_flushingGLOBALYESTRUE
innodb_change_bufferingGLOBALYESinserts
innodb_file_formatGLOBALYESAntelope
innodb_file_format_checkGLOBALYESON
innodb_io_capacityGLOBALYES200
innodb_read_ahead_thresholdGLOBALYES56
innodb_read_io_threadsGLOBALNO4
innodb_spin_wait_delayGLOBALYES6
innodb_stats_sample_pagesGLOBALYES8
innodb_strict_modeGLOBAL|SESSIONYESFALSE
innodb_use_sys_mallocGLOBALNOTRUE
innodb_write_io_threadsGLOBALNO4

Parameters with New Defaults for InnoDB Plugin 1.0.4
NameOld DefaultNew Default
innodb_additional_mem_pool_size1MB8MB
innodb_buffer_pool_size8MB128MB
innodb_log_buffer_size1MB8MB
innodb_max_dirty_pages_pct9075
innodb_sync_spin_loops2030
innodb_thread_concurrency8

mysql> SELECT version();
+------------------+
| version()        |
+------------------+
| 5.1.38-community |
+------------------+

mysql> SHOW VARIABLES LIKE 'innodb_version';
+----------------+-------+
| Variable_name  | Value  |
+----------------+-------+
| innodb_version  | 1.0.4   |
+----------------+-------+


InnoDB Parameters After Setting Plugin

Query show variables like '%innodb%';
Output From Command:show variables like '%innodb%';
Variable_nameValue
have_innodbYES
ignore_builtin_innodbON
innodb_adaptive_flushingON
innodb_adaptive_hash_indexON
innodb_additional_mem_pool_size8388608
innodb_autoextend_increment8
innodb_autoinc_lock_mode1
innodb_buffer_pool_size134217728
innodb_change_bufferinginserts
innodb_checksumsON
innodb_commit_concurrency0
innodb_concurrency_tickets500
innodb_data_file_pathibdata1:10M:autoextend
innodb_data_home_dir(null)
innodb_doublewriteON
innodb_fast_shutdown1
innodb_file_formatAntelope
innodb_file_format_checkAntelope
innodb_file_io_threads4
innodb_file_per_tableOFF
innodb_flush_log_at_trx_commit1
innodb_flush_method(null)
innodb_force_recovery0
innodb_io_capacity200
innodb_lock_wait_timeout50
innodb_locks_unsafe_for_binlogOFF
innodb_log_buffer_size8388608
innodb_log_file_size5242880
innodb_log_files_in_group2
innodb_log_group_home_dir.\
innodb_max_dirty_pages_pct75
innodb_max_purge_lag0
innodb_mirrored_log_groups1
innodb_open_files300
innodb_read_ahead_threshold56
innodb_read_io_threads4
innodb_replication_delay0
innodb_rollback_on_timeoutOFF
innodb_spin_wait_delay6
innodb_stats_on_metadataON
innodb_stats_sample_pages8
innodb_strict_modeOFF
innodb_support_xaON
innodb_sync_spin_loops30
innodb_table_locksON
innodb_thread_concurrency0
innodb_thread_sleep_delay10000
innodb_use_sys_mallocON
innodb_version1.0.4
innodb_write_io_threads4
50 rows in set (0.00 sec)


InnoDB Parameters Before Setting Plugin

 mysql> SHOW VARIABLES LIKE 'ignore_builtin_innodb';
+-----------------------+-------+
| Variable_name             | Value |
+-----------------------+-------+
| ignore_builtin_innodb    | OFF    |
+-----------------------+-------+

Output From Command:  show variables like '%innodb%';
Variable_nameValue
have_innodbYES
ignore_builtin_innodbOFF
innodb_adaptive_hash_indexON
innodb_additional_mem_pool_size1048576
innodb_autoextend_increment8
innodb_autoinc_lock_mode1
innodb_buffer_pool_size8388608
innodb_checksumsON
innodb_commit_concurrency0
innodb_concurrency_tickets500
innodb_data_file_pathibdata1:10M:autoextend
innodb_data_home_dir(null)
innodb_doublewriteON
innodb_fast_shutdown1
innodb_file_io_threads4
innodb_file_per_tableOFF
innodb_flush_log_at_trx_commit1
innodb_flush_method(null)
innodb_force_recovery0
innodb_lock_wait_timeout50
innodb_locks_unsafe_for_binlogOFF
innodb_log_buffer_size1048576
innodb_log_file_size5242880
innodb_log_files_in_group2
innodb_log_group_home_dir.\
innodb_max_dirty_pages_pct90
innodb_max_purge_lag0
innodb_mirrored_log_groups1
innodb_open_files300
innodb_rollback_on_timeoutOFF
innodb_stats_on_metadataON
innodb_support_xaON
innodb_sync_spin_loops20
innodb_table_locksON
innodb_thread_concurrency8
innodb_thread_sleep_delay10000
innodb_use_legacy_cardinality_algorithmON
37 rows in set (0.00 sec)
Posted by at No comments:

Configuring the InnoDB Plugin (1.0.4) in MySQL 5.1.38

The InnoDB Plugin (1.0.4) is  pretty straight forward configuration. These instructions are for MySQL 5.1.38.  There is a new release of InnoDB Plugin with the 5.1.41 release. With MySQL you can use the default version of InnoDB or the new Plugin but not both.  If using a non-default configuration make sure and set the plugin_dir variable to point to the ha_innodb_plugin library for your system. 

To use the new plugin set the following parameters in your my.ini or my.cnf file.

Make sure you set the paths right for your setup and the right MySQL release like 5.1.40, etc.  

Setting the InnoDB Plugin on Unix/Linux:

[mysqld]
ignore-builtin-innodb
plugin_dir=/opt/mysql/5.1.38/lib/mysql/plugin
plugin-load=ha_innodb_plugin.so


Setting the InnoDB Plugin on Windows:
[mysqld]
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.dll
plugin_dir="C:/mysql/5.1.38/lib/plugin"

mysql> SHOW VARIABLES WHERE variable_name like '%plugin%' OR  variable_name like '%builtin%';
Variable_name
Value
ignore_builtin_innodb
ON
plugin_dir
C:/mysql/5.1.38/lib/plugin

My initial settings using the InnoDB Plugin on Windows
[mysqld]
basedir="C:/mysql/5.1.38/"
datadir="C:/mysql/Data/"

#*** INNODB Specific options ***
ignore-builtin-innodb
plugin_dir="C:/opt/mysql/5.1.38/lib/plugin"
plugin-load=innodb=ha_innodb_plugin.dll


default-storage-engine=INNODB
innodb_data_home_dir="C:/mysql/innodb"
innodb_data_file_path=ibdata1:1G;ibdata2:1G:autoextend
innodb_autoextend_increment=100
innodb_log_group_home_dir="C:/mysql/innologs"

innodb_buffer_pool_size=120M
innodb_log_file_size=100M
Posted by at No comments:
Labels:

Thursday, September 3, 2009

MySQL 5.1.38 Releases with InnoDB Plugin

The MySQL 5.1.38 release is available today with the InnoDB Plugin (1.0.4) included in the software distribution.  The InnoDB Plugin offers some key features:
  • Improved performance and scalability.
  • Important management features that will be very helpful to MySQL DBAs.    
I recommend you look into the new features available with the InnoDB Plugin. The InnoDB Plugin (1.0.4) is at end of beta cycle. This storage engine can replace the default InnoDB storage engine by making a few configuration changes.  Blog on Thursday, April 9, 2009 InnoDB Plugin Has Some Very Cool Features highlights the Oracle InnoDB Plugin.

For documentation on installing MySQL 5.1.38 or upgrading from previous MySQL releases, look here:
To look at the changes in the 5.1.38 release compared to the previous release look here.  
Posted by at 2 comments:
Labels:
Subscribe to: Comments (Atom)