Wednesday, December 30, 2009

SHOW INNODB STATUS walk through

The below URL explains the SHOW INNODB STATUS in detail
http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/

Suresh Kuna

Tuesday, December 29, 2009

What is Ajax ?

AJAX isn't a technology, or a language, and there's no recipe to implement it; it's just a combination of various components to achieve something you otherwise couldn't: asynchronous http requests. However, since early 2005, when Google and Flickr popularized the concept, its use has grown rapidly.

The name AJAX is short for Asynchronous JavaScript and XML. It uses the JavaScript XMLHttpRequest function to create a tunnel from the client's browser to the server and transmit information back and forth without having to refresh the page. The data travels in XML format because it transmits complex data types over clear text.

AJAX uses XHTML for the data presentation of the view layer, DOM, short for Document Object Model, which dynamically manipulates the presentation, XML for data exchange, and XMLHttpRequest as the exchange engine that ties everything together.

Because of these requirements, AJAX works on I.E. 5.0+, Mozilla 1.0+, Firefox 1.0+, Netscape 7.0+, and Apple added it to Safari 1.2+.

Traditional HTML sends a request to the server, which processes it and either returns a static HTML page or dispatches the request to some scripting language such as ColdFusion, which creates and returns an HTML page for the browser to render. When this method has to retrieve new data from the server it has to repost and reload another HTML file. In many cases perhaps only a small portion of the returned HTML code varies and the shell itself remains the same resulting in huge overhead because the data has to be downloaded every time.

Some classic examples of applications that would benefit from AJAX are searching for information and displaying it back in a table, related select dropdowns, or checking if a user exists before submitting an entire form.

The below url contains a detail implementation of how it works.
http://ajax.sys-con.com/node/138966

Sunday, December 27, 2009

DBConvert for Oracle and MySQL

Free tools for the Database conversion to Mysql.
http://www.zdnetasia.com/downloads/pc/swinfo/0,39043052,39654405s,00.htm
and http://dev.mysql.com/downloads//gui-tools/5.0.html
The following are some of the key features of the MySQL Migration Toolkit:

* The MySQL Migration Toolkit supports a variety of source database systems, including the following:
o Oracle
o Microsoft SQL Server
o Microsoft Access
o Sybase
o MySQL

* The MySQL Migration Toolkit is fully customizable through its Java runtime interface. Advanced users can use Java to perform custom data and schema transformations.
* The MySQL Migration Toolkit supports agent-based data migrations, with the MySQL Migration Toolkit residing on a separate machine than the source and target database servers, through the use of a special migration agent. The agent-based data migration improves migration performance by allowing data to be transferred directly from the source machine to the target machine without being routed through the MySQL Migration Toolkit.

Wednesday, December 23, 2009

Feature enhancements to MySQL Cluster

How important / useful would the following potential enhancements to MySQL Cluster be ?

Running MySQL Cluster on Windows in a production environment
Running MySQL Cluster within virtual machines in a production environment
Improved performance for complex joins
Support for Foreign Keys
More than 128 columns per row
More than 8 Kbytes of data (excluding BLOBs) per row
Ability for indexes to be stored on disk rather than always in memory
Improved performance for asynchronous (Geographic) replication (This does *not* refer to the synchronous replication between data nodes within a single Cluster).
Disk-durable commits - the ability to specify that a transaction does not commit until the in-memory change has been persisted to disk (Latency would be impacted)
Integrate MySQL Cluster backups with Enterprise backup solutions (e.g. Tivoli, NetBackup, HP DataProtector)
XA Distributed commit
Support for application and data nodes to run on machines with different endian architectures
Allow MySQL Cluster to continue operations after a full node-group has been lost (Depending on configuration, a limited number of rows in each table would be unavailable when a full node group is lost)
Allow effective use of more than 8 cores/hardware threads for a single data node
On-line drop column
Meta data (views, privileges etc.) shared between MySQL Servers
Enhanced asynchronous (geographic) replication management
MySQL Enterprise Monitor support for MySQL Cluster (including data nodes)
Extend conflict resolution for asynchronous (geographic) replication to include deletes and inserts
Extend conflict resolution for asynchronous (geographic) replication to remove requirement for application to manage Timestamps.
Support for the Service Availability Framework (SAF)
NDB API thread contention
2 x Data Node (1 node group) configuration where application selects which data node should be responsible for each row (e.g. all rows active on the same data node)
Variable sized VARCHARS on disk
Improve OPTIMIZE TABLE command
Enhanced memory use reporting
Savepoints
Support Map Reduce
Increase security by allowing table data to be transparently encrypted and the key kept secure.
SNMP support for traps
SNMP support for management
Backup a single database
Filtering of NDB API events
Abort transactions when disks become overloaded in order to avoid data nodes crashing

MySQL Storage Engines

Archive
BerkeleyDB
Blackhole
CSV
Example
Falcon
Federated
InfoBright
InnoDB
Kickfire
Maria
Memory
Merge
MyISAM
NDB
PBXT
TokuDB
Custom Storage Engine

Tuesday, December 22, 2009

Most important MySQL Cluster features

High Availability
Distributed, shared-nothing architecture
Synchronous data replication
Fast Failover
On-line operations (i.e. add nodes, upgrades, schema changes, etc)
On-line back-up
Self-healing (i.e. automatic node recovery)
Geographic redundancy / replication
ACID Compliance / Transactional
Low latency / real-time operations
High write (UPDATE) performance
High read performance
Multiple interfaces / access methods to the data
Ability to scale out on commodity hardware
In-memory data storage
Disk-based data storage
Automatic data partitioning
User-defined data partitioning
Multi-platform support
Open source licensing / distribution
Low cost of acquisition
Low cost of operations

Monday, November 30, 2009

Maakit tool options description

Prerequisites : Perl installation.

Maatkit to prove replication is working correctly, fix corrupted data, automate repetitive tasks, speed up your servers, and much, much more.


1) mk-archiver : Archive rows from a MySQL table into another table or a file.
- Can move the old data into other table or a file format 'load data infile'.
Adv :( used in Dataware housing concept ) - low impact on OLTP.
2) mk-audit : Analyze, summarize and report on MySQL config, schema and operation.
- OS version and flavor, and information on CPU, memory and disks
- It reports some information of Mysqld server's ( instance wise) and the data in it.
3) mk-deadlock-logger : Extract and log MySQL deadlock information.
- Prints the deadlock info currently only innodb or can store in a table also.
4) mk-duplicate-key-checker : Find duplicate indexes and foreign keys on MySQL tables.
- Examines SHOW CREATE TABLE o/p and display duplicates.
5) mk-fifo-split : Split files and pipe lines to a fifo without really splitting.
- Read hugefile.txt in chunks of a million lines without physically splitting it.
6) mk-find : Find MySQL tables and execute actions, like GNU find.
- Uses SHOW TABLES and SHOW TABLE STATUS and display the info like
table engines, data size, index size.
7) mk-heartbeat : Monitor MySQL replication delay.
- Creates a table does the update and checks the update timestamp in the slave and reports the delay time.
8) mk-kill : Kill MySQL queries that match certain criteria.
- Uses SHOW PROCESSLIST. Also called as “Slow Query Snipper”. Criteria is like time, state. It has may options so Be careful, it kill replication threads also.
We can specify certain queries also to kill like “ select * from tablename.”
9) mk-loadavg : Watch MySQL load and take action when it gets too high.
-Watches including MySQL status values from SHOW STATUS, SHOW INNODB STATUS and SHOW SLAVE STATUS, the three system load averages from uptime, and values from VMSTAT executes our given command when it resches threshold.
10) mk-log-player : Split and play MySQL slow logs.
- Split the slow logs into sessions (for parallel execution) and does the stress-test and load-test the server.
11) mk-parallel-dump : Dump MySQL tables in parallel.
- Dumps big table in chunks as specified size. Only tables and data are dumped; view definitions or any kind of stored code (triggers, events, routines, procedures, etc.) are not dumped.
mk-parallel-restore Load files into MySQL in parallel.
- Restore the dump taken by mk-parallel-dump in parallel.
12) mk-query-profiler : Execute SQL statements and print statistics, or measure activity caused by other processes.
- It reads a file containing one or more SQL statements or shell commands, executes them, and analyzes the output of SHOW STATUS afterwards. It then prints statistics about how the batch performed. For example, it can show how many table scans the batch caused, how many page reads, how many temporary tables.
13) mk-profile-compact : Compact the output from mk-query-profiler.
- It slices and aligns the output from mk-query-profiler so you can compare profile results side by side easily
14) mk-query-digest : Parses logs and more. Analyze, transform, filter, review and report on queries.
- Analyses queries from Slow log and PROCESSLIST and gives a report.
15) mk-show-grants : Canonicalize and print MySQL grants so you can effectively replicate, compare and version-control them.
- With this o/p, we can pipe the grants from one server to another server.
16) mk-slave-delay : Make a MySQL slave server lag behind its master.
- It bases the delay on binlog positions in the slave's relay logs by default, so there is no need to connect to the master.
17) mk-slave-find : Find and print replication hierarchy tree of MySQL slaves.
18) mk-slave-move : Move a MySQL slave around in the replication hierarchy.
- Detach and Make the server a slave of its grandparent, so it is a sibling of its master.
19) mk-slave-prefetch : Pipeline relay logs on a MySQL slave to pre-warm caches.
- reads the slave's relay log slightly ahead of where the slave's SQL thread is reading, converts statements into SELECT, and executes them. Keeps the data in the unmodified data in the cache.
20) mk-slave-restart : Watch and restart MySQL replication after errors.
- We can specify errors to skip and run the slaves until a certain binlog position.
21) mk-table-checksum : Perform an online replication consistency check, or checksum MySQL tables efficiently on one or many servers.
- It examines table structure only on the first host specified, so if anything differs on the others, it won't notice. It ignores views.
22) mk-checksum-filter : Filter checksums from mk-table-checksum.
- Filters the o/p of the mk-table-checksum of two files and display the different checksums or count.
23) mk-table-sync : Synchronize MySQL tables efficiently.
- It does not synchronize table structures, indexes, or any other schema changes. It synchronizes only data.
24) mk-upgrade Execute : SQL statements against two MySQL servers and compare the results.
- Useful before an upgrade or configuration change.
25) mk-visual-explain : Format EXPLAIN output as a tree. - In tree format, helpful when we have more joins.

Friday, November 27, 2009

Collection of pdf

http://3pdf.com/download-free-mysql-pdf-ebook.htm

Tuesday, November 24, 2009

MySQL Cluster Query execution process

MySQL Cluster executes the queries depending on the below type of scan’s it does

  1. Primary key lookup
  2. Unique key lookup
  3. Ordered index scan (i.e., non-unique indexes that use T-trees)
  4. Full table scan

Let’s say you have 4 data nodes in your cluster (NoOfReplicas=2). This means you have 2 node groups and each one has half the data. Cluster uses a hash on the primary key (unless you’ve controlled the partitioning using the 5.1 partitioning features). So for any table, half the rows are in one node group and half the rows are the in other node group.

Now for the 4 types of query execution. You can verify which type of execution is used with EXPLAIN. Here’s how each ones works:

  1. Primary key lookup - the MySQL server can calculate the hash on the primary key and know which data node the relevant row resides in. Then the MySQL server contacts that data node and receives the row.
  2. Unique key lookup - the MySQL server cannot know which data node the row might be stored in. So it contacts a pseudo-random node. That data node has a hidden table that maps the unique key values to the primary key. Then the hash on the primary key reveals where the row resides.
  3. Ordered index scans are sent in parallel to all data nodes, where they search their local t-tree.
  4. Full table scans are send in parallel to all data nodes, where they scan their primary fragment.

Summary: primary key lookups are best. If you have more than 2 nodes, throughput goes up because all nodes are actively serving different fragments of data. Ordered index lookup and full table scans are done in parallel, so more nodes leads to better performance.