About MySQL Performance Tips ​

    test–>benchmark–>monitor

    sysbench/mysqlslap,monitor tools

    test–>benchmark–>monitor

    sysbench/mysqlslap,monitor tools

	Depands on your active data and connections

	1.active data should fit in the buffer pool
	2.connections and caches take memory

	Depands on your active data and connections

	1.active data should fit in the buffer pool
	2.connections and caches take memory

	1.SSD
	2.SATA for log files
	3.several disks (raid5 raid10)

	1.SSD
	2.SATA for log files
	3.several disks (raid5 raid10)

	1.Max open files per process:ulimit -n
		limits the number of file handles (connections,open tables,…)
	2.Max threads per user:ulimit -u
		limits the number of threads

	1.Max open files per process:ulimit -n
		limits the number of file handles (connections,open tables,…)
	2.Max threads per user:ulimit -u
		limits the number of threads

	1.jemalloc
	2.tcmalloc

	1.jemalloc
	2.tcmalloc

	1.XFS:excellent,innode_flush_method=O_DIRECT,less stable recently
	2.EXT4:best choice for speed and ease of use,fsyncs a bit slower than ext3,more reliable
	3.EXT3

	1.XFS:excellent,innode_flush_method=O_DIRECT,less stable recently
	2.EXT4:best choice for speed and ease of use,fsyncs a bit slower than ext3,more reliable
	3.EXT3

	1.EXT4(rw,noatime,nodiratime,nobarrier,data=ordered)
	2.XFS(rw,noatime,nodiratime,nobarrier,logbufs=8,logbsize=32k)

	SSD specific:innodb_page_size=4k,innodb_flush_neighbors=0

	1.EXT4(rw,noatime,nodiratime,nobarrier,data=ordered)
	2.XFS(rw,noatime,nodiratime,nobarrier,logbufs=8,logbsize=32k)

	SSD specific:innodb_page_size=4k,innodb_flush_neighbors=0

	1.deadline:generall the best I/O scheduler
		echo deadline > /sys/block/{DEVICE-NAME}/queue/scheduler
	2.noop:the best value is HW and WL specific (SSD,good RAID card…)

	1.deadline:generall the best I/O scheduler
		echo deadline > /sys/block/{DEVICE-NAME}/queue/scheduler
	2.noop:the best value is HW and WL specific (SSD,good RAID card…)

	1.faster fsyncs:innodb redo logs / binary logs / data files
	2.Crash safety

	1.faster fsyncs:innodb redo logs / binary logs / data files
	2.Crash safety

	1.table_open_cache:used to size PS,opened_tables/sec
	2.table_definition_cache:increase
	3.table_cache_instances:8 or 16
	4.innodb_open_files
	5.mdl_hash_instances=256

	1.table_open_cache:used to size PS,opened_tables/sec
	2.table_definition_cache:increase
	3.table_cache_instances:8 or 16
	4.innodb_open_files
	5.mdl_hash_instances=256

	thread_cache_size:decreases threads_created rate

	thread_cache_size:decreases threads_created rate

	max_used_connections*(
		read_buffer_size +
		read_rnd_buffer_size +
		join_buffer_size +
		sort_buffer_size +
		binlog_cache_size +
		thread_stack +
		2 * net_buffer_length …
	)

	max_used_connections*(
		read_buffer_size +
		read_rnd_buffer_size +
		join_buffer_size +
		sort_buffer_size +
		binlog_cache_size +
		thread_stack +
		2 * net_buffer_length …
	)

	innodb_buffer_pool_size:do not swap,beware of memory crash
		eg:Active dat <= innodb_buffer_pool_size <= 80% RAM

	innodb_buffer_pool_size:do not swap,beware of memory crash
		eg:Active dat <= innodb_buffer_pool_size <= 80% RAM

	1.innodb_buffer_pool_instances >=8
	2.reduce rows_examined / sec

	1.innodb_buffer_pool_instances >=8
	2.reduce rows_examined / sec

	a key parameter for write performance:better for write QPS

	a key parameter for write performance:better for write QPS

	1.Redo logs:innodb_flush_log_at_trx_commit
			=1 best durability
			=2 better performance
			=0 best performance
		advice set = 2
	2.Data files only:innodb_flush_method

	O_DIRECT:for Linux,skips the FS cache

	1.Redo logs:innodb_flush_log_at_trx_commit
			=1 best durability
			=2 better performance
			=0 best performance
		advice set = 2
	2.Data files only:innodb_flush_method

	O_DIRECT:for Linux,skips the FS cache

	1.increased manageability
	2.truncate reclaims disk space
	3.better with innodb\_flush\_method=O\_DIRECT
	4.easier to optimize

	1.increased manageability
	2.truncate reclaims disk space
	3.better with innodb\_flush\_method=O\_DIRECT
	4.easier to optimize

	1.not so good with many small tables;
	2.more file handles
	3.more fsyncs

	1.not so good with many small tables;
	2.more file handles
	3.more fsyncs