mysql什么能够影响数据库服务占用cpu使用率过多

CPU占用过高诊断思路，举个栗子~

mpstat -P ALL 1，查看cpu使用情况，主要消耗在sys即os系统调用上

perf top，cpu主要消耗在_spin_lock

生成perf report查看详细情况

CPU主要消耗在mutex争用上，说明有锁热点。

采用pt-pmp跟踪mysqld执行情况，热点主要集中在mem_heap_alloc和mem_heap_free上。

Pstack提供更详细的API调用栈

Innodb在读取数据记录时的API路径为

row_search_for_mysql --》row_vers_build_for_consistent_read --》mem_heap_create_block_func --》mem_area_alloc --》malloc --》  _L_unlock_10151 --》__lll_unlock_wait_private

row_vers_build_for_consistent_read会陷入一个死循环，跳出条件是该条记录不需要快照读或者已经从undo中找出对应的快照版本，每次循环都会调用mem_heap_alloc/free。

而该表的记录更改很频繁，导致其undo history list比较长，搜索快照版本的代价更大，就会频繁的申请和释放堆内存。

Linux原生的内存库函数为ptmalloc，malloc/free调用过多时很容易产生锁热点。

当多条 SQL 并发执行时，会最终触发os层面的spinlock，导致上述情形。

解决方案

将mysqld的内存库函数替换成tcmalloc，相比ptmalloc，tcmalloc可以更好的支持高并发调用。

修改my.cnf，添加如下参数并重启

[mysqld_safe]malloc-lib=tcmalloc

上周五早上7点执行的 *** 作，到现在超过72小时，期间该实例没有再出现cpu长期飙高的情形。

以下是修改前后cpu使用率对比

MySQL服务的进程名是:mysqld-nt

下面那个类.GetProcessInfo这个函数,把进程名传入

using System

using System.Collections.Generic

using System.Diagnostics

using System.Threading

using System.IO

using System.Text

using System.Management

using System.Runtime.InteropServices

namespace Lemony.SystemInfo

{

///

/// 系统信息类 - 获取CPU、内存、磁盘、进程信息

///

public class SystemInfo

{

private int m_ProcessorCount = 0 //CPU个数

private PerformanceCounter pcCpuLoad //CPU计数器

private long m_PhysicalMemory = 0 //物理内存

private const int GW_HWNDFIRST = 0

private const int GW_HWNDNEXT = 2

private const int GWL_STYLE = (-16)

private const int WS_VISIBLE = 268435456

private const int WS_BORDER = 8388608

#region AIP声明

[DllImport("IpHlpApi.dll")]

extern static public uint GetIfTable(byte[] pIfTable, ref uint pdwSize, bool bOrder)

[DllImport("User32")]

private extern static int GetWindow(int hWnd, int wCmd)

[DllImport("User32")]

private extern static int GetWindowLongA(int hWnd, int wIndx)

[DllImport("user32.dll")]

private static extern bool GetWindowText(int hWnd, StringBuilder title, int maxBufSize)

[DllImport("user32", CharSet = CharSet.Auto)]

private extern static int GetWindowTextLength(IntPtr hWnd)

#endregion

#region 构造函数

///

/// 构造函数，初始化计数器等

///

public SystemInfo()

{

//初始化CPU计数器

pcCpuLoad = new PerformanceCounter("Processor", "% Processor Time", "_Total")

pcCpuLoad.MachineName = "."

pcCpuLoad.NextValue()

//CPU个数

m_ProcessorCount = Environment.ProcessorCount

//获得物理内存

ManagementClass mc = new ManagementClass("Win32_ComputerSystem")

ManagementObjectCollection moc = mc.GetInstances()

foreach (ManagementObject mo in moc)

{

if (mo["TotalPhysicalMemory"] != null)

{

m_PhysicalMemory = long.Parse(mo["TotalPhysicalMemory"].ToString())

}

}

}

#endregion

#region CPU个数

///

/// 获取CPU个数

///

public int ProcessorCount

{

get

{

return m_ProcessorCount

}

}

#endregion

#region CPU占用率

///

/// 获取CPU占用率

///

public float CpuLoad

{

get

{

return pcCpuLoad.NextValue()

}

}

#endregion

#region 可用内存

///

/// 获取可用内存

///

public long MemoryAvailable

{

get

{

long availablebytes = 0

//ManagementObjectSearcher mos = new ManagementObjectSearcher("SELECT * FROM Win32_PerfRawData_PerfOS_Memory")

//foreach (ManagementObject mo in mos.Get())

//{

//availablebytes = long.Parse(mo["Availablebytes"].ToString())

//}

ManagementClass mos = new ManagementClass("Win32_OperatingSystem")

foreach (ManagementObject mo in mos.GetInstances())

{

if (mo["FreePhysicalMemory"] != null)

{

availablebytes = 1024 * long.Parse(mo["FreePhysicalMemory"].ToString())

}

}

return availablebytes

}

}

#endregion

#region 物理内存

///

/// 获取物理内存

///

public long PhysicalMemory

{

get

{

return m_PhysicalMemory

}

}

#endregion

#region 获得分区信息

///

/// 获取分区信息

///

public List GetLogicalDrives()

{

List drives = new List()

ManagementClass diskClass = new ManagementClass("Win32_LogicalDisk")

ManagementObjectCollection disks = diskClass.GetInstances()

foreach (ManagementObject disk in disks)

{

// DriveType.Fixed 为固定磁盘(硬盘)

if (int.Parse(disk["DriveType"].ToString()) == (int)DriveType.Fixed)

{

drives.Add(new DiskInfo(disk["Name"].ToString(), long.Parse(disk["Size"].ToString()), long.Parse(disk["FreeSpace"].ToString())))

}

}

return drives

}

///

/// 获取特定分区信息

///

/// 盘符

public List GetLogicalDrives(char DriverID)

{

List drives = new List()

WqlObjectQuery wmiquery = new WqlObjectQuery("SELECT * FROM Win32_LogicalDisk WHERE DeviceID = ’" + DriverID + ":’")

ManagementObjectSearcher wmifind = new ManagementObjectSearcher(wmiquery)

foreach (ManagementObject disk in wmifind.Get())

{

if (int.Parse(disk["DriveType"].ToString()) == (int)DriveType.Fixed)

{

drives.Add(new DiskInfo(disk["Name"].ToString(), long.Parse(disk["Size"].ToString()), long.Parse(disk["FreeSpace"].ToString())))

}

}

return drives

}

#endregion

#region 获得进程列表

///

/// 获得进程列表

///

public List GetProcessInfo()

{

List pInfo = new List()

Process[] processes = Process.GetProcesses()

foreach (Process instance in processes)

{

try

{

pInfo.Add(new ProcessInfo(instance.Id,

instance.ProcessName,

instance.TotalProcessorTime.TotalMilliseconds,

instance.WorkingSet64,

instance.MainModule.FileName))

}

catch { }

}

return pInfo

}

///

/// 获得特定进程信息

///

/// 进程名称

public List GetProcessInfo(string ProcessName)

{

List pInfo = new List()

Process[] processes = Process.GetProcessesByName(ProcessName)

foreach (Process instance in processes)

{

try

{

pInfo.Add(new ProcessInfo(instance.Id,

instance.ProcessName,

instance.TotalProcessorTime.TotalMilliseconds,

instance.WorkingSet64,

instance.MainModule.FileName))

}

catch { }

}

return pInfo

}

#endregion

#region 结束指定进程

///

/// 结束指定进程

///

/// 进程的 Process ID

public static void EndProcess(int pid)

{

try

{

Process process = Process.GetProcessById(pid)

process.Kill()

}

catch { }

}

#endregion

#region 查找所有应用程序标题

///

/// 查找所有应用程序标题

///

/// 应用程序标题范型

public static List FindAllApps(int Handle)

{

List Apps = new List()

int hwCurr

hwCurr = GetWindow(Handle, GW_HWNDFIRST)

while (hwCurr >0)

{

int IsTask = (WS_VISIBLE | WS_BORDER)

int lngStyle = GetWindowLongA(hwCurr, GWL_STYLE)

bool TaskWindow = ((lngStyle &IsTask) == IsTask)

if (TaskWindow)

{

int length = GetWindowTextLength(new IntPtr(hwCurr))

StringBuilder sb = new StringBuilder(2 * length + 1)

GetWindowText(hwCurr, sb, sb.Capacity)

string strTitle = sb.ToString()

if (!string.IsNullOrEmpty(strTitle))

{

Apps.Add(strTitle)

}

}

hwCurr = GetWindow(hwCurr, GW_HWNDNEXT)

}

return Apps

}

#endregion

}

}

load average 就是 CPU 的 Load

它表示 CPU 使用队列的长度 这个数字越小越好

这里有一个误区 那就是 CPU 利用率高 并不意味着负载就一定大！

---