Re: default my.cnf vs huge.cnf nearly same performance with

[Gleb Paharenko]

Hello.

> huge.cnf example.  What about that thread_concurrency setting in
> huge.cnf.. it doesn't seem to show up in a 'show variables' when using
> it.. is it deprecated?

In the manual it is meant that thead_concurrency is used on Solaris.
In the source files I've seen that thread_concurrency supported on those
platforms which have thr_setconcurrency function. See: 
  configure.in (AC_CHECK_FUNCS macros)
	http://dev.mysql.com/doc/mysql/en/server-system-variables.html



Matthew Lenz <matthew@stripped> wrote:
> [-- text/plain, encoding 7bit, charset: US-ASCII, 61 lines --]
> 
> infact .. the default debian config (some of these are just explicit
> defaults but this is what debian provides):
> 
> [mysqld]
> user            = mysql
> pid-file        = /var/run/mysqld/mysqld.pid
> socket          = /var/run/mysqld/mysqld.sock
> port            = 3306
> basedir         = /usr
> datadir         = /var/lib/mysql
> tmpdir          = /tmp
> language        = /usr/share/mysql/english
> skip-external-locking
> old_passwords   = 1
> key_buffer              = 16M
> max_allowed_packet      = 16M
> thread_stack            = 128K
> query_cache_limit       = 1048576
> query_cache_size        = 16777216
> query_cache_type        = 1
> log-bin                 = /var/log/mysql/mysql-bin.log
> max_binlog_size         = 104857600
> skip-bdb
> 
> outperforms the huge.cnf example:
> 
> [mysqld]
> user                    = mysql
> pid-file                = /var/run/mysqld/mysqld.pid
> socket                  = /var/run/mysqld/mysqld.sock
> port                    = 3306
> basedir                 = /usr
> datadir                 = /var/lib/mysql
> tmpdir                  = /tmp
> language                = /usr/share/mysql/english
> old_passwords           = 1
> key_buffer              = 384M
> max_allowed_packet      = 16M
> table_cache             = 512
> sort_buffer_size        = 2M
> read_buffer_size        = 2M
> read_rnd_buffer_size    = 8M
> myisam_sort_buffer_size = 64M
> query_cache_size        = 32M
> thread_concurrency      = 8
> log-bin                 = /var/log/mysql/mysql-bin.log
> server-id               = 1
> skip-bdb
> skip-external-locking
> 
> in almost every regard.  What gives? :)  This is a pretty beefy config:
> 
> dual 3ghz HT xeon .. 2gig 800mhz fsb mem .. U320 SCSI RAID5.  I've
> attached a compare-results for a few machines.  the only important ones
> are 1 and 2.  1 is debians my.cnf and 2 is the slightly modified
> huge.cnf example.  What about that thread_concurrency setting in
> huge.cnf.. it doesn't seem to show up in a 'show variables' when using
> it.. is it deprecated?
> 
> -Matt
> 
> [-- text/plain, encoding 7bit, charset: UTF-8, 117 lines, name: results.txt --]
> 
> The result logs which where found and the options:
> 1 mysql-Linux_2.4.27_2_686_smp_i686       : MySQL 4.1.11 Debian_4sarge1 log
> 2 mysql-Linux_2.4.27_2_686_smp_i686_db0_te: MySQL 4.1.11 Debian_4sarge1 log
> 3 mysql-Linux_2.4.27_2_686_smp_i686_db1   : MySQL 4.1.11 Debian_4sarge1 log
> 4 mysql-Linux_2.6.10-1.770_FC3smp_i686    : MySQL 4.1.12 standard
> 5 mysql-Linux_2.6.10-1.770_FC3smp_i686_rai: MySQL 4.1.12 standard
> 6 mysql-Linux_2.6.11-1.14_FC3_x86_64      : MySQL 4.1.11 standard
> 7 mysql-Linux_2.6.8_2_686_smp_i686_kevinz : MySQL 4.1.11 Debian_4sarge1 log
> 
>
> =============================================================================================
> Operation                           |      1|      2|      3|      4|      5|      6|
>      7|
>                                   
> |mysql-L|mysql-L|mysql-L|mysql-L|mysql-L|mysql-L|mysql-L|
>
> ---------------------------------------------------------------------------------------------
> Results per test in seconds:                                                         
>       |
>
> ---------------------------------------------------------------------------------------------
> ATIS                                |   8.00|   9.00|   8.00|  16.00|  17.00|  13.00|
>  32.00|
> alter-table                         |  14.00|  14.00|  13.00|  13.00|  10.00|  21.00|
>  49.00|
> big-tables                          |  10.00|  10.00|  10.00|  13.00|  12.00|  10.00|
>  36.00|
> connect                             | 108.00| 105.00|  99.00|  72.00|  71.00|  58.00|
> 394.00|
> create                              |  67.00|  89.00|  89.00| 223.00| 219.00|  98.00|
> 475.00|
> insert                              | 904.00| 908.00| 873.00| 854.00| 845.00|
> 959.00|3751.00|
> select                              |  76.00|  76.00|  73.00| 353.00| 351.00| 250.00|
> 291.00|
> wisconsin                           |   7.00|   7.00|   7.00|   6.00|   5.00|   5.00|
>  20.00|
>
> ---------------------------------------------------------------------------------------------
> The results per operation:                                                           
>       |
>
> ---------------------------------------------------------------------------------------------
> alter_table_add (100)               |   6.00|   6.00|   5.00|   5.00|   4.00|   9.00|
>  20.00|
> alter_table_drop (91)               |   6.00|   6.00|   6.00|   6.00|   4.00|   9.00|
>  18.00|
> connect (10000)                     |   6.00|   6.00|   6.00|   5.00|   5.00|   5.00|
>  28.00|
> connect+select_1_row (10000)        |   8.00|   8.00|   8.00|   7.00|   7.00|   7.00|
>  33.00|
> connect+select_simple (10000)       |   8.00|   7.00|   8.00|   6.00|   6.00|   6.00|
>  32.00|
> count (100)                         |   8.00|   9.00|   8.00|   9.00|   8.00|   6.00|
>  43.00|
> count_distinct (1000)               |   1.00|   0.00|   1.00|  11.00|  11.00|   6.00|
>   1.00|
> count_distinct_2 (1000)             |   0.00|   0.00|   0.00|  16.00|  15.00|   8.00|
>   0.00|
> count_distinct_big (120)            |   8.00|   8.00|   7.00|  19.00|  20.00|  14.00|
>  32.00|
> count_distinct_group (1000)         |   1.00|   1.00|   1.00|  14.00|  14.00|   7.00|
>   3.00|
> count_distinct_group_on_key (1000)  |   0.00|   0.00|   0.00|  15.00|  15.00|   8.00|
>   1.00|
> count_distinct_group_on_key_parts (1|   1.00|   1.00|   1.00|  14.00|  13.00|   7.00|
>   3.00|
> count_distinct_key_prefix (1000)    |   0.00|   1.00|   0.00|   8.00|   8.00|   4.00|
>   0.00|
> count_group_on_key_parts (1000)     |   1.00|   0.00|   1.00|  15.00|  15.00|   7.00|
>   4.00|
> count_on_key (50100)                |  22.00|  23.00|  21.00| 130.00| 131.00|  86.00|
>  69.00|
> create+drop (10000)                 |  13.00|  12.00|  12.00|  76.00|  71.00|  32.00|
>  43.00|
> create_MANY_tables (10000)          |  35.00|  56.00|  58.00|  72.00|  73.00|  27.00|
> 323.00|
> create_index (8)                    |   1.00|   1.00|   1.00|   1.00|   1.00|   1.00|
>   5.00|
> create_key+drop (10000)             |  14.00|  14.00|  14.00|  72.00|  72.00|  35.00|
>  48.00|
> create_table (31)                   |   0.00|   0.00|   0.00|   0.00|   1.00|   0.00|
>   0.00|
> delete_all_many_keys (1)            |  22.00|  21.00|  21.00|  31.00|  24.00| 134.00|
>  84.00|
> delete_big (1)                      |   0.00|   0.00|   1.00|   0.00|   0.00|   0.00|
>   0.00|
> delete_big_many_keys (128)          |  22.00|  21.00|  21.00|  31.00|  24.00| 134.00|
>  84.00|
> delete_key (10000)                  |   1.00|   1.00|   2.00|   1.00|   1.00|   0.00|
>   6.00|
> delete_range (12)                   |   5.00|   5.00|   4.00|   4.00|   4.00|   4.00|
>  16.00|
> drop_index (8)                      |   1.00|   1.00|   1.00|   1.00|   1.00|   2.00|
>   6.00|
> drop_table (28)                     |   0.00|   0.00|   0.00|   0.00|   0.00|   0.00|
>   0.00|
> drop_table_when_MANY_tables (10000) |   3.00|   4.00|   3.00|   2.00|   2.00|   2.00|
>  21.00|
> insert (350768)                     |  55.00|  55.00|  52.00|  35.00|  36.00|  28.00|
> 149.00|
> insert_duplicates (100000)          |  12.00|  13.00|  11.00|   8.00|   8.00|   6.00|
>  35.00|
> insert_key (100000)                 |  43.00|  44.00|  44.00|  37.00|  37.00| 240.00|
> 173.00|
> insert_many_fields (2000)           |   3.00|   3.00|   3.00|   3.00|   3.00|   3.00|
>  15.00|
> insert_select_1_key (1)             |   2.00|   2.00|   2.00|   2.00|   2.00|   2.00|
>   7.00|
> insert_select_2_keys (1)            |   2.00|   3.00|   2.00|   3.00|   2.00|   1.00|
>  11.00|
> min_max (60)                        |   3.00|   3.00|   3.00|   6.00|   7.00|   4.00|
>  12.00|
> min_max_on_key (85000)              |  17.00|  17.00|  18.00|  16.00|  16.00|  11.00|
>  54.00|
> multiple_value_insert (100000)      |   1.00|   1.00|   2.00|   1.00|   1.00|   2.00|
>   5.00|
> once_prepared_select (100000)       |  24.00|  25.00|  24.00|  16.00|  14.00|  10.00|
>  99.00|
> order_by_big (10)                   |  22.00|  22.00|  22.00|  24.00|  24.00|  18.00|
>  92.00|
> order_by_big_key (10)               |  21.00|  20.00|  19.00|  20.00|  20.00|  17.00|
>  87.00|
> order_by_big_key2 (10)              |  20.00|  19.00|  18.00|  20.00|  20.00|  15.00|
>  84.00|
> order_by_big_key_desc (10)          |  20.00|  21.00|  19.00|  21.00|  20.00|  17.00|
>  85.00|
> order_by_big_key_diff (10)          |  22.00|  21.00|  21.00|  22.00|  22.00|  17.00|
>  91.00|
> order_by_big_key_prefix (10)        |  20.00|  20.00|  19.00|  19.00|  20.00|  16.00|
>  81.00|
> order_by_key2_diff (500)            |   2.00|   3.00|   2.00|   2.00|   2.00|   2.00|
>   9.00|
> order_by_key_prefix (500)           |   1.00|   1.00|   1.00|   2.00|   2.00|   0.00|
>   5.00|
> order_by_range (500)                |   2.00|   1.00|   2.00|   1.00|   1.00|   2.00|
>   5.00|
> outer_join (10)                     |   2.00|   2.00|   2.00|  19.00|  20.00|  13.00|
>   8.00|
> outer_join_found (10)               |   1.00|   2.00|   2.00|  19.00|  19.00|  12.00|
>   7.00|
> outer_join_not_found (500)          |   2.00|   1.00|   1.00|  13.00|  14.00|   8.00|
>   6.00|
> outer_join_on_key (10)              |   2.00|   1.00|   2.00|  16.00|  15.00|   9.00|
>   6.00|
> prepared_select (100000)            |  33.00|  32.00|  33.00|  23.00|  23.00|  16.00|
> 156.00|
> select_1_row (100000)               |  16.00|  16.00|  16.00|   9.00|   9.00|   6.00|
>  61.00|
> select_1_row_cache (100000)         |   9.00|   9.00|   8.00|   9.00|   8.00|   6.00|
>  16.00|
> select_2_rows (100000)              |  18.00|  17.00|  16.00|  10.00|  10.00|   7.00|
>  67.00|
> select_big (80)                     |  20.00|  20.00|  18.00|  19.00|  21.00|  16.00|
>  82.00|
> select_big_str (10000)              |   6.00|   5.00|   5.00|   4.00|   4.00|   8.00|
>  54.00|
> select_cache (10000)                |   2.00|   2.00|   2.00|  36.00|  33.00|  23.00|
>   5.00|
> select_cache2 (10000)               |  30.00|  29.00|  28.00|  36.00|  34.00|  22.00|
> 142.00|
> select_column+column (100000)       |  18.00|  18.00|  16.00|  10.00|  10.00|   6.00|
>  64.00|
> select_diff_key (500)               |  35.00|  39.00|  35.00|  35.00|  35.00|  29.00|
> 187.00|
> select_distinct (800)               |   1.00|   2.00|   1.00|   4.00|   5.00|   3.00|
>   5.00|
> select_group (2911)                 |   3.00|   3.00|   2.00|  17.00|  17.00|  11.00|
>  10.00|
> select_group_when_MANY_tables (10000|   2.00|   3.00|   2.00|   1.00|   1.00|   2.00|
>  40.00|
> select_join (100)                   |   0.00|   1.00|   0.00|   0.00|   1.00|   0.00|
>   1.00|
> select_key (200000)                 |  60.00|  61.00|  58.00|  46.00|  44.00|  30.00|
> 268.00|
> select_key2 (200000)                |  63.00|  63.00|  60.00|  48.00|  46.00|  32.00|
> 288.00|
> select_key2_return_key (200000)     |  59.00|  60.00|  57.00|  43.00|  43.00|  30.00|
> 275.00|
> select_key2_return_prim (200000)    |  61.00|  62.00|  58.00|  46.00|  44.00|  31.00|
> 292.00|
> select_key_prefix (200000)          |  62.00|  62.00|  61.00|  47.00|  47.00|  33.00|
> 293.00|
> select_key_prefix_join (100)        |   4.00|   3.00|   4.00|   6.00|   5.00|   4.00|
>  16.00|
> select_key_return_key (200000)      |  59.00|  59.00|  57.00|  42.00|  42.00|  29.00|
> 264.00|
> select_many_fields (2000)           |   7.00|   7.00|   7.00|  10.00|   9.00|   7.00|
>  21.00|
> select_range (410)                  |   8.00|   8.00|   8.00|  49.00|  52.00|  64.00|
>  33.00|
> select_range_key2 (25010)           |   4.00|   4.00|   3.00|   7.00|   7.00|   5.00|
>  12.00|
> select_range_prefix (25010)         |   5.00|   3.00|   5.00|   7.00|   7.00|   5.00|
>  12.00|
> select_simple (100000)              |   9.00|  10.00|   8.00|   6.00|   6.00|   3.00|
>  19.00|
> select_simple_cache (100000)        |  10.00|   9.00|   8.00|   6.00|   6.00|   4.00|
>  19.00|
> select_simple_join (500)            |   1.00|   0.00|   1.00|   1.00|   1.00|   2.00|
>   2.00|
> update_big (10)                     |  18.00|  18.00|  18.00|  11.00|  12.00|   9.00|
>  30.00|
> update_of_key (50000)               |   9.00|   9.00|   9.00|   6.00|   8.00|   5.00|
>  37.00|
> update_of_key_big (501)             |   8.00|   9.00|   9.00|   8.00|   8.00|   5.00|
>  30.00|
> update_of_primary_key_many_keys (256|  13.00|  13.00|  14.00|  17.00|  16.00|  34.00|
>  37.00|
> update_with_key (300000)            |  44.00|  43.00|  40.00|  29.00|  28.00|  21.00|
> 148.00|
> update_with_key_prefix (100000)     |  16.00|  16.00|  15.00|  11.00|  11.00|   8.00|
>  59.00|
> wisc_benchmark (114)                |   2.00|   2.00|   1.00|   2.00|   2.00|   2.00|
>   5.00|
>
> ---------------------------------------------------------------------------------------------
> TOTALS                             
> |1207.00|1229.00|1185.00|1557.00|1530.00|1531.00|5099.00|
>
> =============================================================================================
> 
> 
> [-- text/plain, encoding 7bit, charset: us-ascii, 5 lines --]
> 
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com