2008-08-29 Fri

09:48 Log Buffer #112: A Carnival of the Vanities for DBAs (3646 Bytes) » Pythian Group Blog

Welcome to the 112th edition of Log Buffer, the weekly review of database blogs.

First, thanks to last issue’s contributors–Joe Izenman, Dan Norris, and Jason Massie–for snatching victory from the jaws of defeat and making LB#111 a worthwhile read. That’s what it’s all about!

Oracle’s up first, starting with our old friend Doug Burns and his Time Matters series, in which he hold up to the light the concept of DB Time: “. . . [the] total time spent by user processes either actively working or actively waiting in a database call.” He continues, “There’s a lot more I could say about DB Time. Like all of the best performance concepts or methods (e.g. YAPP, Method-R) it can seem so obvious as to not be worth saying, but contains an enormous amount of common sense and technical rigour.”

Arup Nanda writes about the time he spent Diagnosing Library Cache Latch Contention. About half an hour, as it happened, but he’s a real pro, and his analysis just goes to show. To quote, Nuno Souto–who makes the best blog endorsements– “Damn useful stuff . . .  bookmarked.”

Tanel Poder has another script for you to fall in love with, which makes its debut in flexible sampling of any V$ or X$ view with sample.sql. It is, writes Tanel, “ . . . a simple but powerful sqlplus script for ad-hoc sampling of any V$ view.”

Kenneth Downs, the Database Programmer, offers Advanced Algorithm: Sequencing Dependencies, a smart look at satisfying dependencies in databases. What does that mean? Well for example, Kenneth writes, “All popular Linux distributions have a package installation system in which each package lists its required dependencies. If you want to install a large number of packages in one shot, producing a tangled bunch of related dependencies, today’s algorithm can be used to work them all out.”

That’s the kind of task for which we humans use tools like mind maps. Jason Arneil shares his ASM Mind Map.

Laurent Schneider went off-road and came back something not on the map at all: the difference between rollbac and rollback.

(more…)

09:39 Product: ScaleOut StateServer is Memcached on Steroids (4775 Bytes) » High Scalability - Building bigger, faster, more reliable websites.

ScaleOut StateServer is an in-memory distributed cache across a server farm or compute grid. Unlike middleware vendors, StateServer is aims at being a very good data cache, it doesn't try to handle job scheduling as well.

StateServer is what you might get when you take Memcached and merge in all the value added distributed caching features you've ever dreamed of. True, Memcached is free and ScaleOut StateServer is very far from free, but for those looking a for a satisfying out-of-the-box experience, StateServer may be just the caching solution you are looking for. Yes, "solution" is one of those "oh my God I'm going to pay through the nose" indicator words, but it really applies here. Memcached is a framework whereas StateServer has already prepackaged most features you would need to add through your own programming efforts.

Why use a distributed cache? Because it combines the holly quadrinity of computing: better performance, linear scalability, high availability, and fast application development. Performance is better because data is accessed from memory instead of through a database to a disk. Scalability is linear because as more servers are added data is transparently load balanced across the servers so there is an automated in-memory sharding. Availability is higher because multiple copies of data are kept in memory and the entire system reroutes on failure. Application development is faster because there's only one layer of software to deal with, the cache, and its API is simple. All the complexity is hidden from the programmer which means all a developer has to do is get and put data.

StateServer follows the RAM is the new disk credo. Memory is assumed to be the system of record, not the database. If you want data to be stored in a database and have the two kept in sync, then you'll have to add that layer yourself. All the standard memcached techniques should work as well for StateServer. Consider however that a database layer may not be needed. Reliability is handled by StateServer because it keeps multiple data copies, reroutes on failure, and has an option for geographical distribution for another layer of added safety. Storing to disk wouldn't make you any safer.

Via email I asked them a few questions. The key question was how they stacked up against Memcached? As that is surely one of the more popular challenges they would get in any sales cycle, I was very curious about their answer. And they did a great job differentiation themselves. What did they say?

read more

  2008-08-28 Thu

15:58 SQL Function Spotlight: TRIM (3797 Bytes) » Eddie Awad's Blog

The SQL function TRIM has been around since Oracle 8i and maybe earlier. TRIM enables you to trim characters from a character string. The following examples demonstrate its usage and show you a few little known features of this simple function.

Remove leading and trailing blank spaces:

SQL> SELECT TRIM ('  mystring  ') FROM dual;

TRIM('MY
--------
mystring

Remove any leading characters equal to ‘x’:

SQL> SELECT TRIM (LEADING 'x' FROM 'xxmystringxx') FROM dual;

TRIM(LEADI
----------
mystringxx

Remove any trailing characters equal to ‘x’:

SQL> SELECT TRIM (TRAILING 'x' FROM 'xxmystringxx') FROM dual;

TRIM(TRAIL
----------
xxmystring

Removes leading and trailing characters equal to ‘x’:

SQL> SELECT TRIM (BOTH 'x' FROM 'xxmystringxx') FROM dual;

TRIM(BOT
--------
mystring

Removes leading and trailing characters equal to ‘x’ (Same as BOTH):

SQL> SELECT TRIM ('x' FROM 'xxmystringxx') FROM dual;

TRIM('X'
--------
mystring

My usage of this function has mostly been to trim blanks from both ends of a string. How about you?

Related functions: RTRIM and LTRIM.

---
Related Articles at Eddie Awad's Blog:


15:12 Paper: The End of an Architectural Era (It’s Time for a Complete Rewrite) (1468 Bytes) » High Scalability - Building bigger, faster, more reliable websites.

Update 2: H-Store: A Next Generation OLTP DBMS is the project implementing the ideas in this paper: The goal of the H-Store project is to investigate how these architectural and application shifts affect the performance of OLTP databases, and to study what performance benefits would be possible with a complete redesign of OLTP systems in light of these trends. Our early results show that a simple prototype built from scratch using modern assumptions can outperform current commercial DBMS offerings by around a factor of 80 on OLTP workloads.
Update: interesting related thread on Lamda the Ultimate.

A really fascinating paper bolstering many of the anti-RDBMS threads the have popped up on the intertube lately. The spirit of the paper is found in the following excerpt:

In summary, the current RDBMSs were architected for the business data processing market in a time of different user interfaces and different hardware characteristics. Hence, they all include the following System R architectural features:
* Disk oriented storage and indexing structures
* Multithreading to hide latency
* Locking-based concurrency control mechanisms
* Log-based recovery

read more

10:17 Product: Amazon's SimpleDB (1735 Bytes) » High Scalability - Building bigger, faster, more reliable websites.

Update 33: Amazon announces Elastic Block Store (EBS), which provides lots of normal looking disk along with value added features like snapshots and snapshot copying. But database's may find EBS too slow. RightScale tells us Why Amazon’s Elastic Block Store Matters.
Update 32: You can now get all attributes for a property when querying. Previously only the ID was returned and the attributes had to be returned in separate calls. This makes the programmer's job a lot simpler. Artificial levels of parallelization code can now be dumped.
Update 31: Amazon fixes a major hole in SimpleDB by adding the ability to sort query results. Previously developers had to sort results by hand which was a non-starter for many. Now you can do basic top 10 type queries with ease.
Update 30: Amazon SimpleDB - A distributed, highly-scalable, light-weight, query-able, attribute store by Sebastian Stadil. It introduces the CAP theorem and the basics of SimpleDB. Sebastian does a lot of great work in the AWS world and in what must be his limited free time, runs the AWS Meetup group.

read more

2008-08-27 Wed

14:38 What Data Type is Returned by a Mathematical Function? (1179 Bytes) » Pythian Group Blog

Or, “Missing information in the MySQL Manual”.

Just earlier today, I was using POW(), which I’ve grown quite fond of, simply because it makes life easier. I prefer using it like SELECT 512*POW(1024,2) to find out the number of bytes to put in a variable, for example.

First, let’s take a look at the POW function:

Name: 'POW'
Description:
Syntax:
POW(X,Y)

Returns the value of X raised to the power of Y.

Okay, so it gives us a value; but what about the data type? Let’s take 512*POW(1024,2) as an example.

5067 (blogs) > SELECT 512*POW(1024,2) AS example;
+-----------+
| example   |
+-----------+
| 536870912 |
+-----------+
1 row in set (0.00 sec)

What is that? Well, it sure does look like an INT at this point, doesn’t it?

(more…)

09:43 UNKNOWN State Of RAC Resources (4989 Bytes) » AskDba.org Weblog

While Checking the status of database resources, ASM was shown as UNKNOWN on one node of a two node RAC.

$ crs_stat -t

Name           Type           Target    State     Host
------------------------------------------------------------
ora.orcl.db    application    ONLINE    ONLINE    rac1
ora....11.inst application    ONLINE    ONLINE    rac1
ora....SM1.asm application    ONLINE    ONLINE    rac1
ora....DC.lsnr application    ONLINE    ONLINE    rac1
ora....idc.gsd application    ONLINE    ONLINE    rac1
ora....idc.ons application    ONLINE    ONLINE    rac1
ora....idc.vip application    ONLINE    ONLINE    rac1
ora....SM2.asm application    ONLINE    UNKNOWN    rac2
ora....C2.lsnr application    ONLINE    ONLINE    rac2
ora....dc2.gsd application    ONLINE    ONLINE    rac2
ora....dc2.ons application    ONLINE    ONLINE    rac2
ora....dc2.vip application    ONLINE    ONLINE    rac2

Following error was coming while trying to start the +ASM2 instance with SRVCTL:

$srvctl start asm -n rac2

PRKS-1009 : Failed to start ASM instance "+ASM2" on node "rac2",
[CRS-0223: Resource 'ora.rac2.ASM2.asm' has placement error.]

While trying to start the same with crs_start :

$ crs_start -f ora.rac2.ASM2.asm

CRS-1028: Dependency analysis failed because of:
'Resource in UNKNOWN state: ora.rac2.ASM2.asm'
CRS-0223: Resource 'ora.rac2.ASM2.asm' has placement error

There are two ways to come out of this UNKNOWN state of resources:
1. Start the resource from sqlplus
2. Use crs_stop -f to clear the state of database resources.

$ export ORACLE_HOME=+ASM2
$ sqlplus "/ as sysdba"
SQL > startup 

Diskgroup mounted

It will go fine and the +ASM2 instnace will be started.

$ crs_stop -f ora.rac2.ASM2.asm

This will clear the UNKNOWN state and will make the resource as OFFLINE.

Now start the resource as:

$ srvctl start asm -n rac2

After using this check the status :

$ crs_stat -t

Name           Type           Target    State     Host
------------------------------------------------------------
ora.orcl.db    application    ONLINE    ONLINE    rac1
ora....11.inst application    ONLINE    ONLINE    rac1
ora....SM1.asm application    ONLINE    ONLINE    rac1
ora....DC.lsnr application    ONLINE    ONLINE    rac1
ora....idc.gsd application    ONLINE    ONLINE    rac1
ora....idc.ons application    ONLINE    ONLINE    rac1
ora....idc.vip application    ONLINE    ONLINE    rac1
ora....SM2.asm application    ONLINE    ONLINE    rac2
ora....C2.lsnr application    ONLINE    ONLINE    rac2
ora....dc2.gsd application    ONLINE    ONLINE    rac2
ora....dc2.ons application    ONLINE    ONLINE    rac2
ora....dc2.vip application    ONLINE    ONLINE    rac2

Related Posts

09:37 Oracle Silent Mode, Part 8: Add a Node to a 11.1 RAC Database (2530 Bytes) » Pythian Group Blog

The process of adding a node to a 11.1 RAC is very similar to the 10.2 process described in Part 5 of this series. For this reason, this post will just focus on what has changed between the 2 versions. Here is the complete series up to now:

  1. Installation of 10.2 And 11.1 Databases
  2. Patches of 10.2 And 11.1 databases
  3. Cloning Software and databases
  4. Install a 10.2 RAC Database
  5. Add a Node to a 10.2 RAC database
  6. Remove a Node from a 10.2 RAC database
  7. Install a 11.1 RAC Database
  8. Add a Node to a 11.1 RAC database (This post!)
  9. Remove a Node from a 11.1 RAC database
  10. A ton of other stuff you should know

Before you start

Make sure you’ve kept a copy of the voting disk and that you have a backup of the OCR. Check that the locations for all the components to be installed, i.e.: Inventory, Clusterware, ASM, database software, OCR, Voting Disks, and data files, are writable. Confirm that all the prerequisites are met for the node and for the whole cluster with the node to be added.

Refer to the Oracle Clusterware Administration and Deployment Guide
11g Release 1 (11.1) - 4 Adding and Deleting Oracle Clusterware Homes

and the Oracle Real Application Clusters Administration and Deployment Guide
11g Release 1 (11.1) - 9 Adding and Deleting Oracle RAC from Nodes on Linux and UNIX Systems
for the complete reference of how to perform these steps.

(more…)

05:28 Using RDA As RDBMS Pre-Install Check Tool (8717 Bytes) » AskDba.org Weblog

Many of us would have come across RDA (Remote Diagnostic Agent) while working on a ticket with Oracle support. In case you have not heard about it, I would recommend to go through Metalink Note:314422.1 - Remote Diagnostic Agent (RDA) 4 - Getting Started

RDA captures System Information such as OS,Hardware Details (like number of CPU and amount of RAM),OS error log,OS Monitoring tool output (like vmstat,TOP,etc). This can be handy in case you do not know the command or the location of the OS logs.Similary you can find Database version,Database Patch inventory,Database Alert log and trace files.

This can help save lot of time as you need not remember all OS commands to capture the information.
Similarly RDA also collects Database Performance Statistics like OS Statistics (CPU,Memory and Disk I/O Stats) along with TOP SQL, Locking and Latch statistics. In case of 10g, it generates AWR Report (60 mins) and ADDM report based on captured Snapshots. All this information can be helpful for diagnosing a Performance Problem.

There is one more use of RDA which not many people are aware of. i.e RDA Health Check / Validation Engine (HCVE). HCVE Engine can be used to perform Pre-Install checks for Oracle Database and Oracle Application server on Unix system (At time of writing this article, this functionality is not available on windows)


To run this , you need to execute rda.sh -T hcve e.g I need to validate if I can install Oracle 10gR2 on my OEL4 (Linux x86).

$ ./rda.sh -T hcve
Processing HCVE tests ...
Available Pre-Installation Rule Sets:
   1. Oracle Database 10g R1 (10.1.0) PreInstall (Linux-x86)
   2. Oracle Database 10g R1 (10.1.0) PreInstall (Linux AMD64)
   3. Oracle Database 10g R1 (10.1.0) PreInstall (IA-64 Linux)
   4. Oracle Database 10g R2 (10.2.0) PreInstall (Linux AMD64)
   5. Oracle Database 10g R2 (10.2.0) PreInstall (IA-64 Linux)
   6. Oracle Database 10g R2 (10.2.0) PreInstall (Linux-x86)
   7. Oracle Database 11g R1 (11.1.0) PreInstall (Linux AMD64)
   8. Oracle Database 11g R1 (11.1.0) PreInstall (Linux-x86)
   9. Oracle Application Server 10g (9.0.4) PreInstall (Linux)
  10. Oracle Application Server 10g R2 (10.1.2) PreInstall (Linux)
  11. Oracle Application Server 10g R3 (10.1.3) PreInstall (Linux AMD64)
  12. Oracle Application Server 10g R3 (10.1.3) PreInstall (IA-64 Linux)
  13. Oracle Application Server 10g R3 (10.1.3) PreInstall (Linux-x86)
  14. Oracle Portal PreInstall (Generic)
Available Post-Installation Rule Sets:
  15. Oracle Portal PostInstall (generic)
  16. RAC 10G DB and OS Best Practices (Linux)
  17. Data Guard PostInstall (Generic)
Enter the HCVE rule set number
Hit 'Return' to accept the default (1)
> 6

Enter value for < Planned ORACLE_HOME location or if set >
Hit 'Return' to accept the default ($ORACLE_HOME)
> /u01/app/oracle

Test "Oracle Database 10g R2 (10.2.0) PreInstall (Linux-x86)" executed at Wed Aug 27 15:12:18 2008

Test Results
~~~~~~~~~~~~

   ID NAME                 RESULT VALUE
===== ==================== ====== ========================================
   10 OS Certified?        PASSED Adequate
   20 User in /etc/passwd? PASSED userOK
   30 Group in /etc/group? PASSED GroupOK
   40 Input ORACLE_HOME    RECORD /u01/app/oracle
   50 ORACLE_HOME Valid?   PASSED OHexists
   60 O_H Permissions OK?  PASSED CorrectPerms
   70 Umask Set to 022?    PASSED UmaskOK
   80 LDLIBRARYPATH Unset? FAILED IsSet
  100 Other O_Hs in PATH?  FAILED OratabEntryInPath
  110 oraInventory Permiss PASSED oraInventoryOK
  120 /tmp Adequate?       PASSED TempSpaceOK
  130 Swap (in MB)         RECORD 1051
  140 RAM (in MB)          FAILED 1001
  150 Swap OK?             FAILED InsufficientSwap
  160 Disk Space OK?       PASSED DiskSpaceOK
  170 Kernel Parameters OK PASSED KernelOK
  180 Got ld,nm,ar,make?   PASSED ld_nm_ar_make_found
  190 ulimits OK?          FAILED StackTooSmall MaxLockMemTooSmall
  200 EL4 RPMs OK?         PASSED EL4rpmsOK
  204 RHEL3 RPMs OK?       PASSED NotRedHat
  205 RHEL4 RPMs OK?       PASSED NotRedHat
  209 SUSE SLES9 RPMs OK?  PASSED NotSuSE
  212 Patch 3006854 Instal PASSED NotRHEL3
  214 ip_local_port_range  PASSED ip_local_port_rangeOK
  220 Tainted Kernel?      PASSED NotVerifiable
  230 Other OUI Up?        PASSED NoOtherOUI
Result file: /home/oracle/rda/output/RDA_HCVE_A201DB10R2_lnx_res.htm

I also tried out option “RAC 10G DB and OS Best Practices (Linux)” which is part of Post Install but for some reason some of the components failed.

Enter the HCVE rule set number
Hit 'Return' to accept the default (1)
> 16

Enter the password for 'SYSTEM':
Please re-enter it to confirm:

Test "RAC 10G DB and OS Best Practices (Linux)" executed at Wed Aug 27 17:26:33 2008

Test Results
~~~~~~~~~~~~

   ID NAME                 RESULT VALUE
===== ==================== ====== ========================================
   10 ORA_CRS_HOME         RECORD /u01/app/crs
  100 Database Name        RECORD orcl
  102 Database Version     RECORD 10.2.0.4.0
  104 Interconnect Network RECORD
  106 DB Block Size        RECORD 8192
  108 DB File Multiblock R RECORD 16
  120 Max Commit Propagati PASSED 0
  130 SYS.AUDSES$ Cache Si PASSED 10000
  132 SYS.IDGEN1$ Cache Si FAILED 20
  140 Parallel Execution M FAILED 2148
  150 Min Parallel Servers RECORD 1
  152 Min Parallel Servers FAILED 0
  200 $ORA_CRS_HOME Define PASSED Found
  210 Remote Access        PASSED All loaded
  220 _USR_ORA_DEBUG / CRS FAILED blrraclnx1:? blrraclnx2:?
  230 _USR_ORA_DEBUG / ORA FAILED blrraclnx1:? blrraclnx2:?
  240 rmem_max             PASSED OK
  250 UDP Buffer Size      PASSED OK
  260 wmem_max             PASSED OK
  270 rmem_default         PASSED OK
  280 wmem_default         PASSED OK
  290 Sysrq Magic Keys     PASSED OK
  300 Oracle Executable Li PASSED linked
  310 hangcheck-timer      FAILED blrraclnx1:Unknown blrraclnx2:Unknown
  320 aio-max-size Setting FAILED blrraclnx1:Unknown blrraclnx2:Unknown
  330 Memory (32-bit)      PASSED OK
  340 Swap (32-bit)        FAILED [blrraclnx1:]Swap<2RAM [blrraclnx2:]S..>
  350 Swap (64-bit)        PASSED OK
  360 Patch List           PASSED Complete
Result file: /home/oracle/rda/output/RDA_HCVE_P400RAC_lnx_res.htm

We can find details about prescribed values at

https://metalink.oracle.com/metalink/plsql/docs/HCVE_P400RAC_lnx.htm

e.g To fix SYS.IDGEN1$ Cache Size, we need to set cache size for sequence SYS.IDGEN1$ greater than or equal to 10,000.

SQL> alter sequence SYS.IDGEN1$ cache 10200;

Sequence altered.

Now we see that SYS.IDGEN1$ requirement is passed

132 SYS.IDGEN1$ Cache Si PASSED 10200

Refer Note:250262.1 - RDA 4 - Health Check / Validation Engine Guide for more information on HCVE.

Related Posts

2008-08-26 Tue

19:37 MySQL bug #31031 (1240 Bytes) » My SQL Dump
I came across this behavior today while writing some additional comprehensive ALTER TABLE tests for use in the test suite that we run against the Kickfire database appliance to ensure quality.

mysql> CREATE TABLE `t1` (
-> `col1` tinyint(4) DEFAULT NULL,
-> `id` int(11) NOT NULL DEFAULT '0' COMMENT 'min=1,max=65535',
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t1 drop column id,
-> add column id int auto_increment primary key;
ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

This is a regression in 5.0 (and 5.1, I didn't try 6) that was reported almost one year ago. This alter table works just fine in 4.1 and there isn't any reason it should complain about 'prefix index length' when there are no prefix indexes involved.

Ask MySQL to fix this regression in 5.0 before 5.1 GA.

http://bugs.mysql.com/bug.php?id=31031
08:04 Useful Cloud Computing Blogs » High Scalability - Building bigger, faster, more reliable websites.

  2008-08-25 Mon

16:01 OSCon 2008 Video Matrix » Pythian Group Blog
14:51 Why Drizzle Will Succeed » Pythian Group Blog
11:10 Checking Database Feature Usage Stats » AskDba.org Weblog
09:23 37signals Architecture » High Scalability - Building bigger, faster, more reliable websites.
07:59 An Unorthodox Approach to Database Design : The Coming of the Shard » High Scalability - Building bigger, faster, more reliable websites.

  2008-08-24 Sun

13:16 A Scalable, Commodity Data Center Network Architecture » High Scalability - Building bigger, faster, more reliable websites.

  2008-08-23 Sat

06:17 How to track down the source of Aborted_connects » MySQL Performance Blog

  2008-08-22 Fri

19:09 MySQL End Of Life (EOL) Policy » MySQL Performance Blog
18:37 Multiple column index vs multiple indexes » MySQL Performance Blog
14:28 Latency is Everywhere and it Costs You Sales - How to Crush it » High Scalability - Building bigger, faster, more reliable websites.
 123
 123