命令刷新SQL server 中 資料庫:
你提交正常的數據更新後，SQL server 中 資料庫，也會自動更新，在你想刷新的時候，重新讀取載入一次即可刷新，不需要你額外做什麼。
Ⅱ SQL數據磁碟滿了怎麼解決
-- 清空日誌
--壓縮日誌及資料庫文件大小
/*--特別注意
請按步驟進行,未進行前面的步驟,請不要做後面的步驟
否則可能損壞你的資料庫.
--*/
select*fromsysfiles
--1.清空日誌
DUMPTRANSACTIONusernameWITHNO_LOG
--2.截斷事務日誌：
BACKUPLOGusernameWITHNO_LOG
--3.收縮資料庫文件(如果不壓縮,資料庫的文件不會減小
-- 企業管理器--右鍵你要壓縮的資料庫--所有任務--收縮資料庫--收縮文件
--選擇日誌文件--在收縮方式里選擇收縮至XXM,這里會給出一個允許收縮到的最小M數,直接輸入這個數,確定就可以了
--選擇數據文件--在收縮方式里選擇收縮至XXM,這里會給出一個允許收縮到的最小M數,直接輸入這個數,確定就可以了
-- 也可以用SQL語句來完成
--收縮資料庫
DBCCSHRINKDATABASE(username)
--收縮指定數據文件,1是文件號,可以通過這個語句查詢到:select*fromsysfiles
DBCCSHRINKFILE(2)
--4.為了最大化的縮小日誌文件(如果是sql7.0,這步只能在查詢分析器中進行)
-- a.分離資料庫:
-- 企業管理器--伺服器--資料庫--右鍵--分離資料庫
-- b.在我的電腦中刪除LOG文件
-- c.附加資料庫:
-- 企業管理器--伺服器--資料庫--右鍵--附加資料庫
-- 此法將生成新的LOG，大小隻有500多K
-- 或用代碼：
-- 下面的示例分離username，然後將username中的一個文件附加到當前伺服器。
execsp_dboptionusername,'singleuser',true
a.分離
EXECsp_detach_db@dbname='username'
b.刪除日誌文件
execmaster..xp_cmdshell'delD:\ProgramFiles\SQL\database\username_LOG.ldf'
c.再附加
EXECsp_attach_single_file_db@dbname='username',
@physname='D:\ProgramFiles\SQL\database\username_Data.MDF'
--5.為了以後能自動收縮,做如下設置:
-- 企業管理器--伺服器--右鍵資料庫--屬性--選項--選擇"自動收縮"
--SQL語句設置方式:
EXECsp_dboption'資料庫名','autoshrink','TRUE'
--6.如果想以後不讓它日誌增長得太大
-- 企業管理器--伺服器--右鍵資料庫--屬性--事務日誌
--將文件增長限制為xM(x是你允許的最大數據文件大小)
--SQL語句的設置方式:
alterdatabase資料庫名modifyfile(name=邏輯文件名,maxsize=20)
Ⅲ sql server 臨時表佔用硬碟嗎
我們仍然使用兩個會話，一個會話 run，用於運行主 SQL；另一個會話 ps，用於進行 performance_schema 的觀察：
主會話線程號為 29，
可以看到寫入的線程是 page_clean_thread，是一個刷臟操作，這樣就能理解數據為什麼是慢慢寫入的。
也可以看到每個 IO 操作的大小是 16K，也就是刷數據頁的操作。
結論：
我們可以看到，
1. MySQL 會基本遵守 max_heap_table_size 的設定，在內存不夠用時，直接將表轉到磁碟上存儲。
2. 由於引擎不同（內存中表引擎為 heap，磁碟中表引擎則跟隨 internal_tmp_disk_storage_engine 的配置），本次實驗寫磁碟的數據量和實驗 05中使用內存的數據量不同。
3. 如果臨時表要使用磁碟，表引擎配置為 InnoDB，那麼即使臨時表在一個時間很短的 SQL 中使用，且使用後即釋放，釋放後也會刷臟頁到磁碟中，消耗部分 IO。
Ⅳ 如何遠程備份MySQL binlog
用腳本對遠程伺服器進行備份的方式，有個缺點：無法對MySQL伺服器當前正在寫的二進制日誌文件進行備份。所以，只能等到MySQL伺服器全部寫完才能進行備份。而寫完一個binlog的時間並不固定，這就導致備份周期的不確定。
從MySQL5.6開始，mysqlbinlog支持將遠程伺服器上的binlog實時復制到本地伺服器上。
mysqlbinlog的實時二進制復制功能並非簡單的將遠程伺服器的日誌復制過來，它是通過MySQL 5.6公布的Replication API實時獲取二進制事件。本質上，就相當於MySQL的從伺服器。與普通伺服器類似，主伺服器發生事件後，一般都會在0.5~1秒內進行備份。
備份命令
mysqlbinlog--read-from-remote-server--raw--host=192.168.244.145--port=3306--user=repl--password=repl--stop-nevermysql-bin.000001
解釋如下：
--read-from-remote-server：用於備份遠程伺服器的binlog。如果不指定該選項，則會查找本地的binlog。
--raw：binlog日誌會以二進制格式存儲在磁碟中，如果不指定該選項，則會以文本形式保存。
--user：復制的MySQL用戶，只需要授予REPLICATION SLAVE許可權。
--stop-never：mysqlbinlog可以只從遠程伺服器獲取指定的幾個binlog，也可將不斷生成的binlog保存到本地。指定此選項，代表只要遠程伺服器不關閉或者連接未斷開，mysqlbinlog就會不斷的復制遠程伺服器上的binlog。
mysql-bin.000001：代表從哪個binlog開始復制。
除了以上選項外，還有以下幾個選項需要注意：
--stop-never-slave-server-id：在備份遠程伺服器的binlog時，mysqlbinlog本質上就相當於一個從伺服器，該選項就是用來指定從伺服器的server-id的。默認為-1。
--to-last-log：代表mysqlbinlog不僅能夠獲取指定的binlog，還能獲取其後生成的binlog，獲取完了，才終止。如果指定了--stop-never選項則會隱式打開--to-last-log選項。
--result-file：用於設置遠程伺服器的binlog，保存到本地的前綴。譬如對於mysql-bin.000001，如果指定--result-file=/test/backup-，則保存到本地後的文件名為/test/backup-mysql-bin.000001。注意：如果將--result-file設置為目錄，則一定要帶上目錄分隔符「/」。譬如--result-file=/test/，而不是--result-file=/test，不然保存到本地的文件名為/testmysql-bin.000001。
不足：
這個方式有個問題，對於常規的主從復制來說，如果主從直接的連接斷開了，則從會自動再次連接，而對於mysqlbinlog，如果斷開了，並不會自動連接。
解決方案：
可通過腳本來彌補上述不足
#!/bin/sh
BACKUP_BIN=/usr/bin/mysqlbinlog
LOCAL_BACKUP_DIR=/backup/binlog/
BACKUP_LOG=/backup/binlog/backuplog
REMOTE_HOST=192.168.244.145
REMOTE_PORT=3306
REMOTE_USER=repl
REMOTE_PASS=repl
FIRST_BINLOG=mysql-bin.000001
#
SLEEP_SECONDS=10
##createlocal_backup_dirifnecessary
mkdir-p${LOCAL_BACKUP_DIR}
cd${LOCAL_BACKUP_DIR}
##運行while循環，連接斷開後等待指定時間，重新連接
while:
do
if[`ls-A"${LOCAL_BACKUP_DIR}"|wc-l`-eq0];then
LAST_FILE=${FIRST_BINLOG}
else
LAST_FILE=`ls-l${LOCAL_BACKUP_DIR}|grep-vbackuplog|tail-n1|awk'{print$9}'`
fi
${BACKUP_BIN}--raw--read-from-remote-server--stop-never--host=${REMOTE_HOST}--port=${REMOTE_PORT}--user=${REMOTE_USER}--password=${REMOTE_PASS}${LAST_FILE}
echo"`date+"%Y/%m/%d%H:%M:%S"`mysqlbinlog停止，返回代碼：$?"|tee-a${BACKUP_LOG}
echo"${SLEEP_SECONDS}秒後再次連接並繼續備份"|tee-a${BACKUP_LOG}
sleep${SLEEP_SECONDS}
done
腳本解讀：
1. 實際上定義了一個死循環，如果備份失敗，則10s後重新連接。
2. 第一次運行時需指定FIRST_BINLOG的值，指從哪個binlog開始復制，一般為mysql-bin.000001。後續執行的時候就直接獲取備份目錄下最新的binlog，從最新的binlog開始復制。
總結：
1. 如果指定了--raw，mysqlbinlog獲取事件後，並不會實時落盤，而是先保存在本地伺服器的內存中，每4K刷盤一次。這也就減少了頻繁的日誌寫操作。如果此時mysqlbinlog和主伺服器之間的連接斷開了，則內存中的binlog會馬上刷新到磁碟中。
2. 盡管mysqlbinlog類似於從伺服器，但從伺服器上的relaylog卻是實時存檔的，即從伺服器獲取主伺服器產生的事件後，會實時寫入到relaylog中。
3. 如果不指定--raw，這個時候會以文本格式存檔，此時，--result-file=/test/不能指定為目錄，必須明確寫上文件名，譬如--result-file=/test/1.sql，此時，mysqlbinlog獲取事件後，是實時落盤的，不會每4K刷盤一次。
Ⅳ SQL可以安裝在其他盤上嗎
沒有啊，我就安裝在d盤了啊，可以選擇的啊，但是在xp下好像是有一個版本是不能裝的，企業版或者是個人版忘了。
Ⅵ sql刷選資料庫
SELECT * FROM TABLE WHERE X>X1 AND X<X2 AND Y>Y1 AND Y<Y2
Ⅶ mysql插入1000條數據到數據表中如何能加快速度
常用的插入語句如：
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('1', 'userid_1', 'content_1', 1);
修改成：
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);
修改後的插入操作能夠提高程序的插入效率。這里第二種SQL執行效率高的主要原因是合並後日誌量（MySQL的binlog和innodb的事務讓日誌）減少了，降低日誌刷盤的數據量和頻率，從而提高效率。通過合並SQL語句，同時也能減少SQL語句解析的次數，減少網路傳輸的IO。
SQL語句是有長度限制，在進行數據合並在同一SQL中務必不能超過SQL長度限制，通過max_allowed_packet配置可以修改，默認是1M，測試時修改為8M。
Ⅷ 如何優化MySQL insert性能
測試場景
MySQL 5.7.12
主要測試 不同刷盤參數 對性能的影響, 使用以下三個場景:
sync_binlog=1, innodb_flush_log_at_trx_commit=1, 簡寫為b1e1 (binlog-1-engine-1)
sync_binlog=0, innodb_flush_log_at_trx_commit=1, 簡寫為b0e1
sync_binlog=0, innodb_flush_log_at_trx_commit=0, 簡寫為b0e0
- mysql -h127.0.0.1 -P5712 -uroot -pmsandbox -e"truncate table test.sbtest1";
- /opt/sysbench-0.5/dist/bin/sysbench --test=/opt/sysbench-0.5/dist/db/insert.lua --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=msandbox --mysql-port=5712 --oltp-table-size=1000000 --mysql-db=test --oltp-table-name=stest --num-threads=1 --max-time=30 --max-requests=1000000000 --oltp-auto-inc=off --db-driver=mysql run
- global tids
- probe process("/home/huangyan/sandboxes/5.7.12/bin/mysqld").function("mysql_execute_command") {
- if (pid() == target() && tids[tid()] == 0) {
- tids[tid()] = 1;
- }
- }
- global oncpu
- probe timer.profile {
- if (tids[tid()] == 1) {
- oncpu[ubacktrace()] <<< 1;
- }
- }
- probe timer.s(10) {
- exit();
- }
- probe end {
- foreach (i in oncpu+) {
- print_stack(i);
- printf(" %d ", @count(oncpu[i]));
- }
- }
在b0e1場景中,_ZL27log_write_flush_to_disk_lowv的佔比是12.93%, 其絕大部分時間是用於將innodb的log刷盤.
在b0e0場景中,_ZL27log_write_flush_to_disk_lowv的開銷被節省掉, 理論上的事務數比例應是1-12.93%=87.07%, 實際事務數的比例是119274/166424=71.67%, 誤差較大
- global tids
- probe process("/home/huangyan/sandboxes/5.7.12/bin/mysqld").function("mysql_execute_command") {
- if (pid() == target() && tids[tid()] == 0) {
- tids[tid()] = 1;
- }
- }
- global oncpu, offcpu, timer_count, first_cpu_id = -1;
- probe timer.profile {
- if (first_cpu_id == -1) {
- first_cpu_id = cpu();
- }
- if (tids[tid()] == 1) {
- oncpu[ubacktrace()] <<< 1;
- }
- if (first_cpu_id == cpu()) {
- timer_count++;
- }
- }
- global switchout_ustack, switchout_timestamp
- probe scheler.ctxswitch {
- if (tids[prev_tid] == 1) {
- switchout_ustack[prev_tid] = ubacktrace();
- switchout_timestamp[prev_tid] = timer_count;
- }
- if (tids[next_tid] == 1 && switchout_ustack[next_tid] != "") {
- offcpu[switchout_ustack[next_tid]] <<< timer_count - switchout_timestamp[next_tid];
- switchout_ustack[next_tid] = "";
- }
- }
- probe timer.s(10) {
- exit();
- }
- probe end {
- foreach (i in oncpu+) {
- print_stack(i);
- printf(" %d ", @sum(oncpu[i]));
- }
- foreach (i in offcpu+) {
- printf("---");
- print_stack(i);
- printf(" %d ", @sum(offcpu[i]));
- }
- }
- Profiling timers are available to provide probes that execute on all CPUs at each system tick.
- if (first_cpu_id == cpu()) {
- timer_count++;
- }
修改stap腳本, 在b1e1中不統計sync_binlog的代價. 生成的火焰圖表示消除sync_binlog代價後, 理論上的伺服器壓力類型.
與b0e1產生的火焰圖做比較.
- probe end {
- foreach (i in oncpu+) {
- if (isinstr(sprint_stack(i), "my_sync")) {
- continue;
- }
- print_stack(i);
- printf(" %d ", @sum(oncpu[i]));
- }
- foreach (i in offcpu+) {
- if (isinstr(sprint_stack(i), "my_sync")) {
- continue;
- }
- printf("---");
- print_stack(i);
- printf(" %d ", @sum(offcpu[i]));
- }
- }
- global ctxswitch_times
- probe scheler.ctxswitch {
- ctxswitch_times++;
- ...
- }
- probe end {
- ...
- printf("ctxswitch_times=%d ", ctxswitch_times);
- }
利用火焰圖, 可以快速診斷出MySQL伺服器級別的性能瓶頸, 做出合理的參數調整
對於IO類型的操作的觀測, 需要考慮oncpu和offcpu兩種情況
由於觀測手段中使用了上下文切換作為觀測點, 那IO操作數量的不同, 會引起上下文切換次數的不同, 從而引起觀測誤差.
MySQL 環境搭建使用MySQL sandbox, 對應三個場景的啟動參數如下:
1../start --sync-binlog=1 --log-bin=bin --server-id=5712 --gtid-mode=ON --enforce-gtid-consistency=1 --log-slave-updates=1
2../start --sync-binlog=0 --log-bin=bin --server-id=5712 --gtid-mode=ON --enforce-gtid-consistency=1 --log-slave-updates=1
3../start --sync-binlog=0 --log-bin=bin --server-id=5712 --gtid-mode=ON --enforce-gtid-consistency=1 --log-slave-updates=1 --innodb-flush-log-at-trx-commit=0
壓力生成使用sysbench:
性能觀測工具使用systemtap(簡稱stap), version 2.7/0.160
基準
在沒有觀測壓力的情況下, 對三種場景分別進行基準測試, 用以矯正之後測試的誤差:
場景
sysbench事務數
b1e1 67546
b0e1 125699
b0e0 181612
火焰圖與offcpu
火焰圖
火焰圖是Brendan Gregg首創的表示性能的圖形方式, 其可以直觀的看到壓力的分布. Brendan提供了豐富的工具生成火焰圖.
火焰圖比較b0e1和b0e0
使用stap腳本獲取CPU profile, 並生成火焰圖(火焰圖生成的命令略, 參看Brendan的文檔)
stap腳本
注意:
1. 腳本只抓取MySQL的用戶線程的CPU profile, 不抓取後台進程.
2. 腳本只抓取10s, 相當於對整個sysbench的30s過程進行了短期抽樣.
b0e1生成的火焰圖
性能
在開啟觀測的情況下, 觀察性能:
場景
sysbench事務數
b0e1 119274
b0e0 166424
分析
在生成的火焰圖中, 可以看到:
誤差較大的問題, 要引入offcpu來解決.
offcpu
在之前的分析中我們看到理論和實際的事務數誤差較大. 考慮_ZL27log_write_flush_to_disk_lowv的主要操作是IO操作, IO操作開始, 進程就會被OS進行上下文切換換下台, 以等待IO操作結束, 那麼只分析CPU profile就忽略了IO等待的時間, 也就是說_ZL27log_write_flush_to_disk_lowv的開銷被低估了.
offcpu也是Brendan Gregg提出的概念. 對於IO操作的觀測, 除了CPU profile(稱為oncpu時間), 還需要觀測其上下文切換的代價, 即offcpu時間.
修改一下stap腳本可以觀測offcpu時間. 不過為了將oncpu和offcpu的時間顯示在一張火焰圖上作對比, 我對於Brendan的工具做了微量修改, 本文將不介紹這些修改.
stap腳本
注意:timer.profile的說明中是這樣寫的:
也就是說在一個時間片中,timer.profile會對每一個CPU調用一次. 因此代碼中使用了如下代碼, 保證時間片技術的正確性:
b0e1生成的帶有offcpu的火焰圖
性能
由於變更了觀測腳本, 需要重新觀測性能以減小誤差:
場景
sysbench事務數
b0e1 105980
b0e0 164739
分析
在火焰圖中, 可以看到:
1._ZL27log_write_flush_to_disk_lowv的佔比為31.23%
2. 理論上的事務數比例應是1-31.23%=68.77%, 實際事務數的比例是105980/164739=64.33%, 誤差較小.
觀測誤差的矯正
在比較b0e1和b0e0兩個場景時, 獲得了比較好的結果. 但同樣的方法在比較b1e1和b0e1兩個場景時, 出現了一些誤差.
誤差現象
b1e1的火焰圖如圖:
其中_ZN13MYSQL_BIN_LOG16sync_binlog_fileEb(sync_binlog的函數)佔比為26.52%.
但性能差異為:
場景
sysbench事務數
b1e1 53752
b0e1 105980
理論的事務數比例為1-26.52%=73.48%, 實際事務數比例為53752/105980=50.71%, 誤差較大.
分析壓力分布
首先懷疑壓力轉移, 即當sync_binlog的壓力消除後, 伺服器壓力被轉移到了其它的瓶頸上. 但如果壓力產生了轉移, 那麼實際事務數比例應大於理論事務數比例, 即sync_binlog=0帶來的性能提升更小.
不過我們還是可以衡量一下壓力分布, 看看b1e1和b0e1的壓力有什麼不同, 步驟如下:
stap腳本
只修改了probe end部分, 略過對my_sync堆棧的統計:
結果
b1e1, 理論的伺服器壓力圖:
b0e1, 實際的伺服器壓力圖:
可以看到, 壓力分布是非常類似, 即沒有發生壓力分布.
BTW: 這兩張圖的類似, 具有一定隨機性, 需要做多次試驗才能產生這個結果.
分析
既然理論和實際的壓力分布類似, 那麼可能發生的就是壓力的整體等比縮小. 推測是兩個場景上的觀測成本不同, 導致觀測影響到了所有壓力的觀測.
觀察stap腳本, 其中成本較大的是ctxswitch, 即上下文切換時的觀測成本.
上下文切換的觀測成本
如果 「上下文切換的觀測成本 影響 場景觀測 的公平性」 這一結論成立, 那麼我們需要解釋兩個現象:
1. b1e1和b0e1的比較, 受到了 上下文切換的觀測成本 的影響
2. b0e1和b0e0的比較, 未受到 上下文切換的觀測成本 的影響
假設 上下文切換的觀測成本 正比於 上下文切換的次數, 那麼我們只需要:
1. 觀測每個場景的上下文切換次數
2. 對於b1e1和b0e1的比較, 由上下文切換次數計算得到理論的降速比, 與實際的降速比進行比較
3. 對於b0e1和b0e0的比較, 由上下文切換次數計算得到是否會帶來降速.
stap腳本
在probe scheler.ctxswitch和probe end增加了ctxswitch_times相關的內容:
結果
場景
sysbench事務數
上下文切換次數
sync_binlog佔比
b1e1 55352 826370 36.80%
b0e1 105995 693383 –
b0e0 162709 675092 –
分析結果:
1. b1e1與b0e1的比較
1. 理論降速比:693383/826370 = 83.90%
2. 實際降速比:(實際的事務數比例/由sync_binlog佔比推算的理論的事務數比例) = (55352/105995)/(1-36.80%) = 0.5222/0.6320 = 82.63%
3. 誤差很小. 即b1e1與b0e1的比較中, 理論值和實際值的誤差來自於: IO操作的減少導致上下文切換的數量減小, 使得兩個場景的觀察成本不同.
2. b0e1與b0e0的比較: 上下文切換次數相近, 即兩個場景的觀察成本相同.
實驗結果符合之前的分析.
結論
Ⅸ 如何解決主從資料庫同步延遲問題
最簡單的減少slave同步延時的方案就是在架構上做優化，盡量讓主庫的DDL快速執行。還有就是主庫是寫，對數據安全性較高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之類的設置，而slave則不需要這么高的數據安全，完全可以講sync_binlog設置為0或者關閉binlog，innodb_flushlog也可以設置為0來提高sql的執行效率。另外就是使用比主庫更好的硬體設備作為slave。
mysql-5.6.3已經支持了多線程的主從復制。原理和丁奇的類似，丁奇的是以表做多線程，Oracle使用的是以資料庫(schema)為單位做多線程，不同的庫可以使用不同的復制線程。
sync_binlog=1
This makes MySQL synchronize the binary log』s contents to disk each time it commits a transaction
默認情況下，並不是每次寫入時都將binlog與硬碟同步。因此如果操作系統或機器(不僅僅是MySQL伺服器)崩潰，有可能binlog中最後的語句丟 失了。要想防止這種情況，你可以使用sync_binlog全局變數(1是最安全的值，但也是最慢的)，使binlog在每N次binlog寫入後與硬碟 同步。即使sync_binlog設置為1,出現崩潰時，也有可能表內容和binlog內容之間存在不一致性。如果使用InnoDB表，MySQL伺服器 處理COMMIT語句，它將整個事務寫入binlog並將事務提交到InnoDB中。如果在兩次操作之間出現崩潰，重啟時，事務被InnoDB回滾，但仍 然存在binlog中。可以用--innodb-safe-binlog選項來增加InnoDB表內容和binlog之間的一致性。(注釋：在MySQL 5.1中不需要--innodb-safe-binlog；由於引入了XA事務支持，該選項作廢了），該選項可以提供更大程度的安全，使每個事務的 binlog(sync_binlog =1)和(默認情況為真)InnoDB日誌與硬碟同步，該選項的效果是崩潰後重啟時，在滾回事務後，MySQL伺服器從binlog剪切回滾的 InnoDB事務。這樣可以確保binlog反饋InnoDB表的確切數據等，並使從伺服器保持與主伺服器保持同步(不接收 回滾的語句)。
innodb_flush_log_at_trx_commit （這個很管用）
抱怨Innodb比MyISAM慢 100倍？那麼你大概是忘了調整這個值。默認值1的意思是每一次事務提交或事務外的指令都需要把日誌寫入（flush）硬碟，這是很費時的。特別是使用電 池供電緩存（Battery backed up cache）時。設成2對於很多運用，特別是從MyISAM表轉過來的是可以的，它的意思是不寫入硬碟而是寫入系統緩存。日誌仍然會每秒flush到硬 盤，所以你一般不會丟失超過1-2秒的更新。設成0會更快一點，但安全方面比較差，即使MySQL掛了也可能會丟失事務的數據。而值2隻會在整個操作系統 掛了時才可能丟數據。