Here's all the buffer variables:
mysql> show variables like '%buffer%'\G
*************************** 1. row ***************************
Variable_name: bulk_insert_buffer_size
Value: 8388608
*************************** 2. row ***************************
Variable_name: innodb_buffer_pool_awe_mem_mb
Value: 0
*************************** 3. row ***************************
Variable_name: innodb_buffer_pool_size
Value: 8388608
*************************** 4. row ***************************
Variable_name: innodb_log_buffer_size
Value: 1048576
*************************** 5. row ***************************
Variable_name: join_buffer_size
Value: 131072
*************************** 6. row ***************************
Variable_name: key_buffer_size
Value: 402653184
*************************** 7. row ***************************
Variable_name: myisam_sort_buffer_size
Value: 67108864
*************************** 8. row ***************************
Variable_name: net_buffer_length
Value: 16384
*************************** 9. row ***************************
Variable_name: preload_buffer_size
Value: 32768
*************************** 10. row ***************************
Variable_name: read_buffer_size
Value: 67104768
*************************** 11. row ***************************
Variable_name: read_rnd_buffer_size
Value: 67104768
*************************** 12. row ***************************
Variable_name: sort_buffer_size
Value: 67108856
12 rows in set (0.00 sec)
I'll bump innodb_buffer_pool_size to 2G and see how that goes. Thanks
for the tips, if there's additional innodb tuning parameters folks
tend to hit first I'd be glad to try them as well.
--
Ryan Schwartz
On Sep 4, 2008, at 8:16 AM, Johnny Withers wrote:
If you do have a fair about of innodb tables you can increase
performance by increasing the size of innodb_buffer_pool_size.
According to your status output, you are currently using the entire
buffer pool:
*************************** 137. row ***************************
Variable_name: Innodb_buffer_pool_pages_free
Value: 0
It seems to be set small anyway:
Variable_name: Innodb_buffer_pool_pages_data
Value: 501
It also seems that you do have alot of innodb data:
*************************** 151. row ***************************
Variable_name: Innodb_data_read
Value: 27743085907968
Again, i don't know what you have innodb_buffer_pool_size set to,
but you have plenty of RAM, I'd set it to about 4.5GB and see if
that helps. I also don't know mucha bout OS X and your hardware.. is
it 64bit? If it is not 64bit, you probably can't use 4.5GB as the
size of your buffer pool.
-johnny
On 9/3/08, Ryan Schwartz <[EMAIL PROTECTED]> wrote: All,
We're seeing a huge surge in our qps and I'd like to make sure we're
tuned as well as we can be. I'm wondering if I've got some variables
maybe set too large (is that even possible?) ? We do have a fair bit
of innodb, so perhaps I should add some non-defaults there, but I'm
not so sure where to start with that.
Hardware is an Apple Xserve, 2x Quad-Core Intel @ 3Ghz, 32GB RAM, 3x
280 GB SAS drives in Raid-5 config, OS is Mac OS X 10.5.4 and here's
my my.cnf:
[billie:~] admin$ egrep -v '^$|^#' /etc/my.cnf
[client]
port = 3306
socket = /var/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 50M
table_cache = 2048
sort_buffer_size = 64M
read_buffer_size = 64M
read_rnd_buffer_size = 64M
myisam_sort_buffer_size = 64M
thread_cache_size = 100
query_cache_size = 64M
thread_concurrency = 16
skip-thread-priority
max_connections = 750
old-passwords
innodb_file_per_table
innodb_flush_log_at_trx_commit=1
sync_binlog=1
log-slow-queries
long_query_time=2
log_queries_not_using_indexes
log-bin=mysql-bin
server-id = 4
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
SHOW STATUS\G output follows my sig below...
My devs are adding indexes where the slow query log is pointing
them, but any suggestions on how better to tune things up would be
much appreciated. I'm not sure what else to tune here but we're
getting bursts of 1200+ queries per second regularly and seeing
things slow down significantly.
Best,
--
Ryan Schwartz
mysql> SHOW STATUS\G
*************************** 1. row ***************************
Variable_name: Aborted_clients
Value: 1656
*************************** 2. row ***************************
Variable_name: Aborted_connects
Value: 3
*************************** 3. row ***************************
Variable_name: Binlog_cache_disk_use
Value: 276
*************************** 4. row ***************************
Variable_name: Binlog_cache_use
Value: 6416113
*************************** 5. row ***************************
Variable_name: Bytes_received
Value: 134
*************************** 6. row ***************************
Variable_name: Bytes_sent
Value: 70104
*************************** 7. row ***************************
Variable_name: Com_admin_commands
Value: 0
*************************** 8. row ***************************
Variable_name: Com_alter_db
Value: 0
*************************** 9. row ***************************
Variable_name: Com_alter_table
Value: 0
*************************** 10. row ***************************
Variable_name: Com_analyze
Value: 0
*************************** 11. row ***************************
Variable_name: Com_backup_table
Value: 0
*************************** 12. row ***************************
Variable_name: Com_begin
Value: 0
*************************** 13. row ***************************
Variable_name: Com_call_procedure
Value: 0
*************************** 14. row ***************************
Variable_name: Com_change_db
Value: 0
*************************** 15. row ***************************
Variable_name: Com_change_master
Value: 0
*************************** 16. row ***************************
Variable_name: Com_check
Value: 0
*************************** 17. row ***************************
Variable_name: Com_checksum
Value: 0
*************************** 18. row ***************************
Variable_name: Com_commit
Value: 0
*************************** 19. row ***************************
Variable_name: Com_create_db
Value: 0
*************************** 20. row ***************************
Variable_name: Com_create_function
Value: 0
*************************** 21. row ***************************
Variable_name: Com_create_index
Value: 0
*************************** 22. row ***************************
Variable_name: Com_create_table
Value: 0
*************************** 23. row ***************************
Variable_name: Com_create_user
Value: 0
*************************** 24. row ***************************
Variable_name: Com_dealloc_sql
Value: 0
*************************** 25. row ***************************
Variable_name: Com_delete
Value: 0
*************************** 26. row ***************************
Variable_name: Com_delete_multi
Value: 0
*************************** 27. row ***************************
Variable_name: Com_do
Value: 0
*************************** 28. row ***************************
Variable_name: Com_drop_db
Value: 0
*************************** 29. row ***************************
Variable_name: Com_drop_function
Value: 0
*************************** 30. row ***************************
Variable_name: Com_drop_index
Value: 0
*************************** 31. row ***************************
Variable_name: Com_drop_table
Value: 0
*************************** 32. row ***************************
Variable_name: Com_drop_user
Value: 0
*************************** 33. row ***************************
Variable_name: Com_execute_sql
Value: 0
*************************** 34. row ***************************
Variable_name: Com_flush
Value: 0
*************************** 35. row ***************************
Variable_name: Com_grant
Value: 0
*************************** 36. row ***************************
Variable_name: Com_ha_close
Value: 0
*************************** 37. row ***************************
Variable_name: Com_ha_open
Value: 0
*************************** 38. row ***************************
Variable_name: Com_ha_read
Value: 0
*************************** 39. row ***************************
Variable_name: Com_help
Value: 0
*************************** 40. row ***************************
Variable_name: Com_insert
Value: 0
*************************** 41. row ***************************
Variable_name: Com_insert_select
Value: 0
*************************** 42. row ***************************
Variable_name: Com_kill
Value: 0
*************************** 43. row ***************************
Variable_name: Com_load
Value: 0
*************************** 44. row ***************************
Variable_name: Com_load_master_data
Value: 0
*************************** 45. row ***************************
Variable_name: Com_load_master_table
Value: 0
*************************** 46. row ***************************
Variable_name: Com_lock_tables
Value: 0
*************************** 47. row ***************************
Variable_name: Com_optimize
Value: 0
*************************** 48. row ***************************
Variable_name: Com_preload_keys
Value: 0
*************************** 49. row ***************************
Variable_name: Com_prepare_sql
Value: 0
*************************** 50. row ***************************
Variable_name: Com_purge
Value: 0
*************************** 51. row ***************************
Variable_name: Com_purge_before_date
Value: 0
*************************** 52. row ***************************
Variable_name: Com_rename_table
Value: 0
*************************** 53. row ***************************
Variable_name: Com_repair
Value: 0
*************************** 54. row ***************************
Variable_name: Com_replace
Value: 0
*************************** 55. row ***************************
Variable_name: Com_replace_select
Value: 0
*************************** 56. row ***************************
Variable_name: Com_reset
Value: 0
*************************** 57. row ***************************
Variable_name: Com_restore_table
Value: 0
*************************** 58. row ***************************
Variable_name: Com_revoke
Value: 0
*************************** 59. row ***************************
Variable_name: Com_revoke_all
Value: 0
*************************** 60. row ***************************
Variable_name: Com_rollback
Value: 0
*************************** 61. row ***************************
Variable_name: Com_savepoint
Value: 0
*************************** 62. row ***************************
Variable_name: Com_select
Value: 1
*************************** 63. row ***************************
Variable_name: Com_set_option
Value: 0
*************************** 64. row ***************************
Variable_name: Com_show_binlog_events
Value: 0
*************************** 65. row ***************************
Variable_name: Com_show_binlogs
Value: 0
*************************** 66. row ***************************
Variable_name: Com_show_charsets
Value: 0
*************************** 67. row ***************************
Variable_name: Com_show_collations
Value: 0
*************************** 68. row ***************************
Variable_name: Com_show_column_types
Value: 0
*************************** 69. row ***************************
Variable_name: Com_show_create_db
Value: 0
*************************** 70. row ***************************
Variable_name: Com_show_create_table
Value: 0
*************************** 71. row ***************************
Variable_name: Com_show_databases
Value: 0
*************************** 72. row ***************************
Variable_name: Com_show_errors
Value: 0
*************************** 73. row ***************************
Variable_name: Com_show_fields
Value: 0
*************************** 74. row ***************************
Variable_name: Com_show_grants
Value: 0
*************************** 75. row ***************************
Variable_name: Com_show_innodb_status
Value: 1
*************************** 76. row ***************************
Variable_name: Com_show_keys
Value: 0
*************************** 77. row ***************************
Variable_name: Com_show_logs
Value: 0
*************************** 78. row ***************************
Variable_name: Com_show_master_status
Value: 0
*************************** 79. row ***************************
Variable_name: Com_show_ndb_status
Value: 0
*************************** 80. row ***************************
Variable_name: Com_show_new_master
Value: 0
*************************** 81. row ***************************
Variable_name: Com_show_open_tables
Value: 0
*************************** 82. row ***************************
Variable_name: Com_show_privileges
Value: 0
*************************** 83. row ***************************
Variable_name: Com_show_processlist
Value: 0
*************************** 84. row ***************************
Variable_name: Com_show_slave_hosts
Value: 0
*************************** 85. row ***************************
Variable_name: Com_show_slave_status
Value: 0
*************************** 86. row ***************************
Variable_name: Com_show_status
Value: 2
*************************** 87. row ***************************
Variable_name: Com_show_storage_engines
Value: 0
*************************** 88. row ***************************
Variable_name: Com_show_tables
Value: 0
*************************** 89. row ***************************
Variable_name: Com_show_triggers
Value: 0
*************************** 90. row ***************************
Variable_name: Com_show_variables
Value: 0
*************************** 91. row ***************************
Variable_name: Com_show_warnings
Value: 0
*************************** 92. row ***************************
Variable_name: Com_slave_start
Value: 0
*************************** 93. row ***************************
Variable_name: Com_slave_stop
Value: 0
*************************** 94. row ***************************
Variable_name: Com_stmt_close
Value: 0
*************************** 95. row ***************************
Variable_name: Com_stmt_execute
Value: 0
*************************** 96. row ***************************
Variable_name: Com_stmt_fetch
Value: 0
*************************** 97. row ***************************
Variable_name: Com_stmt_prepare
Value: 0
*************************** 98. row ***************************
Variable_name: Com_stmt_reset
Value: 0
*************************** 99. row ***************************
Variable_name: Com_stmt_send_long_data
Value: 0
*************************** 100. row ***************************
Variable_name: Com_truncate
Value: 0
*************************** 101. row ***************************
Variable_name: Com_unlock_tables
Value: 0
*************************** 102. row ***************************
Variable_name: Com_update
Value: 0
*************************** 103. row ***************************
Variable_name: Com_update_multi
Value: 0
*************************** 104. row ***************************
Variable_name: Com_xa_commit
Value: 0
*************************** 105. row ***************************
Variable_name: Com_xa_end
Value: 0
*************************** 106. row ***************************
Variable_name: Com_xa_prepare
Value: 0
*************************** 107. row ***************************
Variable_name: Com_xa_recover
Value: 0
*************************** 108. row ***************************
Variable_name: Com_xa_rollback
Value: 0
*************************** 109. row ***************************
Variable_name: Com_xa_start
Value: 0
*************************** 110. row ***************************
Variable_name: Compression
Value: OFF
*************************** 111. row ***************************
Variable_name: Connections
Value: 275608
*************************** 112. row ***************************
Variable_name: Created_tmp_disk_tables
Value: 0
*************************** 113. row ***************************
Variable_name: Created_tmp_files
Value: 160
*************************** 114. row ***************************
Variable_name: Created_tmp_tables
Value: 2
*************************** 115. row ***************************
Variable_name: Delayed_errors
Value: 0
*************************** 116. row ***************************
Variable_name: Delayed_insert_threads
Value: 0
*************************** 117. row ***************************
Variable_name: Delayed_writes
Value: 0
*************************** 118. row ***************************
Variable_name: Flush_commands
Value: 1
*************************** 119. row ***************************
Variable_name: Handler_commit
Value: 0
*************************** 120. row ***************************
Variable_name: Handler_delete
Value: 0
*************************** 121. row ***************************
Variable_name: Handler_discover
Value: 0
*************************** 122. row ***************************
Variable_name: Handler_prepare
Value: 0
*************************** 123. row ***************************
Variable_name: Handler_read_first
Value: 0
*************************** 124. row ***************************
Variable_name: Handler_read_key
Value: 0
*************************** 125. row ***************************
Variable_name: Handler_read_next
Value: 0
*************************** 126. row ***************************
Variable_name: Handler_read_prev
Value: 0
*************************** 127. row ***************************
Variable_name: Handler_read_rnd
Value: 0
*************************** 128. row ***************************
Variable_name: Handler_read_rnd_next
Value: 227
*************************** 129. row ***************************
Variable_name: Handler_rollback
Value: 0
*************************** 130. row ***************************
Variable_name: Handler_savepoint
Value: 0
*************************** 131. row ***************************
Variable_name: Handler_savepoint_rollback
Value: 0
*************************** 132. row ***************************
Variable_name: Handler_update
Value: 0
*************************** 133. row ***************************
Variable_name: Handler_write
Value: 358
*************************** 134. row ***************************
Variable_name: Innodb_buffer_pool_pages_data
Value: 501
*************************** 135. row ***************************
Variable_name: Innodb_buffer_pool_pages_dirty
Value: 5
*************************** 136. row ***************************
Variable_name: Innodb_buffer_pool_pages_flushed
Value: 8038482
*************************** 137. row ***************************
Variable_name: Innodb_buffer_pool_pages_free
Value: 0
*************************** 138. row ***************************
Variable_name: Innodb_buffer_pool_pages_latched
Value: 7
*************************** 139. row ***************************
Variable_name: Innodb_buffer_pool_pages_misc
Value: 11
*************************** 140. row ***************************
Variable_name: Innodb_buffer_pool_pages_total
Value: 512
*************************** 141. row ***************************
Variable_name: Innodb_buffer_pool_read_ahead_rnd
Value: 23210107
*************************** 142. row ***************************
Variable_name: Innodb_buffer_pool_read_ahead_seq
Value: 63905948
*************************** 143. row ***************************
Variable_name: Innodb_buffer_pool_read_requests
Value: 43197939155
*************************** 144. row ***************************
Variable_name: Innodb_buffer_pool_reads
Value: 499689385
*************************** 145. row ***************************
Variable_name: Innodb_buffer_pool_wait_free
Value: 0
*************************** 146. row ***************************
Variable_name: Innodb_buffer_pool_write_requests
Value: 60487082
*************************** 147. row ***************************
Variable_name: Innodb_data_fsyncs
Value: 11785833
*************************** 148. row ***************************
Variable_name: Innodb_data_pending_fsyncs
Value: 0
*************************** 149. row ***************************
Variable_name: Innodb_data_pending_reads
Value: 0
*************************** 150. row ***************************
Variable_name: Innodb_data_pending_writes
Value: 0
*************************** 151. row ***************************
Variable_name: Innodb_data_read
Value: 27743085907968
*************************** 152. row ***************************
Variable_name: Innodb_data_reads
Value: 624111101
*************************** 153. row ***************************
Variable_name: Innodb_data_writes
Value: 17135371
*************************** 154. row ***************************
Variable_name: Innodb_data_written
Value: 272150367744
*************************** 155. row ***************************
Variable_name: Innodb_dblwr_pages_written
Value: 8038482
*************************** 156. row ***************************
Variable_name: Innodb_dblwr_writes
Value: 297489
*************************** 157. row ***************************
Variable_name: Innodb_log_waits
Value: 0
*************************** 158. row ***************************
Variable_name: Innodb_log_write_requests
Value: 8761455
*************************** 159. row ***************************
Variable_name: Innodb_log_writes
Value: 9854707
*************************** 160. row ***************************
Variable_name: Innodb_os_log_fsyncs
Value: 9915308
*************************** 161. row ***************************
Variable_name: Innodb_os_log_pending_fsyncs
Value: 0
*************************** 162. row ***************************
Variable_name: Innodb_os_log_pending_writes
Value: 0
*************************** 163. row ***************************
Variable_name: Innodb_os_log_written
Value: 8713881088
*************************** 164. row ***************************
Variable_name: Innodb_page_size
Value: 16384
*************************** 165. row ***************************
Variable_name: Innodb_pages_created
Value: 127040
*************************** 166. row ***************************
Variable_name: Innodb_pages_read
Value: 1693305750
*************************** 167. row ***************************
Variable_name: Innodb_pages_written
Value: 8038482
*************************** 168. row ***************************
Variable_name: Innodb_row_lock_current_waits
Value: 0
*************************** 169. row ***************************
Variable_name: Innodb_row_lock_time
Value: 1652073
*************************** 170. row ***************************
Variable_name: Innodb_row_lock_time_avg
Value: 188
*************************** 171. row ***************************
Variable_name: Innodb_row_lock_time_max
Value: 51016
*************************** 172. row ***************************
Variable_name: Innodb_row_lock_waits
Value: 8765
*************************** 173. row ***************************
Variable_name: Innodb_rows_deleted
Value: 388078
*************************** 174. row ***************************
Variable_name: Innodb_rows_inserted
Value: 3360570
*************************** 175. row ***************************
Variable_name: Innodb_rows_read
Value: 68002285940
*************************** 176. row ***************************
Variable_name: Innodb_rows_updated
Value: 1553227
*************************** 177. row ***************************
Variable_name: Key_blocks_not_flushed
Value: 0
*************************** 178. row ***************************
Variable_name: Key_blocks_unused
Value: 322999
*************************** 179. row ***************************
Variable_name: Key_blocks_used
Value: 6288
*************************** 180. row ***************************
Variable_name: Key_read_requests
Value: 7290692
*************************** 181. row ***************************
Variable_name: Key_reads
Value: 926308
*************************** 182. row ***************************
Variable_name: Key_write_requests
Value: 1318656
*************************** 183. row ***************************
Variable_name: Key_writes
Value: 692107
*************************** 184. row ***************************
Variable_name: Last_query_cost
Value: 0.000000
*************************** 185. row ***************************
Variable_name: Max_used_connections
Value: 459
*************************** 186. row ***************************
Variable_name: Not_flushed_delayed_rows
Value: 0
*************************** 187. row ***************************
Variable_name: Open_files
Value: 2400
*************************** 188. row ***************************
Variable_name: Open_streams
Value: 0
*************************** 189. row ***************************
Variable_name: Open_tables
Value: 1931
*************************** 190. row ***************************
Variable_name: Opened_tables
Value: 0
*************************** 191. row ***************************
Variable_name: Prepared_stmt_count
Value: 0
*************************** 192. row ***************************
Variable_name: Qcache_free_blocks
Value: 5997
*************************** 193. row ***************************
Variable_name: Qcache_free_memory
Value: 45175024
*************************** 194. row ***************************
Variable_name: Qcache_hits
Value: 48939926
*************************** 195. row ***************************
Variable_name: Qcache_inserts
Value: 28589030
*************************** 196. row ***************************
Variable_name: Qcache_lowmem_prunes
Value: 1368595
*************************** 197. row ***************************
Variable_name: Qcache_not_cached
Value: 2718220
*************************** 198. row ***************************
Variable_name: Qcache_queries_in_cache
Value: 9254
*************************** 199. row ***************************
Variable_name: Qcache_total_blocks
Value: 24834
*************************** 200. row ***************************
Variable_name: Questions
Value: 94454573
*************************** 201. row ***************************
Variable_name: Rpl_status
Value: NULL
*************************** 202. row ***************************
Variable_name: Select_full_join
Value: 0
*************************** 203. row ***************************
Variable_name: Select_full_range_join
Value: 0
*************************** 204. row ***************************
Variable_name: Select_range
Value: 0
*************************** 205. row ***************************
Variable_name: Select_range_check
Value: 0
*************************** 206. row ***************************
Variable_name: Select_scan
Value: 2
*************************** 207. row ***************************
Variable_name: Slave_open_temp_tables
Value: 0
*************************** 208. row ***************************
Variable_name: Slave_retried_transactions
Value: 0
*************************** 209. row ***************************
Variable_name: Slave_running
Value: OFF
*************************** 210. row ***************************
Variable_name: Slow_launch_threads
Value: 0
*************************** 211. row ***************************
Variable_name: Slow_queries
Value: 0
*************************** 212. row ***************************
Variable_name: Sort_merge_passes
Value: 0
*************************** 213. row ***************************
Variable_name: Sort_range
Value: 0
*************************** 214. row ***************************
Variable_name: Sort_rows
Value: 0
*************************** 215. row ***************************
Variable_name: Sort_scan
Value: 0
*************************** 216. row ***************************
Variable_name: Table_locks_immediate
Value: 43499188
*************************** 217. row ***************************
Variable_name: Table_locks_waited
Value: 43
*************************** 218. row ***************************
Variable_name: Tc_log_max_pages_used
Value: 0
*************************** 219. row ***************************
Variable_name: Tc_log_page_size
Value: 0
*************************** 220. row ***************************
Variable_name: Tc_log_page_waits
Value: 0
*************************** 221. row ***************************
Variable_name: Threads_cached
Value: 43
*************************** 222. row ***************************
Variable_name: Threads_connected
Value: 416
*************************** 223. row ***************************
Variable_name: Threads_created
Value: 651
*************************** 224. row ***************************
Variable_name: Threads_running
Value: 4
*************************** 225. row ***************************
Variable_name: Uptime
Value: 615425
*************************** 226. row ***************************
Variable_name: Uptime_since_flush_status
Value: 615425
226 rows in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
-----------------------------
Johnny Withers
601.209.4985
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
