Mysql 5.0 / Debian / INNODB performance

[gozer]

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

[scoundrel]

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

[gozer]

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

[scoundrel]

You can try to increase it up to 1.5Gb.

[gozer]

Ok ill do that tonight. Anything else looks OK? When i run on XFS filesystem i will noticeable performance?

[sterin]

[B]gozer[/B]

Ok ill do that tonight. Anything else looks OK? When i run on XFS filesystem i will noticeable performance?

No, changing to XFS will most probably never be noticed. When looking at performance for different file systems some parts can sometimes be very fast. But these things are usually marginal because at the end of the day the data is stored/retrieved from physical disks and that hardware is so incredibly slow in comparison with RAM and CPU, that any optimizations you try to do in file system software is usually not noticed.

Now to some things that you should do to speed things up (tried to arrange them in order of impact on performance):
1.
This:
Select_full_join | 408691 |
indicates that quite a few of the queries that contain JOIN’s does not utilize an index for the join.
So find the queries that does not use index and add indexes to the tables.

This:
| Created_tmp_disk_tables | 836017 |
indicates that you could probably increase the sort_buffer or the tmp_table_size since it is creating temp files on disk instead of in RAM.

And since 1/5 of your queries is an update/insert command you could think about setting:
innodb_flush_log_at_trx_commit = 2
That way (depending on your hardware) you will gain a lot of insert/update speed while sacrificing some robustness in case of an operating system crash.

This:
Opened_tables | 623644 |
Indicates that you could maybe increase the table_open_cache variable because according to your statistics about every 20 queries MySQL needs to open a table.

There, now you have some things to play with. )

[gozer]

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?

[sterin]

You forgot increasing the sort_buffer_size.
Or has it already been increased?

[B]gozer wrote on Fri, 02 November 2007 10:22[/B]

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:

[B]gozer wrote on Fri, 02 November 2007 10:22[/B]

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.

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.

[sterin]

Depends on how large the result sets for your queries are.

But generally if you increase it to maybe 8M then you usually get a good result.

[gozer]

does every user get a sort buffer size of 8M then? i have something like 150 user connecting to the dbs.