OBSERVATION #1
Look at this query
SELECT * FROM likes WHERE userid='100002047302002' and pageid='113623891994626' Limit 1
The likes table have no indexes other than the PRIMARY KEY. Please run this:
ALTER TABLE likes ADD INDEX userid_pageid_ndx (userid,pageid);
Unfortunately, you cannot index likes because of the datatype (MEDIUMTYPE). Please consider changing the datatype of userid and friendtype.
Your should create a new version of the likes table.
CREATE TABLE likesnew LIKE likes;
ALTER TABLE likesnew MODIFY COLUMN userid CHAR(15) NOT NULL;
ALTER TABLE likesnew MODIFY COLUMN friendid CHAR(15) NOT NULL;
ALTER TABLE likesnew ADD INDEX userid_pageid_ndx (userid,pageid);
ALTER TABLE likesnew DISABLE KEYS;
INSERT INTO likesnew SELECT * FROM likes;
ALTER TABLE likesnew ENABLE KEYS;
ALTER TABLE likes RENAME likesbak;
ALTER TABLE likesnew RENAME likes;
Please implement the datatype suggested.
Looking at the other query
SELECT * FROM friends WHERE userid='100001087100886' and friendid='100005456647732' Limit 1
I find the same story. Please make the same changes
CREATE TABLE friendsnew LIKE friends;
ALTER TABLE friendsnew MODIFY COLUMN userid CHAR(15) NOT NULL;
ALTER TABLE friendsnew MODIFY COLUMN friendid CHAR(15) NOT NULL;
ALTER TABLE friendsnew ADD INDEX userid_pageid_ndx (userid,pageid);
ALTER TABLE friendsnew DISABLE KEYS;
INSERT INTO friendsnew SELECT * FROM friends;
ALTER TABLE friendsnew ENABLE KEYS;
ALTER TABLE friends RENAME friendsbak;
ALTER TABLE friendsnew RENAME friends;
If you ever want to revert back to the old tables, then do this:
CREATE TABLE likesnew LIKE likesbak;
INSERT INTO likesnew SELECT * FROM likes;
ALTER TABLE likes RENAME likeszap;
ALTER TABLE likesnew RENAME likes;
CREATE TABLE friendsnew LIKE friendsbak;
INSERT INTO friendsnew SELECT * FROM friends;
ALTER TABLE friends RENAME friendszap;
ALTER TABLE friendsnew RENAME friends;
OBSERVATION #2
Your key_buffer_size is way too big. Remember the query I asked you to run ?
mysql> SELECT SUM(index_length) FROM information_schema.tables WHERE engine='MyISAM';
+-------------------+
| SUM(index_length) |
+-------------------+
| 150684672 |
+-------------------+
1 row in set (0.01 sec)
That's about 150M. So, here is the deal:
If you do not implement OBSERVATION #1 set key_buffer_size to 150M
[mysqld]
key_buffer_size=150M
If you do implement OBSERVATION #1, after making the new indexes, rerun that query like this:
SELECT SUM(index_length)/power(1024,2) RKBS
FROM information_schema.tables WHERE engine='MyISAM';
Whatever number comes back, use that number to set the key_buffer_size.
OBSERVATION #3 (Optional)
Keep in mind that the MyISAM Storage Engine does not take advantage of Multiple CPUs/Core no matter how many CPUs/Cores are installed. You need to consider changing all the tables to InnoDB. Here are my past posts on MySQL 5.5 and activating multiple cores for InnoDB
Depends on your options FIELDS and LINES used in the LOAD DATA statement.
MySQL documentation
From MySQL doc:
Handling of NULL values varies according to the FIELDS and LINES
options in use:
For the default FIELDS and LINES values, NULL is written as a field
value of \N for output, and a field value of \N is read as NULL for
input (assuming that the ESCAPED BY character is “\”).
If FIELDS ENCLOSED BY is not empty, a field containing the literal
word NULL as its value is read as a NULL value. This differs from the
word NULL enclosed within FIELDS ENCLOSED BY characters, which is read
as the string 'NULL'.
If FIELDS ESCAPED BY is empty, NULL is written as the word NULL.
With fixed-row format (which is used when FIELDS TERMINATED BY and
FIELDS ENCLOSED BY are both empty), NULL is written as an empty
string. Note that this causes both NULL values and empty strings in
the table to be indistinguishable when written to the file because
both are written as empty strings. If you need to be able to tell the
two apart when reading the file back in, you should not use fixed-row
format.
Best Answer
This 'random person on the Internet' can shed some light...
information_schemais not a 'real' database (until MySQL 8.0), rather it is derived from a bunch of data that may or may not be in RAM already..frmfiles (etc). "Thousands" in your case. So, this could be quite costly.Data_length, etc are relatively stable; I hope your monitoring is not doing this query every minute; that may be a noticeable burden. Once an hour should be sufficient.table_open_cache? Is it more than the number of tables? If not, then this random person on the Internet thinks that you could be in deep ?.I have been involved in the monitoring of hundreds of machines. A simple
dfis sufficient to alert a human to dig into what is causing the problem. Checking the MySQL directory with aduis one of several secondary steps.But, I disagree with every monitor I have seen. I don't care how big the data is. It does not matter if the disk is 90% full -- as long as it is not growing. I do care if it is 20% full but growing so fast that it will fill up before the end of the week.
Sure graphing the size is handy, but that means that a human has to look at it (and not fall asleep). I want an alert. Furthermore, due to cyclical things, such as binlog purging, the graph may look like saw teeth -- up, down, up down! This makes "trend analysis" difficult.
Back to your question of whether that query is "intensive". If Zabbix is worth having it will give you the answer on a silver (or electronic) platter! Does it monitor the "slowlog"? If so, does this query show up? QED.