Load - Revision history

Admin: /* Prevent OOM Killer from killing MySQL */

2017-08-14T17:09:28Z

Prevent OOM Killer from killing MySQL

← Older revision		Revision as of 17:09, 14 August 2017
Line 12:		Line 12:
	=== Prevent OOM Killer from killing MySQL ===		=== 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:
			<code>/1 * * * root pgrep -f "/usr/bin/mysql" \| while read PID; do echo -1000 > /proc/$PID/oom_score_adj; done</code>

	== References ==		== References ==

Admin at 17:05, 14 August 2017

2017-08-14T17:05:33Z

← Older revision		Revision as of 17:05, 14 August 2017
Line 4:		Line 4:

	[[MySQL#Performance_Tuning \| MySQL Performance Tuning]]		[[MySQL#Performance_Tuning \| 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 ===


			== References ==

			<ul>
			<li>[https://backdrift.org/oom-killer-how-to-create-oom-exclusions-in-linux Backdrift.org - OOM Killer exclusions]</li>
			</ul>

Admin: /* MySQL */

2017-05-12T03:09:27Z

MySQL

← Older revision		Revision as of 03:09, 12 May 2017
Line 3:		Line 3:
	== MySQL ==		== MySQL ==

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

Admin at 03:08, 12 May 2017

2017-05-12T03:08:43Z

← Older revision		Revision as of 03:08, 12 May 2017
Line 3:		Line 3:
	== MySQL ==		== MySQL ==

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

Admin: Replaced content with " == MySQL == MySQL#Performance_Tuning MySQL Performance Tuning"

2017-05-12T03:08:30Z

Replaced content with " == MySQL == MySQL#Performance_Tuning MySQL Performance Tuning"

← Older revision		Revision as of 03:08, 12 May 2017
Line 3:		Line 3:
	== MySQL ==		== MySQL ==

	[[MySQL#Performance_Tuning]]		[[MySQL#Performance_Tuning MySQL Performance Tuning]]



	~~=== Performance Tuning ===~~

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

	~~Run the below to get the size of data in INNODB and MYISAM tables:<br />~~
	<pre>mysql -e 'SELECT SQL_NO_CACHE Engine, count() "# of Tables", concat(round(sum(table_rows)/1000,2),"K") "Rows", concat(round(sum(data_length)/(10241024),2),"M") "Size of Data", concat(round(sum(index_length)/(10241024),2),"M") "Size of Indexes", concat(round(sum(data_length+index_length)/(10241024),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;'</pre>

	~~In /etc/my.cnf, change:<br />~~

	~~<code>innodb_buffer_pool_size</code> - set this to the value from the Total column, innodb row.<br />~~
	~~<br />~~
	'''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) Also note that this will set the buffer pool size as 1G, and break that 1G into 4 chunks (It will NOT create 4, 1GB pools.)

	~~<code>key_buffer_size</code> - set this to the value from the Index column, myisam row.~~


	~~'''2. Opens'''~~

	~~a.) Get the total number of tables on the server:<br />~~
	~~<code>find /var/lib/mysql -name '*.frm' \| wc -l</code> - Example output: 4568~~

	~~b.) Set table_open_cache variable in /etc/my.cnf to 30% of that number<br />~~
	~~Example, based on the above number: <code>table_open_cache = 1371</code>~~

	~~c.) Set open_files_limit to three times the corrected value of table_open_cache<br />~~
	~~Example: <code>open_files_limit = 3951</code>~~

	You can check to see if this value is good by running <code>mysqladmin stat</code>. That command will show the number of Opens, and the number of Open tables. Ideally, these numbers should be identical.

	~~=== MySQL Error log ===~~

	~~To check the MySQL error log, run:<br />~~
	~~<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. (You're looking for stuff like innodb errors, disk space issues, connection messages, etc)~~


	~~=== View MySQL Variables ===~~

	~~You can view the variables that MySQL is using with:~~

	~~<code>mysql -e "show variables like 'max_connections%';" </code> - This shows the max_connections variable. You can substitute any MySQL variable here.~~

	~~This is helpful for database connection errors, or if a change to /etc/my.cnf isn't 'sticking' (In that case, look for other my.cnf files)~~


	~~=== View Currently Running Queries ===~~

	<code>mysqladmin process stat</code> - This will show the queries that are running, how many threads, Open tables, slow queries, and a bunch of other helpful stuff. (Note that you can "tail -f" by running: <code>watch mysqladmin process stat</code> )

Admin at 03:07, 12 May 2017

2017-05-12T03:07:12Z

← Older revision		Revision as of 03:07, 12 May 2017
Line 2:		Line 2:

	== MySQL ==		== MySQL ==

			[[MySQL#Performance_Tuning]]



	=== Performance Tuning ===		=== Performance Tuning ===

Admin: /* Performance Tuning */

2017-04-14T02:56:45Z

Performance Tuning

← Older revision		Revision as of 02:56, 14 April 2017
Line 17:		Line 17:

	<code>key_buffer_size</code> - set this to the value from the Index column, myisam row.		<code>key_buffer_size</code> - set this to the value from the Index column, myisam row.


	'''2. Opens'''		'''2. Opens'''

Admin: /* Performance Tuning */

2017-04-14T02:56:28Z

Performance Tuning

← Older revision		Revision as of 02:56, 14 April 2017
Line 12:		Line 12:
	In /etc/my.cnf, change:<br />		In /etc/my.cnf, change:<br />

	<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))		<code>innodb_buffer_pool_size</code> - set this to the value from the Total column, innodb row.<br />
			<br />
			'''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) Also note that this will set the buffer pool size as 1G, and break that 1G into 4 chunks (It will NOT create 4, 1GB pools.)

	<code>key_buffer_size</code> - set this to the value from the Index column, myisam row.		<code>key_buffer_size</code> - set this to the value from the Index column, myisam row.
Line 28:		Line 30:

	You can check to see if this value is good by running <code>mysqladmin stat</code>. That command will show the number of Opens, and the number of Open tables. Ideally, these numbers should be identical.		You can check to see if this value is good by running <code>mysqladmin stat</code>. That command will show the number of Opens, and the number of Open tables. Ideally, these numbers should be identical.


	=== MySQL Error log ===		=== MySQL Error log ===

Admin: /* MySQL */

2017-03-10T05:03:13Z

MySQL

← Older revision		Revision as of 05:03, 10 March 2017
Line 15:		Line 15:

	<code>key_buffer_size</code> - set this to the value from the Index column, myisam row.		<code>key_buffer_size</code> - set this to the value from the Index column, myisam row.

			'''2. Opens'''

			a.) Get the total number of tables on the server:<br />
			<code>find /var/lib/mysql -name '*.frm' \| wc -l</code> - Example output: 4568

			b.) Set table_open_cache variable in /etc/my.cnf to 30% of that number<br />
			Example, based on the above number: <code>table_open_cache = 1371</code>

			c.) Set open_files_limit to three times the corrected value of table_open_cache<br />
			Example: <code>open_files_limit = 3951</code>

			You can check to see if this value is good by running <code>mysqladmin stat</code>. That command will show the number of Opens, and the number of Open tables. Ideally, these numbers should be identical.

Admin at 04:48, 10 March 2017

2017-03-10T04:48:34Z

← Older revision		Revision as of 04:48, 10 March 2017
Line 22:		Line 22:
	<code>mysql -e "show variables like '%log_error%';"</code>		<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.		This will show the location of the MySQL error log, which you can then grep, tail, etc. (You're looking for stuff like innodb errors, disk space issues, connection messages, etc)


			=== View MySQL Variables ===

			You can view the variables that MySQL is using with:

			<code>mysql -e "show variables like 'max_connections%';" </code> - This shows the max_connections variable. You can substitute any MySQL variable here.

			This is helpful for database connection errors, or if a change to /etc/my.cnf isn't 'sticking' (In that case, look for other my.cnf files)


			=== View Currently Running Queries ===

			<code>mysqladmin process stat</code> - This will show the queries that are running, how many threads, Open tables, slow queries, and a bunch of other helpful stuff. (Note that you can "tail -f" by running: <code>watch mysqladmin process stat</code> )