List:General Discussion« Previous MessageNext Message »
From:Justin Date:September 3 2007 4:49pm
Subject:Re: servers full potential / FT searches locking tables
View as plain text  
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" <mysql@stripped>
To: <mysql@stripped>
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" <mdykman@stripped>
> To: "Justin" <mysql@stripped>
> Cc: <mysql@stripped>
> 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 <mysql@stripped> 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" <mathieu.bruneau@stripped>
>> To: "Justin" <mysql@stripped>
>> Cc: <mysql@stripped>
>> 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=1
>>
>>
>
>
> -- 
> - 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=1
>
> 

Thread
servers full potential / FT searches locking tablesJustin27 Aug
  • Re: servers full potential / FT searches locking tablesJay Pipes27 Aug
    • Re: servers full potential / FT searches locking tablesRolando Edwards27 Aug
      • Re: servers full potential / FT searches locking tablesJay Pipes27 Aug
        • Re: servers full potential / FT searches locking tablesJustin27 Aug
          • Re: servers full potential / FT searches locking tablesnigel wood27 Aug
    • Re: servers full potential / FT searches locking tablesJustin27 Aug
  • Re: servers full potential / FT searches locking tablesMathieu Bruneau28 Aug
    • Re: servers full potential / FT searches locking tablesJustin28 Aug
      • Re: servers full potential / FT searches locking tablesMichael Dykman28 Aug
        • Re: servers full potential / FT searches locking tablesJustin28 Aug
        • Re: servers full potential / FT searches locking tablesKen Peng29 Aug
          • Re: servers full potential / FT searches locking tablesMichael Dykman30 Aug
            • Re: servers full potential / FT searches locking tablesKen Peng30 Aug
              • Re: servers full potential / FT searches locking tablesMichael Dykman30 Aug
        • Re: servers full potential / FT searches locking tablesJustin1 Sep
          • Re: servers full potential / FT searches locking tablesJustin3 Sep
            • Re: servers full potential / FT searches locking tablesBaron Schwartz3 Sep
              • Re: servers full potential / FT searches locking tablesJustin4 Sep
                • Memory Issue would someone confirmJustin4 Sep
                  • Re: Memory Issue would someone confirmJustin4 Sep