AP與DBMaker的搭配 – Performance Tuning初探

AP與DBMaker的搭配 – Performance Tuning初探

節錄

Database的原理，與上述的情形頗為類似，其中所謂的三層館藏，指的是實際存放資料的硬碟或其他儲存媒體，「工作平台」指的是記憶體。所有資料庫的搜尋或傳給客戶端的資料，都必須一定要先放在記憶體，搜尋動作會損耗CPU資源，但資料的處理、搜尋都必須先放在記憶體中，此處意指記憶體有 3000個單位(pages)。

所謂「客戶詢問」，指的便是由客戶端來的各式各樣的query，而無論客戶端的query如何，資料庫一定得將資料找到，並傳回客戶端。

---

索引如同其他資料頁一樣，必須先載入至記憶體後，CPU才能針對其中的內容，進行「比較」運算；而由於磁碟速度極慢，DBMaker會將常用的資料頁(含索引頁)儘量放在記憶體中，如此一來，當要再搜尋某資料頁時，就有相對較大的可能，可以在記憶體中找到，而不須到磁碟上去搜尋。

---

在定義索引時，自然也是以「排除效果」(filter effect)大的為佳，在條件判斷過程中，並不是只搜尋索引頁，而是有可能進一步搜尋資料頁，所以要想辦法讓第一次搜尋索引頁時，儘可能第一時間排除所有「不可能」的資料，上述中，作者的排除效果顯然就沒有書名好，也可以想作，書名比較「unique」

---

有許多SA，在設計之初，將所有query可能用到的欄位，都一一設定成索引。由上述的推演可以看出，這樣的作法不見得明智，理論上，DBMaker會從最符合的索引開始搜尋(如上述的書藉-作者索引)，但搜尋所花的工夫，卻還不如用單一的作者索引來得快。在設計索引時，必須要將這些因素考量在內，複合鍵有時會造成索引變大，甚至過肥， I/O加重的結果，反而拖慢了performance。

有些query是很難去定義出一個好的index，這時只要是「還好」的索引，可能就夠了，上述的 query，若將全部欄位建立成索引，可能會造成索引肥大，不見得好，建議將排除效果的欄位先建立好，若是能將所有搜尋的資料頁降低到25%以下，就算是不錯的索引，有些SA甚至會定義「血型」、「性別」為索引，這種索引的排除效果都不是甚佳，除非必要，這樣的索引應儘量避免。

另外常見的情形，便是一個常被變動的表格，本身資料量也很大，也常被查詢，表格本身可能有十個以上的索引，因為常變動，任何的變動都會造成索引值須被更新，十個以上的索引頁，都須被放入記憶體以便進行「新增索引」的動作，想想，這會造成系統效能多大的負擔?!

---

記憶體的大小調整，是所有DBA在系統效能調整時，必須先考量的，有些評比報告，常將兩個不同記憶體大小的資料庫效能加以比較，在先天上將造成了不公平，評比的結果自然也讓人存疑，在linux journal中文版雜誌2001年9月號，針對某家資料庫的效能調整，全文都在探討如何放大記憶體，由此可見記憶體大小對performance的重要。
記憶體當然能放大就儘量放大，讓所有資料能儘量在記憶體中，減少I/O的次數。但這樣也須付出代價，便是當系統毀損時，變動的資料都可能還在記憶體中，雖然DBMaker有LOG可記錄所有變動，但還是會讓recovery的時間拉長。有些個案甚至造成了資料庫的毀損，所以在設計系統時，亦將此因素一併考量，DBMaker在4.0後，會針對系統可用的資源，自動幫使用者劃好可用記憶體，但若使用者本身有其他考量，當然也可自己設定，而不依賴系統的調整； DBMaker要調整記憶體大小，相當容易，只要去調整dmconfig.ini中的DB_NBUFS即可，詳細內容，請參閱Administrator Guide。

sql,DATE,group by

想要計算某些column 每天的總數時, 可以用sql 的DATE<br />#select 出這個月份每天的render 數<br />>> Render.count(:all, :conditions => {:created_at => Time.now.beginning_of_month..Time.now}, :group => 'DATE(created_at)')<br /><br />SELECT count(*) AS count_all, DATE(created_at) AS date_created_at FROM `renders` WHERE (`renders`.`created_at` BETWEEN '2008-12-01 00:00:00' AND '2008-12-17 16:09:20') GROUP BY DATE(created_at)<br /><br />=> [["2008-12-16", 1], ["2008-12-17", 24]]<br />
結果出來是 OrderedHash 所以要用
hash["2008-12-16"] 去取值, 當然hash 的相關method 也可以用

sql,DATE,group by

想要計算某些column 每天的總數時, 可以用sql 的DATE<br />#select 出這個月份每天的render 數<br />>> Render.count(:all, :conditions => {:created_at => Time.now.beginning_of_month..Time.now}, :group => 'DATE(created_at)')<br /><br />SELECT count(*) AS count_all, DATE(created_at) AS date_created_at FROM `renders` WHERE (`renders`.`created_at` BETWEEN '2008-12-01 00:00:00' AND '2008-12-17 16:09:20') GROUP BY DATE(created_at)<br /><br />=> [["2008-12-16", 1], ["2008-12-17", 24]]<br />
結果出來是 OrderedHash 所以要用
hash["2008-12-16"] 去取值, 當然hash 的相關method 也可以用

rails execute sql

HowtoQueryTheDatabaseDirectly
<br />ActiveRecord::Base.connection.execute(sql)<br /><br />ActiveRecord::Base.connection.execute <<-SQL<br /> INSERT INTO clicks (item, location, created_at) VALUES('#{params[:item]}', '#{params[:loc]}', '#{Time.now.to_s(:db)}')<br />SQL<br /><br />

rails execute sql

HowtoQueryTheDatabaseDirectly
<br />ActiveRecord::Base.connection.execute(sql)<br /><br />ActiveRecord::Base.connection.execute <<-SQL<br /> INSERT INTO clicks (item, location, created_at) VALUES('#{params[:item]}', '#{params[:loc]}', '#{Time.now.to_s(:db)}')<br />SQL<br /><br />

看不懂等級的mysql random

http://blog.gslin.org/archives/2008/07/02/1535/
http://blog.xdite.net/?p=598
http://jan.kneschke.de/projects/mysql/order-by-rand

MySQL for Hosting Providers - how do they manage ?

MySQL for Hosting Providers - how do they manage ?

Computing 95 percentile in MySQL

Computing 95 percentile in MySQL

db,connection,execute,sql

http://wiki.rubyonrails.org/rails/pages/HowtoQueryTheDatabaseDirectly
http://api.rubyonrails.org/classes/ActiveRecord/Base.html#M001794

<br />require "memcache"<br /><br />CACHE_SERVERS = []<br />rs = ActiveRecord::Base.connection.execute('select * from memcached_servers')<br />rs.each { |r| CACHE_SERVERS << r }<br />CACHE_SERVERS.flatten!<br />CACHE = MemCache.new(GOOKUS_CACHE_SERVERS)<br />

length, count

SELECT length(todo_name) as l FROM records order by l limit 1;

SELECT COUNT(*) FROM records where todo_name = 'wake_up';

just sql select, as, relationship

select * from x_alerts as A, alert_targets as B, x_alert_relationships as C where A.status = 'ale' and C.x_alert_id = A.id and C.alert_target_id = B.id and B.user_id = 2590;

這樣加好幾個table 就等於inner join

scope_out :history, :conditions => 'x_alerts.status = "ale"'

XAlert.find_history(:all, :conditions => "id in (select x_alert_id from x_alert_relationships where alert_target_id in (select id from alert_targets where user_id in (#{id})))")

subquery or inner join 誰好 都不一定每種database實作這種東西的方法不同, 自己可以用benchmark測, 不然就是靠知道內部作法的高手 XD


ps:
select id from alert_targets where user_id in (2590);
select id from alert_targets where user_id = 2590;
一樣

subsqery

http://www.1keydata.com/sql/sql-subquery.html
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

sql, subsquery

http://www.1keydata.com/sql/sql-subquery.html
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html
http://railsforum.com/viewtopic.php?pid=68484

include 有時候太大包了, 這時我們可以用的就是subquery

[ActiveRecord]
User.find(:all, :conditions => "id in (select user_id from statuses where fight = true)")
[Sql]
SELECT * FROM `users` WHERE (id in (select user_id from statuses where fight = true))

[ActiveRecord]
User.update_all("group_nickname = 'iamlakela'","users.id in (select user_id from statuses where fight = true)")
[Sql]
UPDATE `users` SET group_nickname = 'iamlakela' WHERE (users.id in (select user_id from statuses where fight = true))

[ActiveRecord]
User.find(:all, :include => :status, :conditions => {'statuses.fight' => true})
[Sql]
SELECT `users`.`id` AS t0_r0, `users`.`name` AS t0_r1, `users`.`password_salt` AS t0_r2, `users`.`password_hash` AS t0_r3, `users`.`email` AS t0_r4, `users`.`created_at` AS t0_r5, `users`.`cookie_hash` AS t0_r6, `users`.`target_time_now` AS t0_r7, `users`.`reset_password_code` AS t0_r8, `users`.`reset_password_code_until` AS t0_r9, `users`.`yahoo_userhash` AS t0_r10, `users`.`group_id` AS t0_r11, `users`.`group_nickname` AS t0_r12, `users`.`time_zone` AS t0_r13, `statuses`.`id` AS t1_r0, `statuses`.`user_id` AS t1_r1, `statuses`.`fight` AS t1_r2, `statuses`.`state` AS t1_r3, `statuses`.`average` AS t1_r4, `statuses`.`success_rate` AS t1_r5, `statuses`.`continuous_num` AS t1_r6, `statuses`.`num` AS t1_r7, `statuses`.`last_record_created_at` AS t1_r8, `statuses`.`group_join_date` AS t1_r9, `statuses`.`attendance` AS t1_r10, `statuses`.`diff` AS t1_r11 FROM `users` LEFT OUTER JOIN `statuses` ON statuses.user_id = users.id WHERE (`statuses`.`fight` = 1)
[

從sample 就可以看出來差距多大了

ps:update 本來就是update 所有符合條件的object

find, like, sql

User.find(:all, :conditions => ["name LIKE ?", "%#{params[:search]}%"])

create table, column

  def self.up
    create_table :mugshots do |t|
      t.column :user_id, :integer
      t.column :created_at, :datetime
      t.column :parent_id,  :integer
      t.column :content_type, :string
      t.column :filename, :string
      t.column :thumbnail, :string
      t.column :size, :integer
      t.column :width, :integer
      t.column :height, :integer
    end
  end

CREATE TABLE `mugshots` (`id` int(11) DEFAULT NULL auto_increment 
PRIMARY KEY, `user_id` int(11) DEFAULT NULL NULL, `created_at` 
datetime DEFAULT NULL NULL, `parent_id` int(11) DEFAULT NULL NULL, 
`content_type` varchar(255) DEFAULT NULL NULL, `filename` 
varchar(255) DEFAULT NULL NULL, `thumbnail` varchar(255) DEFAULT 
NULL NULL, `size` int(11) DEFAULT NULL NULL, `width` int(11) DEFAULT
 NULL NULL, `height` int(11) DEFAULT NULL NULL) ENGINE=InnoDB

sql, alter, change the database character

http://dev.mysql.com/doc/refman/5.1/en/alter-database.html
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
ALTER DATABASE db_name DEFAULT CHARACTER SET utf8

http://dev.mysql.com/doc/refman/5.1/en/show-create-table.html
http://dev.mysql.com/doc/refman/5.1/en/show-create-database.html
show create table alert_targets;

sql, not, <>, in

User.find(:all, :conditions => ["id not in (?)",[2,3,5]]).map(&:id)
id <>(?)
not = <>

find, sql in ; select detect partition concat gsub split

http://www.1keydata.com/sql/sqlin.html
http://www.rubyinside.com/clever-find-conditions-in-rails-without-sql-56.html
list = [1,2]
User.find(:all, :conditions => {:id => list, :staus => 'health'})
User.find(:all, :conditions => ["follows_counter =0 and id In (?)",list])

SELECT * FROM `users` WHERE (`users`.`id` IN (1,2))

---

http://www.ruby-doc.org/core/classes/Enumerable.html#M003154
#把參數處理成 array
emails = params[:email].gsub(/\s/, "").gsub(/;/, ",").split(/,/)

in_email_users = User.find(:all, :conditions => {:nickname => emails })
in_email = in_email_users.map(&:nickname)

#就像是兩個回圈下去跑
out_emails = emails.select { |e| !in_email.detect{|i| i == e } }

#email 的驗證 regular expression
out_emails, wrong_emails = out_emails.partition { |e|
e =~ /^([_a-z0-9-]+)(\.[_a-z0-9-]+)*@([a-z0-9-]+)(\.[a-z0-9-]+)*(\.[a-z]{2,4})$/
}

out_emails.concat(in_email)

---

>> (1..20).select {|i| i % 5 == 0 }
=> [5, 10, 15, 20]
>> (1..20).detect {|i| i % 5 == 0 }
=> 5