Announcement

Announcement Module
Collapse
No announcement yet.

Help/advice for my.cnf on dedicated DBServer

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

  • Help/advice for my.cnf on dedicated DBServer

    Hello,

    I need some help/advice for my dedicated DBServer.

    Here are the server specs:

    2 x Dual Core Xeon 5110
    6 GB DDR
    2 x 73GB RAID
    Linux version 2.6.18-3-686-bigmem (Debian 2.6.18-8~bpo.1) (nobse@backports.org) (gcc version 3.3.5 (Debian 1:3.3.5-13))

    Only MySQL 4.1.22-standard is running on the server.
    DBServer is connected to WebServer with Gb link.

    Persistent connection is used in PHP script.

    I would like to know if my.cnf is well optimized for such server. Here is:

    [mysqld]port=3306bind-address=192.168.1.2set-variable = max_connections=1000safe-show-databaseskip-name-resolvekey_buffer = 128Mmyisam_sort_buffer_size = 128Mjoin_buffer_size = 1Mread_buffer_size = 1Msort_buffer_size = 8Mtable_cache = 1800thread_cache_size = 284tmp_table_size = 64Mmax_allowed_packet = 64Mthread_concurrency = 2query_cache_limit = 2Mquery_cache_size = 400Mquery_cache_type = 1query_prealloc_size = 16384query_alloc_block_size = 16384



    and here extended-status:

    +----------------------------+-------------+| Variable_name | Value |+----------------------------+-------------+| Aborted_clients | 7559423 || Aborted_connects | 709324 || Binlog_cache_disk_use | 0 || Binlog_cache_use | 0 || Bytes_received | 3227180633 || Bytes_sent | 2698085351 || Com_admin_commands | 692807724 || Com_alter_db | 0 || Com_alter_table | 11 || Com_analyze | 0 || Com_backup_table | 0 || Com_begin | 0 || Com_change_db | 698742429 || Com_change_master | 0 || Com_check | 0 || Com_checksum | 0 || Com_commit | 0 || Com_create_db | 0 || Com_create_function | 0 || Com_create_index | 0 || Com_create_table | 0 || Com_dealloc_sql | 0 || Com_delete | 16725128 || Com_delete_multi | 0 || Com_do | 0 || Com_drop_db | 0 || Com_drop_function | 0 || Com_drop_index | 0 || Com_drop_table | 0 || Com_drop_user | 0 || Com_execute_sql | 0 || Com_flush | 1 || Com_grant | 0 || Com_ha_close | 0 || Com_ha_open | 0 || Com_ha_read | 0 || Com_help | 0 || Com_insert | 54970627 || Com_insert_select | 0 || Com_kill | 0 || Com_load | 0 || Com_load_master_data | 0 || Com_load_master_table | 0 || Com_lock_tables | 21 || Com_optimize | 22302 || Com_preload_keys | 0 || Com_prepare_sql | 0 || Com_purge | 0 || Com_purge_before_date | 0 || Com_rename_table | 0 || Com_repair | 27300 || Com_replace | 0 || Com_replace_select | 0 || Com_reset | 0 || Com_restore_table | 0 || Com_revoke | 0 || Com_revoke_all | 0 || Com_rollback | 0 || Com_savepoint | 0 || Com_select | 1964781708 || Com_set_option | 3050 || Com_show_binlog_events | 0 || Com_show_binlogs | 97 || Com_show_charsets | 626 || Com_show_collations | 626 || Com_show_column_types | 0 || Com_show_create_db | 0 || Com_show_create_table | 647 || Com_show_databases | 96 || Com_show_errors | 0 || Com_show_fields | 839 || Com_show_grants | 277 || Com_show_innodb_status | 0 || Com_show_keys | 71 || Com_show_logs | 0 || Com_show_master_status | 0 || Com_show_ndb_status | 0 || Com_show_new_master | 0 || Com_show_open_tables | 1 || Com_show_privileges | 0 || Com_show_processlist | 0 || Com_show_slave_hosts | 0 || Com_show_slave_status | 0 || Com_show_status | 40 || Com_show_storage_engines | 3 || Com_show_tables | 3693 || Com_show_variables | 1470 || Com_show_warnings | 0 || Com_slave_start | 0 || Com_slave_stop | 0 || Com_stmt_close | 0 || Com_stmt_execute | 0 || Com_stmt_prepare | 0 || Com_stmt_reset | 0 || Com_stmt_send_long_data | 0 || Com_truncate | 0 || Com_unlock_tables | 21 || Com_update | 520363594 || Com_update_multi | 0 || Connections | 6647522 || Created_tmp_disk_tables | 0 || Created_tmp_files | 12855 || Created_tmp_tables | 139543245 || Delayed_errors | 0 || Delayed_insert_threads | 0 || Delayed_writes | 0 || Flush_commands | 1 || Handler_commit | 0 || Handler_delete | 24485830 || Handler_discover | 0 || Handler_read_first | 3570653 || Handler_read_key | 2616539365 || Handler_read_next | 4223065123 || Handler_read_prev | 1225739368 || Handler_read_rnd | 303268371 || Handler_read_rnd_next | 940210884 || Handler_rollback | 0 || Handler_update | 4211170258 || Handler_write | 358121899 || Key_blocks_not_flushed | 0 || Key_blocks_unused | 109055 || Key_blocks_used | 30399 || Key_read_requests | 20911721687 || Key_reads | 42038656 || Key_write_requests | 42950661 || Key_writes | 26590746 || Max_used_connections | 1001 || Not_flushed_delayed_rows | 0 || Open_files | 1311 || Open_streams | 0 || Open_tables | 1664 || Opened_tables | 2355973 || Qcache_free_blocks | 5692 || Qcache_free_memory | 395729176 || Qcache_hits | 792984928 || Qcache_inserts | 1689011587 || Qcache_lowmem_prunes | 104384 || Qcache_not_cached | 276446640 || Qcache_queries_in_cache | 19485 || Qcache_total_blocks | 44685 || Questions | 4051645296 || Rpl_status | NULL || Select_full_join | 0 || Select_full_range_join | 0 || Select_range | 160490 || Select_range_check | 0 || Select_scan | 49379051 || Slave_open_temp_tables | 0 || Slave_retried_transactions | 0 || Slave_running | OFF || Slow_launch_threads | 281 || Slow_queries | 38068 || Sort_merge_passes | 6193 || Sort_range | 163303568 || Sort_rows | 1167894234 || Sort_scan | 141092778 || Table_locks_immediate | 1313186832 || Table_locks_waited | 1287658550 || Threads_cached | 282 || Threads_connected | 425 || Threads_created | 254787 || Threads_running | 8 || Uptime | 2063456 |+----------------------------+-------------+


    My big problem is the load average on peak hours.

    If you need more information, please tell me.

    Thanks for your help.

    Best Regards,
    javvy

  • #2
    How big in MB are your tables respectively your indexes?

    Does your application perform a lot of select/updates against the same table?

    Your table_locks_waited/table_locks_immediate ratio is very high which indicate either:
    1.
    That a lot of your queries aren't optimized and selects take a lot of time.

    2.
    Or that you are performing a lot of updates/selects against the same table. Where you pretty fast run into the big disadvantage with MyISAM's table locking.


    Suggestions is to check if you have any queries that take a long time.
    Or change to InnoDB tables since they have row level locking instead.

    Comment


    • #3
      Hello sterin,

      First of all, thanks for your reply.

      > How big in MB are your tables respectively your indexes?

      Database is 142,7 MB. I don't know how to see the size of the indexes (

      > Does your application perform a lot of select/updates against the same table?

      Yes, it's a HEAP table. Here is the structure of that table:

      CREATE TABLE `p` (
      `t` int(5) unsigned NOT NULL default '0',
      `p_id` varchar(20) character set latin1 collate latin1_bin NOT NULL default '',
      `ip` varchar(15) NOT NULL default '',
      `pt` smallint(5) unsigned NOT NULL default '0',
      `u` bigint(20) unsigned NOT NULL default '0',
      `d` bigint(20) unsigned NOT NULL default '0',
      `t_g` bigint(20) unsigned NOT NULL default '0',
      `s` enum('yes','no') NOT NULL default 'no',
      `st` datetime NOT NULL default '0000-00-00 00:00:00',
      `l_a` datetime NOT NULL default '0000-00-00 00:00:00',
      `conn` enum('yes','no') NOT NULL default 'yes',
      UNIQUE KEY `t_p_id` (`t`,`p_id`),
      KEY `t_s` (`t`,`s`),
      KEY `l_a` (`l_a`),
      KEY `t` (`t`)
      ) ENGINE=HEAP DEFAULT CHARSET=latin1;


      For example, this query: SELECT t FROM p GROUP BY t don't use the index. How can I correct that ?

      > That a lot of your queries aren't optimized and selects take a lot of time.

      Yes, I'm going to log slow queries and report here. Perahps could you help me to optimize them )

      Best Regards,
      javvy

      Comment


      • #4
        Quote:

        For example, this query: SELECT t FROM p GROUP BY t don't use the index. How can I correct that ?



        The default index for a MEMORY table is a HASH index they are very fast when finding const values. But you can't use them for ranges or numerical comparison (sorting).
        Which is what a GROUP BY needs to do.

        So when you create the table you must define that the index should be a BTREE.

        So something like this should solve your problem:

        CREATE TABLE `p` (`t` int(5) unsigned NOT NULL default '0',`p_id` varchar(20) character set latin1 collate latin1_bin NOT NULL default '',`ip` varchar(15) NOT NULL default '',`pt` smallint(5) unsigned NOT NULL default '0',`u` bigint(20) unsigned NOT NULL default '0',`d` bigint(20) unsigned NOT NULL default '0',`t_g` bigint(20) unsigned NOT NULL default '0',`s` enum('yes','no') NOT NULL default 'no',`st` datetime NOT NULL default '0000-00-00 00:00:00',`l_a` datetime NOT NULL default '0000-00-00 00:00:00',`conn` enum('yes','no') NOT NULL default 'yes',UNIQUE INDEX`t_p_id` USING BTREE (`t`,`p_id`),INDEX t_s USING BTREE (`t`,`s`),INDEX l_a USING BTREE (`l_a`)) ENGINE=HEAP DEFAULT CHARSET=latin1;

        Note that I removed your index on t since t is the first part of t_s and hence it is redundant and not needed.

        Comment


        • #5
          Hello sterin,

          Thank you very much, query is now using the index and server load is better. Great !

          The next table who causes me a headache is the following:


          CREATE TABLE `users` ( `id` int(7) unsigned NOT NULL auto_increment, `u` varchar(25) NOT NULL default '', `p` varchar(40) NOT NULL default '', `st` varchar(20) character set latin1 collate latin1_bin NOT NULL default '', `em` varchar(80) NOT NULL default '', `sta` enum('pending','confirmed') NOT NULL default 'pending', `ad` datetime NOT NULL default '0000-00-00 00:00:00', `la_lo` datetime NOT NULL default '0000-00-00 00:00:00', `la_acc` datetime NOT NULL default '0000-00-00 00:00:00', `es` varchar(20) character set latin1 collate latin1_bin NOT NULL default '', `pr` enum('strong','normal','low') NOT NULL default 'normal', `sty` int(10) default '1', `lang` varchar(20) NOT NULL default '3', `info` char(0) default NULL, `apms` enum('yes','no') NOT NULL default 'yes', `ip` varchar(15) NOT NULL default '', `cl` tinyint(3) unsigned NOT NULL default '0', `av` varchar(100) NOT NULL default '', `pl` bigint(20) unsigned NOT NULL default '0', `nl` bigint(20) unsigned NOT NULL default '0', `t` varchar(30) NOT NULL default '', `do` int(5) unsigned NOT NULL default '0', `cy` int(10) unsigned NOT NULL default '0', `not` enum('yes','no') NOT NULL default 'yes', `en` varchar(10) NOT NULL default 'Yes', `mod` varchar(100) default NULL, `ge` varchar(6) NOT NULL default '', `ct` varchar(25) NOT NULL default '', `age` int(3) NOT NULL default '0', PRIMARY KEY (`id`), UNIQUE KEY `pl` (`pl`), KEY `sta_ad` (`sta`,`added`), KEY `ip` (`ip`), KEY `pl` (`pl`), KEY `nl` (`nl`), KEY `cy` (`cy`), KEY `sta` (`sta`), KEY `la_acc` (`la_acc`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=545231 ;


          These queries are not using indexes:

          SELECT * FROM users WHERE id = 12 AND en='yes' AND sta = 'confirmed'SELECT id FROM users WHERE ip='xxx.xxx.xxx.xxx' AND en='yes' AND sta='confirmed' LIMIT 1SELECT id, pl, nl, cl FROM users WHERE ip='xxx.xxx.xxx.xxx' AND en = 'yes' ORDER BY la_acc DESC LIMIT 1


          Any solutions to solve this ?

          Another time...thanks for your help )

          Best Regards,
          javvy

          Comment


          • #6
            To solve these two queries:

            SELECT id FROM users WHERE ip='xxx.xxx.xxx.xxx' AND en='yes' AND sta='confirmed' LIMIT 1SELECT id, pl, nl, cl FROM users WHERE ip='xxx.xxx.xxx.xxx' AND en = 'yes' ORDER BY la_acc DESC LIMIT 1

            You can create a combined index on users(ip, en, sta).
            Since (ip,en) are the first columns in this index mysql will be able to use it for the second query also.
            And by creating this index mysql will also be able to use this index instead of your ip index.
            So you can drop the ip index after you have created this.

            To solve this query:

            SELECT * FROM users WHERE id = 12 AND en='yes' AND sta = 'confirmed'

            You should create a index on (id, en, sta).
            And the same applies here for redundancy with indexes.
            If you have an index on only the first column you can remove that since mysql can use this index instead.

            Comment


            • #7
              Hi sterin,

              After reflexion, I removed the "sta='confirmed'" in the queries, because all users are "confirmed" confused:

              So now, the indexes are the following:

              PRIMARY KEY (`id`),UNIQUE KEY `u` (`u`),KEY `sta_ad` (`sta`,`ad`),KEY `pl` (`pl`),KEY `nl` (`nl`),KEY `cy` (`cy`),KEY `la_acc` (`la_acc`)KEY `ip_en` (`ip`,`en`),KEY `id_en` (`id`,`en`)


              but an EXPLAIN of the queries give me that:

              EXPLAIN SELECT * FROM users WHERE id = 12 AND en='yes'id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE users const PRIMARY,id_en PRIMARY 4 const 1 EXPLAIN SELECT id FROM users WHERE ip='xxx.xxx.xxx.xxx' AND en='yes' LIMIT 1id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE users ref ip_en ip_en 25 const,const 2 Using whereSELECT id, pl, nl, cl FROM users WHERE ip='xxx.xxx.xxx.xxx' AND en = 'yes' ORDER BY la_acc DESC LIMIT 1id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE users ref ip_en ip_en 25 const,const 2 Using where; Using filesort


              What have I made wrong ?

              Thanks.

              Best Regards,
              javvy

              Comment


              • #8
                You haven't done anything wrong.

                That's about as good as it can get.

                The only other addition that you can do is to add the la_acc last in the ip_en index.

                That way you will get rid of the filesort on the last query also.

                And then you can't speed it up any more unless it is identical queries that are issued all the time which is when the query cache can make wonders for you.

                Comment


                • #9
                  It's ok (only 'Using where' is used now).

                  On the other hand, I have something strange with my HEAP table `p`. It seems that now this table is limited to 99270 record, but I'm sure this should be higher. I really don't know why this limitation. Have you an idea ? It's like that since I have modified the indexes with USING BTREE.

                  Thanks for your help...you're helping me a lot.


                  Edit

                  Sorry I found the solution: max_heap_table_size - Google is my friend too

                  Comment


                  • #10
                    Good for you! -> Google for you!

                    Comment

                    Working...
                    X