SELECT @min_value REGEXP '^[0-9]*$ INTO v_flag_min;

IF v_flag_min = 0 THEN
no numeral
ELSE
numeral
END IF;

The detail description as following:
http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Tuesday, April 10, 2012

Listener starting Error with IFILE

When we start oracle listener, the command as following:
#export ORACLE_SID=db1
#lsnrctl start
but it display error as following:TNS-12541: TNS:no listener
why?
check the listener configuration as following:
#vi listener.ora
IFILE = /space1/oracle10g/oracle/OraHome1/network/admin/devdb_db1.lsr
IFILE = /space1/oracle10g/oracle/OraHome1/network/admin/devdb_db2.lsr
IFILE = /space1/oracle10g/oracle/OraHome1/network/admin/devdb_db3.lsr

we know the reason is listener.ora configuration format, we need start listener as following command.
#lsnrctl start devdb_db1
#lsnrctl start devdb_db2
#lsnrctl start devdb_db3

Wednesday, March 28, 2012

Distributed Database Systems

Distributed_Database_System from Philip Zhong

Sunday, March 4, 2012

YQL and Vitess

1.YQL: Yahoo! Query Language (YQL)

http://developer.yahoo.com/yql/

YQL:

USE "http://your_domain_name/bart.xml" AS bart_table; SELECT name,eta FROM bart_table WHERE eta.destination LIKE "%SF%"

2.google vitess

https://code.google.com/p/vitess/wiki/ProjectGoals

Motivation and vision

Vtocc is the first usable product of vitess. It acts as a front-end to MySQL providing an RPC interface that accepts and transmits SQL commands. It is capable of efficiently multiplexing a large number of incoming connections (10K+) over a small number of db connections at reasonable throughput (~10kqps). It also has an SQL parser which gives the server the ability to understand and intelligently reshape the queries it receives.

Vtocc is already being used in a large scale production environment. It is the core of YouTube's new MySQL serving infrastructure.

Relational databases (like MySQL) were initially built and optimized for non-web OLTP systems. However, they have still managed to fulfill most of the needs of large scale web applications. Many of their legacy requirements have held them back from being able to optimally meet the needs of today’s applications. This has led to the development of alternate storage solutions (NoSQL) which in some respects have thrown the baby out with the bathwater.

With Vitess, we take a different approach. We think that databases like mysql have what it takes to provide an efficient data storage layer. What is missing is the ability to easily scale out and then coordinate many instances of a single logical schema. The way we plan to achieve this is by providing a subset SQL front end with limited guarantees and a loosely coupled distributed system to automate complex management scenarios.

Sessionless connections: A mysql connection carries a lot of context. This is usually unnecessary for web apps. Most Vitess connections are sessionless, which makes them lightweight, and this allows us to pool a very small number of connections to mysql.
ACID (Atomicity): This feature is an overkill for most web apps. Instead of honoring system-wide atomicity, Vitess gives you atomic guarantees for a given entity addressed by a “key” (like a user id). This allows us to transparently scale the database by splitting on key ranges.
ACID (Consistency): Vitess relaxes this rule towards data being eventually consistent. This allows us to use replication to distribute read traffic in situations where the app does not need up-to-date data. You can always request data to be read from the master db if you explicitly need up-to-date data.
Buffer cache vs. row cache: The mysql buffer cache is optimized for range scans over indices and tables, particularly when data is densely packed. Unfortunately, it’s not good for random access tables. Vitess will allow you to designate certain tables as random access. For such cases, it will maintain row based caches and keep them consistent by fielding all DMLs that could potentially affect them.

Vitess feature set

Sharding

All tables in a sharded database need to contain a “key” column. Vitess will use these values to decide the target shard for such data.
All tables that are indexed by a set of keys are known as a keyspace, which basically represents the logical database that combines all the shards that store them.
We are going with range based sharding. The main advantage of this scheme is that the shard map is a simple in-memory lookup. The downside of this scheme is that it creates hot-spots for sequentially increasing keys. In such cases, we recommend that the application hash the keys so they distribute more randomly.
The implied requirement from the above constraints is that all DMLs are expected to specify the key that is being updated. This also implies that the key will usually be the leading primary key column.
The shard key data type is expected to be a natural number or a varbinary. We do not support varchar for keys because of character set complications that contradict range-based sharding schemes.
When a shard becomes too ‘hot’, Vitess can decide to split it into two. This is done through filtered replication.
Vitess will also have the ability to merge shards.

Replication

You can specify a replication factor for a keyspace. This will make Vitess create that many replicas for each master database.
A shard map is a single list of databases that spans the entire keyspace. For example, a master shard map will contain all the master databases for the keyspace.
There will be a server (wrangler) that can serve these shard maps to the application based on what it requires. For example, the application can request a “random” replica shard map for reading data, or it could request the master shard map if it wishes to write data, or needs to read up-to-date info.
Vitess will support multiple data centers. It assumes that there is only one master data center, where all the master databases reside. Each data center will have a 'wrangler' server that will serve information about the local shard map in that data center.
Vitess is capable of electing a new master for maintenance (or due to failure). It will also provide a scheme to correspondingly reparent all downstream replicas automatically.

Schema rollout

Many DDLs cannot be performed on high traffic live systems due to their locking requirements. Vitess will allow you to coordinate such rollout with un-noticeable downtime by deploying the DDL to offline replicas and a reparenting process.

Thursday, March 1, 2012

flashcache

FlashCache

View more PowerPoint from Chris Westin

Thursday, February 23, 2012

mysql performance tuning

Mysql performance tuning

View more PowerPoint from Philip Zhong

Wednesday, February 22, 2012

compare oracle mysql5.5.20 performance with MariaDB-5.3.4 and Percona-Server 5.5.20

http://blog.montyprogram.com/benchmarking-mariadb-5-3-4/
http://www.mysqlperformanceblog.com/2012/02/18/mariadb-5-3-4-benchmarks/

In-memory database (inspired by 864GB of RAM for $12,000)

https://groups.google.com/forum/#!topic/nodejs/aqVYp5ABX_4

Monday, February 20, 2012

Mysql5.1 character set performance testing

Mysql5.1 character set testing

View more PowerPoint from Philip Zhong

Sunday, February 19, 2012

How to Write Language “Compiler”

How to write_language_compiler from Philip Zhong

Compare Innodb performance mysql5.1.50 mysql5.5.8

Compare mysql5.1.50 mysql5.5.8 from Philip Zhong

Why using Mysql5.1 Handler Socket

Mysql handle socket

View more PowerPoint from Li Zhong

mysql5.1 Architecture and Parameters

Mysql architecture&parameters

View more PowerPoint from Li Zhong

yacc Compiler

http://epaperpress.com/lexandyacc/

Wednesday, December 21, 2011

How Twitter Stores 250 Million Tweets a Day Using MySQL

Twitter's new tweet store:

When you tweet it's stored in an internal system called T-bird, which is built on top of Gizzard. Secondary indexes are stored in a separate system called T-flock, which is also Gizzard based.

Unique IDs for each tweet are generated by Snowflake, which can be more evenly sharded across a cluster. FlockDB is used for ID to ID mapping, storing the relationships between IDs (uses Gizzard).

Gizzard is Twitter's distributed data storage framework built on top of MySQL (InnoDB).

InnoDB was chosen because it doesn't corrupt data. Gizzard us just a datastore. Data is fed in and you get it back out again.

To get higher performance on individual nodes a lot of features like binary logs and replication are turned off. Gizzard handles sharding, replicating N copes of the data, and job scheduling.

Gizzard is used as a building block for other storage systems at Twitter.

http://highscalability.com/blog/2011/12/19/how-twitter-stores-250-million-tweets-a-day-using-mysql.html

innodb file format :antelope and barracuda

The InnoDB Plugin introduces the idea of a named file format and a configuration parameter to enable the use of features that require use of that format. The new file format is the “Barracuda” format, and the file format supported by prior releases of InnoDB is called file format “Antelope”. Compressed tables and the new row format that stores long columns “off-page” require the use of the “Barracuda” file format or newer. Future versions of InnoDB may introduce a series of file formats, identified with the names of animals, in ascending alphabetical order.

Beginning with this release, every InnoDB per-table tablespace file is labeled with a file format identifier. This does not apply to the system tablespace (the ibdata files) but only the files of separate tablespaces (the *.ibd files where tables and indexes are stored in their own tablespace). As noted below, however, the system tablespace is tagged with the “highest” file format in use in a group of InnoDB database files, and this tag is checked when the files are opened.

In this release, when you create a compressed table, or a table with ROW_FORMAT=DYNAMIC, the file header for the corresponding .ibd file and the table type in the InnoDB data dictionary are updated with the identifier for the “Barracuda” file format. From that point forward, the table cannot be used with a version of InnoDB that does not support this new file format. To protect against anomalous behavior, InnoDB version 5.0.21 and later will perform a compatibility check when the table is opened, as described below. (Note that the ALTER TABLE command will, in many cases, cause a table to be recreated and thereby change its properties.

detail URL link as following:

http://www.mysqlsystems.com/manual/innodb10en/innodb-file-format.html