Professional Web Applications Themes

Suggest for MySQL Configuration - MySQL

Hi, I've a server with MySQL Community installed. I host 115 DB for web applications (Mambo Portal) with MyISAM tables and only 3 InnoDB tables for each DB. I've notice that the server sometimes become very slow for this service. The server is a Dual Xeon 3,2 dualcore with 2,5 gb di RAM with SCSI 360 HDD in RAID5. Can someone help me to optimize the my.ini config? This is my: # MySQL Server Instance Configuration File # SERVER SECTION # ---------------------------------------------------------------------- [mysqld] port=3306 basedir="C:/Programmi/MySQL/MySQL Server 5.0/" datadir="C:/Programmi/MySQL/MySQL Server 5.0/Data/" default-character-set=latin1 default-storage-engine=INNODB sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" max_connections=800 query_cache_size=84M table_cache=1520 tmp_table_size=30M thread_cache_size=38 #*** MyISAM ...

  1. #1

    Default Suggest for MySQL Configuration

    Hi,
    I've a server with MySQL Community installed.
    I host 115 DB for web applications (Mambo Portal) with MyISAM tables and
    only 3 InnoDB tables for each DB.
    I've notice that the server sometimes become very slow for this service.
    The server is a Dual Xeon 3,2 dualcore with 2,5 gb di RAM with SCSI 360 HDD
    in RAID5.
    Can someone help me to optimize the my.ini config?
    This is my:

    # MySQL Server Instance Configuration File


    # SERVER SECTION
    # ----------------------------------------------------------------------

    [mysqld]

    port=3306
    basedir="C:/Programmi/MySQL/MySQL Server 5.0/"
    datadir="C:/Programmi/MySQL/MySQL Server 5.0/Data/"
    default-character-set=latin1
    default-storage-engine=INNODB

    sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    max_connections=800
    query_cache_size=84M
    table_cache=1520
    tmp_table_size=30M
    thread_cache_size=38

    #*** MyISAM Specific options

    myisam_max_sort_file_size=100G
    myisam_max_extra_sort_file_size=100G
    myisam_sort_buffer_size=30M
    key_buffer_size=129M

    read_buffer_size=64K
    read_rnd_buffer_size=256K

    sort_buffer_size=256K


    #*** INNODB Specific options ***

    innodb_additional_mem_pool_size=6M
    innodb_flush_log_at_trx_commit=1
    innodb_log_buffer_size=3M
    innodb_buffer_pool_size=250M
    innodb_log_file_size=50M
    innodb_thread_concurrency=10


    Thank you very very much.


    Andrea Guest

  2. #2

    Default Re: Suggest for MySQL Configuration

    == Quote from Andrea (net)'s article 

    here's what i have on a server(change paths to conform to win32) with two
    processors (high end) and 4 gig of ram and a tone of disk space and several
    hundred tables:

    key_buffer_size=64M
    max_allowed_packet = 1M
    open_files_limit = 1920
    table_cache = 512
    sort_buffer_size=1024
    read_buffer_size = 4M
    read_rnd_buffer_size = 2M
    myisam_sort_buffer_size = 32M
    thread_cache_size = 64
    thread_stack = 4M
    query_cache_size = 64M
    query_cache_type = 1
    query_cache_limit = 4M
    max_connections = 1000
    max_user_connections = 200
    key_buffer_size = 64M
    tmp_table_size = 64M
    thread_concurrency = 4
    myisam-recover=FORCE,BACKUP,QUICK


    for innodb, i have the following which makes every table its own file:

    innodb_file_per_table
    innodb_data_home_dir = /usr/local/mysql/data/
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_group_home_dir = /usr/local/mysql/logs/
    innodb_log_arch_dir = /usr/local/mysql/logs/
    innodb_buffer_pool_size = 512M
    innodb_additional_mem_pool_size = 20M
    innodb_log_file_size = 128M
    innodb_log_buffer_size = 4M
    transaction-isolation = READ-COMMITTED
    innodb_file_per_table


    and then for the rest of them, i have the following:

    [mysqldump]
    quick
    max_allowed_packet = 16M
    [mysql]
    no-auto-rehash
    [isamchk]
    key_buffer = 256M
    sort_buffer_size = 256M
    myisam_max_sort_file_size=1024k
    myisam_sort_buffer_size=8192k
    query_cache_type=1
    [myisamchk]
    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M
    [mysqlhotcopy]
    interactive-timeout
    --
    POST BY: PHP News Reader
    lark Guest

  3. #3

    Default Re: Suggest for MySQL Configuration

    On 2007-05-24, lark <net> wrote: [/ref]

    <snip>
     [/ref]

    <snip>
     

    <snip>

    It may be useful to increase the buffer pool size
    for InnoDb to (no more than) 80% of physical RAM.
    Try this and see if it improves performance.

    -- Charles

    Charles Guest

Similar Threads

  1. CFMX 7 and mySQL 5 configuration
    By jkdfjk in forum Coldfusion Database Access
    Replies: 4
    Last Post: November 23rd, 02:50 AM
  2. Please suggest
    By Support in forum ASP.NET Web Services
    Replies: 1
    Last Post: July 5th, 04:28 PM
  3. What a little UPS you suggest ?
    By Ben Kamen in forum AIX
    Replies: 0
    Last Post: August 14th, 06:00 PM
  4. What do you suggest ?
    By Joachim Smit in forum Debian
    Replies: 0
    Last Post: July 13th, 04:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139