As you may already know, Google recently released Google Gears, an open source browser extension that lets developers create web applications that can run offline using JavaScript APIs.

Google Gears provides three key features:

• A local server, to cache and serve application resources (HTML, JavaScript, images, etc.) without needing to contact a server.
• A database, to store and access data from within the browser.
• A worker thread pool, to make web applications more responsive by performing expensive operations in the background.

Apart from the coolness and usefulness of Gears (I can now read my Google Reader feeds offline), what also caught my attention was its use of a database to store and access data. After all, that’s what databases are good at, data storage and management. I was curious to know what type of a database Gears uses. Well, according to its database module API, Google Gears uses the open source SQLite database system.

SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine.

SQLite is really interesting. There is no set up procedure to initialize it before using it. Databases need minimal or no administration. There is no need to maintain a separate server process dedicated to SQLite. It supports ACID and is thread-safe.

SQLite supports a large subset of SQL-92 data definition and manipulation features. You can create tables, indexes, triggers, and views. You can manipulate stored information using INSERT, DELETE, UPDATE, and SELECT SQL constructs. However, some SQL-92 features are not yet supported.

SQLite stores an entire database in a single, ordinary native file that can reside anywhere in a directory of the native file system. Any user who has a permission to read the file can read anything from the database.

What’s also interesting about SQLite is its size. The whole code footprint ranges from 224KB up to 513KB depending on what compiler optimizations are used. I downloaded the pre-compiled SQLite binaries for windows, it is just one 359KB file, sqlite3.exe.

sqlite3 is somewhat similar to Oracle’s SQL*Plus. You can use it to enter SQL commands. For example, from the DOS command prompt, the following creates a database file named eddie.db:

C:\Users\Eddie\Documents\sqlite>sqlite3 eddie.db
SQLite version 3.3.17
Enter “.help” for instructions
sqlite>

Now, let’s do some DML and DDL:

sqlite> create table tbl1(one varchar(10), two smallint);
sqlite> insert into tbl1 values(’hello!’,10);
sqlite> insert into tbl1 values(’goodbye’, 20);
sqlite> select * from tbl1;
hello!|10
goodbye|20
sqlite> CREATE TABLE tbl2 (
   …>    f1 varchar(30) primary key,
   …>    f2 text,
   …>    f3 real
   …>    );
sqlite>

Does SQLite have a data dictionary? Yes it does, and it’s only one table called sqlite_master:

sqlite> select * from sqlite_master;
table|tbl1|tbl1|2|CREATE TABLE tbl1(one varchar(10), two smallint)
table|tbl2|tbl2|3|CREATE TABLE tbl2 (
   f1 varchar(30) primary key,
   f2 text,
   f3 real
   )
index|sqlite_autoindex_tbl2_1|tbl2|4|
sqlite>

Interesting. Going back to Gears, if you are wondering where the location of the database file is, it basically depends on the browser and the platform you are using.

If you are wondering what can SQLite be used for, of course, you have Google Gears as a perfect example. But, there are other appropriate uses as well.

By now, you may be thinking about Oracle Database Express Edition as also being an entry-level, small-footprint database, but hey, SQLite does not even come close to Oracle XE. Each one serves and is suitable for different types of applications. Moreover, do you really think that a 359KB RDBMS can put a dent into a 165MB RDBMS?

Share This

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

• Your SQL Tool Part II
• Did You Know That About PL/SQL Variables?
• 3 Useful SQL*Plus Tips
• Ubuntu on Microsoft Virtual PC Works Like a Charm
• How to Give Your System a Break from Oracle XE
• Give Me One Minute And I'll Tell You If You Are Liberal Or Conservative

I prefer to use Integers for joins whenever possible and today I worked with client which used character keys, in my opinion without a big need. I told them this is suboptimal but was challenged with rightful question about the difference. I did not know so I decided to benchmark.

The results below are for MySQL 5.1.18 using MyISAM and Innodb tables. This time unlike other benchmarks I decided to do Join not on primary key and have query to read data for both tables. If the query would be index covering I would expect us to see different ratio. The query I use here is constructed to stress out join code while avoid sending data to the client Do not try to find any good meaning for query or schema. For joins which fetch just few rows difference is likely to be less as the join code itself is likely to be responsible for less portion of response time.

OK. Lets start with first simple MyISAM table and join query performed on INT fields:

So what about Innodb ? Innodb executed the same query in 2.9 seconds which was a bit disappointing for me as I expected MyISAM to be slower due to amount of extra system calls it has to read row data from OS Cache.

So what if we convert i and j columns to varchar while sticking to utf8 character set which we had as default ? Joining on Char columns completes in 4.5 seconds on Innodb which is about 50% slower compared to Joining on Int, for MyISAM however the time became 11.0 seconds which is over 6 times slower than joining on the integer.

In fact this was expected as MyISAM uses key compression for varchar columns so random key lookups become significantly slower. I tried to set pack_keys=0 which typically helps in similar cases but it looks like there is regression bug and this setting does not work any more.

The next test I decided to do is to convert Innodb table to latin1 character set. I was expected this to shorten some internal buffers MySQL has to allocate for key comparison as well as have comparison function significantly faster. The reason for this test was - latin1 encoding is enough for most character based keys - uuid, sha1/md5 based etc.

Latin1 encoding indeed gave significant improvement to 3.5 seconds which is just 20% slower than integer based join for Innodb.

Finally I decided to check if using longer strings slows down things significantly and so I replaced all numbers with their sha1() hashes which still made eq join to run the same ways but gave me much longer keys. The performance dropped down to 6.1 seconds which makes it over 2 times slower compared to integer based join.

So how do I read these results ?

1. CHAR keys are indeed slower for joins compared to integer keys
2. Performance degradation can range from few percent to couple of times for Innodb tables
3. MyISAM Tables may suffer significantly if key compression is not disabled
4. Joining on Shorter CHAR keys is significantly faster than Long keys
5. Latin1 (I guess any simple encoding) is significantly faster for joins compared to UTF8

These tests were preformed for in memory tables, for IO bound workload results are likely to be different as longer indexes expected to have much worse cache fit, especially if you keep them unpacked.

想照出好照片么？避免下面这些错误吧：

1. 画面太拥挤。有时候，画面能包含的内容越少越好。
2. 天气不好我就照不好。
3. 不能耐心等到最美的景色出现的时候。
4. 在小屏幕上觉得不好就匆匆删除。
5. 反正我回头可以用Photoshop修改。
6. 不小心包含不该包含的景物。
7. 总是从站立的视角拍摄。
8. 总是要在景色前配个人。
9. 绝对不肯照别人。
10. 错误的透视效果。

十个常见的摄影坏习惯－来自Andre Gunther Photography

如何减轻和释放工作压力？来试试下面的方法：

1. 仰天长啸。最好组队同啸。
2. 抖落恐慌。发言前活动活动关节，让自己放松。
3. 明白自己能改变什么，不能改变什么。
4. 分而治之。
5. 不要凡事操心，学会交给别人去做。
6. 玩玩压力球。
7. 准备一个高尔夫球或者空瓶子，踩在脚下做足底按摩。
8. 保持冷静。
9. 如果可以，重新粉刷你的办公室。柔和的蓝色可以帮助你降低血压。
10. 尽量不要分散注意力。
11. 把工作留在工作的时候做。
12. 现实一点。决大多数压力都是自己给自己的。
13. 微笑是个好办法，大笑就更棒了。
14. 和别人交流一下，大家都是人。
15. 休息一下，出去走走。
16. 任何时候都有条不紊。
17. 吃鲑鱼或者沙丁鱼可以减轻压力。
18. 该休假就休假，不要怕。
19. 向别人求助。

“19个战地窍门来解决工作时的压力”－来自Rrian Project

orion，一款由Oracle公司提供，专门用于模拟数据库运行机制来测试存储的优秀IO存储测试软件，可以在不运行oracle数据库的情况下，仿真OLTP随机操作（测试IOPS）或者是OLAP连续性操作（测试吞吐量）。

先看看此软件的下载地址与说明：

    ORION is the Oracle I/O Numbers Calibration Tool designed to
    simulate Oracle I/O workloads
    - Without having to create and run an Oracle database
    - Using the Oracle database's I/O libraries
    - Using small I/Os to simulate OLTP workloads
    - Using large I/Os to simulate data warehouses

    ORION is useful for understanding the performance capabilities of a storage system,
    either to uncover performance issues or to size a new database installation.
    The Users Guide contains a Getting Started section, detailed usage documentation,
    and trouble-shooting tips. Please note that ORION is not supported by Oracle. 

    Download the files:
     ·orion_AIX64.gz (11,670,726 bytes)
     ·orion_solaris64_sparc.gz (898,929 bytes)
     ·orion_solaris_x8664.gz (655,975 bytes)
     ·orion_linux_em64t.gz (767,380 bytes)
     ·orion10.2_linux.gz (630,354 bytes)
     ·orion10.2_windows.msi (7,865,856 bytes)
     ·Users Guide

可以见到，此软件已经支持多个OS环境，遗憾的是，Oracle并不对该软件提供服务支持，不过，这并不影响该软件的正常使用，从我的测试结果来看，该软件真的是很不错的存储测试软件。

下载到的软件，已经分别编译好，不需要任何编译即可以在各自的OS环境中运行，这个比很多压力测试软件需要另外重新编译好多了，也方便多了，如，在AIX环境下，在解压的目录下，运行./orion -help，即可以看到该软件的详细帮助。

该软件支持三种运行方式

Simple：简单的测试随机的小IO（默认8k）以及大IO（默认1024K），这个方式对初次运行该软件，或者大致了解存储基本特性比较有用。

Normal：可以组合不同的IO类型，但是还是不能自定义IO大小

Advanced：可以支持多种高级选项，如IO大小，压力大小，IO类型，测试方式等等

以及两种不同的压力方式

典型的OLTP环境：选择随机的小IO，测试存储所能支持的最大IOPS以及响应时间

典型的OLAP环境：选择顺序的大IO，测试存储所能支持的最大吞吐量以及响应时间

该软件的运行只需要一个配置文件，<testname>.lun，配置了测试所需要用到的磁盘信息，而分别返回如下信息：

<testname>_iops.csv：不同压力类型的IOPS值

<testname>_mbps.csv：不同压力类型的吞吐量

<testname>_lat.csv：不同压力类型下的响应时间

<testname>_summary.txt：测试结果的汇总信息

我在分别运行load runner+oracle模拟数据库活动以及仅仅是运行该软件模拟数据库的活动中，可以明显的发现该软件的优势所在：

1、不需要运行load runner以及配置大量的clinet

2、不需要运行oracle数据库，以及准备大量的测试数据

3、测试结果更具有代表性，如随机IO测试中，该软件可以让存储的命中率接近为0，而更仿真出了磁盘的真实的IOPS，而load runner很难做到这些，最终的磁盘IOPS需要换算得到。

4、可以根据需要定制一定比例的写操作（默认没有写操作），但是需要注意，如果磁盘上有数据，需要小心数据被覆盖掉。

当然，也有其缺点

1、到现在为止，无法指定自定义的总体的运行时间以及加压的幅度，这里完全是自动的

2、无法进行一些自定义的操作类型，如表的扫描操作，装载测试等等，不过可以与oracle数据库结合起来达到这个效果

下面，我就给出几个具体的例子说明其操作

1、数据库OLTP类型，假定IO类型全部是8K随机操作，压力类型，自动加压，从小到大，一直到存储压力极限

#nohup ./orion -run advanced -testname mytest -num_disks 96 -size_small 8 -size_large 8 -type rand &

2、数据库吞吐量测试，假定IO全部是1M的序列性IO

#nohup ./orion -run advanced -testname mytest -num_disks 96 -size_small 1024 -size_large 1024 -type seq &

3、指定特定的IO类型与IO压力，如指定小IO为压力500的时候，不同大IO下的压力情况

#nohup ./orion -run advanced -testname mytest -num_disks 96 -size_small 8 -size_large 128 -matrix col -num_small 500 -type rand &

4、结合不同的IO类型，测试压力矩阵

#nohup ./orion -run advanced -testname mytest -num_disks 96 -size_small 8 -size_large 128 -matrix detailed -type rand &

因为其测试结果是csv文件，所以可以很方便的根据结果在excle中绘制压力曲线，如某存储的压力测试，根据Orion的测试结果绘得的IOPS与响应时间关系表：

其中，横轴是响应时间，纵轴是IOPS值，表示了在不同的IOPS情况下，单个IO的平均响应时间分别是多少。

经过了多次的论证，房子的基本设计已经成型，在上周末交了工程款的60%后，今天，新房子的装修正式开始了。
这个是从进门的地方往里面拍的，幽静的小路...

GMail免费注册，Google account 已经不用邀请/推荐了。

俺们新公司的职位.

要求:

1、三年以上ORACLE DBA/技术支持相关工作经验，可以独立承担安装部署、备份恢复、性能优化和故障分析工作，熟悉UNIX/RAC/PARTITION/DATA GUARD优先

2、对OCP知识有深刻理解，有OCP证书优先

3、主要服务通讯行业客户，出差较少，熟悉通讯行业优先

4、有良好的人际关系和沟通能力，有项目管理或大型项目经验优先

5、请在简历注明待遇要求，如果附件发送简历请在附件名称注明个人姓名

工作地点:杭州,基本不出差.
待遇:

1:100k-150k/年

2:每月提供交通和通信补贴

3:外地来杭者,提供黄龙商住区附近三室二厅中的房间一间

有意者,请简历给我 guoyue@gmail.com

那位大哥有熟人推荐给我也行,有丰厚推荐费哟.

 

occasionally I notice that sunOS 5.10 can support 256 M large page for oracle SGA.SO large. QADB007$> uname -aSunOS QADB007 5.10 Generic_118833-33 sun4v sparc SUNW,Sun-Fire-T200QADB007$> pmap -sx 25886 | grep -i ism0000000380000000   25690112   25690112          -   25690112 256M rwxsR    [ ism shmid=0x1 ]00000009A0000000     200704     200704          -  [...]

Oracle 10g提供的asm new features集成了lvm和文件系统的一些特性，给db在空间、性能管理上都带来了极大的方便。唯一一点感觉不是很方便是不像file system一样可直观，也不像raw device可以使用系统工具来进行管理维护，对dba而言diskgroup就像一个“黑匣子”，你所能摸到的仅有几条维护命令和几个视图。对asm file的操纵基本都是通过rman来解释执行。在某些情况下就不是那么方便，因为rman对datafile的操纵需要控制文件存在。幸运的是，除了rman我们还能使用dbms_file_transfer来操作数据文件。