From: Justin Date: September 3 2007 4:49pm Subject: Re: servers full potential / FT searches locking tables List-Archive: http://lists.mysql.com/mysql/208872 Message-Id: <0D7813D7F48A477CBF728ED80A3918DC@JustinPC> MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 8bit lockup just happened again.. here's a innodb status. mysql> show innodb status; | ===================================== 070903 12:22:31 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 21 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 2, signal count 2 Mutex spin waits 1, rounds 20, OS waits 0 RW-shared spins 4, OS waits 2; RW-excl spins 1, OS waits 0 ------------ TRANSACTIONS ------------ Trx id counter 0 2304 Purge done for trx's n:o < 0 0 undo n:o < 0 0 History list length 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 15743, OS thread id 160672656 MySQL thread id 2567306, query id 598325913 localhost root show innodb status -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 25 OS file reads, 3 OS file writes, 3 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 0, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 43675 Log flushed up to 0 43675 Last checkpoint at 0 43675 0 pending log writes, 0 pending chkp writes 8 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 18365012; in additional pool allocated 864768 Buffer pool size 512 Free buffers 493 Database pages 19 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 19, created 0, written 0 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 15743, id 2996472720, state: waiting for server activity Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 1 warning (0.06 sec) ----- Original Message ----- From: "Justin" To: Sent: Friday, August 31, 2007 4:28 PM Subject: Re: servers full potential / FT searches locking tables > Alright.. I think I see what's is happening after this latest lockup.. > > here's what I think is happening.. > > When a replace into query locks a table for a few seconds there are a boot > load of connections to the db, and then when the table is unlocked the > connections start to filter through and usually they all finish and > de-queue nicely but this last time it seemed there were 400-500 constant > connections never actually going away.. the query it's self finished. but > there was one right behind it to take it's place.. > > Almost like it's giving it's self a dos.. Is there any settings I can > adjust on the server to help with this? or would it be more on the code > side. > > As always when I restart the instance of mysql all goes back smoothly so > it makes me wonder if it's something in the mysql config that is lagging > for some reason. > > thanks. > > > ----- Original Message ----- > From: "Michael Dykman" > To: "Justin" > Cc: > Sent: Tuesday, August 28, 2007 1:31 PM > Subject: Re: servers full potential / FT searches locking tables > > > No, I'm afraid not. 32 bit architectures have a theoretical limit of > 4G of memory space for the entire application: in actual practice, for > a variety of reasons too complex to go into here (and are well > documented elsewhere) your key buffer should be limited to around 2.5G > max, and this is assuming a pure MyISAM implementation. There simply > is no way a 32 bit build can make use of all that RAM, regardless of > OS. > > - michael dykman > > > On 8/28/07, Justin wrote: >> 32bit, but I have all available memory.. >> >> MemTotal: 8179612 kB >> MemFree: 43684 kB >> >> on the box. I think the 4gb is only windows. >> >> All my tables are in myisam >> >> so if I was to set >> key_buffer_size=5500M >> >> That'd be acceptable? >> >> ----- Original Message ----- >> From: "Mathieu Bruneau" >> To: "Justin" >> Cc: >> Sent: Tuesday, August 28, 2007 12:51 AM >> Subject: Re: servers full potential / FT searches locking tables >> >> >> > Your settings doesn't seem optimized much. >> > >> > So here first question, do you use 32bits or 64 bits platform? If you >> > have >> > 64 bits platform with 64 bits mysql and os you can boost most the >> > settings >> > to use almost the 8G of ram you have on the server. If you are using >> > 32bits you will have to do some calculation so you don't go over ~2.6G >> > (why not 4Gb?, go read on that on the net) >> > >> > So the 2 most importants settings are: >> > key_buffer_size (mainly myisam table) >> > and/or >> > innodb_buffer_pool_size (innodb table) >> > >> > Depending if you're using more innodb or myisam (or a mix) you'll tweak >> > those pamareters differently, it's usually however not recommended to >> > go >> > over 4Gb for the key_buffer_size. MyIsam only stores the key into that >> > buffer, so you don't have much index, not worth taking it too big for >> > no >> > reason. Innodb however can cache data as well, and will benefit from >> > the >> > biggest value possible. >> > >> > The server generate statistic that you can look to know the effect of >> > that. If you are using phpmyadmin in the variables and status part you >> > can >> > see the index usage to guide you. >> > >> > >> > You can have a look at the different my.cnf that comes with mysql >> > distribution they put comment in there with interesting value for >> > thumbs >> > rule. Here the except for key_buffer_size and innodb_buffer_pool_size: >> > # Size of the Key Buffer, used to cache index blocks for MyISAM tables. >> > # Do not set it larger than 30% of your available memory, as some >> > memory >> > # is also required by the OS to cache rows. Even if you're not using >> > # MyISAM tables, you should still set it to 8-64M as it will also be >> > # used for internal temporary disk tables. >> > key_buffer_size=2G >> > >> > # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and >> > # row data. The bigger you set this the less disk I/O is needed to >> > # access data in tables. On a dedicated database server you may set >> > this >> > # parameter up to 80% of the machine physical memory size. Do not set >> > it >> > # too large, though, because competition of the physical memory may >> > # cause paging in the operating system. Note that on 32bit systems you >> > # might be limited to 2-3.5G of user level memory per process, so do >> > not >> > # set it too high. >> > innodb_buffer_pool_size=2G >> > >> > Regards, >> > -- >> > Mathieu Bruneau >> > aka ROunofF >> > >> > === >> > GPG keys available @ http://rounoff.darktech.org >> > >> > Justin a écrit : >> >> Ok.. Straight to the point.. Here is what I currently have. >> >> >> >> MySQL Ver 14.12 Distrib 5.0.27 >> >> RHEL vs 5 >> >> 584GB Raid 5 storage >> >> 8GB of RAM >> >> and Dual 5130 processors (2.0GHz Intel Dual-Core Xeon) >> >> >> >> what my question is.. is am I utilizing the servers potential with the >> >> following as my settings. The server is a dedicated MySQL server so I >> >> want all power to go to the server. It just seems to be laggy at >> >> times. >> >> And I want to be sure I've optimized to the fullest potential >> >> >> >> My biggest issue is with FT searches. Tables get locked during larger >> >> queries and I can't select anything when that happens. Is there any >> >> way >> >> not to lock the tables on a Full Text search? (does that make sense?) >> >> >> >> thanks again for any insight >> >> >> >> Justin. >> >> >> >> Here's a dump of the my.cnf and the phpmyadmin dump of vars. >> >> ------------ >> >> /etc/my.cnf >> >> >> >> [mysqld] >> >> datadir=/var/lib/mysql >> >> socket=/var/lib/mysql/mysql.sock >> >> wait_timeout=60 >> >> default-character-set=utf8 >> >> max_allowed_packet = 3000M >> >> max_connections = 5000 >> >> ft_min_word_len=3 >> >> >> >> server-id=1 >> >> log-error = /var/log/mysql/error.log >> >> expire_logs_days = 3 >> >> >> >> >> >> # Default to using old password format for compatibility with mysql >> >> 3.x >> >> # clients (those using the mysqlclient10 compatibility package). >> >> old_passwords=0 >> >> >> >> [mysql.server] >> >> user=mysql >> >> >> >> [mysqld_safe] >> >> err-log=/var/log/mysql/mysqld.log >> >> pid-file=/var/run/mysqld/mysqld.pid >> >> ------------ >> >> >> >> auto increment increment 1 >> >> auto increment offset 1 >> >> automatic sp privileges ON >> >> back log 50 >> >> basedir / >> >> binlog cache size 32,768 >> >> bulk insert buffer size 8,388,608 >> >> character set client utf8 >> >> character set connection utf8 >> >> character set database utf8 >> >> character set filesystem binary >> >> character set results utf8 >> >> character set server utf8 >> >> character set system utf8 >> >> character sets dir /usr/share/mysql/charsets/ >> >> collation connection utf8_general_ci >> >> collation database utf8_general_ci >> >> collation server utf8_general_ci >> >> completion type 0 >> >> concurrent insert 1 >> >> connect timeout 5 >> >> datadir /var/lib/mysql/ >> >> date format %Y-%m-%d >> >> datetime format %Y-%m-%d %H:%i:%s >> >> default week format 0 >> >> delay key write ON >> >> delayed insert limit 100 >> >> delayed insert timeout 300 >> >> delayed queue size 1,000 >> >> div precision increment 4 >> >> engine condition pushdown OFF >> >> expire logs days 3 >> >> flush OFF >> >> flush time 0 >> >> ft boolean syntax + -><()~*:""&| >> >> ft max word len 84 >> >> ft min word len 3 >> >> ft query expansion limit 20 >> >> ft stopword file (built-in) >> >> group concat max len 1,024 >> >> have archive YES >> >> have bdb NO >> >> have blackhole engine NO >> >> have compress YES >> >> have crypt YES >> >> have csv NO >> >> have dynamic loading YES >> >> have example engine NO >> >> have federated engine NO >> >> have geometry YES >> >> have innodb YES >> >> have isam NO >> >> have merge engine YES >> >> have ndbcluster NO >> >> have openssl DISABLED >> >> have query cache YES >> >> have raid NO >> >> have rtree keys YES >> >> have symlink YES >> >> init connect >> >> init file >> >> init slave >> >> innodb additional mem pool size 1,048,576 >> >> innodb autoextend increment 8 >> >> innodb buffer pool awe mem mb 0 >> >> innodb buffer pool size 8,388,608 >> >> innodb checksums ON >> >> innodb commit concurrency 0 >> >> innodb concurrency tickets 500 >> >> innodb data file path ibdata1:10M:autoextend >> >> innodb data home dir >> >> innodb doublewrite ON >> >> innodb fast shutdown 1 >> >> innodb file io threads 4 >> >> innodb file per table OFF >> >> innodb flush log at trx commit 1 >> >> innodb flush method >> >> innodb force recovery 0 >> >> innodb lock wait timeout 50 >> >> innodb locks unsafe for binlog OFF >> >> innodb log arch dir >> >> innodb log archive OFF >> >> innodb log buffer size 1,048,576 >> >> innodb log file size 5,242,880 >> >> innodb log files in group 2 >> >> innodb log group home dir ./ >> >> innodb max dirty pages pct 90 >> >> innodb max purge lag 0 >> >> innodb mirrored log groups 1 >> >> innodb open files 300 >> >> innodb support xa ON >> >> innodb sync spin loops 20 >> >> innodb table locks ON >> >> innodb thread concurrency 8 >> >> innodb thread sleep delay 10,000 >> >> interactive timeout 28,800 >> >> join buffer size 131,072 >> >> key buffer size 8,388,600 >> >> key cache age threshold 300 >> >> key cache block size 1,024 >> >> key cache division limit 100 >> >> language /usr/share/mysql/english/ >> >> large files support ON >> >> large page size 0 >> >> large pages OFF >> >> lc time names en_US >> >> license GPL >> >> local infile ON >> >> locked in memory OFF >> >> log OFF >> >> log bin OFF >> >> log bin trust function creators OFF >> >> log error /var/log/mysql/error.log >> >> log queries not using indexes OFF >> >> log slave updates OFF >> >> log slow queries OFF >> >> log warnings 1 >> >> long query time 10 >> >> low priority updates OFF >> >> lower case file system OFF >> >> lower case table names 0 >> >> max allowed packet 1,073,740,800 >> >> max binlog cache size 4,294,967,295 >> >> max binlog size 1,073,741,824 >> >> max connect errors 10 >> >> max connections 5,000 >> >> max delayed threads 20 >> >> max error count 64 >> >> max heap table size 16,777,216 >> >> max insert delayed threads 20 >> >> max join size 18446744073709551615 >> >> max length for sort data 1,024 >> >> max prepared stmt count 16,382 >> >> max relay log size 0 >> >> max seeks for key 4,294,967,295 >> >> max sort length 1,024 >> >> max sp recursion depth 0 >> >> max tmp tables 32 >> >> max user connections 0 >> >> max write lock count 4,294,967,295 >> >> multi range count 256 >> >> myisam data pointer size 6 >> >> myisam max sort file size 2,147,483,647 >> >> myisam recover options OFF >> >> myisam repair threads 1 >> >> myisam sort buffer size 8,388,608 >> >> myisam stats method nulls_unequal >> >> net buffer length 16,384 >> >> net read timeout 30 >> >> net retry count 10 >> >> net write timeout 60 >> >> new OFF >> >> old passwords OFF >> >> open files limit 25,010 >> >> optimizer prune level 1 >> >> optimizer search depth 62 >> >> pid file /var/lib/mysql/dbs.live.pid >> >> port 3,306 >> >> preload buffer size 32,768 >> >> prepared stmt count 0 >> >> protocol version 10 >> >> query alloc block size 8,192 >> >> query cache limit 1,048,576 >> >> query cache min res unit 4,096 >> >> query cache size 0 >> >> query cache type ON >> >> query cache wlock invalidate OFF >> >> query prealloc size 8,192 >> >> range alloc block size 2,048 >> >> read buffer size 131,072 >> >> read only OFF >> >> read rnd buffer size 262,144 >> >> relay log purge ON >> >> relay log space limit 0 >> >> rpl recovery rank 0 >> >> secure auth OFF >> >> server id 1 >> >> skip external locking ON >> >> skip networking OFF >> >> skip show database OFF >> >> slave compressed protocol OFF >> >> slave load tmpdir /tmp/ >> >> slave net timeout 3,600 >> >> slave skip errors OFF >> >> slave transaction retries 10 >> >> slow launch time 2 >> >> socket /var/lib/mysql/mysql.sock >> >> sort buffer size 2,097,144 >> >> sql big selects ON >> >> sql mode >> >> sql notes ON >> >> sql warnings OFF >> >> ssl ca >> >> ssl capath >> >> ssl cert >> >> ssl cipher >> >> ssl key >> >> storage engine MyISAM >> >> sync binlog 0 >> >> sync frm ON >> >> system time zone EDT >> >> table cache 64 >> >> table lock wait timeout 50 >> >> table type MyISAM >> >> thread cache size 0 >> >> thread stack 196,608 >> >> time format %H:%i:%s >> >> time zone SYSTEM >> >> timed mutexes OFF >> >> tmp table size 33,554,432 >> >> tmpdir /tmp/ >> >> transaction alloc block size 8,192 >> >> transaction prealloc size 4,096 >> >> tx isolation REPEATABLE-READ >> >> updatable views with limit YES >> >> version 5.0.27-standard >> >> version comment MySQL Community Edition - Standard (GPL) >> >> version compile machine i686 >> >> version compile os pc-linux-gnu >> >> wait timeout 60 >> >> Open new phpMyAdmin window >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> > >> > >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@stripped >> >> > > > -- > - michael dykman > - mdykman@stripped > > - All models are wrong. Some models are useful. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@stripped > >