Benchmark MySQL database

Installing Windows Server 2008 R2 Enterprise Edition
We used Microsoft Windows Server 2008 R2 Enterprise Edition for the operating system on the server.
To install Windows Server 2008 R2, complete the following steps:

  1. Boot the server, and insert the Windows Server 2008 R2 installation DVD in the DVD-ROM drive.
  2. At the Language Selection screen, click Next.
  3. Click Install Now.
  4. Select Windows Server 2008 Enterprise (Full Installation), and click Next.
  5. Click the I accept the license terms check box, and click Next.
  6. Click Custom.
  7. Click Drive options (advanced).
  8. Delete any existing partitions.
  9. Ensure the first drive is selected, and click New.
  10. Click Apply.
  11. Click OK.
  12. Click Next.
  13. At the User’s password must be changed before logging on warning screen, click OK.
  14. Type your new password into both fields, and click the arrow to continue.
  15. At the Your password has been changed screen, click OK.
    Windows Server 2008 R2 settings
    We installed all recommended and critical Windows updates through 2/03/2011. In addition, we
    downloaded and installed the latest drivers.

Installing and setting up MySQL Server 5.1.54
We used MySQL Server 5.1.54 to create our test database. To install and set up MySQL Server 5.1.54, complete the following steps:

  1. Download mysql-essential-5.1.54-winx64.msi from http://dev.mysql.com/downloads/.
  2. Double-click the mysql-essential-5.1.54-winx64.msi file to launch the MySQL Server 5.1 Setup Wizard.
    Click Next.
  3. Select Custom, and click Next.
  4. Highlight MySQL Server Datafiles, and click Change…
  5. Set the MySQL Server Datafiles directory as the RAID volume F:\, and click OK
  6. Ensure that the Destination Folder is C:\Program Files\MySQL\MySQL Server 5.1 and the Date Folder is
    F:\
  7. and click Install.
  8. Click Next.
  9. Click Next.
  10. At the Wizard Completed screen, click Finish. The MySQL Server Instance Configuration Wizard automatically launches.

Configuring MySQL Server 5.1.54
After you have installed and set up MySQL Server 5.1.54, configure it by completing the following
steps:

  1. At the Welcome to MySQL Server Instance Configuration Wizard 1.0.16.0 screen, click Next.
  2. At the MySQL Server Instance Configuration screen, select Reconfigure Instance, and click Next.
  3. Select Detailed Configuration, and click Next.
  4. Select Dedicated MySQL Server Machine, and click Next.
  5. Select Transactional Database Only, and click Next.
  6. To change the InnoDB Tablespace Settings RAID volume, click Modify, and in at the Are you sure you
    want to change the InnoDB datafile path? pop-up window, click Yes.
  7. Click the … button, select the RAID volume target, and click Open.
  8. Note that the drive drop-down menu under InnoDB Tablespace Settings changed to F:\, and click Next.
  9. Select Online Transaction Processing (OLTP), and click Next.
  10. Select Enable TCP/IP Networking, type 3306 for the Port Number, select Enable Strict Mode, and click
    Next.
  11. Leave the default Standard Character Set, and click Next.
  12. Select Install As Windows Service and Include Bin Directory in Windows PATH, and click Next.
  13. Select Modify Security Setting, type password1 in the New root password and Confirm boxes, and
    check Enable root access from remote machines. Click Next.
  14. To complete the configuration, click Execute.
  15. When the wizard finishes, click Finish.
  16. Open MySQL Command Line Client.
  17. At the Enter password prompt, type password1 and press Enter.
  18. Type show databases and press Enter.
  19. Type create database sbtest and press Enter.
  20. Type show databases and press Enter.
  21. Type use sbtest to switch MySQL to use the SysBench (sbtest) database, and press Enter.
  22. Type \q to close the MySQL Command Line Client.

Building the SysBench test database
We used SysBench to create a 400,000,000-row (90GB) MySQL database and executed a batch of
online transaction processing (OLTP) transactions against that data. Each run lasted one hour, performing 74
percent reads and 26 percent writes to simulate a standard database workload. To build the SysBench
database, complete the following steps:

  1. Run the SysBench “prepare” phase to build a 90GB database using the following parameters:
    sysbench –debug=off –mysql-host=localhost –mysql-user=root -mysql-password=Password1 –test=oltp –oltp-table-size=400000000 prepare
  2. Make a backup copy of the database on an external storage volume before running SysBench.

Increasing database performance using the Dell PERC H700 with CacheCade technology

Running SysBench

  1. Ensure you have a fresh copy of the database copied over to the target volume.
  2. To run the benchmark, open a command prompt, and type the following command:
    sysbench –debug=off –mysql-host=localhost –mysql-user=root -mysql-password=Password1 –test=oltp –oltp-table-size=400000000 -num-threads=10 –max-time=3600 –max-requests=10000000 run
  3. After the benchmark completes, refer to the command prompt window to review the results.
  4. Repeat steps 2 and 3 to conduct three more runs without restoring the database or rebooting the
    server. The first run allows for data caching as would occur in a real-world environment. The following
    runs provide the stable performance results of the testing.
  5. Report the median score.
  6. Repeat steps 1 through 5 for each drive configuration.

Clear test data
sysbench –debug=off –mysql-host=localhost –mysql-user=root -mysql-password=Password1 –test=oltp –oltp-table-size=400000000 -num-threads=10 –max-time=3600 –max-requests=10000000 cleanup

Read more

世界越快心越慢

在晚飯後的休息時間,我特別享受在客廳瀏灠youtube上各樣各式創作者的影音作品。很大不同於傳統媒體,節目多是針對大多數族群喜好挑選的,在youtube上我會依心情看無腦的動畫、一些旅拍記錄、新聞時事談論。 尤其在看了大量的Youtube的分享後,我真的感受到會限制我的是我的無知,特別是那些我想都沒想過的實際應用,在學習後大大幫助到我的生活和工作層面。 休息在家時,我喜歡想一些沒做過的菜,動手去設計生活和工作上的解決方案,自己是真的很難閒著沒事做。 如創作文章,陪養新的習慣都能感覺到成長的喜悅,是不同於吃喝玩樂的快樂的。 創作不去限制固定的形式,文字是創作、影像聲音也是創作,記錄生活也是創作,我想留下的就是創造—》實現—》回憶,這樣子的循環過程,在留下的足跡面看到自己一路上的成長、失敗、絕望、重新再來。 雖然大部份的時候去做這些創作也不明白有什麼特別的意義,但不去做也不會留下什麼,所以呀不如反事都去試試看,也許能有不一樣的水花也許有意想不到的結果,投資自己永遠不會是失敗的決定,不是嗎?先問問自己再開始計畫下一步,未來沒人說得準。 像最近看youtube仍大一群人在為DOS開

By Phillips Hsieh

知識管理的三個步驟:一小時學會把知識運用到生活上

摘錄瓦基「閱讀前哨站」文章作為自己學習知識管理的內容 Part1「篩選資訊」 如何從海量資訊中篩選出啟發性、實用性和相關性的精華,讓你在學習過程中不再迷失方向。 1. 實用性 2. 啟發性 Part2「提高理解」 如何通過譬喻法和應用法,將抽象的知識與日常生活和工作緊密結合,建立更深刻的理解。 1. 應用法 2. 譬喻法 Part3「運用知識」 如何連結既有知識,跟自己感興趣的領域和專案產生關聯,讓你在運用知識的路途上游刃有餘。 1. 跟日常工作專案、人際活動產生連結 # 為什麼要寫日記? * 寫日記是為了忘記,忘卻瑣碎事情,保持專注力 * 寫日記就像在翻譯這個世界,訓練自己的解讀能力 * 不只是透過日記來記錄生活,而是透過日記來發展生活 #如何寫日記? * 不要寫流水帳式的日記,而是寫覆盤式的日記 當我們試著記錄活動和感受之間的關聯,有助於辦認出真正快樂的事 日記的記錄方式要以過程為主,而非結果 * 感恩日記的科學建議,每日感恩的案例

By Phillips Hsieh
2024年 3月30日 14屆美利達環彰化百K

2024年 3月30日 14屆美利達環彰化百K

這是場半小時就被秒報名額滿的經典賽事, 能順利出賽實屬隊友的功勞, 這次的準備工作想試試新買的外胎, 因為是無內胎用的外胎, 特別緊超級難安裝的, 問了其他朋友才知道, 要沾上肥皂水才容易滑入車框。 一早四點起床準備, 五點集合備好咖啡在車上飲用, 約了六點在彰化田尾鄉南鎮國小, 整好裝四人一起出發前往會場。 被排在最後一批出發, 這次的路線會繞行的員林148上139縣道, 其實在早上五點多天就開始有點飄雨, 大伙就開始擔心不會要雨戰吧! 果不其然才出發準備上148爬坡雨勢越來越大, 戴著防風眼鏡的我在身體的熱氣加上雨水冷凝效果下, 鏡面上滿是霧氣肉眼可視距離才剩不到五公尺, 只能緊依前前方的車友幫忙開路, 之後洪大跟上來我立馬請求他幫忙開路, 上了139停下車把防風眼鏡收起來, 反正下雨天又陰天完全用不到太陽眼鏡了。 雨是邊下邊打雷, 大伙都在這條139上一台一台單車好像避電針, 一時有點害怕不然想平時沒做什麼壞事, 真打到自己就是天意了。 下了139雨勢開始變小, 大伙的速度開始有所提昇, 開高鐵列車的時機己成熟, 物色好列車就跟好跟滿。 最後找了一隊似乎整團有固定在練

By Phillips Hsieh
2023 12月9號 美利達單車嘉年華

2023 12月9號 美利達單車嘉年華

第二次參加美利達環南投賽事, 還記得去年第一次參加這美利達環南投, 還特地提前一天跟車友在魚池住了一晚。 這回用上了剛在7月份剛安裝的車頂架, 安裝了二種不同的攜車架, 都樂這邊可以不用拆車輪直上車頂, YAKIMA這邊選了經濟的款式, 折掉前輪利用前叉固定在攜車架上。 約了唯一一位一起參加的朋友, 二人一早四點約見面, 幫朋友帶上了拿鐵咖啡, 開上日月潭在水社碼頭停好車, 騎往向山遊客中心, 路過美麗的日月潭簡直不要太美了拍一張。 抵達會場己是人山人海了, 跟著大伙排隊順便也看網紅也欣賞名車。 出發就先沿著日月潭順時針騎, 騎到玄裝寺很急停下來上一下廁所, 比賽時都會尿都特別的滿, 一方面是比較緊張,一方面是特別興奮。 這時己經跟車友失散了, 只能獨推沿路看有沒有車友可以一起組隊的, 很可惜在山區大家的實力不一只求平安順騎了, 原則就是有補給就停有食物就吃。 下到水里人群再次聚集起來, 光等紅綠燈就是一條車龍。 騎行了一大圈水里再回到131縣道, 這時背後傳來熟悉的聲音叫菲哥, 終於跟車友重新集合接下來就一路邊聊邊騎。 最後來幾張專業攝影師拍攝的照片 回到終點台上

By Phillips Hsieh