Portal Home > Knowledgebase > Articles Database > mysql server engine problem
mysql server engine problem
| Posted by nomankhn, 11-25-2009, 04:42 AM |
| Hello folks
i have one mysql server running but it give following error.
091125 8:25:46 [ERROR] /usr/local/mysql/bin/mysqld: Sort aborted
/usr/local/mysql/bin/mysqladmin: query failed; error: 'Got error 12 from storage engine'
While error log
__________________
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=536870912
read_buffer_size=2093056
max_used_connections=14
max_connections=500
threads_connected=14
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1349676 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0xa259df8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x2a7078, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x816b962
0x148420
0x81fec4b
0x81fec4b
0x82483bd
0x81bbad6
0x81ba8c9
0x81ba71c
0x81ba410
0x81abf95
0x8249530
0x8151914
0x814dc4d
0x814ea39
0x846f745
0x8135ddc
0x81195e1
0x813a063
0x81195e1
0x813a0df
0x81195e1
0x813a063
0x81ba800
0x81ba71c
0x81ba8c9
0x81ba71c
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Us...ack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
RAM Status
__________
free -m
total used free shared buffers cached
Mem: 3042 2469 573 0 642 1321
-/+ buffers/cache: 504 2537
Swap: 4102 0 4102
my.cnf
______
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
datadir = /data/db
#key_buffer = 16M
max_allowed_packet = 16M
table_cache = 2048
sort_buffer_size = 16M
read_buffer_size = 2M
myisam_sort_buffer_size = 128M
thread_cache = 8
query_cache_size = 128M
query_cache_limit = 2M
max_connections = 500
slave-skip-error = 1062
bulk_insert_buffer_size = 64M
key_buffer_size = 512M
max_heap_table_size = 64M
tmp_table_size = 64M
thread_concurrency = 4
pid-file=/data/logs/mydb.pid
log-error=/data/logs/mydb.log
long_query_time = 10
log_slow_queries=/data/logs/mydb-slow_queries.log
#log-long-format
skip-bdb
#memlock
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
#log-bin
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 3
relay-log = /data/logs/db-relay-bin
relay-log-index = /data/logs/db-relay-index
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /data/innodb_data/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /data/innodb_data/
innodb_log_arch_dir = /data/innodb_data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 40M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 250M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
DATABASE & TABLES
_________________
Its mix contains INNODB & MYISAM. Total databases 20.
Please give your suggestions.
|
| Posted by ksv2nash, 11-25-2009, 05:47 AM |
| Hi,
For this use following scripts for ur my.cnf
nano /etc/my.cnf
delete all lines in it and then paste following
------------------------------------------------------
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_user_connections=25
max_connections=500
interactive_timeout=10
wait_timeout=10
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10# Try number of CPU's*2 for thread_concurrencythread_concurrency=8
myisam_sort_buffer_size=64M
#log-binserver-id=1
old-passwords = 1
[mysql.server]
user=mysqlbasedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192
[mysqldump]
quickmax_allowed_packet=16M
[mysql]
no-auto-rehash
#safe-updates
[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeout
Thanks
skype: kedar.varma
|
| Posted by Miraenda, 11-25-2009, 11:36 AM |
| If you might be running out of memory, your key_buffer_size is too large. That's a lot of memory you are giving the key buffer (512M). I'd suggest just changing that value rather than revising all of your values as was suggested lol
Possibly, just change the key_buffer_size to 80M or 100M in your /etc/my.cnf and then re-start MySQL for the new values to take effect.
|
| Posted by nomankhn, 11-29-2009, 06:07 AM |
| Thanks for kind reply, i have done this change after that mysql is running fine from two days and some hours, i have check some mysql optimization scripts so please check below detail and suggest me. Earlier in first post i have already post my.cnf completely.
How to check/repair myisam table
How to check/repair innodb table
How to defragment all tables.
Please i am waiting for your kind good suggestions.
_______________________________________________________________________________________________
http://mysqltuner.com/mysqltuner.pl
_______________________________________________________________________________________________
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.18-max-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 5G (Tables: 4090)
[--] Data in InnoDB tables: 514M (Tables: 324)
[--] Data in MEMORY tables: 0B (Tables: 16)
[!!] Total fragmented tables: 78
-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 5h 1m 48s (1M q [5.747 qps], 2K conn, TX: 460M, RX: 563M)
[--] Reads / Writes: 84% / 16%
[--] Total buffers: 952.0M global + 18.5M per thread (500 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 10.0G (335% of installed RAM)
[OK] Slow queries: 0% (116/1M)
[OK] Highest usage of available connections: 11% (57/500)
[OK] Key buffer size / total MyISAM indexes: 200.0M/1.9G
[OK] Key buffer hit rate: 99.9% (55M cached / 51K reads)
[OK] Query cache efficiency: 87.0% (815K cached / 936K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (5 temp sorts / 10K sorts)
[!!] Temporary tables created on disk: 30% (7K on disk / 25K total)
[OK] Thread cache hit rate: 93% (141 created / 2K connections)
[!!] Table cache hit rate: 0% (2K open / 1M opened)
[!!] Open file limit used: 87% (4K/4K)
[OK] Table locks acquired immediately: 97% (182K immediate / 187K locks)
[!!] InnoDB data size / buffer pool: 514.7M/512.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
tmp_table_size (> 64M)
max_heap_table_size (> 63M)
table_cache (> 2048)
open_files_limit (> 4606)
innodb_buffer_pool_size (>= 514M)
_______________________________________________________________________________________________
http://day32.com/MySQL/tuning-primer.sh
_______________________________________________________________________________________________
MySQL Version 5.0.18-max-log i686
Uptime = 2 days 5 hrs 4 min 0 sec
Avg. qps = 5
Total Questions = 1097411
Threads Connected = 8
Server has been running for over 48hrs.
It should be safe to follow these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/...variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 10 sec.
You have 116 out of 1097425 that take longer than 10 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.0/...-recovery.html
WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 3
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 500
Current threads_connected = 8
Historic max_used_connections = 57
The number of used connections is 11% of the configured maximum.
Your max_connections variable seems to be fine.
INNODB STATUS
Current InnoDB index space = 199 M
Current InnoDB data space = 514 M
Current InnoDB buffer pool free = 45 %
Current innodb_buffer_pool_size = 512 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory
MEMORY USAGE
Max Memory Ever Allocated : 1.89 G
Configured Max Per-thread Buffers : 9.02 G
Configured Max Global Buffers : 888 M
Configured Max Memory Limit : 9.89 G
Physical Memory : 2.97 G
nMax memory limit exceeds 90% of physical memory
KEY BUFFER
Current MyISAM index space = 1.89 G
Current key_buffer_size = 200 M
Key cache miss rate is 1 : 1053
Key buffer free ratio = 88 %
Your key_buffer_size seems to be fine
QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 76 M
Current query_cache_limit = 2 M
Current Query cache Memory fill ratio = 59.92 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 16 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 132.00 K
You have had 198 queries where a join could not use an index properly
You have had 1 joins without keys that check for key usage after each row
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.
OPEN FILES LIMIT
Current open_files_limit = 4606 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
You currently have open more than 75% of your open_files_limit
You should set a higher value for open_files_limit in my.cnf
TABLE CACHE
Current table_cache value = 2048 tables
You have a total of 4447 tables
You have 2048 open tables.
Current table_cache hit rate is 0%
, while 100% of your table cache is in use
You should probably increase your table_cache
TEMP TABLES
Current max_heap_table_size = 63 M
Current tmp_table_size = 64 M
Of 17889 temp tables, 30% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.
TABLE SCANS
Current read_buffer_size = 1 M
Current table scan ratio = 4673 : 1
You have a high ratio of sequential access requests to SELECTs
You may benefit from raising read_buffer_size and/or improving your use of indexes.
TABLE LOCKING
Current Lock Wait ratio = 1 : 35
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.
|
| Posted by ramsh, 11-29-2009, 10:05 AM |
| You can run the following command to check/repair a mysql table.
Regarding the innodb you can check the tutorial http://www.mysqlperformanceblog.com/...le-corruption/
|
Add to Favourites 
Print this Article
Also Read
