GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Mysql 5.0 / Debian / INNODB performance

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Mysql 5.0 / Debian / INNODB performance

    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

  • #2
    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).

    Comment


    • #3
      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

      Comment


      • #4
        You can try to increase it up to 1.5Gb.

        Comment


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

          Comment


          • #6
            gozer


            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.

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

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

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

            Comment


            • #7
              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?

              Comment


              • #8
                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.

                Comment


                • #9
                  I have not changed sort_buffer_size what value could i set there? sort buffer size = 2.097.144

                  But i notice now that it works faster, i had alot of users complaining that it was slow at loading screens.


                  But when i check CPU load the machine isnt very busy.

                  One more question when i restart the mysql service it takes 1 hour that the cpu load is back to idle from 100%. Is there a way to let the mysql server to become faster ready.

                  Comment


                  • #10
                    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.

                    Comment


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

                      Comment


                      • #12
                        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.

                        Comment

                        Working...
                        X