Category Archive Database

Byphunsanit

MySql: Basic Config โดย MySQLTuner

เว็บบน DigitalOcean มันชอบบอกว่า monitoring resolved: Memory Utilization is running high กับ NAS server ที่บ้าน ram แค่ 1 GB มันต๊อแต๊ อยากจะสลบอยู่ตลอด เลยต้อง configuration เพิ่มประสิทธิภาพใหม่ให้มันทำงานได้ โดยใช้ MySQLTuner ที่จะช่วยปรับแต่ง database server ได้หลายตัวทั้ง Galera, MariaDB, MySQL, Percona

  1. ก่อนอื่นก็ download MySQLTuner มาช่วยตรวจสอบการติดตั้งที่เหมาะสมก่อนโดยใช้ command
    wget http://mysqltuner.pl/ -O mysqltuner.pl
  2. เรียกใช้โดย
    perl mysqltuner.pl
    จะเห็นข้อความที่จะต้องใช้ในการปรับแต่ง MySql หรือดาต้าเบสตัวอื่น ๆ โดยให้ดูบรรทัดที่ขึ้นต้นด้วย [!!] เป็นหลัก
  3. แก้มายเอสคิวเอล และเทสโดยคำสั้ง
    mysqld –validate-config
    sudo systemctl restart mysql.service
    perl mysqltuner.pl
    วนไปจนกว่าจะได้ performance ที่พอใจ
  4. ตัวอย่างไฟล์ config ที่ได้สำหรับ NAS ที่มี ram 1 GB
    #
    # The MySQL database server configuration file.
    #
    # You can copy this to one of:
    # - "/etc/mysql/my.cnf" to set global options,
    # - "~/.my.cnf" to set user-specific options.
    #
    # One can use all long options that the program supports.
    # Run program with --help to get a list of available options and with
    # --print-defaults to see which it would actually understand and use.
    #
    # For explanations see
    # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
    
    #
    # * IMPORTANT: Additional settings that can override those from this file!
    #   The files must end with '.cnf', otherwise they'll be ignored.
    #
    
    !includedir /etc/mysql/conf.d/
    !includedir /etc/mysql/mysql.conf.d/
    
    #test by mysqltuner
    
    [mysqld]
    # Skip reverse DNS lookup of clients.
    # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_skip_name_resolve
    skip-name-resolve
    
    # Ratio InnoDB redo log capacity / InnoDB Buffer pool size should be equal to 25%.
    #https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-resize.html
    #of RAM but beware of setting memory usage too high
    innodb_buffer_pool_size=300M
    
    #https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_connections
    max_connections=70

นอกจากนี้ คำสั่ง perl mysqltuner.pl ยังสามารถใช้ options เพิ่มเติมได้อีกเช่น

optionsความหมายค่าเริ่มต้นตัวอย่างชนิดตัวแปร
–debugdebugging information
–dumpdirexport to–dumpdir=./resultfile path
–forcememแรมทั้งหมด1024–forcemem 32768megabytes
–forceswapswap memory–forceswap 16384megabytes
–hostremote serverlocalhost–host 127.0.0.1IP
–jsonออกรายงานเป็น JSON–json > reports.jsonfile path
–outputfileออกรายงานเป็นไฟล์–outputfile /tmp/result_mysqltuner.txtfile path
–pass, –passwordรหัสผ่าน–pass admin_passwordstring
–portport3306–port 3306 int
–socketใช้ socket ต่างออกไปสำหรับ localhost
–userusername–user admin_userstring
–verboseรายละเอียด–verbose
options บางส่วนที่สามารถใช้ได้

อ่านเพิ่มเติม

Byphunsanit

ติดตั้ง SQL Server Management Studio (smss) แล้วค้าง

ถ้าติดตั้ง SQL Server Management Studio แล้วค้างอยู่ที่ข้อความ “Loading packages. Please wait…” เป็นชั่วโมงไม่ขยับไปไหน แก้ได้โดย

  1. พยาม Cancel ตัวติดตั้งก่อนถ้าทำไม่ได้ให้ end task ไปเลย
  2. ลบไฟล์ทั้งหมดใน temp file
    • โดยใส่ %temp% ใน Windows File Explorer แล้ว enter
    • หรือจะเปิด C:\Users\<username>\AppData\Local\Temp ก็ได้
      จากนั้นลบไฟล์ทั้งหมด ที่สามารถจะลบได้ออกไป
  3. ลบ registry
    • เปิด command prompt โดยสิทธ์ administrator พิมพ์
      reg DELETE “HKLM\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server Management Studio” /reg:32
      กด enter จะเห็นคำถาม
      Permanently delete the registry key HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server Management Studio (Yes/No)?
      พิมพ์ yes แล้ว enter
    • หรือเปิด registry editor โดยสิทธ์ administrator แล้วค้นหา “HKLM\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server Management Studio” จากนั้นก็ลบออกไป
  4. restart windows อาจจะเข้าไปลบไฟล์อีกครั้ง
  5. ติดตั้ง smss ตามปกติ

ขอบคุณ How To Fix SSMS Installation Stuck Issue

Byphunsanit

SQL Server: Kill process ของข้าให้หมด

มีแก้งานที่เขียน query ได้โหดยูเครนมาก (รัสเซียได้พิสูจน์ให้เห็นแล้วว่ากาก) ใช้ cpu สูงมาก ใช้ ram เกือบหมดเม็ค แล้วมีคนใช้งานอยู่พร้อม ๆ กัน T-T เลยหา script มาจัดการดีด connection ที่ใช้อยู่ในเครื่องตัวเองทิ้งยกเว้นที่ใช้อยู่ใน sql server management studio (ssms) และโปรแกรม app ที่กำลังเขียนอยู่

DECLARE @SqlCmd VARCHAR(1000)
  
	,@HostName VARCHAR(100)-- Set the hostname name from which to kill the connections

  
	--SET @HostName = 'Pitt_P'

SET @HostName = HOST_NAME()

SET @SqlCmd = ''  SELECT @Sqlcmd = @SqlCmd + CHAR(13) + CHAR(10) + 'KILL ' + convert(CHAR(10), spid) + ' '
 
FROM master.dbo.sysprocesses
 
WHERE hostname = @HostName
 
	AND DBID <> 0
 
	AND spid <> @@spid PRINT @sqlcmd EXEC(@Sqlcmd)
 
GO

แก้จากต้นแบบ Kill all processes associated with a hostname โดยดึงข้อมูล HOST HOST_NAME แทนที่จะใส่ชื่อเครื่องตัวเองไป

Byphunsanit

SQL Server: update limit

เทสงานที่มีการบันทึกข้อมูลไว้ ถ้าจะทำอีกครั้งก็ต้อง update กลับไปเป็นเหมือนเดิม แต่เพราะว่าเงื่อนไขมันกว้างมาก ๆ เลยไปอัพเดตทั้ง table เลยมันช้าไป เปลืองทรัพยากร เลยหาวิธีอื่นดู

  • ROWCOUNT อย่างต้องการให้ update แค่ 100 รายการแรก
    • SET ROWCOUNT 100;
      UPDATE table_name
      SET colunm_name = …
      WHERE column_name = …
      SET ROWCOUNT 0;
  • TOP จะง่ายกว่าหน่อย
    • UPDATE TOP(100) table_name
      SET colunm_name = …
      WHERE column_name = …
  • CTE
    • ;WITH CTE AS
      (
      SELECT TOP 100 *
      FROM table_name
      ORDER BY colunm_name
      )
      UPDATE CTE SET colunm_name = …
  • Sub Query
    • UPDATE table_name
      SET column_name =…
      WHERE column_id IN (
      SELECT TOP 100 column_id
      FROM table_name
      WHERE column_name = 0
      )

เลือกเอาแบบที่ชอบได้เลยครับ ตัวที่ผมชอบที่สุดคือ ROWCOUNT เพราะว่าใช้ query จริง ๆ มาวางได้เลย

Byphunsanit

ติดตั้ง Poor Man’s T-SQL Formatter ใน SSMS รุ่นใหม่ ๆ

Poor Man’s T-SQL Formatter จะติดตั้งได้ใน SQL Server Management Studio ( SSMS ) เวอร์ชั่นเก่า ๆ ได้ แต่ตัวติดตั้งรุ่นใหม่ ๆ เมื่อติดตั้งแล้ว จะไม่สามารถคลิกเมนูของตัว extension ได้ แก้ได้ง่าย ๆ โดย

  1. ปิด SQL Server Management Studio
  2. ถ้ายังไม่ได้ติดตั้ง .Net 2.0 ให้ติดตั้ง .NET Framework 2.0 ก่อนโดยวิธี How-To How to Enable .NET Framework 2.0 and 3.5 in Windows 11
  3. ถ้าต้องการใช้กับ SSMS 19 ต้องติดตั้ง SSMS 18 ในเครื่องก่อน จากนั้นติดตั้ง plugin ตามปกติ แล้ว copy C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Extensions ไปเป็น C:\Program Files (x86)\Microsoft SQL Server Management Studio 19\Extensions
  4. เปิดไฟล์ C:\Program Files (x86)\Microsoft SQL Server Management Studio {version}\Common7\IDE\Ssms.exe.config เช่น
    • C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe.config
    • C:\Program Files (x86)\Microsoft SQL Server Management Studio 19\Common7\IDE\Ssms.exe.config
  5. ค้นหาบรรทัด <assemblyBinding
  6. เพิ่มข้อความใต้<probing privatePat
    <dependentAssembly><assemblyIdentity name="Microsoft.VisualStudio.Shell.12.0" publicKeyToken="b03f5f7f11d50a3a" culture="neutral"/> <bindingRedirect oldVersion="2.0.0.0-15.0.0.0" newVersion="15.0.0.0"/></dependentAssembly>
  7. เปิด SQL Server Management Studio ใหม่

เท่านี้ก็สามารถใช้ Poor Man’s T-SQL Formatter จัดรูปแบบ sql ได้แล้ว