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.

== Quote from Andrea (EMAIL REMOVED)'s article
> 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.

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

On 2007-05-24, lark <EMAIL REMOVED> wrote:
>== Quote from Andrea (EMAIL REMOVED)'s article
>> 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]

<snip>

>> innodb_buffer_pool_size=250M

<snip>

> innodb_buffer_pool_size = 512M

<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