So, I think we need a fuller view.
In the other VPS is hard to say, the sites could not stay online 24 hours below...
However, in Digitalocean I have 2 servers, one in NY and the other in Bangalore, but they practically go into crisis together.
The error that I find most often in the logs is always that of too many redirects, it is not gone and I do not know what else to do, but it is an error that there is always, not only when the server goes into crisis, but also when the CPU works at 20-30%.
The page with most visits in all my sites is the internal search engine, about 80-90% of the visits and a MySQL table that also contains about 80-90% of the content of the site, in this case about 400,000 search tags, about 5000 pages of content.
This is the structure of the table:
Code:
-- Struttura della tabella `tgp_search`
CREATE TABLE `tgp_search` (
`id_search` int(11) NOT NULL,
`query` varchar(255) NOT NULL,
`views` int(11) NOT NULL DEFAULT '1',
`insitemap` int(1) NOT NULL DEFAULT '0',
`data_ins` varchar(255) NOT NULL DEFAULT '1388796621',
`last_mod` varchar(255) DEFAULT NULL,
`engine` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- Indici per le tabelle `tgp_search`
ALTER TABLE `tgp_search`
ADD PRIMARY KEY (`id_search`),
ADD KEY `query` (`query`);
ALTER TABLE `tgp_search` ADD FULLTEXT KEY `query_2` (`query`);
-- AUTO_INCREMENT per la tabella `tgp_search`
ALTER TABLE `tgp_search`
MODIFY `id_search` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=409143;
These are the queries that are executed on the page, in this sequence:
Code:
SELECT query, MATCH(query) AGAINST('tits') as score_search FROM tgp_search WHERE MATCH(query) AGAINST('tits') ORDER BY views, score_search DESC LIMIT 25
UPDATE tgp_search SET views='2407', last_mod = '1509683869' WHERE query = :query
SELECT SQL_CALC_FOUND_ROWS id_photo, title, description, url, img, MATCH(title, description) AGAINST('tits') as score FROM tgp_photo WHERE MATCH(title, description) AGAINST('tits') ORDER BY score DESC LIMIT 0, 66
SELECT FOUND_ROWS() AS 'found_rows';
SELECT query FROM tgp_search WHERE MATCH(query) AGAINST('tits') ORDER BY views DESC LIMIT 25
This,
Code:
SELECT SQL_CALC_FOUND_ROWS id_photo, title, description, url, img, MATCH(title, description) AGAINST('tits') as score FROM tgp_photo WHERE MATCH(title, description) AGAINST('tits') ORDER BY score DESC LIMIT 0, 66
has always left me some doubt about its use of resources... They seem to be a fantastic and wonderful type of query for search engines, but maybe it weighs too much on the server?
Compared to a simple query like: LIKE '% boobs% '
I have made complete PDFs of the nixstat statistics of the last 15 days, including everything.
I/O is used very little, as well as the disk, the RAM is little, but we only have that, the most active are CPU and MySQL process.
ubuntu-1gb-nyc3-01-NIXStats.pdf ubuntu-2gb-blr1-14-04-3-NIXStats.pdf
Obvious that the site uses heavily MySQL, when the CPU goes to 100% the MySQL process goes to 150-200% (although I do not understand how it is possible
)
I activated the log on long queries, about 4 hours ago, for now it is empty.
I almost thought I'd try to block Bing for a few days, just to see what happens, and if it can be his fault, but maybe it's a stupid idea.
I'm very undecided on whether or not to use the last-mod and if-modified-since headers, because I'm always afraid that once you set, big G you don't consider it anymore.
Anyway, now my sites are online 24 hours a day, seem fast and work without problems even when the server goes into crisis.