Why does a site with the simplest sql queries crash VPS already with 50 active visitors?

Question

PHP

There is a site with a fairly large attendance. Previously, he was on a regular hosting and everything worked well. The maximum response time did not exceed a second. But due to the growth of visitors, it was decided to transfer it to VPS.
After that, terrible things began. Already with 3 requests per second and 50 active users, the site response time exceeded 30 seconds! I wrote to the support of the hoster and they answered me that the problem is database queries (mysql), which eat up 97% of server resources and the problem is in optimizing the VPS itself. Like, their servers for hosting are optimized as much as possible and therefore such a problem did not appear there.
And now I’m thinking how it is necessary to optimize the VPS so that it holds at least 20 users. Laughter and nothing more.
Here is an example of the requests that the site performs:

$res = mysql_query("SELECT * FROM content WHERE category = 'new' AND title LIKE '%".mysql_real_escape_string($search)."%' GROUP BY thumb ORDER BY id DESC LIMIT $p, $limit") ;

There is nowhere to be easier, and these requests are definitely not frequent with 50 users.
Next, I will show graphs from loadimpact tests (from 0 to 100 visitors). The first 2 pictures are the graphics of the site with VPS (everything is bad):

And now I will show for comparison the graphics of the same site, but with a regular hosting:

Please tell me which way to dig and where to start. Maybe there are some already well-known rakes that I have now stepped on.
If you need any additional information, I will provide it.
Thanks in advance!

Answer 1

Really. Some stupid VPS. Can LIKE '%...' slow down?

Answer 2

But I used to go by bus and get to work in 20 minutes. A week ago I bought a car, so I get into traffic jams in it, and it drives slowly. The service said that the car is optimized for driving, everything should be in order. Here are the schedules of my trips by car
[picture jpg]
but on the bus before
[picture jpg]
Please tell me what could be the problem.

Answer 3

The problem is LIKE ' % ...'. This query does not use indexes and searches row by row. For search it is better to use Sphinx or something like it.

Answer 4

Do the output of this query
Then go to phpMyAdmin (or whatever you have there) and execute the request.
We add before the EXPLAIN request and see what happens.

Answer 5

1. Oversell
2. Slow storage
3. Still, shared hosting can be faster than cheap VPS.

Answer 6

1. Regular hosting is stronger than many VPS - at the time of the request, almost all the power of a physical server is available to you, very rarely hosters make restrictions here
2. The request can be simple, but did you write the number of data in this table 100? 10,000,000?
3. Do you have one table? Why sin precisely on this request? Again, how much data is in the database and what structure (indexes)
4. It used to slow down the VPS until it was transferred to another piece of hardware, there are bad neighbors and they completely rape the hard drive.

Answer 7

To identify the cause of the brakes, use the Slow Query Log (you can read it here ). When you find slow queries, try to optimize them. Perhaps it makes sense to cache the results of slow queries in memory (memcache, redis, etc.).
And another little tip to improve database performance - switch from mysql (which is already deprecated) to mysqli or PDO . It is not difficult to migrate an existing system to Mysqli, it can work in functional mode and is syntactically similar to mysql.

Answer 8

1. Well, everyone said about LIKE. It is obvious. Now it's obvious.
2. What do you have with the RAM on the VPS? What's the OS?
3. Kill any Apache and PHP extensions you don't use. It won't do much, to be honest.
4. How many requests do you have per user when he accesses one page!? And then suddenly you have a story like vBulletin stuffed with mods!? 50 requests per user request.
5. Are there indexes in the database everywhere!? Can you do more!? Let the size of the database increase, but the speed can grow.
Give more information to the public. And then everyone will find fault with LIKE.

Answer 9

did you like that called the simplest request?

Answer 10

You have a virtual machine on openvz, most likely. Run from there)
Moreover, most likely, it is really badly tuned.
Come to Jabber, I'll help you settle on digitalocean (there is kvm), I'll tell you how to set it up. The first month at do is free.

Answer 11

Try rewriting LIKE '%...%' using MyISAM's FULLTEXT. To do this, just add a FULLTEXT index and rewrite the query a bit. The keyword "MyISAM FULLTEXT search tutorial" will google a lot of articles.

Answer 12

this query may well use an index on category to search for category = 'new'
Another question is that then there is a grouping in the complete absence of aggregate functions .. why it is not clear.

Answer 13

The very fact that your "regular hosting" holds the load better than VPS indicates that something is wrong with the VPS. First, what kind of MySQL do you have? Try MariaDB, tune it up. Try to give it as little RAM as possible (google "mysql low memory", see my-small.cnf example). What storage engine are you using, InnoDB/MyISAM? If not Inno, try with it, and if it's not relevant, disable it.
You may have general_log enabled in MySQL - this is a log of all queries to the DBMS. Turn it off, it really strains the machine, especially if IO slows down!
Well, for starters, think about creating a cache using software so that you don’t constantly go to the DBMS.