MySQL et NoSQL: Aidez-moi à choisir la bonne

Question

Il y a une grosse base de données, plus 1 000 000 000 de lignes, appelé fils (ces fils existent réellement, je ne fais pas les choses plus difficiles juste parce que j'aime ça). Fils n'a que quelques trucs, faire les choses plus vite: (int id, string hachage, int replycount, int ligne de changement de date (timestamp), int forumid, string titre)

Requête:

select * from thread where forumid = 100 and replycount > 1 order by dateline desc limit 10000, 100

Depuis qu'il y a 1G de dossiers, il est tout à fait une requête lente. Alors j'ai pensé, nous allons diviser cette 1G de dossiers en autant de tableaux que de nombreux forums(catégorie) j'ai! C'est presque parfait. Ayant beaucoup de tables, j'ai moins de record à les chercher partout et il est vraiment plus rapide. La requête devient:

select * from thread_{forum_id} where replycount > 1 order by dateline desc limit 10000, 100

C'est vraiment plus rapide avec 99% des forums (catégorie) étant donné que la plupart de ceux qui ont seulement quelques-uns des sujets (100k-1M). Cependant, parce qu'il y a quelques avec environ 10M de données, dont certaines requêtes sont encore à ralentir (0.1/.2 secondes, pour beaucoup pour mon application!, Je suis déjà en utilisant des indices!).

Je ne sais pas comment faire pour améliorer cela à l'aide de MySQL. Est-il possible?

Pour ce projet je vais utiliser 10 Serveurs (12 go de ram, 4x7200rpm disque dur sur le raid logiciel 10, quad core)

L'idée était simplement de diviser les bases de données entre les serveurs, mais le problème est expliqué ci-dessus qui n'est toujours pas assez.

Si j'installe cassandra sur ces 10 serveurs (en supposant que je trouve le temps de faire qu'il fonctionne comme il est censé) devrais-je suppose que pour avoir un gain de performance?

Que dois-je faire? Continuer à travailler avec MySQL avec base de données distribuée sur plusieurs machines ou de construire un cluster cassandra?

On m'a demandé de poster ce sont les indices, ils sont ici:

mysql> show index in thread;
PRIMARY id
forumid
dateline
replycount

Sélectionnez expliquer:

mysql> explain SELECT * FROM thread WHERE forumid = 655 AND visible = 1 AND open <> 10 ORDER BY dateline ASC LIMIT 268000, 250;
+----+-------------+--------+------+---------------+---------+---------+-------------+--------+-----------------------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref         | rows   | Extra                       |
+----+-------------+--------+------+---------------+---------+---------+-------------+--------+-----------------------------+
|  1 | SIMPLE      | thread | ref  | forumid       | forumid | 4       | const,const | 221575 | Using where; Using filesort | 
+----+-------------+--------+------+---------------+---------+---------+-------------+--------+-----------------------------+

Answer 1

Vous devriez lire la suite et d'en apprendre un peu plus sur les avantages d'un système bien conçu de table innodb et la meilleure façon d'utiliser les index en cluster - disponible uniquement avec innodb !

http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html

http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/

ensuite, la conception de votre système quelque chose le long des lignes de l'exemple simplifié:

Exemple de schéma (simplifié)

Les caractéristiques importantes sont que les tables d'utiliser le moteur innodb et la clé primaire pour les filets de table n'est plus un seul auto_incrementing clé, mais un composite cluster clé, basée sur une combinaison de forum_id et thread_id. par exemple

threads - primary key (forum_id, thread_id)

forum_id    thread_id
========    =========
1                   1
1                   2
1                   3
1                 ...
1             2058300  
2                   1
2                   2
2                   3
2                  ...
2              2352141
...

Chaque forum ligne inclut un compteur de next_thread_id (unsigned int) qui est maintenue par un élément déclencheur, et augmente à chaque fois qu'un thread est ajouté à un forum. Cela signifie également que nous pouvons stocker 4 milliards de discussions par le forum plutôt que de 4 milliards de discussions au total si l'aide d'un seul type auto_increment primary key pour thread_id.

forum_id    title   next_thread_id
========    =====   ==============
1          forum 1        2058300
2          forum 2        2352141
3          forum 3        2482805
4          forum 4        3740957
...
64        forum 64       3243097
65        forum 65      15000000 -- ooh a big one
66        forum 66       5038900
67        forum 67       4449764
...
247      forum 247            0 -- still loading data for half the forums !
248      forum 248            0
249      forum 249            0
250      forum 250            0

L'inconvénient de l'utilisation d'une clé composite est que vous ne pouvez pas il suffit de sélectionner un thread par une seule valeur de clé comme suit:

select * from threads where thread_id = y;

que vous avez à faire:

select * from threads where forum_id = x and thread_id = y;

Toutefois, le code de votre application doivent être conscients de qui forum un utilisateur navigue donc ce n'est pas exactement difficile à mettre en œuvre - le magasin à l'heure actuelle considérée forum_id dans une variable de session ou cachés champ de formulaire, etc...

Voici le schéma simplifié:

drop table if exists forums;
create table forums
(
forum_id smallint unsigned not null auto_increment primary key,
title varchar(255) unique not null,
next_thread_id int unsigned not null default 0 -- count of threads in each forum
)engine=innodb;


drop table if exists threads;
create table threads
(
forum_id smallint unsigned not null,
thread_id int unsigned not null default 0,
reply_count int unsigned not null default 0,
hash char(32) not null,
created_date datetime not null,
primary key (forum_id, thread_id, reply_count) -- composite clustered index
)engine=innodb;

delimiter #

create trigger threads_before_ins_trig before insert on threads
for each row
begin
declare v_id int unsigned default 0;

  select next_thread_id + 1 into v_id from forums where forum_id = new.forum_id;
  set new.thread_id = v_id;
  update forums set next_thread_id = v_id where forum_id = new.forum_id;
end#

delimiter ;

Vous avez peut-être remarqué, j'ai inclus reply_count en tant que partie de la clé primaire qui est un peu étrange comme (forum_id, thread_id) composite est unique en soi. C'est juste un indice d'optimisation qui permet de gagner du I/O lorsque les requêtes qui utilisent reply_count sont exécutées. Veuillez consulter les 2 liens ci-dessus pour plus d'info sur cette.

Exemple de requêtes

Je suis toujours en train de charger des données dans mes tables et pour l'instant j'ai un chargé env. 500 millions de lignes (la moitié de votre système). Lorsque le processus de chargement est terminé je devrais attendre d'avoir environ:

250 forums * 5 million threads = 1250 000 000 (1.2 billion rows)

J'ai délibérément fait de quelques-uns des forums contiennent plus de 5 millions de threads par exemple, le forum de 65 ans a 15 millions de threads:

forum_id    title   next_thread_id
========    =====   ==============
65        forum 65      15000000 -- ooh a big one

Exécution de la requête

select sum(next_thread_id) from forums;

sum(next_thread_id)
===================
539,155,433 (500 million threads so far and still growing...)

sous innodb en additionnant les next_thread_ids pour donner un total de nombre de threads est beaucoup plus rapide que d'habitude:

select count(*) from threads;

Le nombre de threads n'forum de 65 ans:

select next_thread_id from forums where forum_id = 65

next_thread_id
==============
15,000,000 (15 million)

encore une fois c'est plus rapide que d'habitude:

select count(*) from threads where forum_id = 65

Ok, maintenant nous savons que nous avons environ 500 millions de threads à ce jour et forum de 65 ans a 15 millions de threads - nous allons voir comment le schéma effectue :)

select forum_id, thread_id from threads where forum_id = 65 and reply_count > 64 order by thread_id desc limit 32;

runtime = 0.022 secs

select forum_id, thread_id from threads where forum_id = 65 and reply_count > 1 order by thread_id desc limit 10000, 100;

runtime = 0.027 secs

Semble assez performant pour moi - c'est pour une seule table avec plus de 500 millions de lignes (et plus) avec une requête qui couvre 15 millions de lignes dans 0.02 secondes (alors que sous la charge !)

Autres optimisations

Parmi ceux-ci:

• le partitionnement par gamme

• sharding

• jeter de l'argent et le matériel à elle

etc...

j'espère que vous trouverez cette réponse utile :)

Answer 2

EDIT: Votre colonne indices ne sont pas suffisants. Vous devez, au moins, les trois colonnes concernées.

Plus avancé solution: remplacer replycount > 1 avec hasreplies = 1 par la création d'un nouveau hasreplies champ est égal à 1 lorsque l' replycount > 1. Une fois cela fait, créer un index sur les trois colonnes, dans l'ordre: INDEX(forumid, hasreplies, dateline). Assurez-vous que c'est un ARBRE de l'indice à l'appui de la commande.

Vous êtes à la sélection des basé sur:

• un forumid
• un hasreplies
• commandé par dateline

Une fois que vous faites cela, votre exécution de la requête seront:

• descendre de l'ARBRE pour trouver le sous-arbre qui correspond forumid = X. C'est une échelle logarithmique de fonctionnement (durée : log(nombre de forums)).
• d'aller plus loin en bas de l'ARBRE pour trouver le sous-arbre qui correspond hasreplies = 1 (tout en restant correspondance forumid = X). C'est une constante de temps de l'opération, car hasreplies seulement 0 ou 1.
• se déplaçant à travers la ligne de changement de date, triées de la sous-arborescence afin d'obtenir les résultats requis, sans avoir à lire et à re-trier l'ensemble de la liste des éléments dans le forum.

Ma suggestion antérieure à l'index sur replycount a été incorrect, parce que ça aurait été une requête de plage et ainsi empêcher l'utilisation d'un dateline pour trier les résultats (si vous avez sélectionné les fils avec des réponses très rapides, mais le résultat de millions ligne à la liste aurait dû être triés complètement avant de chercher les 100 éléments dont vous avez besoin).

IMPORTANT: si cela améliore les performances dans tous les cas, votre immense valeur de DÉCALAGE (10000!) va diminuer les performances, car MySQL ne semble pas être en mesure de sauter de l'avant malgré la lecture de tout droit dans un ARBRE. Ainsi, la plus grande, le DÉCALAGE est, le ralentissement de la demande va devenir.

J'ai peur que le DÉCALAGE problème n'est pas automatiquement résolu par la diffusion le calcul sur plusieurs calculs (comment voulez-vous passer un décalage en parallèle, de toute façon?) ou le déplacement vers le NoSQL. Toutes les solutions (y compris NoSQL) de faire bouillir vers le bas pour la simulation de DÉCALAGE basé sur dateline (ce qui revient à dire dateline > Y LIMIT 100 au lieu de LIMIT Z, 100 où Y est la date de l'élément à la position Z). Cela fonctionne, et élimine les problèmes de performance liés à l'offset, mais empêche d'aller directement à la page 100 sur 200.

Answer 3

Il y a une partie de la question, relative à des NoSQL ou MySQL option. Effectivement, c'est une chose fondamentale caché ici. Le langage SQL est facile à écrire pour les humains et peu difficile à lire pour ordinateur. Dans un volume élevé de bases de données que je recommande d'éviter backend SQL comme ceci nécessite une étape supplémentaire - commande de l'analyse. J'ai fait une évaluation comparative et il ya des cas où l'analyseur SQL est plus lent point. Il n'y a rien que vous pouvez faire à ce sujet. Ok, vous pouvez possible l'utilisation de pré-analysé les déclarations et leur accès.

BTW, il n'est pas connu mais que MySQL a été développée à partir de bases de données NoSQL. Société où les auteurs de MySQL David et Monty travaillé était d'entreposage de données de l'entreprise et ils ont souvent eu à écrire des solutions personnalisées pour les rares tâches. Cela l'a menée à grande pile de homebrew C des bibliothèques utilisées écrire manuellement les fonctions de base de données lors de l'Oracle et d'autres ont été peu performants. SQL a été ajouté à cela près de 20 ans un zoo en 1996 pour le plaisir. Ce qui est arrivé après que vous connaissez.

En fait, vous pouvez éviter de SQL en tête avec MySQL. Mais généralement, l'analyse SQL n'est pas la partie la plus lente mais tout juste bon à savoir. Pour tester l'analyseur de frais généraux que vous pourriez juste faire référence pour "SÉLECTIONNER 1" par exemple ;).

Answer 4

Vous ne devriez pas essayer de s'adapter à une architecture de base de données de matériel que vous avez l'intention d'acheter, mais plutôt l'intention d'acheter du matériel pour s'adapter à votre architecture de base de données.

Une fois que vous avez assez de RAM pour garder le jeu de travail de l'index en mémoire, toutes vos requêtes que peut faire usage de l'index sera rapide. Assurez-vous que votre clé de mémoire tampon est assez grand pour stocker les index.

Donc, si 12GO n'est pas assez, ne pas utiliser de 10 serveurs avec 12 go de RAM, utiliser moins de 32 ou 64 go de RAM.

Answer 5

Les Indices sont un must, mais n'oubliez pas de choisir le bon type de l'index: BTREE est plus approprié lors de l'utilisation de requêtes avec "<" ou ">" dans votre OÙ l'une des clauses, tandis que le HACHAGE est plus approprié lorsque vous avez beaucoup de valeurs distinctes dans une colonne et que vous utilisez "=" ou "<=>" dans votre clause where.

Lectures complémentaires http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html