"Query failed. Got error 12 from storage engine" under heavy load.
I've been soak/load testing a search engine application that is running over a snapshot from a larger Oracle database, all is well until somewhere around the 150-180 concurrent user boundary at which point I start seeing this error both via the HTTP interface and via the client.
To be specific I get...
"
Query failed. Got error 12 from storage engine
query: SELECT SQL_CACHE WD_NAMES.BID_UNQ_ID AS BID_UNQ_ID, (MATCH (WD_NAMES.NAME) AGAINST ("test")) * ((NOT ISNULL(WD_SERIES.BID_UNQ_ID)) + 1) as RELEVANCE FROM WD_NAMES LEFT JOIN WD_SERIES USING (BID_UNQ_ID) WHERE WD_NAMES.SUB_ID IS NOT NULL AND MATCH (WD_NAMES.NAME) AGAINST ("test") ORDER BY RELEVANCE DESC, BID_UNQ_ID LIMIT 20
"
As a bit of background I did previously start seeing errors about the servers ulimits which are now...
"
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 1024
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 386553
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 73728
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
"
...I increased the open files value to it's maximum and that seemed to sort it out. The machine is a brand new Dell 2850, Dual Core, Dual Xeons with HT giving 8 logical processors, running Centos and Apache 1.3.34 + PHP 5.1.2, 4G of RAM and doing very little else. RAID drives and all the usual other stuff.
There are three databases, the main one has 34 tables ranging from a dozen or two records to another that has approximately 5.5 million entries. Total disk size is somewhere in the region of 2.5G with everything indexed and optimized.
I've been using a small shell script spread across other machines that spawns off say 50 children and simply requests dictionary words via Curl against the basic search feature of the site as fast as possible. The server is very loaded but manages to return results in a couple of seconds or so when around the 150 concurrent user mark (although I think some of the delay is due to other latencies) but any higher than that and we start hitting this problem.
It's mainly running Full Text queries and a few other bit's and bobs, the SQL has been checked through the optimizer and I think there was only one particular query that needs attention left (not involved in this test). Below is the main jist of the config file, any general suggestions/recommendations would be appreciated but enlightenment as to how to get past these "Got error 12 from storage engine" would be most appreciated, or have I found the limits?
Regards,
Philip Mather
"
[mysqld]
interactive_timeout=60
wait_timeout=60
max_connections=500
max_allowed_packet=8M
max_tmp_tables=256
server-id=686
skip-networking
skip-name-resolve
skip-bdb
skip-innodb
skip-ndbcluster
skip-external-locking
ft_min_word_len=3
ft_stopword_file=/home/databases/mysql/stopwords.txt
tmpdir=/tmp
log-slow-queries=/home/databases/mysql/slow-queries.log
table_cache=1024
thread_cache_size=16
thread_concurrency=16
query_cache_type=1
query_cache_size=2G
query_cache_limit=2G
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
join_buffer_size=2M
key_buffer_size=512M
myisam_sort_buffer_size = 8M
"