MySQL load problems.

[Maximo Migliari]

Hello all,

I've been having some problems with MySQL crashing. I use a mixture of MyISAM and InnoDB tables, but most of the data is under InnoDB tables. My web applications all do relatively complex join queries. Almost all fields in the WHERE clauses of queries are indexed.

I'm running RedHat 7.2, and MySQL version is 3.23.53a

The server is:
PIII-866 Mhz,
512 MB RAM
18 GIG SCSI drive (no RAID).

The server acts as an Apache server AND as a MySQL server

The server seems to work fine and has +- an uptime load of 1 - 1.5 when MySQL is running arround 20 queries/sec
When it reaches about 30-40 queries/sec, the machine goes to 3-5 load in uptime, often registers slow queries, and eventually dies without any error messages (except that the socket file doesn't exist - i.e. crash).

How should I set my memory usage in my.cnf ???? I have a hard time understanding how MySQL works with memory, how it manages it, etc. What should I consider when setting my memory settings??? I have included a paste of my.cnf


# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = key_buffer=128M
set-variable = max_allowed_packet=1M
set-variable = table_cache=256
set-variable = record_buffer=1M
set-variable = sort_buffer=1M
set-variable = net_buffer_length=16K
set-variable = myisam_sort_buffer_size=96M
set-variable = thread_cache=8
set-variable = max_connections=500
log-bin
server-id = 1


# Uncomment the following if you are using Innobase tables
innodb_data_file_path = innodb/ibdata1:1000M:autoextend
innodb_data_home_dir = /usr/local/mysql/data/
innodb_log_group_home_dir = /usr/local/mysql/data/innodb
innodb_log_arch_dir = /usr/local/mysql/data/innodb
set-variable = innodb_mirrored_log_groups=1
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=32M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_log_archive=0
set-variable = innodb_buffer_pool_size=160M
set-variable = innodb_additional_mem_pool_size=96M
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50

[mysqldump]
quick
set-variable = max_allowed_packet=128M

[mysql]
no-auto-rehash

[isamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[mysqlhotcopy]
interactive-timeout


---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php