The Ji Village News

季庄新闻–Haidong Ji’s Blog

2011-06-26 update:

I am not sure if there are any changes in the latest make and gcc packages. Anyway, I noticed when run make, I encountered the message below:

make: g: Command not found
make: [qgen] Error 127 (ignored)

To fix this, find where gcc is at, then created a symbolic link g that points to gcc. All is well afterwards:
[root@ip-10-245-209-196 dbgen]# which gcc
/usr/bin/gcc
[root@ip-10-245-209-196 dbgen]# cd /usr/bin/
[root@ip-10-245-209-196 bin]# ln -s gcc g

End update
Recently I found myself doing some data loading benchmark testing with table partition. Data loading and storing for BI/DW/DSS stuff almost always involves data partitioning. SQL Server partition has a nice feature called partition switch, where you can swap data in and out of a partitioned table. In MySQL, it is called exchange, but it hasn’t been released. Perhaps other RDBMS have similar things as well.

To facilitate testing, I need some data that lends itself easily for partition, which led me to TPC-H’s dbgen tool.

dbgen is pretty easy to build and compile on Linux. It also has a Windows version. I didn’t bother trying because getting it built on Linux was such a quick and easy process. However, the documentation of dbgen, if it can be called as such, leaves a lot to be desired. But by experimenting and surfing around, I was able to get what I needed.

Instructions here are for CentOS. Building it on other flavors of Linux should also be easy.

1. yum install make and yum install gcc if you don’t have that already;

2. wget source code;

3. tar xf tarball;

4. cd dbgen

5. vi makefile.suite and change 4 lines. See uncommented lines below:

################
## CHANGE NAME OF ANSI COMPILER HERE
################
CC      = gcc
# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
#                                  SQLSERVER, SYBASE, ORACLE
# Current values for MACHINE are:  ATT, DOS, HP, IBM, ICL, MVS,
#                                  SGI, SUN, U2200, VMS, LINUX, WIN32
# Current values for WORKLOAD are:  TPCH
DATABASE= SQLSERVER
MACHINE = LINUX
WORKLOAD = TPCH

Note: It does not have MySQL and others for DATABASE setting, but that really doesn’t matter, because the file generated, which is delimited by a pipe, |, can be used for loading into any system.

6. make -f makefile.suite
This creats the dbgen binary that we can use in the next step.

7. ./dbgen -h gives you a brief description of switches that can be used. I am just interested in creating the lineitem table, because it has a good combination of integer, decimal, date, and character fields. The date inside the tables spans between 1992-01-01 and 1998-12-31, which is good for a partitioned table based on date. The DDL for this table is inside the dss.ddl file in the same directory. Please modify it as necessary. In fact, Lubor Kollar has it ready for SQL Server here, and Vadim Tkachenko has it for MySQL here.

Here is the command I used and its results:

[root@centos dbgen]# ./dbgen -v -T L -s 2
TPC-H Population Generator (Version 2.14.0)
Copyright Transaction Processing Performance Council 1994 - 2010
Generating data for lineitem table/
Preloading text ... 100%
Do you want to overwrite ./lineitem.tbl ? [Y/N]: Y
done.

-v: verbose, -T L: lineitem only, -s 2: scale factor of 2. My understanding is that it roughly indicates that the end file will be close to 2 gig.

It turns out the end file is about 1.5 gig, with close to 12 million rows in it.

-rw-r--r-- 1 root root  1.5G Mar 30 21:33 lineitem.tbl
[root@centos dbgen]# wc -l lineitem.tbl
11997996 lineitem.tbl

You can play around with different key switches to get the size of file you want. For example, quoting from Lubor Kollar:

dbgen –T L -s 4 -C 3 -S 1

Using the –s option, we set the scale to 4 that generates a Lineitem table of 3GB. Using the –C option we split the table into 3 portions, and then using the –S option we chose only the first 1GB portion of the Lineitem table.

Running the command above, I got a file named lineitem.tbl.1 with close to 8 million rows, about 976 meg.

When trying to load it into SQL Server with SSIS bulk copying task, remember the delimiter is | and line separator is LF (line feed). For MySQL, something like this should do:

load data local infile '/root/dbgen/lineitem.tbl.1' into table lineitem fields terminated by '|' lines terminated by '\n';

工作上的原因:认识人，开会，制定计划等，我一月初在盐湖城呆了几天。在回来的飞机上手写了点儿记录，一直没时间输入电脑。现在儿子在冰球训练，看我能不能在这段时间里把它输进去。更新：这篇文章就两气儿输完的，第一次是2月14日晚儿子练冰球时，第二次是今天，3月24日晚。

——

星期天早来了几个小时。因为这是我第一次来盐湖城，所以下午到市中心有名的“庙场”（Temple Square）去蹓了一圈儿。

庙场是摩门教的大本营所在。我坐上盐湖城数年前为办冬奥建的有轨电车，在体育馆站上车，坐了15分钟左右就到了。这个体育馆应当属于犹他大学吧，是冬奥时代主会场。那届冬奥据说还盈利了，不知这个盈利的说法有没有考虑到美国组委会用来贿赂和收买的花销。

在庙场站下车，穿过街道，稍走几步，就到了庙场西门。西门内有工作人员在一个小房间里散发游览图、说明书之类的东西。这一大块地盘都属摩门教会，所有工作人员应当都是信徒。我拿了张地图，看了看，就先漫无目的地往里走。

庙场本身不大，大概不到3000平方米吧。几步路后，我发现自己在南游客中心了。这时两个女的过来搭讪，胸前别着方形的徽章，像个大校徽。这徽章上有这个“姐妹”的姓，和标志该人国籍的国徽。这两个mm，一个来自亚利桑那，另一个来自墨西哥。她们问我从哪里来，需要什么帮助。我应酬并表示感谢，说只想来转转。我看到那个来自亚利桑那的女士有点两眼发直，眼光呆滞。她们放过我，我们前后脚地步入了南游客中心。

只见厅内的墙上贴有摩门教的所谓重视家庭的全家和睦的宣传画，和耶稣同志及其他摩门教先知和先行者的语录和最高指示，并放录像。另外，还有这个庙的模型，因为庙虽然就在旁边，但对不信者和级别低的信徒是不开放的。所以，对于大部分人来说，想了解庙的内部结构，只能看模型。

这时一个带着几个孩子的女人看到我一个人逛，就问我有没有兴趣要个导游领着和讲解，并且这种服务是免费的。我说那好啊。她把我领到前台，那儿有两位姑娘，分别带着加拿大和巴基斯坦的国徽。她们问我从哪里来，要中文还是英文解说。我说中英均可，中文更好。“加拿大”拿起电话，叫来了另外两名姑娘，一个自称来自加州，另一个是台湾同胞，来自台中。我问她会不会说闽南话，她说会听，但说得不太好。如果我没记错的话，她的爷爷和外公辈分别来自河南和广东吧。

台胞姓方或范，一开始用英文给我讲解，我用中文回应。一来二去，她知道我主要想讲中文，这样我们就开始了主要是中文的交流，加州妹跟着我们走。

台胞问我的信仰。我知道这里的工作人员都是做思想工作的老手，遂开门见山地说我曾经信过一阵子基督教，读过新旧约，略知一二，现在是坚定的无神论者。但我的原则是不以人的有无信仰或何种信仰来判断一个人，我希望我做得到。暗示的意思就是说你就死了拉我入伙的心吧。

她试了好几次，要我给她们填联系卡片，被我拒绝。她和加州妹领着我参观了三所建筑，其中包含那个合唱厅，也怪有意思的。最后我们来到了北游客中心，按照旋转楼梯来到了一个耶稣的雕像前。节目的最后是放录音来给游客以召唤，还可以选用外语的召唤。我点了中文的，恕我直言，听得有点荒唐滑稽。

我了解到在摩门教的庙里是没有耶稣钉在十字架上的雕塑的。并且在任何时期，他们都会有12个使徒即apostle。现任的一个使徒好像还是德国老头。我没问一夫多妻的问题，我想她们对此早有应对，但问一问可能也不错。摩门大使徒兼先驱Bringham Young有55个老婆，他住的地方叫狮子屋，Lion House；他的另外一个住所叫蜂窝，Beehive House，挺逗的。狮屋和蜂窝我都没去。

说起这个Bringham名字，我是最近两三年才搞明白这个词的发音：h不发音！我以前不知道，读到类似的词儿，都发出“喝喝”的声音，像什么Graham，Durham等等。哈哈，这下臭大了，各位见笑。我猜下，这个以ham和gham结尾的词儿是不是来自据说和山/陕西人同样爱吃羊肉的苏格兰？

加州妹和台胞对我稍有失望，我们道别后我一个人下来。我以前读到并且从她们的口中确认到摩门教徒可能要用18个月来做传教。这些姑娘的导游工作应当可以算进这18个月里。想来这应当是很吃香的差使，大家应当会挤破头地来争取吧。

一个有意思的现象是在庙场工作的人员几乎是清一色的大闺女，大都留长发，不大有刘海，额头上的头发都向后梳起来，有点蓬松和古典，并且穿长裙的居多。给游客做导游的，全部成双结对，两人一个队伍，互相监督和支持，很讲礼貌。

来到楼下后，又看到两位年轻的mm工作人员，分别带着西班牙和美国国徽。那个可爱的美国同胞听说我来自中国，半开玩笑半皱眉地耸耸肩说“Really，from the PRC?”，酸吧啦叽地，好像有点怜悯、憎恶、无奈、好奇等说不出名堂的滋味来。我笑笑说，对呀，我是来自PRC的一条好汉，但我现在和你一样，也是美国人。她笑了。接下来宾主在亲近友好的气氛中会谈，讨论了附近的饭馆儿情况，她们给了我一些建议后我们告别。

越过一条马路，就到了摩门教会议中心。这个建筑很大，可以同时容纳21000人开会，并且三层的大会厅里没有一根柱子，每个座位都可以直视中央舞台。摩门教看来对国际扩张很有兴趣，开大会时有各国语言的同声翻译。

星期一晚上和同事去了个叫“Ruth’s diner”的一个小饭馆，所谓“hole in the wall”的规模。我点了个Pork Tenderloin，猪肉里脊，很好吃。星期二和同事一起去了叫Bombay House的印度餐馆，我点了“加辣”（extra hot）的Aloo gobi，一道黄黄的菜花和地蛋做成的菜，和一个烧饼（Naan bread），都不孬吃。我要了一瓶泰姬陵牌印度啤酒，味道一般。那天比较忙，没空解大手，弄得有点腹胀，也就没喝完。星期三吃了个墨西哥的Burrito，就是个单饼卷肉之类的东西，这个口味不咋地。

基本上临睡前天天读书，靠在床头上，读小说和历史，中英文交替。老婆要睡，我就关灯，有时给她讲一下我读中国史的感想和体会。实际上她不一定听得懂也不一定感兴趣，但她总是礼貌地听下去。嗯，我要读一些瑞典史。

去年读完了林海音的《城南旧事》，我很喜欢，很为里面的真实、纯真、善良、悲伤和爱所感动。后来在网上搜林海音，读到了她在台湾办文学社，真诚地关心、鼓励和提携同辈和后辈的创作者，就很佩服。好吧，你可以说我唱高调搞说教，站着说话不腰疼，但我们少一些浮躁和虚伪，少一些势利和自私，少一些谣言和冷嘲热讽，少一些自卑和钱权的讨论，多一些踏实和真诚，多一些礼貌和微笑，给自己、别人和陌生人留下体谅、时间和空间，可以吗？

然后读了三联书店的《七十年代》，很不错。经推荐，读了吴法宪的《岁月艰难──吴法宪回忆录》，也很喜欢。刚读完John Leighton Stuart/司徒雷登的Fifty Years in China，欣赏他的人格和善良，但不同意他的观点和结论，也更增加了我对马歇尔和史迪威的尊重。靠，都晚上11点多了，打住，该上床歇觉了，以后再写吧。各位晚安。

The sample my-small.cnf, my-medium.cnf, my-large.cnf, my-huge.cnf, and my-innodb-heavy-4G.cnf files from MariaDB and Percona (I didn’t bother checking Oracle’s distro) were from the dinosaur age, with comment like this for my-huge.cnf:

This is for a large system with memory of 1G-2G where the system runs mainly MySQL.

So I suggested some updating is needed on IRC and mailing lists. After getting a kick in the pants from the always affable Kurt von Finck (I thought “kick in the pants” is a pretty good rendition of “遣将不如激将”, for those who followed our exchanges in mailing lists), I decided to take this upon myself.

It’s actually not hard to update this. I believe just 6 files in total, at most, need to be touched under the support-files directory inside the trunk: my-small.cnf.sh, my-medium.cnf.sh, my-large.cnf.sh, my-huge.cnf.sh, my-innodb-heavy-4G.cnf.sh, and Makefile. Please let me know if my understanding is wrong or incomplete.

Here are a few ideas with the new version of sample my.cnf files, just to get started:

1. Remove the server-id = 1, add a comment like this: use the last octet of the server’s IP address might be a good idea;
2. Add
user = mysql
3. Remove any mention of replication. It is better to set up replication via command line, and let that information be saved into master.info
4. I am on the fence on this one: should I set the default character to utf8?
5. Simplicity is good. One implication of that is to make the sample my.cnf short, I think. Don’t put things there if they are not necessary or important;
6. Watch out for those per session settings, unless you enjoy seeing random crashes;
7. set wait_timeout to be 600?
8. Default is not necessarily bad;

I then started researching various settings for MyISAM and InnoDB, such as key_buffer_size, innodb_buffer_pool_size, table_cache, query_cache_size, etcetera. I came across a few interesting articles, such as Peter Zaitsev’s “What to tune in MySQL Server after installation”, dataStrangler’s “Custom MySQL config files to ensure maximum performance”, and Baron Schwartz’s tongue-in-cheek “The ultimate tool for generating optimal my.cnf files for MySQL”. It looks like it is pretty difficult to find something that is generic enough.

What do you think? What best practices should be converted into entries inside sample my.cnf? Or is this even necessary, after removing the long-in-the-tooth comment?

Vadim Tkachenko published interesting benchmark results with PCI-E based SSDs here. I recently got a chance to benchmark FusionIO’s 320 GB PCI-E drive. It was really impressive. My results, done on Windows with sqlio, are consistent (not identical, of course, but in the same ballpark) with what Vadim reported in that blog post, done with sysbench on Linux.

sqlio is a popular IO throughput testing tool from Microsoft. I didn’t get to test the throughput when the SSD is close to full. The key takeaways that I learned from my testing are:

1. I can confirm that there is no difference between random and sequential IO, contrary to the traditional spindle based hard disks;

2. Read is significantly faster than write. Reads and writes with 64 threads can achieve around 1.4 GB/S and 400 MB/S throughput, respectively.

It is good to hear that another vendor, Virident, also offers similar PCI-E based SSDs around similar price points. I think as the technology behind SSDs matures and price of it decreases, its impact on the database field could be significant.

Most, if not all, database platforms have put a lot of emphasis on achieving sequential reads and minimizing fragmentation. This is especially true for business intelligence databases. However, as SSDs gradually take over high end storage market for mission critical databases, with no difference between random and sequential access on those disks, it could relegate our obsession on sequential reads and fragmentation to the back burner, which is significant, in my opinion.

I am not suggesting that SSDs effectively make the fragmentation issue disappear, mind you. Because many database software, be it MySQL (perhaps the InnoDB storage engine in particular), SQL Server, or Oracle, uses prefetch, read-ahead read, or whatever the lingo maybe, to anticipate the need and bring additional data into cache in advance in case they are used next. This can be especially effective for business intelligence applications. In the case of prefectching, fragmentation is still undesirable, but the speed of SSDs could make it a less pressing issue than it has been, which is significant, I think.

A few years back, when multi-core servers started emerging, database software vendors invested time and energy to take advantage of that, with new features added and white paper created for NUMA architecture. I think it will be interesting to watch how emerging SSDs in the server market will be exploited. Percona’s white paper, Scaling MySQL Deployments With Percona Server and Virident tachIOn Drives, that talks “scaling up” with SSDs, instead of “scaling out” with sharding, is a pretty interesting idea.

Note: It is good to know when the external process runs and for how long, which is included in the code snippet below. For the “when” part, it would have been nice to know the time zone as well. But due to the limitation mentioned here, it is not as easy as it sounds with the datetime module.

It seems the time module in Python has better handling of time zone, but it ends at year 2038, which is a slight concern of mine, so I decided against it. The datetime module’s upper limit is year 9999. That is pretty far into the future, if we humans haven’t managed to extinct ourselves by then.

Speaking of the Linux/Unix epoch time, I wonder if we would encounter year 2038 issue, like the much talked about Y2K back in the day

import shlex, subprocess, sys
from datetime import datetime
def runCmd(cmd):
    proc = subprocess.Popen(shlex.split(cmd), stdout=subprocess.PIPE, stderr=subprocess.PIPE, shell=False)
    out, err = proc.communicate()
    ret = proc.returncode
    return (ret, out, err)
startTime = datetime.now()
# "df -h" is a *nix command. To test on Windows, replace it with "notepad" or something like that.
returnCode, stdOut, stdErr = runCmd('df -h')
endTime = datetime.now()
print "The command started at " + str(startTime) + " and ran for " + str((endTime - startTime)) + "\n"
print stdOut
if returnCode:
	print >> sys.stderr, "There was an error (%d): \n" % returnCode
	print >> sys.stderr, stdErr

Update:

When I first posted this, I wrote that to test it on Windows, you can try the “dir” command. Actually, if you do, you will encounter the error below:

C:\Users\haidong\Documents\work\python>tt.py
Traceback (most recent call last):
  File "C:\Users\haidong\Documents\work\python\tt.py", line 14, in <module>
    returnCode, stdOut, stdErr = runCmd('df -h')
  File "C:\Users\haidong\Documents\work\python\tt.py", line 5, in runCmd
    proc = subprocess.Popen(shlex.split(cmd), stdout=subprocess.PIPE, stderr=subprocess.PIPE, shell=False)
  File "C:\Python27\lib\subprocess.py", line 672, in __init__
    errread, errwrite)
  File "C:\Python27\lib\subprocess.py", line 882, in _execute_child
    startupinfo)
WindowsError: [Error 2] The system cannot find the file specified

The reason is that dir is a DOS command that needs to be run on top of the command line process. If you replace “dir” with some other independent executibles, it should work. I’ve tried both “notepad” and “sqlcmd”, and both worked for me fine. “sqlcmd” is really the one I am after.