Differences

In marketing scenarios, one finds that the more similar two products are, the more granular differentials are in focus. As James Carse says: Belief is always belief against something. There are no large followings of people proclaiming the sun will rise - because there’s no one who doubts it. This reductio ad absurdum collapses many products down to the point nearing identity; save some minor salient feature, which is then magnified to inappropriate proportions. But in actuality, Burger King is just McDonald’s, replacing the creepy clown with a creepy king; Lutheranism is just Catholicism without the Pope.

It’s from this vantage we can view the classic OSS DB feud: MySQL v. PostgreSQL. Forget that they’re both ACID compliant, (mostly standard) SQL RDBMS, with over a decade of continuous production use and development. MySQL has swappable storage engines (MyISAM, InnoDB, etc), PostgreSQL allows customized contributions (GiST indexes, or cube-datatypes). On the other hand, MySQL sucks at multi-core processors, while PostgreSQL’s scale-out is not as mature as MySQL. MySQL can be friendlier to non-expert users (selecting non grouped-by fields won’t fail, and it’s always had an amazing console), and PostgreSQL can claim to adhere more to (at least the spirit of) the standards.

I offer this topic to the sacrifice, because just last week I conversed with a chap to whom PostgreSQL was a poor misunderstood genius, locked away in the closet, whilst MySQL was just the dumb quarterback, galavanting around town and sharing his herpes. Being the database votary I am, I marvel at the ongoing polemic. I had hoped the arise of NoSQL would temper this debate (common enemies being the greatest of peacemakers), but it seems too deeply embedded - like bones.

So let me be the condescending adult and say: You’re both beautiful children, in your own special way. You are 99% of the same. Now please stop fighting. At least we can all agree, you’re both better than Oracle.

Passa lo straniero

Il refactoring che più mi spaventa è quello di installare tutte le relazioni tra le varie tabelle del sistema e poi usarle nel gioco.

E’ un refactoring importante perché, oltre al tema dell’efficienza, può aiutarmi a capire meglio come ragiona DBIx::Class. 

La cosa che più mi rende nervoso riguardo la cosa è che io sono proprio un ignorante nello sviluppare i DB, anzi, a dirla tutta, i DB mi stanno proprio antipatici. E’ stata quindi già una piccola impresa capire come fare ad appiccicare le foreign alle varie tabelle.

Scopro oggi, da bravo dilettante, che si può attaccare una foreign key solo a una colonna che sia indicizzata e solo a tabelle il cui engine sia InnoDB. 

Poiché le tabelle sono state create con engine di default (quindi MyISAM) la prima cosa da fare è stato produrre uno script che alterasse tutti gli engine. Una volta fatto ciò mi sono messo a censire tutti i collegamenti e ad aggiungerli. Riporto qui uno script d’esempio:

CREATE INDEX player
ON GAMES (USER_ID);

ALTER TABLE `GAMES`
ADD FOREIGN KEY ( `USER_ID` )
REFERENCES `djakarta`.`USERS` (`ID` );

E’ una cosa da cui non si torna indietro. Se passo a un’architettura del genere mi scordo di poter usare un SQLite di supporto. Mi sono quindi messo a riorganizzare le cartelle dei miei script e ho prodotto (ok, vergogna su di me, usando phpmyadmin) uno script di dump con tutta la struttura a oggi, che diventa anche un comodo sistema per ripristinare il sistema attuale o installarlo su un nuovo ambiente.

Accanto a questo dump, ora, ci sono anche tutti gli script per le modifiche, fortunatamente indipendenti tra loro e già installati sul mio ambiente locale.

La palla passa a DBIx::Class.

MySql: ¿Waiting for table level lock? - Cambia de MyISAM a InnoDB

En la medida que los proyectos web van creciendo y se requiere guardar más y más data en tus tablas de MySql, sentirás que tu servidor va llegando lento… es hora de optimizar.

En esa situación lo primero sería ejecutar: “show full processlist;” en MySql para conocer los procesos que se están ejecutando en tu servidor, se mostrará una tabla con todos ellos, toma en cuenta la columna “State”, si entre los registros aparece el status “Waiting for table level lock” y tu tabla usa MyISAM la solución puede ser migrar a InnoDB.

Diferencia entre MyISAM y InnoDB

MyISAM es la alternativa por defecto a InnoDB a la hora de escoger el tipo de almacenamiento de datos en MySQL. Estas son algunas de las diferencias entre estos dos:

  • InnoDB se recupera de un problema volviendo a ejecutar sus logs, mientras que MyISAM necesita repasar todos los índices y tablas que hayan sido actualizados y reconstruirlos si esos cambios no han sido escritos en disco. El primer proceso requiere más o menos el mismo tiempo siempre, mientras que el segundo aumenta con el tamaño de la base de datos.

  • MyISAM deja al sistema operativo la tarea de hacer la caché de las lecturas y escrituras de los registros, mientras que InnoDB realiza él mismo la tarea, combinando cachés de registro y de índice. InnoDB no envía directamente los cambios en las tablas al sistema operativo para que las escriba, lo que puede hacerlo mucho más rápido que MyISAM en ciertos escenarios.

  • InnoDB almacena físicamente los registros en el orden de la clave primaria, mientras que MyISAM los guarda en el orden en que fueron añadidos. Cuando la clave primaria se escoge de acuerdo con las necesidades de las consultas más habituales esto puede suponer una mejora sustancial del rendimiento. Por otro lado, si los datos se insertan en un orden que difiera sustancialmente del orden de la clave primaria, se obliga a InnoDB a reordenar mucho los datos para mantenerlos en el orden adecuado.

  • InnoDB no dispone de la compresión de datos de la que disfruta MyISAM, de modo que tanto el espacio en disco como la caché en la memoria RAM pueden ser más grandes. Este problema se ha reducido en MySQL 5.0, reduciéndolo en aproximadamente un 20%.

  • Cuando opera con transacciones ACID, InnoDB debe escribir en disco al menos una vez por cada transacción, aunque puede combinar las escrituras de varias inserciones concurrentes. Para los discos duros típicos, esto supone un límite de aproximadamente 200 transacciones por segundo, por lo que aumentarlas exige controladores de disco con caché de escritura y sistema de alimentación ininterrumpido para mantener la integridad. InnoDB ofrece diversos modos de funcionamiento que reducen este efecto, pero conllevan una pérdida de integridad transaccional. MyISAM no tiene ese problema porque no soporta transacciones.

¿Como Resolverlo?

Para convertir tu tabla debes tener el motor de Innodb correctamente configurado y solo necesitarás ejecutar:

ALTER TABLE nombre_tabla ENGINE=InnoDB;

Esto por lo general será suficiente para remover el mensaje y conseguir una base de datos más rápida.

Siéntete libre de comentar… ¿Te ha pasado algo parecido? ¿Conoces mejores técnicas para optimizar tus tablas?

Fulltext Search with InnoDB

question :

I’m developing a high-volume web application, where part of it is a MySQL database of discussion posts that will need to grow to 20M+ rows, smoothly.

I was originally planning on using MyISAM for the tables (for the built-in fulltext search capabilities), but the thought of the entire table being locked due to a single write operation makes me shutter. Row-level locks make so much more sense (not to mention InnoDB’s other speed advantages when dealing with huge tables). So, for this reason, I’m pretty determined to use InnoDB.

The problem is… InnoDB doesn’t have built-in fulltext search capabilities.

Should I go with a third-party search system? Like Lucene(c++) / Sphinx? Do any of you database ninjas have any suggestions/guidance? LinkedIn’s zoie (based off Lucene) looks like the best option at the moment… having been built around realtime capabilities (which is pretty critical for my application.) I’m a little hesitant to commit yet without some insight…

(FYI: going to be on EC2 with high-memory rigs, using PHP to serve the frontend)

answer1:

I can vouch for MyISAM fulltext being a bad option - even leaving aside the various problems with MyISAM tables in general, I’ve seen the fulltext stuff go off the rails and start corrupting itself and crashing MySQL regularly.

A dedicated search engine is definitely going to be the most flexible option here - store the post data in MySQL/innodb, and then export the text to your search engine. You can set up a periodic full index build/publish pretty easily, and add real-time index updates if you feel the need and want to spend the time.

Lucene and Sphinx are good options, as is Xapian, which is nice and lightweight. If you go the Lucene route don’t assume that Clucene will better, even if you’d prefer not to wrestle with Java, although I’m not really qualified to discuss the pros and cons of either.

answer2:

Along with the general phasing out of MyISAM, InnoDB full-text search (FTS) is finally available in MySQL 5.6.4 release.

From http://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html#innodb-fulltext-index:

These indexes are physically represented as entire InnoDB tables, which are acted upon by SQL keywords such as the FULLTEXT clause of the CREATE INDEX statement, the MATCH() … AGAINST syntax in a SELECT statement, and the OPTIMIZE TABLE statement.

While other engines have lots of different features, this one is InnoDB, so it’s native (which means there’s an upgrade path), and that makes it a worthwhile option.

answer:3

You should spend an hour and go through installation and test-drive of Sphinx and Lucene. See if either meets your needs, with respect to data updates.

One of the things that disappointed me about Sphinx is that it doesn’t support incremental inserts very well. That is, it’s very expensive to reindex after an insert, so expensive that their recommended solution is to split your data into older, unchanging rows and newer, volatile rows. So every search your app does would have to search twice: once on the larger index for old rows and also on the smaller index for recent rows. If that doesn’t integrate with your usage patterns, this Sphinx is not a good solution (at least not in its current implementation).

I’d like to point out another possible solution you could consider: Google Custom Search. If you can apply some SEO to your web application, then outsource the indexing and search function to Google, and embed a Google search textfield into your site. It could be the most economical and scalable way to make your site searchable. 

MySQL: Converting InnoDb tables to MyISAM posted on hochwald dot net

New Post has been published on http://hochwald.net/11262_mysql-converting-innodb-tables-to-myisam/

MySQL: Converting InnoDb tables to MyISAM

Today i installed a plugin that use a InnoDb! But i want too use MyISAM. After some tests, i figured out, that the following command helps:
ALTER TABLE ENGINE=MyISAM

In my case:
ALTER TABLE wp_nxs_log ENGINE=MyISAM
Works great!!!

If you want to convert from MyISAM to InnoDb, the command is the other way around:
ALTER TABLE
ENGINE=InnoDb

Réparation de tables MyISAM dans MariaDB

Le passage à MariaDB n’a pas été une franche réussite. En 6 ans, c’est la 1ère fois que je dois réindexer mes tables MyISAM des sites des serveurs que j’administre !!! Je n’avais jamais eu d’erreurs avec MySQL.

 Dsfc

Autour du sujet :

  1. MySQL : maintenance des tables et des index MyISAM et InnoDB
  2. MySQL : could not drop object ???
  3. Désactiver les moteurs inutilisés dans MariaDB / MySQL
  4. Désactiver l’historique des commandes MariaDB / MySQL
  5. Upgrade MySQL 5.6 vers MariaDB 10.0 sur Windows


Source : http://bit.ly/1wmNBo4
Text
Photo
Quote
Link
Chat
Audio
Video