Home » Performance » MySQL » Mysql 5.0 / Debian / INNODB performance
| Mysql 5.0 / Debian / INNODB performance [message #2136] |
Mon, 29 October 2007 08:42  |
 gozer
Messages: 11
Registered: October 2007 |
Junior Member |
|
|
Hi,
Im running a Intel Xeon Duall cpu 32bit 3ghz, 4gb ram , RAID1 with Linux Debian OS on a Gbit network.
With 22databases each db is around 100mb. The thing is it was first fast at the beginning now its slower. I have edited some values in my.cnf. Now im wondering how can i do some more optimalisation. Does this look OK? Im running on RAID 1 with DAS SCSI 10.000RPM discs with all partitions on 1 disc.
top - 13:46:00 up 18 days, 16:21, 1 user, load average: 0.21, 0.11, 0.08
Tasks: 72 total, 1 running, 71 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.2%us, 0.2%sy, 0.0%ni, 98.7%id, 0.8%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 3897492k total, 3459648k used, 437844k free, 160496k buffers
Swap: 1502036k total, 36k used, 1502000k free, 1366960k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2254 mysql 15 0 1449m 1.3g 6008 S 1 35.6 1242:19 mysqld
[Updated on: Mon, 29 October 2007 08:44]
|
|
|
| Re: Mysql 5.0 / Debian / INNODB performance [message #2144 is a reply to message #2136 ] |
Thu, 01 November 2007 02:32  |
|
Server is pretty cold now, but to make it even colder I'd suggest to give more memory to innodb (show me your "show global status" command results please to let me figure out an optimal value).
Alexey Kovyrin, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
|
|
|
| Re: Mysql 5.0 / Debian / INNODB performance [message #2147 is a reply to message #2136 ] |
Thu, 01 November 2007 07:51 |
gozer
Messages: 11
Registered: October 2007 |
Junior Member |
|
|
mysql> show global status;
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 1253 |
| Aborted_connects | 77 |
| Binlog_cache_disk_use | 376 |
| Binlog_cache_use | 1816678 |
| Bytes_received | 574760579 |
| Bytes_sent | 1558608980 |
| Com_admin_commands | 35 |
| Com_alter_db | 0 |
| Com_alter_table | 26316 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 4672226 |
| Com_change_master | 0 |
| Com_check | 4311 |
| Com_checksum | 0 |
| Com_commit | 58438 |
| Com_create_db | 11 |
| Com_create_function | 0 |
| Com_create_index | 2000 |
| Com_create_table | 36925 |
| Com_create_user | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 555049 |
| Com_delete_multi | 11 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 780 |
| Com_drop_table | 40758 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 24 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 1247376 |
| Com_insert_select | 280275 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 828 |
| Com_optimize | 54247 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 2 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 1705 |
| Com_savepoint | 0 |
| Com_select | 11673729 |
| Com_set_option | 240036 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 41 |
| Com_show_charsets | 93 |
| Com_show_collations | 93 |
| Com_show_column_types | 0 |
| Com_show_create_db | 3 |
| Com_show_create_table | 89961 |
| Com_show_databases | 56937 |
| Com_show_errors | 0 |
| Com_show_fields | 90636 |
| Com_show_grants | 27 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 725 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 4 |
| Com_show_privileges | 0 |
| Com_show_processlist | 37 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 53 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 50551 |
| Com_show_triggers | 89186 |
| Com_show_variables | 255 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 828 |
| Com_update | 1001557 |
| Com_update_multi | 1 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 1959593 |
| Created_tmp_disk_tables | 836017 |
| Created_tmp_files | 51450 |
| Created_tmp_tables | 1726483 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 2 |
| Handler_commit | 5084745 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 3633220 |
| Handler_read_first | 1364137 |
| Handler_read_key | 1452831996 |
| Handler_read_next | 582231462 |
| Handler_read_prev | 173487 |
| Handler_read_rnd | 298113028 |
| Handler_read_rnd_next | 2158821521 |
| Handler_rollback | 1759 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 254244 |
| Handler_write | 542531149 |
| Innodb_buffer_pool_pages_data | 62464 |
| Innodb_buffer_pool_pages_dirty | 34 |
| Innodb_buffer_pool_pages_flushed | 7350197 |
| Innodb_buffer_pool_pages_free | 1 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 3071 |
| Innodb_buffer_pool_pages_total | 65536 |
| Innodb_buffer_pool_read_ahead_rnd | 8451 |
| Innodb_buffer_pool_read_ahead_seq | 6136 |
| Innodb_buffer_pool_read_requests | 2319129462 |
| Innodb_buffer_pool_reads | 589656 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 976096555 |
| Innodb_data_fsyncs | 1311097 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 1956237312 |
| Innodb_data_reads | 674219 |
| Innodb_data_writes | 9942635 |
| Innodb_data_written | 3223045120 |
| Innodb_dblwr_pages_written | 7350197 |
| Innodb_dblwr_writes | 212665 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 81764958 |
| Innodb_log_writes | 4347348 |
| Innodb_os_log_fsyncs | 891335 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 2821309440 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 633079 |
| Innodb_pages_read | 1167842 |
| Innodb_pages_written | 7350197 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 513 |
| Innodb_row_lock_time_avg | 9 |
| Innodb_row_lock_time_max | 464 |
| Innodb_row_lock_waits | 53 |
| Innodb_rows_deleted | 307079 |
| Innodb_rows_inserted | 146527584 |
| Innodb_rows_read | 3005696002 |
| Innodb_rows_updated | 2014619 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 14497 |
| Key_blocks_used | 1183 |
| Key_read_requests | 615267285 |
| Key_reads | 1438475 |
| Key_write_requests | 137313255 |
| Key_writes | 823 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 144 |
| Ndb_cluster_node_id | 0 |
| Ndb_config_from_host | |
| Ndb_config_from_port | 0 |
| Ndb_number_of_data_nodes | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 21 |
| Open_streams | 0 |
| Open_tables | 1250 |
| Opened_tables | 623644 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 5923 |
| Qcache_free_memory | 24705424 |
| Qcache_hits | 32465421 |
| Qcache_inserts | 8721545 |
| Qcache_lowmem_prunes | 1165770 |
| Qcache_not_cached | 2992062 |
| Qcache_queries_in_cache | 21700 |
| Qcache_total_blocks | 51064 |
| Questions | 54872911 |
| Rpl_status | NULL |
| Select_full_join | 408691 |
| Select_full_range_join | 4 |
| Select_range | 263818 |
| Select_range_check | 0 |
| Select_scan | 1236046 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 766 |
| Sort_merge_passes | 30862 |
| Sort_range | 702473 |
| Sort_rows | 266341914 |
| Sort_scan | 595652 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 18905948 |
| Table_locks_waited | 15 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 14 |
| Threads_connected | 87 |
| Threads_created | 8332 |
| Threads_running | 1 |
| Uptime | 1868019 |
+-----------------------------------+------------+
251 rows in set (0.01 sec)
The innodb buffer pool size is set to 1024MB should i give more, howmuch more ?
Thanks in advance
[Updated on: Thu, 01 November 2007 07:54]
|
|
| |
| Re: Mysql 5.0 / Debian / INNODB performance [message #2151 is a reply to message #2150 ] |
Thu, 01 November 2007 11:25 |
gozer
Messages: 11
Registered: October 2007 |
Junior Member |
|
|
|
Ok ill do that tonight. Anything else looks OK? When i run on XFS filesystem i will noticeable performance?
[Updated on: Thu, 01 November 2007 11:46]
|
|
| |
| Re: Mysql 5.0 / Debian / INNODB performance [message #2162 is a reply to message #2136 ] |
Fri, 02 November 2007 05:22 |
gozer
Messages: 11
Registered: October 2007 |
Junior Member |
|
|
Running with the new setting on the MYSQL.
top - 10:18:21 up 10:16, 1 user, load average: 0.00, 0.03, 0.00
Tasks: 69 total, 1 running, 68 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.1%us, 0.2%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 3897492k total, 3408788k used, 488704k free, 65144k buffers
Swap: 1502036k total, 0k used, 1502036k free, 1227320k cached
2256 mysql 15 0 1921m 1.8g 5980 S 2 47.9 139:06.71 mysqld
Edited some settings in my.cnf
# * Fine Tuning
# innodb_buffer_pool_size = 1024M <-- OLD Setting
innodb_buffer_pool_size = 1500M
innodb_additional_mem_pool_size = 20M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
# table_cache = 1250 <-- OLD Setting
table_cache = 2000
tmp_table_size = 75M
lower_case_table_names = 1
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 16
max_connections = 500
query_cache_limit = 1M
query_cache_size = 64M
Now i see my free RAM = 488mb left isnt this kinda to low?
[Updated on: Fri, 02 November 2007 05:24]
|
|
|
| Re: Mysql 5.0 / Debian / INNODB performance [message #2163 is a reply to message #2162 ] |
Fri, 02 November 2007 07:42 |
sterin
Messages: 324
Registered: March 2007
Location: Sweden |
Senior Member |
|
|
1.
You forgot increasing the sort_buffer_size.
Or has it already been increased?
2.
| gozer wrote on Fri, 02 November 2007 10:22 |
Now i see my free RAM = 488mb left isnt this kinda to low?
|
No, it is a bit difficult to see but you are reading the output from top wrong.
All OS is using free RAM as a file cache to avoid reading/writing to the disks.
This part of the RAM is "used" only if there is free RAM available, but it will still be reported as used by top.
I marked the key values A|B|.. below:
| gozer wrote on Fri, 02 November 2007 10:22 |
Running with the new setting on the MYSQL.
top - 10:18:21 up 10:16, 1 user, load average: 0.00, 0.03, 0.00
Tasks: 69 total, 1 running, 68 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.1%us, 0.2%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: A3897492k total, B3408788k used, C488704k free, 65144k buffers
Swap: 1502036k total, 0k used, 1502036k free, D1227320k cached
2256 mysql 15 0 1921m 1.8g 5980 S 2 47.9 139:06.71 mysqld
|
So in this case C is reporting how much free memory you have and this is usually a very low figure because Linux tries to use all free RAM as OS file cache.
But since the OS file cache gives back memory if some other process needs it better your actual free memory should be calculated by:
C(free) + D(os file cache) = ~1.5GB
So you are pretty safe.
3.
I have to ask.
Do you feel that you experience any performance problem with your database?
The reason I'm asking you this is because on both top outputs that you have posted the machine is basically idle.
And that is why I don't think that you actually need to optimize anything.
|
|
| | | |
| Re: Mysql 5.0 / Debian / INNODB performance [message #2206 is a reply to message #2204 ] |
Thu, 15 November 2007 13:26 |
sterin
Messages: 324
Registered: March 2007
Location: Sweden |
Senior Member |
|
|
| gozer wrote on Wed, 14 November 2007 09:49 |
does every user get a sort buffer size of 8M then? i have something like 150 user connecting to the dbs.
|
Only if it is needed.
The sort buffer is allocated when needed and freed when it is not needed any longer.
So if all 150 connections where to perform a query with a large ORDER BY at the same time then yes it could potentially consume 8*150 MB RAM.
But generally that chance is so very slim that you often set it higher to speed up the specific queries with ORDER BY's.
|
|
|
Goto Forum:
Current Time: Thu Jul 9 23:56:51 EDT 2009
Total time taken to generate the page: 0.02936 seconds |
Members
Search
Help
Register
Login
Home
