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 < 0 0 undo n < 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@b0rker.com>
To: <mysql@lists.mysql.com>
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 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@gmail.com>
> To: "Justin" <mysql@b0rker.com>
> Cc: <mysql@lists.mysql.com>
> 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@b0rker.com> 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@argontechnologies.ca>
>> To: "Justin" <mysql@b0rker.com>
>> Cc: <mysql@lists.mysql.com>
>> 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@gmail.com
>>
>>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> - 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@b0rker.com
>
>

Justin wrote:
> lockup just happened again.. here's a innodb status.

InnoDB status will be basically useless, as full-text is only applicable
to MyISAM, and indeed your status output shows only one transaction is
running (the one running 'show innodb status') and InnoDB has done zero
work since the server was started (look in the ROW OPERATIONS section).

So your server isn't using InnoDB for any tables (at least none you've
queried since starting the server).

But you're still allocating some memory and other resources to it. If I
were you I'd put the following in your /etc/my.cnf just to ensure InnoDB
isn't interfering or complicating anything:

skip_innodb

Cheers
Baron

Figure it out at 3am this morning while I was sleeping I remembered there
was an rsync on the databases every 10 mins to replicate the files across
the network.

I killed the rsync.. and all has been flawless! =)

gotta love the dream world. answers are always there!



----- Original Message -----
From: "Baron Schwartz" <baron@xaprb.com>
To: "Justin" <mysql@b0rker.com>
Cc: <mysql@lists.mysql.com>
Sent: Monday, September 03, 2007 4:42 PM
Subject: Re: servers full potential / FT searches locking tables


> Justin wrote:
>> lockup just happened again.. here's a innodb status.
>
> InnoDB status will be basically useless, as full-text is only applicable
> to MyISAM, and indeed your status output shows only one transaction is
> running (the one running 'show innodb status') and InnoDB has done zero
> work since the server was started (look in the ROW OPERATIONS section).
>
> So your server isn't using InnoDB for any tables (at least none you've
> queried since starting the server).
>
> But you're still allocating some memory and other resources to it. If I
> were you I'd put the following in your /etc/my.cnf just to ensure InnoDB
> isn't interfering or complicating anything:
>
> skip_innodb
>
> Cheers
> Baron
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@b0rker.com
>
>

alright.. after some testing this afternoon I'm beginning to wonder if this
is a memory issue.. here's what the test was..

I did a insert select from a table that has approx 500,000 rows and the
table data is about 1gb in size.. the query went and was in the repair by
sorting phase when all of a sudden all my queries started to show up with a
state of Locked.

As soon as I killed the Insert select query all queries ran fine.

Any insight on how I can prevent this would be much appriciated.

thanks

Ju.

I just changed to these values


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
wait_timeout=30
default-character-set=utf8
max_allowed_packet = 14M (lowered from 3000MB)
max_connections = 600 (lowered from 3000)
ft_min_word_len = 3

key_buffer_size=2500M

now looking into table_cache.


----- Original Message -----
From: "Justin" <mysql@b0rker.com>
To: <mysql@lists.mysql.com>
Sent: Tuesday, September 04, 2007 1:46 PM
Subject: Memory Issue would someone confirm


> alright.. after some testing this afternoon I'm beginning to wonder if
> this is a memory issue.. here's what the test was..
>
> I did a insert select from a table that has approx 500,000 rows and the
> table data is about 1gb in size.. the query went and was in the repair by
> sorting phase when all of a sudden all my queries started to show up with
> a state of Locked.
>
> As soon as I killed the Insert select query all queries ran fine.
>
> Any insight on how I can prevent this would be much appriciated.
>
> thanks
>
> Ju.
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@b0rker.com
>
>

Perhaps if you show us the table structure and exactly what operations
you are using, we might be able to provide some insight.. as it is,
your report is too vague to make much sense of it.

- michael

On 9/4/07, Justin <mysql@b0rker.com> wrote:
> alright.. after some testing this afternoon I'm beginning to wonder if this
> is a memory issue.. here's what the test was..
>
> I did a insert select from a table that has approx 500,000 rows and the
> table data is about 1gb in size.. the query went and was in the repair by
> sorting phase when all of a sudden all my queries started to show up with a
> state of Locked.
>
> As soon as I killed the Insert select query all queries ran fine.
>
> Any insight on how I can prevent this would be much appriciated.
>
> thanks
>
> Ju.
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>
>


--
- michael dykman
- mdykman@gmail.com

- All models are wrong. Some models are useful.


--
- michael dykman
- mdykman@gmail.com

- All models are wrong. Some models are useful.