Webroy
13/11/08, 11:09
Probleempje,
Na het updaten van mysql 4 naar mysql 5 heb ik problemen.
De database draait op een eigenserver , verder niks erop.
Een php script voegt Select en update queries op de database uit.
In mysql 4 waren er meerdere mysql processen die draaide, echter in mysql 5 is dat 1 process geworden waardoor de snelheid aanzienlijk is vertraagd.
De my.cnf heb ik overgenomen uit de mysql4 versie en al wat mee geexperimenteerd.
T meesta wat mij dwars zit is waarom hij nu maar 1 mysql proces start en in mysql 4 meerdere.
Heeft 1 van jullie hier ervaring mee?
--------------------------------------------------------------------------
Hieronder mijn huidige my.cnf:
# The MySQL server
[mysqld]
set-variable=local-infile=0
safe-show-database
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable=max_connections=2000
set-variable=max_user_connections=1000
skip-locking
set-variable = key_buffer=1992M
set-variable = max_allowed_packet=1M
set-variable = table_cache=56
set-variable = sort_buffer=16M
set-variable = record_buffer=2M
set-variable = thread_cache=64
# Try number of CPU's*2 for thread_concurrency
#set-variable = thread_concurrency=2
set-variable = myisam_sort_buffer_size=256M
set-variable = interactive_timeout=10
set-variable = wait_timeout=10
#log-bin
#server-id = 1
log-slow-queries = /var/log/slow-queries.log
long_query_time = 5
#log-slow-queries =/var/log/slow-queries.log
#long_query_time = 60
old_passwords=1
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=384M
#set-variable = bdb_max_lock=100000
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#set-variable = innodb_buffer_pool_size=384M
#set-variable = innodb_additional_mem_pool_size=20M
# Set .._log_file_size to 25 % of buffer pool size
#set-variable = innodb_log_file_size=100M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#set-variable = innodb_lock_wait_timeout=50
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
set-variable = key_buffer=512M
set-variable = sort_buffer=512M
set-variable = read_buffer=8M
set-variable = write_buffer=2M
[myisamchk]
set-variable = key_buffer=512M
set-variable = sort_buffer=512M
set-variable = read_buffer=16M
set-variable = write_buffer=8M
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Na het updaten van mysql 4 naar mysql 5 heb ik problemen.
De database draait op een eigenserver , verder niks erop.
Een php script voegt Select en update queries op de database uit.
In mysql 4 waren er meerdere mysql processen die draaide, echter in mysql 5 is dat 1 process geworden waardoor de snelheid aanzienlijk is vertraagd.
De my.cnf heb ik overgenomen uit de mysql4 versie en al wat mee geexperimenteerd.
T meesta wat mij dwars zit is waarom hij nu maar 1 mysql proces start en in mysql 4 meerdere.
Heeft 1 van jullie hier ervaring mee?
--------------------------------------------------------------------------
Hieronder mijn huidige my.cnf:
# The MySQL server
[mysqld]
set-variable=local-infile=0
safe-show-database
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable=max_connections=2000
set-variable=max_user_connections=1000
skip-locking
set-variable = key_buffer=1992M
set-variable = max_allowed_packet=1M
set-variable = table_cache=56
set-variable = sort_buffer=16M
set-variable = record_buffer=2M
set-variable = thread_cache=64
# Try number of CPU's*2 for thread_concurrency
#set-variable = thread_concurrency=2
set-variable = myisam_sort_buffer_size=256M
set-variable = interactive_timeout=10
set-variable = wait_timeout=10
#log-bin
#server-id = 1
log-slow-queries = /var/log/slow-queries.log
long_query_time = 5
#log-slow-queries =/var/log/slow-queries.log
#long_query_time = 60
old_passwords=1
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=384M
#set-variable = bdb_max_lock=100000
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#set-variable = innodb_buffer_pool_size=384M
#set-variable = innodb_additional_mem_pool_size=20M
# Set .._log_file_size to 25 % of buffer pool size
#set-variable = innodb_log_file_size=100M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#set-variable = innodb_lock_wait_timeout=50
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
set-variable = key_buffer=512M
set-variable = sort_buffer=512M
set-variable = read_buffer=8M
set-variable = write_buffer=2M
[myisamchk]
set-variable = key_buffer=512M
set-variable = sort_buffer=512M
set-variable = read_buffer=16M
set-variable = write_buffer=8M
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid