OVH Community, votre nouvel espace communautaire.

Plantage mysql réguliers


TBC_Ly0n
03/09/2009, 17h50
Une barrette est HS... A remplacer.

vandevan
03/09/2009, 17h40
salut,

j'ai peut être trouvé la raison...
je viens de tester la mémoire en mode rescue et voici le log :

Current limits:
RLIMIT_RSS 0xffffffff
RLIMIT_VMEM 0x10000
Raising limits...
Allocated 3848273920 bytes...trying mlock...success. Starting tests...

Testing 3848269824 bytes at 0x1287b000 (4088 bytes lost to page alignment).

Run 1:
Test 1: Stuck Address: Setting...Passed.
Test 2: Random value: Setting...Testing...Passed.
Test 3: XOR comparison: Setting...Testing...
FAILURE: 0x0498c2a9 != 0x0498c2b9 at offset 0x158f455f.
Skipping to next test...
Test 4: SUB comparison: Setting...Testing...
FAILURE: 0x90c9fa94 != 0x90c9faa4 at offset 0x158f455f.
Skipping to next test...
Test 5: MUL comparison: Setting...Testing...
FAILURE: 0x53643dfc != 0xca84432c at offset 0x158f455f.
Skipping to next test...
Test 6: DIV comparison: Setting...Testing...
FAILURE: 0x00000000 != 0x00000001 at offset 0x158f455f.
Skipping to next test...
Test 7: OR comparison: Setting...Testing...Passed.
Test 8: AND comparison: Setting...Testing...Passed.
Test 9: Sequential Increment: Setting...Testing...Passed.
Test 10: Solid Bits: Setting...
FAILURE: 0x00000010 != 0x00000000 at offset 0x162f935f.
Skipping to next test...
Test 11: Block Sequential: Setting...
FAILURE: 0x20202020 != 0x20202030 at offset 0x15e3955f.
Skipping to next test...
Test 12: Checkerboard: Setting...
FAILURE: 0xaaaaaaba != 0xaaaaaaaa at offset 0x1633b35f.
Skipping to next test...
Test 13: Bit Spread: Setting...
FAILURE: 0xfffffffb != 0xffffffeb at offset 0x1709035f.
Skipping to next test...
Test 14: Bit Flip: Setting...
FAILURE: 0x00000011 != 0x00000001 at offset 0x161f935f.
Skipping to next test...
Test 15: Walking Ones: Setting...Passed.
Test 16: Walking Zeroes: Setting...
FAILURE: 0xffffffef != 0xffffffff at offset 0x1683955f.
Skipping to next test...
Run 1 completed in 714 seconds (10 tests showed errors).
munlock'ed memory.
1 runs completed. 10 errors detected. Total runtime: 714 seconds.

Exiting...



donc 10 erreurs cela expliquerait le log sql qui indique un probleme de configuration mémoire. la config est bonne mais si la mémoire flanche...

j'ouvre un ticket chez ovh on verra bien.
Merci de ton aide et je garde l'idée sous le coude si le changement de mémoire ne résout pas mon probleme.

TBC_Ly0n
03/09/2009, 17h25
1 - la R2 en 64 bits est réputée pour poser problème avec Mysql en 64 bits.
2 - Est-ce que tu peux faire un dump complet sans erreur ? L'idée serait de faire un dump complet... Dropper les databases, et réimporter toutes les bases.
Comment fais-tu tes backups ?
3 - Tu n'utilises que du MyISAM ?

vandevan
03/09/2009, 16h05
Bonjour,

je possède un site hébergé sur 2 serveurs dèdiés. L'un pour apache l'autre pour la base. Depuis hier soir la base de donnée mysql plante a intervalles irréguliers.
En gros déjà 5 plantage pour la journée.
Cette base est donc sur un dèdié gentoo realese 2, et tourne très bien depuis mars de cette année (environs 50 000 visiteurs / jours).

voici quelques infos :
df -h
Sys. de fich. Tail. Occ. Disp. %Occ. Monté sur
/dev/md/1 3,0G 1,8G 1,1G 64% /
udev 2,0G 176K 2,0G 1% /dev
/dev/md/2 685G 4,3G 646G 1% /home
shm 2,0G 0 2,0G 0% /dev/shm


./tuning-primer.sh
mysqld is alive

-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 5.0.44 x86_64

Uptime = 0 days 0 hrs 20 min 2 sec
Avg. qps = 309
Total Questions = 371701
Threads Connected = 2

Warning: Server has not been running for at least 48hrs.
It may not be safe to use 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 NOT enabled.
Current long_query_time = 5 sec.
You have 227 out of 371729 that take longer than 5 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 = 16
Current threads_cached = 10
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 200
Current threads_connected = 6
Historic max_used_connections = 118
The number of used connections is 59% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 2 G
Configured Max Per-thread Buffers : 2 G
Configured Max Global Buffers : 634 M
Configured Max Memory Limit : 3 G
Physical Memory : 3.84 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 82 M
Current key_buffer_size = 600 M
Key cache miss rate is 1 : 154
Key buffer fill ratio = 1.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 8 M
Current query_cache_used = 4 M
Current query_cache_limit = 6 M
Current Query cache Memory fill ratio = 60.18 %
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 = 6 M
Current read_rnd_buffer_size = 1 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly

OPEN FILES LIMIT
Current open_files_limit = 4210 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 2000 tables
You have a total of 148 tables
You have 513 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 64 M
Current tmp_table_size = 64 M
Of 4182 temp tables, 0% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 5 M
Current table scan ratio = 34450 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 2564
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'.

ns351774 tmpsql #


voila pour la mémoire, évidement vu que ça plante toute les deux heures, je n'ai pas 48 heures de recul, mais la charge serveur est tout a fait normale.


Et maintenant la partie rigolotte... enfin si on veux... le log mysql :


090903 14:43:48 - 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=629145600
read_buffer_size=6287360
max_used_connections=48
max_connections=200
threads_connected=38
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3071198 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x2aae2a84a460
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=0x40e59068, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x9b0034342e302e35
Stack trace seems successful - bottom reached
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...
thd->query at 0x2aae34a99e20 is invalid pointer
thd->thread_id=317083
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
090903 14:47:20 [Warning] Found an entry in the 'db' table with empty database name; Skipped
090903 14:47:20 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.44' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Gentoo Linux mysql-5.0.44-r2


je suis paumé.... j'ai checké mes table certainnes étaient en corrupt, mais malgré les repair table cela replante une heure apres, quelqu'un peux t il m'aider a me sortir de cette désagréable situation ?

merci de votre aide.