Some weeks now, i have a very serious problem: one server that normally had 0.10-0.60 load, it started giving high load, until 70 and of course the server crashes. I had to suspend the site, in order to avoid server crash. Any time i unsuspend it, in a few minutes, even with one only user online, the load starts increasing second by second and in 4-5 minutes, it goes so high until the server crash. I d really nees some help from a mysql guru. I have no idea if this has to do with server parameteres or with website database. The site is a forum with a db of 1.6GB and until this situation, it was running fast and smoothly. Please have in mind that i have no knowledge about these situtations, so please inform me in a detailed way, if possible. Also let me know what informations do you need in order to help, so as i post them here. Thanks in advance for any help./
Hope you have some backups, what does your log file say? You might want to try myisamchk if you are using MyISAM.
i do have back up. Which log file should i check? Is this website database related or server related? And how can i try myisamchk?
Check the log files from Apache and MySQL, maybe your database is not efficient enough and may need some pruning.
if you have linux you'll probably find your mysql server error log in /var/log/ if windows, it's some .err file somewhere. Of course you can start the mysql server with --log-error[=file_name] to define where to log errors. There is a chance that you have a hardware problem too, have you checked your hard drives etc?
In this case, arent there any other options instead of pruning? Im in linux, so i went to var/log/ but i coundnt find any mysql related file. Also, if it is a hardware error, there are 3-4 more small websites in this server. Wouldnt they behave strange, too?
i ll try the db username and password that you suggest and i ll report back the results. Do you think that a ddos attack could be the reason for this unexpected continuus load?
ye on my site i havw this porblem bat rename databse and changhe server to other host my problem solved
This can happen from poorly written SQL, or code causing loops. A very simple example. You do an SQL up date, your site refreshes with the new data, your site thinks something has changed causing a new update, loop continues. I would shut the site down by changing the domain default page, and go through the site one page at a time until you find the one causing the problem. Review the code and sql to find the loop.
live report I ve changed the db username and password, so far, 15 minutes now, the site runs smoothly. Waiting for more monitoring.... and for possible good news
actually it is a forum so its hard to find any page causing the error. Today, it was in 0.20 load to 0.70 for 2 hours. Then suddenly, it again went high up to 60. It seems that there is a mysql query running continusly. So, does it seem like a server side issue 9msql parametres/optimization or it seems like a website issue? How can i find some msql professional to work with this situation? My dilima with that is that I d really like to pay someone to work for it, but i m afraid of giving access to someone that i dont know at all.
allrite.. try mysql command SHOW PROCESSLIST; to see what kind of querys there are going on. I still have a strong feeling it is a HDD problem. What RAID are you using ?
here is proccesslist, seconds before the server crashes. Id User Host db Command Time State Info 104 eximstats localhost eximstats Sleep 61 NULL 38297 leechprotect localhost leechprotect Sleep 4390 NULL 42747 ***** localhost ************ Query 306 Copying to tmp table SELECT t.lastpost, t.tid, t.subject, l.dateline FROM xmbF_threads t LEFT JOIN xmbF_forums f ON (f.fi 42755 ****** localhost ****** Query 91 Copying to tmp table SELECT p.pid, p.dateline, t.subject, t.tid FROM xmbF_posts p LEFT JOIN xmbF_threads t ON (t.tid=p.ti 43055 ***** localhost ******* Query 80 Locked UPDATE xmbF_threads SET views=views+1 WHERE tid=10034 43071 ***** localhost ******* Query 64 Locked SELECT count(*) FROM xmbF_threads
the server is: AMD Athlon 64 X2 5600+ Dual Core 4 GB DDR2 RAM 2 x 400 GB SATA II 1 GBit OnBoard ------------------------ the etc/my.cnf has [mysqld] skip-bdb skip-innodb set-variable = max_connections=200 set-variable = open_files_limit=1024 set-variable = concurrent_insert=2 safe-show-database old-passwords = 1 set-variable = max_allowed_packet=16M default-character-set = utf8 character-set-server = utf8 collation-server = utf8_general_ci set-variable = query_cache_size=1355776 set-variable = query_cache_type=2 set-variable = max_heap_table_size=256M set-variable = tmp_table_size=256M set-variable = key_buffer_size=1717986917 set-variable = join_buffer_size=2M set-variable = table_cache=1024 log-slow-queries long_query_time = 5
42747 ***** localhost ************ Query 306 Copying to tmp table SELECT t.lastpost, t.tid, t.subject, l.dateline FROM xmbF_threads t LEFT JOIN xmbF_forums f ON (f.fi Ok this query is really slow and because of it the following querys gets locked. The locks build up and crash the server. Now atleast try to check and repair those tables involved in that query. Also if it is mysql problem, update might fix it.
after some mysql queries optimizzations, it seems to run fine, 2 days now. I ll report here for any news. Thank you for your kind help so far.