MySQL - Revision history

Admin: /* Performance Tuning */

2019-11-23T21:15:51Z

Performance Tuning

← Older revision		Revision as of 21:15, 23 November 2019
Line 73:		Line 73:

	'''3. CPU Usage Issues '''		'''3. CPU Usage Issues '''

			thread_cache_size - MySQL setting to adjust

			SHOW GLOBAL STATUS LIKE 'Threads_created';

			SHOW GLOBAL STATUS LIKE 'Connetions';

	== View the MySQL Error log ==		== View the MySQL Error log ==

Admin: /* Performance Tuning */

2019-11-08T20:45:53Z

Performance Tuning

← Older revision		Revision as of 20:45, 8 November 2019
Line 47:		Line 47:
	It's a good idea to backup the current settings, just in case:<br />		It's a good idea to backup the current settings, just in case:<br />
	<code>cp -a /etc/my.cnf /root/my.cnf.</code>		<code>cp -a /etc/my.cnf /root/my.cnf.</code>


			'''Are these settings effective?'''

			mysql -e "show global status like '%innodb_buffer_pool_read%%';" \| awk '/reads/ {print $1,"=",$2}';mysql -e "show global status like '%key_read%%';" \| awk '/reads/ {print $1,"=",$2}'

			The results should be close to the values for innodb_buffer_pool_size and key_buffer_size


Line 63:		Line 70:

	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.


			'''3. CPU Usage Issues '''

	== View the MySQL Error log ==		== View the MySQL Error log ==

Admin: /* Performance Tuning */

2018-10-04T02:51:11Z

Performance Tuning

← Older revision		Revision as of 02:51, 4 October 2018
Line 29:		Line 29:
	Run the below to get the size of data in INNODB and MYISAM tables:<br />		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>		<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>

			<pre>
			mysql -Bse 'show variables like "datadir";'\|awk '{print $2}'\|xargs -I{} find {} -type f -printf "%s %f\n"\|awk -F'[ ,.]' '{print $1, $NF}'\|awk '{array[$2]+=$1} END {for (i in array) {printf("%-15s %s\n", sprintf("%.3f MB", array[i]/1048576), i)}}' \| egrep '(MYI\|ibd)'
			</pre>

	(You can use the below to get the size of each database.)<br />		(You can use the below to get the size of each database.)<br />

Admin at 02:03, 7 December 2017

2017-12-07T02:03:54Z

← Older revision		Revision as of 02:03, 7 December 2017
Line 80:		Line 80:

	<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> )		<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> )


			== Dump and Import ==

			<code>mysql database_name < file.sql</code> - Import a file to the named database from a .sql file

			<code>mysqldump dbname > backupfile.sql</code> - dump one database to a .sql file

Admin: /* Performance Tuning */

2017-10-31T17:45:35Z

Performance Tuning

← Older revision		Revision as of 17:45, 31 October 2017
Line 37:		Line 37:
	<code>innodb_buffer_pool_size</code> - set this to the value from the Total column, innodb row.<br />		<code>innodb_buffer_pool_size</code> - set this to the value from the Total column, innodb row.<br />
	<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.)		'''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.) Also note that ideally each pool chunk should be 1Gb in size.

	<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.

Admin: /* Performance Tuning */

2017-08-11T22:31:31Z

Performance Tuning

← Older revision		Revision as of 22:31, 11 August 2017
Line 46:		Line 46:

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

			Warning: Don't mess with this unless the server is running out of memory. Even then, bigger is better than too small.

	a.) Get the total number of tables on the server:<br />		a.) Get the total number of tables on the server:<br />
	<code>find /var/lib/mysql -name '*.frm' \| wc -l</code> - Example output: 4568		<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 />		b.) Set table_open_cache variable in /etc/my.cnf to 50% of that number<br />
	Example, based on the above number: <code>table_open_cache = 1371</code>		Example, based on the above number: <code>table_open_cache = 1371</code>

Admin: /* Performance Tuning */

2017-07-21T01:35:46Z

Performance Tuning

← Older revision		Revision as of 01:35, 21 July 2017
Line 31:		Line 31:

	(You can use the below to get the size of each database.)<br />		(You can use the below to get the size of each database.)<br />
	<code>mysql-e 'select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;'</code>		<code>mysql -e 'select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;'</code>

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

Admin: /* Performance Tuning */

2017-07-13T03:44:21Z

Performance Tuning

← Older revision		Revision as of 03:44, 13 July 2017
Line 30:		Line 30:
	<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>		<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>

	(You can use the below to get the size of each database.)		(You can use the below to get the size of each database.)<br />
	<code>mysql-e 'select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;'</code>		<code>mysql-e 'select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;'</code>

Admin: /* Performance Tuning */

2017-07-13T03:44:09Z

Performance Tuning

← Older revision		Revision as of 03:44, 13 July 2017
Line 29:		Line 29:
	Run the below to get the size of data in INNODB and MYISAM tables:<br />		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>		<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>

			(You can use the below to get the size of each database.)
			<code>mysql-e 'select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;'</code>

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

Admin: /* Performance Tuning */

2017-06-04T00:09:43Z

Performance Tuning

← Older revision		Revision as of 00:09, 4 June 2017
Line 37:		Line 37:

	<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.

			It's a good idea to backup the current settings, just in case:<br />
			<code>cp -a /etc/my.cnf /root/my.cnf.</code>


Line 51:		Line 54:

	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.


	== View the MySQL Error log ==		== View the MySQL Error log ==