Load: Difference between revisions

From Psygen Wiki
Jump to navigation Jump to search
Created page with " == MySQL == === Performance Tuning === '''1. First, Check innodb and myisam settings''' Run the below to get the size of data in INNODB and MYISAM tables:<br /> <code>mys..."
 
 
(10 intermediate revisions by the same user not shown)
Line 3: Line 3:
== MySQL ==
== MySQL ==


=== Performance Tuning ===
[[MySQL#Performance_Tuning | MySQL Performance Tuning]]


'''1. First, Check innodb and myisam settings'''


Run the below to get the size of data in INNODB and MYISAM tables:<br />
== OOM Killer ==
<code>mysql -e 'SELECT SQL_NO_CACHE Engine, count(*) "# of Tables", concat(round(sum(table_rows)/1000,2),"K") "Rows", concat(round(sum(data_length)/(1024*1024),2),"M") "Size of Data", concat(round(sum(index_length)/(1024*1024),2),"M") "Size of Indexes", concat(round(sum(data_length+index_length)/(1024*1024),2),"M") "Total Size", concat(round(sum(index_length)/sum(data_length)*(100),2), "%") "% Data in Indexes" FROM information_schema.TABLES GROUP BY engine ORDER BY sum(data_length+index_length) DESC LIMIT 10;'</code>


In /etc/my.cnf, change:<br />
OOM Killer kills the process with the highest memory usage to prevent the system from running out of memory. It often triggers due to memory load.


<code>innodb_buffer_pool_size</code> - set this to the value from the Total column, innodb row. (Note: if server has lots of RAM, and lots of data, set the innodb_buffer_pool_size = 1G, then set innodb_buffer_pool_instances = 4  (or smaller, or larger, depending on RAM))
=== Prevent OOM Killer from killing MySQL ===


<code>key_buffer_size</code> - set this to the value from the Index column, myisam row.
You can create a cron job to keep the PID for MySQL updated in the OOM Killer exclusion list:
<code>*/1 * * * * root pgrep -f "/usr/bin/mysql" | while read PID; do echo -1000 > /proc/$PID/oom_score_adj; done</code>


== References ==


=== MySQL Error log ===
<ul>
 
  <li>[https://backdrift.org/oom-killer-how-to-create-oom-exclusions-in-linux Backdrift.org - OOM Killer exclusions]</li>
To check the MySQL error log, run:<br />
</ul>
<code>mysql -e "show variables like '%log_error%';"</code>
 
This will show the location of the MySQL error log, which you can then grep, tail, etc.

Latest revision as of 17:09, 14 August 2017


MySQL

MySQL Performance Tuning


OOM Killer

OOM Killer kills the process with the highest memory usage to prevent the system from running out of memory. It often triggers due to memory load.

Prevent OOM Killer from killing MySQL

You can create a cron job to keep the PID for MySQL updated in the OOM Killer exclusion list: */1 * * * * root pgrep -f "/usr/bin/mysql" | while read PID; do echo -1000 > /proc/$PID/oom_score_adj; done

References

Retrieved from "https://wiki.psygen.org/index.php?title=Load&oldid=12952"