Hi :) We're running 3 MySQL-servers for our customers databases (hosting-provider), and once in a while they give us REAL headaches. This time it's our 2nd server. At night, the load is +/- 34-50. Daily, it's between 2 and 20.
All servers are dual PIII/1000 with 2GB of memory and U160 10K SCSI disks. Also one disk for the OS, and a RAID1-array for MySQL. (0+1 would be better I guess...) Would dual Xeon's (2 gHz) improve performance ? When our first server had this problem, we isolated it by blocking servers to see where the problem came from. We found one database that had some old ISAM-tables, instead of MyISAM. Converted those and now it only spontaneously has a load higher then 8. (Avg. 3 - < 5 between 09:00AM and 02:00AM) This time, we haven't been able to find the problem by blocking one host at a time. And that's strange, since it has run pretty smoothly. I've been puzzeling with the my.cnf options, but nothing seems to help. Increasing key_buffer to 50% of RAM doesn't help much. Is it wise to do it ? If I do a 'SHOW PROCESSLIST', I see 90 connections, with only 4 or 5 query's. The rest is 'sleep'. And also, 1 Delayed_insert. Load at this moment: 11.93, 9.42, 7.01 Threads: 98 Questions: 550167 Slow queries: 31 Opens: 8194 Flush tables: 4 Open tables: 1024 Queries per second avg: 420.296 Open tables ... could this be the problem ? If I increase it to 8192, show status let me see that it grows to this value. We run about 750 - 800 databases per server. We now are lowering this to 500. ulimit -a shows me: core file size (blocks) 0 data seg size (kbytes) unlimited file size (blocks) unlimited max memory size (kbytes) unlimited stack size (kbytes) 8192 cpu time (seconds) unlimited max user processes 10240 pipe size (512 bytes) 8 open files 10240 virtual memory (kbytes) 2105343 /proc/sys/fs/file-max: 65536 /proc/sys/kernel/threads-max: 65536 Running MySQL 3.23.49 on RedHat 6.2 with kernel 2.4.18-ac3, linked to it's own libpthread.a of glibc-2.2.2, as 'recommended' at the MySQL-website (gcc 2.95.3). Compiled MySQL with: export CFLAGS="-O3 -mpentiumpro" export CXX=gcc export CXXFLAGS="-O3 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --with-other-libc=/usr/local/glibc \ --with-mysqld-user=mysql --with-extra-charsets=none --without-debug \ --without-bench --without-docs --enable-assembler \ --with-mysqld-ldflags=-all-static Running the MySQL-binary, _does not_ improve performance :( my.cnf looks like this: [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking # key_buffer 25% of RAM set-variable = key_buffer=512M set-variable = max_allowed_packet=1M set-variable = sort_buffer=8M set-variable = record_buffer=8M set-variable = thread_cache=8 set-variable = thread_concurrency=4 set-variable = myisam_sort_buffer_size=64M set-variable = table_cache=1024 set-variable = max_connections=1250 set-variable = max_connect_errors=999999999 set-variable = wait_timeout=30 # tmpdir on another disk tmpdir = /mnt/mysql_tmp/ Please give me comments ... *desperate* looking for a way to fix this. Also, I have .err ... with a few backtraces ... changed my.cnf options, so it's not showing the correct values as they are now. 020425 14:35:33 mysqld started /opt/database/mysql-glibc/libexec/mysqld: ready for connections Status information: Current dir: /opt/database/mysql-glibc/var/ Current locks: lock: 5964b134: <cut> lock: 59966cf4: read read : 59993070 (1499178:1); <cut> key_cache status: blocks used: 14263 not flushed: 0 w_requests: 40556 writes: 37503 r_requests: 2544986 reads: 13831 handler status: read_key: 1400215 read_next: 3124503 read_rnd 747065 read_first: 4166 write: 159821 delete 456 update: 601275 Table status: Opened tables: 4109 Open tables: 512 Open files: 966 Open streams: 0 mysqld got signal 11; key_buffer_size=402649088 record_buffer=2093056 sort_buffer=2097144 max_used_connections=337 max_connections=1250 threads_connected=91 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 1313898 K bytes of memory Hope that's ok, if not, decrease some variables in the equation Stack range sanity check OK, backtrace follows: 0x806cb54 0x8116c2a 0x80e715e 0x80b5de8 0x8069d75 0x8069bea 0x8084e34 0x808725d 0x80736ac 0x8077808 0x80728f4 0x8071ca7 Stack trace seems successful - bottom reached Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x610a9940 is invalid pointer thd->thread_id=868173 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 868173 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid Number of processes running now: 1 020425 22:20:11 mysqld restarted Should I run with --log to be able to solve this ? Logfile grows to 150MB per 30-45 minutes. (I apologize for this long message ... but I didn't want to forget to mention things :) -- Met vriendelijke groet/With kind regards, Wouter de Jong System-Administrator CABLE & WIRELESS Delivering the Internet promise(tm) URL: http://www.widexs.nl Email: [EMAIL PROTECTED] Tel: +31 (0) 23 5698070 Fax: +31 (0) 23 5698099 *************************************************************************************** This message may contain information which is confidential or privileged. If you are not the intended recipient, please advise the sender immediately by reply e-mail and delete this message and any attachments without retaining a copy. *************************************************************************************** --------------------------------------------------------------------- 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
