Subject: Re: Performance problems... From: Vic Cekvenich <[EMAIL PROTECTED]> === DB's are IO bound. Get more cache in Raid? So 2 CPU should not help. Conisder PostgreSQL.
Wouter de Jong wrote: > 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 :) > --------------------------------------------------------------------- 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
