Alireza's questions

Há momentos em que algumas consultas pesadas aumentam a carga do servidor e, após algum tempo, a CPU e a memória atingem 100%.

Este é my.cnfo arquivo de configuração:

[mysqld]
expire_logs_days= 2

pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/storage/mysql

# LOGGING #
log-error                      = /var/storage/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/storage/mysql/mysql-slow.log

# MyISAM #
key-buffer-size                = 32M
myisam-recover-options         = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# BINARY LOGGING #
log-bin                        = /var/storage/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb-log-files-in-group      = 2
innodb_log_file_size=500M
innodb_buffer_pool_size=6G
innodb_flush_log_at_trx_commit=2
# recently added for compression, omit it if mysql has problems with it
innodb_file_format=Barracuda

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


collation-server = utf8mb4_general_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4
skip-character-set-client-handshake

[client]
default-character-set   = utf8mb4

[mysql]
default-character-set   = utf8mb4

O servidor possui 6 núcleos com 16 GB de RAM. Alguma sugestão para restringir MySQLe torná-lo seguro?

EDIT-1 Esta é uma das consultas lentas que levou 4,3 segundos MySQLpara retornar os dados e examinou cerca de 3 milhões de linhas:

# Time: 190107  6:22:30
# User@Host: root[root] @ localhost [127.0.0.1]
# Thread_id: 204732  Schema: my_db  QC_hit: No
# Query_time: 4.306051  Lock_time: 0.000058  Rows_sent: 0  Rows_examined: 3253235
# Rows_affected: 0
SET timestamp=1546842150;
SELECT ((old_credit + (1*amount))/10) credit
FROM `credits`
WHERE user_id=' ' or user_id=(SELECT user_id FROM accounts WHERE email=' ')
ORDER BY id DESC
LIMIT 1;

A consulta é criada por um aplicativo de BI front-end (pode ser alterada até certo ponto).

Os índices da tabela são:

MariaDB [my_db]> show index from credits;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| credits |          0 | PRIMARY    |            1 | id          | A         |     2014915 |     NULL | NULL   |      | BTREE      |         |               |
| credits |          1 | credit_id  |            1 | credit_id   | A         |     2014915 |     NULL | NULL   | YES  | BTREE      |         |               |
| credits |          1 | user_id    |            1 | user_id     | A         |      134327 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

EDIT-2:

Database changed
MariaDB [my_db]> show index from accounts;
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| accounts |          0 | PRIMARY             |            1 | id          | A         |      486034 |     NULL | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id             |            1 | user_id    | A         |      486034 |     NULL | NULL   |      | BTREE      |         |               |
| accounts |          1 | email_idx           |            1 | email       | A         |      486034 |      190 | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id_email_phone |            1 | user_id    | A         |      486034 |     NULL | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id_email_phone |            2 | email       | A         |      486034 |       40 | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id_email_phone |            3 | phone       | A         |      486034 |       15 | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)

Meu mecanismo de armazenamento atual é WiredTigere seu nível de compactação é padrão, rápido. Eu me deparei com a documentação do MongoDB e foi mencionado que usar o zlib compacta melhor, mas precisa de mais CPU.

Eu quero saber vai zlibarmazenar mais dados na memória em comparação com snappycomo comprimir os dados? Eu tenho um servidor com 16 núcleos de CPU. Como a RAM é mais cara, prefiro economizar na memória caso ela mantenha mais dados.

Isso está correto? Posso mudar cegamente para zlib para armazenar mais dados em cache e melhorar o desempenho de leitura?

NOTA: Nosso servidor é de leitura intensiva.

Da MongoDBdocumentação é mencionado que:

Quando você precisa apenas de um subconjunto de campos de documentos, pode obter melhor desempenho retornando apenas os campos necessários

Como os campos de filtragem afetam o desempenho? O desempenho está relacionado ao tamanho dos dados que são transmitidos pela rede? ou o tamanho dos dados que serão mantidos na memória? Como exatamente esse desempenho é melhorado? Qual é esse desempenho que está sendo mencionado na documentação?

Eu tenho consultas lentas do MongoDB. O retorno de um subconjunto afeta minha consulta lenta (tenho índice composto no campo)?

Como innodb_file_per_tablea opção separa os arquivos da tabela em vez de colocar todos os dados e índices dos BDs em um arquivo ibdata, usar essa opção melhora a velocidade de alteração da tabela?

Tenho uma tabela de 40M linhas e quando altero uma tabela específica demora cerca de 5 a 6 horas.

Esta solução ajuda? Existem outras maneiras de melhorar a velocidade de alteração da mesa em mesas pesadas?

Existe uma maneira de alternar consultas com base em valores? Digamos que eu tenha a consulta abaixo:

select pdate as 'Payment Date', ROUND(@sum_amount := @sum_amount + `amount`) AS 'Total Amount' from
(
select @sum_amount := 0, date(issue_date) pdate, sum(amount) amount from payments group by YEAR(date(issue_date))
) inner_tbl

Desejo alterar o intervalo de consulta (anual, mensal, diário) com base na entrada do usuário. Algo como abaixo:

SELECT CASE input WHEN 1 THEN **RUN YEARLY QUERY**
     WHEN 2 THEN **RUN MONTHLY QUERY** ELSE **RUN DAILY QUERY** END;

Eu estava tentando separar índices de banco de dados de ibdata1Eu segui as etapas em qual-é-a-melhor-maneira-de-reduzir-o-tamanho-de-ibdata-em-mysql

Eu descartei dados, descartei bancos de dados e iniciei o serviço mysql.

Eu recebi o seguinte erro e agora estou preso:

mysql -u root -p < alldb.sql
mysql: unknown option '--innodb_file_per_table'

Qual é a solução agora?

Quando eu removo as linhas abaixo de my.cnftudo funciona bem:

[mysql]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Por que innodb_file_per_tableé uma opção desconhecida?

O estranho é que posso definir innodb_file_per_tabledinamicamente e separar o .ibdarquivo quando os altero para o mecanismo InnoDB.

EDIT:
Deve ser uma opção desconhecida, pois coloquei abaixo [mysql]não [mysqld]. Fiz o processo novamente e agora funciona perfeitamente. ibdos arquivos estão em suas respectivas pastas de banco de dados.

Ufa...

Tenho duas dúvidas:

A primeira é a seguinte:

SELECT * 
FROM batch bp 
  INNER JOIN sender_log sl ON sl.id=bp.log_id 
    AND bp.protocol='someprotocol'

A segunda consulta é usada para excluir linhas:

delete from batch where protocol='someprotocol'

Eu sei que no postgres temos RETURNING * para retornar linhas apagadas. O que desejo alcançar é excluir linhas e retorná-las em uma consulta. Como posso fazer isso?

Quando insiro um registro preciso retornar o id inserido por RETURNING. O problema é que a tabela é particionada e na tabela particionada não consigo usar RETURNING. Eu executo várias consultas ao mesmo tempo, então estou precisando urgentemente de RETURNING. Existe uma maneira de conseguir isso?

Eu uso a idade para obter o intervalo entre os campos de data. Seu tipo é timestamp.

Meu código é como abaixo:

select age(timestamp MySecondDateField, timestamp MyFirstDateField)

Os dados no meu campo contêm:

2014-01-26 09:53:17.036359

Como devo subtrair duas datas?

Quando eu uso a idade abaixo, funciona:

select age(timestamp '2001-04-10', timestamp '1957-06-13')

Eu tenho uma tabela muito grande para usuários conforme abaixo:

CREATE TABLE `karbar` (
 `uid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `email` varchar(70) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `password` char(41) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `token` char(40) NOT NULL,
 `joined` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `totalspam` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `flname` varchar(60) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `activation_link` char(40) NOT NULL,
 `role` tinyint(3) unsigned NOT NULL DEFAULT '1',
 `gender` char(1) NOT NULL DEFAULT 'u',
 `is_shop_active` char(0) DEFAULT '',
 `english_name` varchar(30) NOT NULL,
 `followings` mediumint(8) unsigned NOT NULL DEFAULT '0' ,
 `followers` mediumint(8) unsigned NOT NULL DEFAULT '0' ,
 PRIMARY KEY (`uid`),
 UNIQUE KEY `email` (`email`),
) ENGINE=InnoDB

Eu quero preencher esta tabela com muitos dados. Digamos milhões de registros. Como fazer isso? Eu preciso produzir e-mails exclusivos. Dados diferentes para e-mails. Eu sei que posso usar CONCAT, mas não sei como produzir dados aleatórios

Digamos que eu tenha uma tabela chamada wordscom muitos registros.
As colunas são ide name.

Na wordstabela tenho por exemplo:

 'systematic', 'سلام','gear','synthesis','mysterious', etc.  

NB: também temos palavras utf8. Como consultar com eficiência para ver quais palavras incluem
letras 's'e (todas elas)?'m''e'

A saída seria:

systematic,mysterious

Não tenho ideia de como fazer uma coisa dessas. Deve ser eficiente porque nosso servidor sofreria caso contrário.

Nas versões recentes do PostgreSQL (a partir de dezembro de 2013), podemos compartilhar uma consulta entre dois ou mais núcleos para obter um aumento de desempenho? Ou devemos obter núcleos mais rápidos?

Eu tenho uma tabela para usuários em um site de rede social. Eu quero que seja eficiente desde o início. Se eu colocar os campos mencionados na tabela, terei que atualizar a tabela do usuário a cada alteração. Devo ter outra tabela para campos como esses que mudam com frequência? Devo me preocupar com a fragmentação?

Existe alguma abordagem melhor que eu desconheço?

Eu tenho uma chave composta exclusiva como fr(fromid,toid) na tabela, quando executo a consulta com explicação recebo o seguinte resultado:

Impossible WHERE noticed after reading const tables`

A consulta que fiz:

explain SELECT rid FROM relationship WHERE fromid=78 AND toid=60   

Qualquer ajuda?

EDIT1:
Quando eu uso a consulta abaixo:

explain SELECT rid FROM relationship WHERE fromid=60 and toid=78 AND is_approved='s'  OR is_approved='f' OR is_approved='t'

Eu vejo USING WHEREem vez da mensagem anterior, mas quando uso a consulta abaixo:

explain SELECT rid FROM relationship WHERE fromid=60 and toid=78 AND (is_approved='s'  OR is_approved='f' OR is_approved='t')  

Eu novamente recebo a primeira impossible ...mensagem! O que esses parênteses fazem aqui?

EDIT2:

CREATE TABLE `relationship` (
 `rid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `fromid` mediumint(8) unsigned NOT NULL,
 `toid` mediumint(8) unsigned NOT NULL,
 `type` tinyint(3) unsigned NOT NULL,
 `is_approved` char(1) NOT NULL,
 PRIMARY KEY (`rid`),
 UNIQUE KEY `fromid` (`fromid`,`toid`),
 KEY `toid` (`toid`),
 CONSTRAINT `relationship_ibfk_1` FOREIGN KEY (`fromid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `relationship_ibfk_2` FOREIGN KEY (`toid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

EDIT3:
Como o site mysql diz:

Impossível ONDE notado depois de ler as tabelas const

O MySQL leu todas as tabelas const (e do sistema) e notou que a cláusula WHERE é sempre falsa.

Mas na consulta recebo o resultado que quero, a WHEREparte não é false. Existe alguém que poderia explicar isso e lançar alguma luz sobre o assunto?

Tenho uma tabela em nome de ips conforme abaixo:

CREATE TABLE `ips` (
 `id` int(10) unsigned NOT NULL DEFAULT '0',
 `begin_ip_num` int(11) unsigned DEFAULT NULL,
 `end_ip_num` int(11) unsigned DEFAULT NULL,
 `iso` varchar(3) DEFAULT NULL,
 `country` varchar(150) DEFAULT NULL
) ENGINE=InnoDB

Vamos supor que eu tenha um countryidcampo nesta tabela da tabela de países que é como abaixo:

CREATE TABLE `country` (
 `countryid` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `ordering` smallint(5) unsigned NOT NULL DEFAULT '0',
 `iso` char(2) NOT NULL,
 PRIMARY KEY (`countryid`)
) ENGINE=InnoDB

Há cerca de 100.000 registros na tabela ips. Existe alguma consulta para o seguinte cenário:
Verifique se ips.isoé igual a country.iso, se for igual adicione country.coutryid a esse registro. Eu não conseguia pensar em nenhuma maneira de fazer isso. Você tem alguma ideia de como fazer isso?

Quando indexamos um campo timestamp como timetime. Podemos usá-lo para ORDER BY timetime DESCou é apenas utilizável para ORDER BY timetime ASCOR ORDER BY timetime? O mySQL usa esse índice ao fazer a ordenação do verso do torno?

tenho userstabela:

CREATE TABLE `users` (
 `uid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `email` varchar(70) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `flname` varchar(60),
 PRIMARY KEY (`uid`)
) ENGINE=InnoDB

E uma tabela de relacionamento:

CREATE TABLE `relationship` (
 `rid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `from` mediumint(8) unsigned NOT NULL,
 `to` mediumint(8) unsigned NOT NULL,
 PRIMARY KEY (`rid`),
 UNIQUE KEY `from_2` (`from`,`to`),
 KEY `to` (`to`),
 CONSTRAINT `relationship_ibfk_1` FOREIGN KEY (`from`) REFERENCES `users` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `relationship_ibfk_2` FOREIGN KEY (`to`) REFERENCES `users` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

Quero buscar os usuários que não estão no círculo de um usuário específico (como o G+). Isso significa que o usuário ainda não adicionou essas pessoas ao seu círculo. Eu tentei a consulta abaixo, mas estava vazia:

SELECT uid FROM users WHERE flname LIKE'%john%' AND NOT EXISTS (Select `to` FROM relationship WHERE `from`=60)  

A consulta abaixo retorna 3 registros:

SELECT uid,flname FROM users WHERE flname LIKE'%john%' 

Agora os usuários em nome de johnquem foi adicionado por uid=60 não deveriam ser mostrados! O resultado desta consulta está vazio. Não consigo entender, o que estou fazendo de errado?

Quando edito my.cnf no MySQL 5.0 no gerenciador de arquivos e adiciono as seguintes configurações, sou desconectado automaticamente do Kloxo e quando removo essas linhas e reinicio o servidor está tudo ok então:

innodb_buffer_pool_size = 50M
innodb_log_file_size = 15M

mas quando adiciono a seguinte linha e reinicio não tenho nenhum problema e esta configuração foi definida:

bulk_insert_buffer_size=25M

Por que isso está acontecendo?

PS:
Devo observar que o banco de dados Horde_groupware tem tabelas innoDB, quando tudo está bagunçado e SELECT * FROM Horde_groupware.tblnamerecebo o erro que diz informações incorretas no arquivo .frm.

Eu tenho um aplicativo da web de gravação intensiva e comprei um VDS com 768 MB de RAM

• Este my.cnf é bom para meus propósitos?

Eu especifiquei bulk_insert_buffer_size para 126M e alguns outros parâmetros como abaixo:

[mysqld]
bulk_insert_buffer_size=126M
set-variable = max_connections=500
safe-show-database
max_user_connections=200
key_buffer_size = 16M
query_cache_size = 50M
tmp_table_size = 50M
max_heap_table_size  = 200M
thread_cache_size = 4
table_cache = 80
thread_concurrency = 8
innodb_buffer_pool_size = 100M
innodb_log_file_size = 25M
query_cache_limit = 50M
innodb_flush_log_at_trx_commit = 2

Quando houver uma chave única em uma tabela (não PRIMARY KEY) e o procedimento estiver em execução, em caso de erro de chave duplicada, todo o processo será interrompido. Desejo retomar em caso de erro e chamar o procedimento novamente.

O procedimento:

DELIMITER $$

CREATE PROCEDURE `injatest`.`LoadData` ()
BEGIN

    DECLARE x INT;

    SET x = 1;
    WHILE x <= 14400 DO
        INSERT INTO junc_question_course_iq(q_id,iq_id,ct_id) VALUES
        (CEIL(RAND()*1000), CEIL(RAND()*48), CEIL(RAND()*10));
        SET x = x + 1;
    END WHILE;

END $$


DELIMITER ;  

Quero inserir cerca de 14000 registros em uma tabela de junção, mas o problema surge quando há uma chave duplicada para unique(iq_id,q_id)? o que fazer?