Hosting Company Response to Website Crashes - Right/Wrong??

Hello,

I'm on a dedicated server and recently my website keeps crashing and to fix this I have to go to cPanel>WHM and restart my mySQL service. Once the mySQL service restarts everything works fine again.

I am running some large queries on certain links that I have that I think is probably causing these crashes.

Anyway, I asked my hosting company to look into this and this is what they responded to me. I just wanted to get opinions from other expierenced mySQL users to see if what they are telling me is 100% right or wrong??

===hosting company response start===

Hello,

I had gone through your dedicated server and found that you server memory usages by mysql is very high.

The following is the current memory usages which is given below as:

Cpu(s): 99.0% us, 1.0% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 499664k total, 469600k used, 30064k free, 49784k buffers
Swap: 1052248k total, 117696k used, 934552k free, 251860k cached
Which user (blank for all):
PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
19322 mysql 16 0 99.5 53:36.37 5.3 110m 26m 3400 S mysqld

Your server has left 30 MB of memory which is not sufficient to execute mysql queries and thats why mysql keeps crashing. For this case i advise you to please install one GB additional memory and it will sort out all your issue.

I hope it helps. If you have any questions, please don't hesitate to ask us, we will be happy to answer them. Please update us if you feel any issues. Please feel free to contact us for further help. We are committed to making your hosting experiences pleasant and fulfilling.

Thank you for contacting the support team.

===hosting company response end===

They want to charge me $20.00/month to add 1GB of memory to my server. I didn't think I would have to physically add more memory to fix this mySQL issue.

I thought that there was just a setting on the server for mySQL where I can increase the memory size that mySQL uses - is what I'm thinking right or is the only way to fix this issue is by adding more memory to the server??

Thank you,

Bernie

A server should never ever crash when it needs more memory. I might be slow (because of swapping to disk), but a crash? No way!

See the logs to find out what query slows things down and what eats up all your memory.

MySQL ain't the best database, but things like this should not happen. $20 extra doesn't sound like a solution to me.

Hi,

Yes, that's what I thought too.

I think the issue is with my "quick search" query because it searches in 5 or 6 different fields and I have over 180,000 products in my mySQL database; which probably doesn't make it go run any faster.

So do you think my best solution is to modify the query so it runs better, and not to add the 1GB of RAM memory to the server??

Thank you,

olimits7

Well, whatever the query might do, it shouldn't be possible to crash the server. You could optimize the query, but the risk of crashing the server will remain. It might be a MySQL-issue but could also be a problem with bad configuration, bad hardware, etc. etc. But it shouldn't be the query, a query should never crash a server.

Use EXPLAIN before your query to find out how the database executes the query:
EXPLAIN SELECT blablabla FROM blablabla WHERE etc.

Hi,

Okay, I will try using the "EXPLAIN" at the beginning to see what it says.

I should have picked a better word than "crash" but the whole sever doesn't really physically crash, but "hangs" when the query is running and in turn stops any access to the website.

I asked the following question below again to my hosting company and they told me again that the 1GB RAM memory might help the website.

Do you think they make a good point in their response?? I would really hate to pay that extra $20 a month for no reason!! :-(

===My Question Start===
1. Regarding the 102MB of "swap space", doesn't "swap space" act as virtual memory from the hard drive and is the same as adding physical RAM to the server??

What I mean by this is if my 512MB of RAM is being fully used by the server, the server creates extra virtual memory in "swap space" on the HD to be able to keep on running the other programs, right??

I'm not 100% sure on this, that is why I'm asking if the same performance level can be reached with using "swap space" instead of adding more physical RAM.
===My Question End===

===Hosting Company Response Start===
Regarding the memory issue let me provide more informations.I have checked again your usage :

[root@server ~]# free -m
total used free shared buffers cached
Mem: 487 397 90 0 21 264
-/+ buffers/cache: 111 376
Swap: 1027 113 913

Out of the 487MB of memory on the system, Linux is managing 397MB of that. The important parts to look at here are the -/+ buffers/cache and Swap lines. To obtain accurate memory usage, subtract the 'buffers' and 'cached' values from the 'used' value. In this example, 111MB is actually in use by applications. If you notice, this value is the same as the used value in the buffers/cache line.

Swap space, be it a partition or a file, is much, much slower than actual ram. The only time that swap is used is if all available ram is currently in use. This means that the system has run out of memory and had to start using swap as additional “ram”. A Linux server should never have to use swap. If a server is using swap, it is a good indication that a ram upgrade is needed.

As my colleague told you the logs and usage reports indicated that your server is running out of ram due mysql processing and because its using swap means there you must consider to upgrade.
===Hosting Company Response End===

Thank you, again!!

olimits7

that has "table scan" written all over it

Hi,

Can you explain what "table scan" is; I'm not familiar with that term?

I still don't know if the reason for my website hanging is due to having a small amount of RAM memory or because the "quick search" query has not been written logically.

What do you guys think??

Thank you,

olimits7

"table scan" means that the query goes through all the rows of the table, applying the WHERE criteria to each row

compare to an indexed search, where the query uses an index to find the keys it wants, and then retrieves only those rows

here, i'll give you a comparison

grab your white pages telephone book (even if nobody has the actual book in their home any more, y'all still remember how they work, yeah?)

now find how many people have a last name Wilson with first initial Q

easy, right? you jumped right to the Ws, scanned ahead to find the Wilsons, and searched only the Wilsons for first initial Q

okay, now find how many people live on Elm street

you have to read the entire friggin book from front to back

that's a table scan

Optimize your queries (seach Google or these forums for examples), then tune your server parameters (again search before posting), then upgrade your hardware and tune your server parameters again.
Search before posting means come here with an idea or action plan, basic as it can be.
Describe your problems carefully and with details, don't say "crash" when you mean "crawl"