Marten Mickos sara' presente a un evento presso l'Universita' La Sapienza di Roma, Venerdi 30 Maggio.
Marten parlera' della sostenibilita' del modello Open Source, del presente e del futuro di MySQL.
L'agenda e il modulo di iscrizione, per chi fosse interessato, e' disponibile qui.
In coda all'intervento di Marten, parleremo di scalabilita' e compareremo l'approccio alle architetture basate su MySQL rispetto a quelle utilizzate con altri database relazionali.
Giuseppe Maxia illustrera' il ruolo della Community nell'eco-sistema di MySQL e spieghera' come e' possibile contribuire al miglioramento dei prodotti e delle soluzioni.
Ci sara' anche spazio per un'ampia sezione di domande e risposte... meglio approfittarne!
Spero di vedervi numerosi a Roma!
-ivan
Labels: Italy, Marten Mickos, MySQL
Are you ready for the next webinar? This time we will talk about the use of MySQL with memcached. Most of you may already know memcached and how to use it in a typical web environment with high traffic and requirements for high scalability.
During the webinar we will cover different scenarios and best practices regarding the use of MySQL and memcached together.
The webinar will be live next week, Wednesday 28th of May @ 9am GMTD (London, Dublin, Lisbon) / 10am CETD (Paris, Madrid, Munich, Rome etc.)
The registration is available here.
I hope to see you all online next week!
-ivan
-ivan
Italian Webinar - Materiale, Domande e Risposte per il Webinar "Soluzioni MySQL per l'alta disponibilita'"
0 comments Posted by Ivan at 8:25 AMLe slide e la registrazione del webinar che si e' tenuto il 7 Maggio scorso, sono disponibili qui.
Di seguito ho preparato la sessione di domande e risposte.
Q from Andrea: E' possibile creare un sistema multi-master tramite l'assegnazione di impostazioni auto_increment_offset diversi?
A: Si, questo e' il meccanismo piu' tipico. Ogni server master utilizza un diverso offset per creare una PK e quindi inserire nuove righe nella stessa tabella, quindi
Q from Massimo: Cosa succede se viene effettuata una lettura su uno slave di alcuni records modificati nel master e non ancora replicati? si puo prevenire questa situazione?
A: La situazione si puo' prevenire, ma la logica da applicare e' abbastanza complessa. Detto questo, se le specifiche richiedono questo tipo di lettura aggiornata in real time, l'utilizzo della replicazione non e' consigliabile. Le soluzioni che posso ipotizzare sono due:
1. L'utilizzo di due connessioni, una per la lettura su server replicati e l'altra per la scrittura e la lettura di dati che devono essere aggiornati in realtime. Quest'ultima connessione viene indirizzata sul master
2. L'utilizzo di una struttura a storage condiviso in configurazione active-active, qualora l'utilizzo di MyISAM abbia senso (active-active e' possibile solo con MyISAM).
Q from Angelo: Chi decide a chi demandare eventuali query verso i servers slave?
A: La scelta deve essere definita a livello applicativo, utilizzando una logica che e' parte integrante dell'applicazione, uno strato logico di gestione delle connessioni o in alcuni casi un middle layer come ad esempio MySQL Proxy.
Q from Andrea: Il failback automatico può funzionare solo con due server? o per la circolarità della struttura è possibile definire dinamicamente i server che compongono l'anello?
A: La circolarita' puo' essere cambiata, ma l'operazione e' sempre manuale (ovvero non esistono automatismi gia' pronti all'uso).
Q from Angelo: Se io ho un database che occupa 8GB, devo avere 8GB di RAM su ENNE nodi che fanno parte di Mysql Cluster?
A: No, la formula e' la seguente: somma della memoria disponibile in tutti i nodi diviso il numero di repliche (normalmente 2).
Per esempio: 4 nodi con 8GB disponibili su ciascun nodo = 32GB, diviso 2 (2 copie dei dati) = 16GB di memoria disponibile per i dati e gli indici.
A: La circolarita' puo' essere cambiata, ma l'operazione e' sempre manuale (ovvero non esistono automatismi gia' pronti all'uso).
Q from Angelo: Se io ho un database che occupa 8GB, devo avere 8GB di RAM su ENNE nodi che fanno parte di Mysql Cluster?
A: No, la formula e' la seguente: somma della memoria disponibile in tutti i nodi diviso il numero di repliche (normalmente 2).
Per esempio: 4 nodi con 8GB disponibili su ciascun nodo = 32GB, diviso 2 (2 copie dei dati) = 16GB di memoria disponibile per i dati e gli indici.
Q from Massimo: Le connessioni ai diversi SQL node , come possono essere gestite in maniera trasparente all'utente? Devo per forza passare per Mysql Proxy o esistono altre soluzioni che non siano dipendenti da un applicativo?
A: Non ci sono meccanismi automatici, in quanto un eventuale logica gestita a livello di connessione dovrebbe avere conoscenza del tipo di transazione da eseguire - infatti cio' dovrebbe avvenire tramite Proxy e per tale ragione la soluzione assolutamente piu' sicura e' quella di gestire questa suddivisione a livello applicativo.
A: Non ci sono meccanismi automatici, in quanto un eventuale logica gestita a livello di connessione dovrebbe avere conoscenza del tipo di transazione da eseguire - infatti cio' dovrebbe avvenire tramite Proxy e per tale ragione la soluzione assolutamente piu' sicura e' quella di gestire questa suddivisione a livello applicativo.
Q from Eleonora: Usando DRBD c'è possibilità di perdita di dati nel caso in cui il master cade prima di replicare i dati sul nodo slave?
A: No, questo non puo' avvenire, in quanto la replicazione e' sincrona. Solo cambiando i parametri di configurazione e abbassando il livello di sicurezza della replicazione c'e' il rischio di perdita dei dati - ma ovviamente in una configurazione HA questo e' sconsigliato.
A: No, questo non puo' avvenire, in quanto la replicazione e' sincrona. Solo cambiando i parametri di configurazione e abbassando il livello di sicurezza della replicazione c'e' il rischio di perdita dei dati - ma ovviamente in una configurazione HA questo e' sconsigliato.
Q from Angelo: Durante il failover, la connessione e quindi la query eventuale, viene persa?
A: Si, le connessioni aperte e le conseguenti transazioni aperte vengono interrotte. Le transazioni aperte non vengono scritte su disco, mantenendo cosi' consistente lo stato del DB.
A: Si, le connessioni aperte e le conseguenti transazioni aperte vengono interrotte. Le transazioni aperte non vengono scritte su disco, mantenendo cosi' consistente lo stato del DB.
Q from Massimo: Si può usare il sistema DRDB su macchine virtuali?
A: E' possibile, ma occorre essere certi che a livello hardware non siano presenti eventuali Single Point Of Failure.
A: E' possibile, ma occorre essere certi che a livello hardware non siano presenti eventuali Single Point Of Failure.
Q from Stefano: Nella replica master-master, se cade il master active e successivamente viene riattivato, la risincronizzazione è automatica o richiede operazioni manuali?
A: La sincronizzazione e' automatica. L'unica accortezza e' quella di verificare, prima di attivare il meccanismo di risincronizzazione, che tutte le operazioni siano state trasferite dalla macchina "master active" alla macchina "master passive" prima del fault.
A: La sincronizzazione e' automatica. L'unica accortezza e' quella di verificare, prima di attivare il meccanismo di risincronizzazione, che tutte le operazioni siano state trasferite dalla macchina "master active" alla macchina "master passive" prima del fault.
Q from Alessandro: MySql Replication e MySQL Cluster possono essere implementati anche in sistemi windows?
A: La replicazione e' disponibile anche su Windows, MySQL Cluster e' disponibile solo su sistemi Solaris, Linux, OS X, FreeBSD e Windriver.
A: La replicazione e' disponibile anche su Windows, MySQL Cluster e' disponibile solo su sistemi Solaris, Linux, OS X, FreeBSD e Windriver.
Q from Alberto: MySQL Proxy non e' considerato un servizio HA?
A: Non nella sua funzionalita' di base e non ancora, essendo ancora in una fase alfa. Tramite l'implementazione di appositi script, verra' sicuramente utilizzato anche per operazioni di failover automatico.
A: Non nella sua funzionalita' di base e non ancora, essendo ancora in una fase alfa. Tramite l'implementazione di appositi script, verra' sicuramente utilizzato anche per operazioni di failover automatico.
Q from Marco: Posso fare la replica con due istanze che risiedono su server con sistemi operativi differenti? Ad esempio Windows e Linux
A: Si, questo e' possibile e supportato.
A: Si, questo e' possibile e supportato.
Q from Mauro: Volevo avere qualche informazione in più sulla gestione del load balancing in ambiente Cluster.
A: La mia risposta si riferisce all'utilizzo di MySQL Cluster, non del cluster a livello di sistema operativo. Le operazioni di load balancing vengono gestite automaticamente e trasparentemente tramite il server MySQL e i data node che fanno parte del Cluster. Il bilanciamento tra il sistema client e il server MySQL deve essere gestito tramite le normali regole di balancing tra server, ovvero con bilanciatori SW, bilanciatori HW o tramite l'utilizzo delle funzionalita' di alcuni connettori, come il connettore JDBC.
A: La mia risposta si riferisce all'utilizzo di MySQL Cluster, non del cluster a livello di sistema operativo. Le operazioni di load balancing vengono gestite automaticamente e trasparentemente tramite il server MySQL e i data node che fanno parte del Cluster. Il bilanciamento tra il sistema client e il server MySQL deve essere gestito tramite le normali regole di balancing tra server, ovvero con bilanciatori SW, bilanciatori HW o tramite l'utilizzo delle funzionalita' di alcuni connettori, come il connettore JDBC.
Q from Eleonora: C'è un numero minimo di data node e server node nella configurazione cluster?
A: Si, il numero minimo e' di due nodi mysql, due data node e un management node, dislocati su un minimo di tre server. Questa configurazione evita qualsiasi Single Point Of Failure. E' possibile e consigliabile duplicare anche il management node, sebbene l'utilizzo di questo componente avvenga solo in fase di startup o di guasto di un altro nodo, per cui non viene considerata "configurazione minima".
A: Si, il numero minimo e' di due nodi mysql, due data node e un management node, dislocati su un minimo di tre server. Questa configurazione evita qualsiasi Single Point Of Failure. E' possibile e consigliabile duplicare anche il management node, sebbene l'utilizzo di questo componente avvenga solo in fase di startup o di guasto di un altro nodo, per cui non viene considerata "configurazione minima".
Q from Stefano: master-master: l'automatic failback + multimaster autoincrement funziona anche sotto windows?
A: La replicazione funziona allo stesso modo in Windows come in Linux, Solaris o altri sistemi. Ricordo pero' che se si utilizza la replicazione per l'alta disponibilita' non e' possibile utilizzarla anche per separare le scritture con il meccanismo dell'autoincrement.
A: La replicazione funziona allo stesso modo in Windows come in Linux, Solaris o altri sistemi. Ricordo pero' che se si utilizza la replicazione per l'alta disponibilita' non e' possibile utilizzarla anche per separare le scritture con il meccanismo dell'autoincrement.
Q from Gabriele: Quando e' meglio utilizzare repliche gestite su mysql e quando e' meglio utilizzare repliche gestite dalle tecnologie di replica dello storage?
A: La replicazione di MySQL e' piu' semplice da implementare ed e' multipiattaforma, ma esiste sempre la possibilita', seppure remota, di perdita dei dati. Cio' e' dovuto alla natura asincrona della replicazione. Una replicazione con DRBD e' invece sincrona, pertanto garantisce che i dati presenti su ambedue le repliche siano consistenti.
A: La replicazione di MySQL e' piu' semplice da implementare ed e' multipiattaforma, ma esiste sempre la possibilita', seppure remota, di perdita dei dati. Cio' e' dovuto alla natura asincrona della replicazione. Una replicazione con DRBD e' invece sincrona, pertanto garantisce che i dati presenti su ambedue le repliche siano consistenti.
Labels: Italy, MySQL, webinar performance
The EMEA webinar on Performance and Tuning last week (22 Apr 2008) was a great success! Great attendance, lots of questions and an amazing job from Henrik Ingo and Erik Granstrom!
I have posted the recording of the webinar here and the slides here - unfortunately we could not post the material on our MySQL website, since it's pretty crowded now with P&T docs.
Here is the Q&A session:
Q from Ketil: I have a DB that is "read many, write few", and will remain relatively small (less than RAM). How do I tune a InnoDB MySQL-database for optimal query performance? There are 8 hasAndBelongsToMany relations to categories and a few 100 000 objects.
A: First of all, have a look here: http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html
You should work with some system params, for example the innodb_buffer_pool_size, to set a specific amount of RAM as buffer for InnoDB.
Q from Alon: Is the query cache only available in version >=5?
A: No, it is available from 4.0.1
Q from Prakash: Is it efficient to put numerical criteria in quotes ? (eg cust_id = '675') --- [Oracle queries would be affected by this]
A: If the column you are using is a character columns, then yes, that is the way to use the index. If It's a numeric column, then you should avoid it.
Q from Konstantinos: Is there a possibility to save a whole table in some kind of cache?
A: Yes, you can use the MEMORY storage engine to keep the data in memory. Another option is to use the caching mechanisms provided by the storage engine, the key buffer for MyISAM and the buffer pool for InnoDB. At startup you can use a LOAD INDEX INTO CACHE command for MyISAM or a simple SELECT for InnoDB to cache the data.
Q from Erik: "queries must be identical" means logically or really literally ? ex.: select a.col from a is equal or not to select col from a;
A: They must be the same, byte by byte
Q from Laszlo: Can MySQL Enterprise Monitor advisor help us to determine the cache size/options?
A: Yes, it provides recommendations on how big the cache should be, if it's over or under utilised.
Q from Ketil: Are there any speed (or stability) issues I should be aware of using the .NET connector? And whichh version is the latest stable version? 5.1.5?
A: Yes, you may use 5.1.5. Starting from 5.0.X, the connector includes support up to MySQL 5.1 and ADO.Net 2.0 insterfaces and subclasses. 5.1.5 contains new membership/role provider, Compact Framework 2.0, a new stored procedure parser and improvements to GetSchema. Connector/NET 5.1 also includes the Visual Studio Plugin as a standard installable component.
Q from Saravanan: Does query cache cache sql or data?
A: It caches the result (data) of a SELECT query. The exact text of the query is used as key.
Q from Ivan: Does EXPLAIN work in other databases (apart from MySQL)?
A: Yes, EXPLAIN is an ANSI SQL Command
Q from Alfred: In ORACLE you can use variables as plpaceholders in the where criteria. Doing so, the cache is hit much more often when only some string in the criteria has changed. is this possible in mysql too?
A: it is definitely possible. The use of variables and placeholders will result in a query that can be executed using the query cache, or the storage engine cache or simply retrieving data on disk - it simply depends on the availability of the data in one of the cache.
Q from Jörgen: In which version was EXPLAIN EXTENDED introduced in MySQL?
A: It's certainly available in 4.1, I do not remember if it was available in 4.0
Q from Pierrick: Join type index is bad ? How so ? I thought it was the best join, the fastest.
A: Join type "index" means that the server has to perform a sequential scan on the index tree. Only ALL, which is a full table scan is worse.
Q from Elinor: what exactly does qcache_lowmem_prunes represent? (e.g. 3937244) and how can you be sure your cache size is right?
A: It counts the number of queries that have been removed from the cache to free up memory for caching new queries. See here http://dev.mysql.com/doc/refman/5.0/en/query-cache-status-and-maintenance.html
In other words, a low number is typically good, a high number means you should consider more memory for the cache.
Q from Prakash: In Oracle we can use hints to influence teh optimiser to take a specific path to obtain the data; Is there a similar facility in MySql ? (eg. use a specific index for a specified table)
A: MySQL has something similar, you can force the optimiser to use or not use an index. See here http://dev.mysql.com/doc/refman/5.0/en/index-hints.html
Q from Kjeld: Does the query cache exists in RAM or on Disk?
A: The query cache is in RAM, it resides within the data context of the mysqld process.
Q from Renaud: Is the query cache available by instance or by user ? I mean that if 2 users execute the same query but with not the same rights... when the second user execute the query, it will re-parse the query to check table rights ?
A: Yes, the rights for the user are checked before retrieving the data from the query.
Q from Aron: Is there a fast way of filling a memory table, because if it takes 1 minute to fill, it will lock all reads to it
A: Not really. Typical use case is to fill it on server startup and then it is only read after that.
Q from Markus: I have always "filesort" in the Extra field when checking my queries with EXPLAIN. I heard this is not the best but I do allways have this when I am using ORDER BY in the query. Is it right?
A: it means that the server must execute a sort of the resultset before providing the result to the client. This can be avoided if the ORDER BY of the query is related to an index.
Q from Hendrik: Is EXPLAIN also available in 4.1?
A: Yes, absolutely
Q from Madhusudan: I have a ndb cluster with 4 data nodes. All the 4 data nodes sit on disk-less blades (i.e. they have only RAM). Now if I create a table with storage engine as myisam or innodb, then where it will be created?
A: It will be created on the SQL node you are connected to. ndb data nodes are completely unaffected, and what's more, other mysql nodes are also unaffected.
Q from Andrea: I wrote a few stored functions in 5.1 and I noticed that the performance is a much slower than executing calculation directly in the query. Now I noticed that even the cache size commands cannot be used with functions. Any suggestions?
A: It's true that you cannot use the query cache for SELECTs with functions. Regarding the performance of your functions, I am not sure the issue is related to the version of the server. We should check which kind of calculation you execute within the function. It's anyway true that if you can execute a formula directly within a SELECT without calling a function it should be faster.
Q from Jörgen: Is there any performance difference creating a table with a PRIMARY KEY, compared to just creating a UNIQUE INDEX with the same columns as the primary key instead?
A: It depends on the storage engine and on the type of index - Btree, clustered, hash etc.
Q from Pierrick: Where can I find info re the MEMORY Storage Engine?
A: Check the ref manual, here http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html
Q from Ketil: What is the best practice for maintaining a sum/average? Let's say, for a given object, I want to store how many times it has been viewed. Just incrementing with select/update is not "safe", adding each entry to a table and using sum() can be slow.
A: If you just want the sum (or avg) and not at all the individual result, you can do it with one update and no select at all. This is safe. Something like UPDATE tablename SET sumcolumn=sumcolumn+1 WHERE id=...
Q from Donald: How does the size of a query influence how many queries per second can be issued? e.g. If I update blobs of 500K what would be the max QPS?
A: The bigger the query, the more you have to read and write from/to disk. This will affect the number of queries per second.
Q from Walter: Is there any performance loss when you create a view from 'large' queries?
A: A view always generates an overhead, but this is often insignificant and there is a great advantage in using views.
Q from Matthijs: Is it possible to partition a table if the data is already in it? And if so, is it also possible to change the partitioning. And is it also possible to drop partitioning?
A: Yes, all of this is possible using the ALTER TABLE command in 5.1
Q from Pascal: So, vertical partitioning is a good idea for example in a users-table, small partition with the encrypted password and id, all user data in another partition?
A: Yes, that might work pretty well. If you have a Users table with a user profile, you do not want to have a single table with tens of columns
Q from Thijs: From which version of MySQL partitioningis supported?
A: It's supported from 5.1
Q from Ricardo: What is COALESCE? What could i do with that instruction?
A: COALESCE PARTITION reduces the number of partitions when the partitioning mechanism is hash or key.
Q from John: What is the column limit for a mysql table?
A: If you refer to any datatype in general, it's 4GB for a BLOB
Q from Shane: Are all these performance advisors available at all levels of MySQL Enterprise? (e.g. basic)
A: No, performance advisors are available only with the Platinum edition.
Q from Ketil: Can I replicate an InnoDB to MEMORY, so that I can write with transactional security and foreign keys, and read really fast?
A: MEMORY is not a transactional storage engine and an in memory structure, unless it's not redundant and synchronised, is not safe.
Q from Jelle: Is it possible to get the analysuing tools without buying the enterprise server?
A: Unfortunately the performance advisors are part of the MySQL Enterprise subscription.
Q from Rami: what is the performance impact of the Enterprise Monitor?
A: the agent running on the database server needs a very small amount of memory.
Q from Dainius: is it really better to make more little sqls than one big sql?
A: It depends on the query, but in general the optimiser will work better on small queries. Also, a large query will contain a considerable number of joins and this would affect performance.
Q from J.M.: What about query strategies?. Join vs. sub-queries...
A: Joins are usually preferable to sub-queries because of the optimisation of subqueries in MySQL. But there may be some situations where subqueries would be another way to break a single query in smaller queries.
Q from John: What is the maximum number of columns for a mysql table?
A: It depends on the storage engine. InnoDB, for example, cannot contain more than 1,000 columns, MyISAM does not have this limit.
Q from Ketil: I have a DB that is "read many, write few", and will remain relatively small (less than RAM). How do I tune a InnoDB MySQL-database for optimal query performance? There are 8 hasAndBelongsToMany relations to categories and a few 100 000 objects.
A: First of all, have a look here: http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html
You should work with some system params, for example the innodb_buffer_pool_size, to set a specific amount of RAM as buffer for InnoDB.
Q from Alon: Is the query cache only available in version >=5?
A: No, it is available from 4.0.1
Q from Prakash: Is it efficient to put numerical criteria in quotes ? (eg cust_id = '675') --- [Oracle queries would be affected by this]
A: If the column you are using is a character columns, then yes, that is the way to use the index. If It's a numeric column, then you should avoid it.
Q from Konstantinos: Is there a possibility to save a whole table in some kind of cache?
A: Yes, you can use the MEMORY storage engine to keep the data in memory. Another option is to use the caching mechanisms provided by the storage engine, the key buffer for MyISAM and the buffer pool for InnoDB. At startup you can use a LOAD INDEX INTO CACHE command for MyISAM or a simple SELECT for InnoDB to cache the data.
Q from Erik: "queries must be identical" means logically or really literally ? ex.: select a.col from a is equal or not to select col from a;
A: They must be the same, byte by byte
Q from Laszlo: Can MySQL Enterprise Monitor advisor help us to determine the cache size/options?
A: Yes, it provides recommendations on how big the cache should be, if it's over or under utilised.
Q from Ketil: Are there any speed (or stability) issues I should be aware of using the .NET connector? And whichh version is the latest stable version? 5.1.5?
A: Yes, you may use 5.1.5. Starting from 5.0.X, the connector includes support up to MySQL 5.1 and ADO.Net 2.0 insterfaces and subclasses. 5.1.5 contains new membership/role provider, Compact Framework 2.0, a new stored procedure parser and improvements to GetSchema. Connector/NET 5.1 also includes the Visual Studio Plugin as a standard installable component.
Q from Saravanan: Does query cache cache sql or data?
A: It caches the result (data) of a SELECT query. The exact text of the query is used as key.
Q from Ivan: Does EXPLAIN work in other databases (apart from MySQL)?
A: Yes, EXPLAIN is an ANSI SQL Command
Q from Alfred: In ORACLE you can use variables as plpaceholders in the where criteria. Doing so, the cache is hit much more often when only some string in the criteria has changed. is this possible in mysql too?
A: it is definitely possible. The use of variables and placeholders will result in a query that can be executed using the query cache, or the storage engine cache or simply retrieving data on disk - it simply depends on the availability of the data in one of the cache.
Q from Jörgen: In which version was EXPLAIN EXTENDED introduced in MySQL?
A: It's certainly available in 4.1, I do not remember if it was available in 4.0
Q from Pierrick: Join type index is bad ? How so ? I thought it was the best join, the fastest.
A: Join type "index" means that the server has to perform a sequential scan on the index tree. Only ALL, which is a full table scan is worse.
Q from Elinor: what exactly does qcache_lowmem_prunes represent? (e.g. 3937244) and how can you be sure your cache size is right?
A: It counts the number of queries that have been removed from the cache to free up memory for caching new queries. See here http://dev.mysql.com/doc/refman/5.0/en/query-cache-status-and-maintenance.html
In other words, a low number is typically good, a high number means you should consider more memory for the cache.
Q from Prakash: In Oracle we can use hints to influence teh optimiser to take a specific path to obtain the data; Is there a similar facility in MySql ? (eg. use a specific index for a specified table)
A: MySQL has something similar, you can force the optimiser to use or not use an index. See here http://dev.mysql.com/doc/refman/5.0/en/index-hints.html
Q from Kjeld: Does the query cache exists in RAM or on Disk?
A: The query cache is in RAM, it resides within the data context of the mysqld process.
Q from Renaud: Is the query cache available by instance or by user ? I mean that if 2 users execute the same query but with not the same rights... when the second user execute the query, it will re-parse the query to check table rights ?
A: Yes, the rights for the user are checked before retrieving the data from the query.
Q from Aron: Is there a fast way of filling a memory table, because if it takes 1 minute to fill, it will lock all reads to it
A: Not really. Typical use case is to fill it on server startup and then it is only read after that.
Q from Markus: I have always "filesort" in the Extra field when checking my queries with EXPLAIN. I heard this is not the best but I do allways have this when I am using ORDER BY in the query. Is it right?
A: it means that the server must execute a sort of the resultset before providing the result to the client. This can be avoided if the ORDER BY of the query is related to an index.
Q from Hendrik: Is EXPLAIN also available in 4.1?
A: Yes, absolutely
Q from Madhusudan: I have a ndb cluster with 4 data nodes. All the 4 data nodes sit on disk-less blades (i.e. they have only RAM). Now if I create a table with storage engine as myisam or innodb, then where it will be created?
A: It will be created on the SQL node you are connected to. ndb data nodes are completely unaffected, and what's more, other mysql nodes are also unaffected.
Q from Andrea: I wrote a few stored functions in 5.1 and I noticed that the performance is a much slower than executing calculation directly in the query. Now I noticed that even the cache size commands cannot be used with functions. Any suggestions?
A: It's true that you cannot use the query cache for SELECTs with functions. Regarding the performance of your functions, I am not sure the issue is related to the version of the server. We should check which kind of calculation you execute within the function. It's anyway true that if you can execute a formula directly within a SELECT without calling a function it should be faster.
Q from Jörgen: Is there any performance difference creating a table with a PRIMARY KEY, compared to just creating a UNIQUE INDEX with the same columns as the primary key instead?
A: It depends on the storage engine and on the type of index - Btree, clustered, hash etc.
Q from Pierrick: Where can I find info re the MEMORY Storage Engine?
A: Check the ref manual, here http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html
Q from Ketil: What is the best practice for maintaining a sum/average? Let's say, for a given object, I want to store how many times it has been viewed. Just incrementing with select/update is not "safe", adding each entry to a table and using sum() can be slow.
A: If you just want the sum (or avg) and not at all the individual result, you can do it with one update and no select at all. This is safe. Something like UPDATE tablename SET sumcolumn=sumcolumn+1 WHERE id=...
Q from Donald: How does the size of a query influence how many queries per second can be issued? e.g. If I update blobs of 500K what would be the max QPS?
A: The bigger the query, the more you have to read and write from/to disk. This will affect the number of queries per second.
Q from Walter: Is there any performance loss when you create a view from 'large' queries?
A: A view always generates an overhead, but this is often insignificant and there is a great advantage in using views.
Q from Matthijs: Is it possible to partition a table if the data is already in it? And if so, is it also possible to change the partitioning. And is it also possible to drop partitioning?
A: Yes, all of this is possible using the ALTER TABLE command in 5.1
Q from Pascal: So, vertical partitioning is a good idea for example in a users-table, small partition with the encrypted password and id, all user data in another partition?
A: Yes, that might work pretty well. If you have a Users table with a user profile, you do not want to have a single table with tens of columns
Q from Thijs: From which version of MySQL partitioningis supported?
A: It's supported from 5.1
Q from Ricardo: What is COALESCE? What could i do with that instruction?
A: COALESCE PARTITION reduces the number of partitions when the partitioning mechanism is hash or key.
Q from John: What is the column limit for a mysql table?
A: If you refer to any datatype in general, it's 4GB for a BLOB
Q from Shane: Are all these performance advisors available at all levels of MySQL Enterprise? (e.g. basic)
A: No, performance advisors are available only with the Platinum edition.
Q from Ketil: Can I replicate an InnoDB to MEMORY, so that I can write with transactional security and foreign keys, and read really fast?
A: MEMORY is not a transactional storage engine and an in memory structure, unless it's not redundant and synchronised, is not safe.
Q from Jelle: Is it possible to get the analysuing tools without buying the enterprise server?
A: Unfortunately the performance advisors are part of the MySQL Enterprise subscription.
Q from Rami: what is the performance impact of the Enterprise Monitor?
A: the agent running on the database server needs a very small amount of memory.
Q from Dainius: is it really better to make more little sqls than one big sql?
A: It depends on the query, but in general the optimiser will work better on small queries. Also, a large query will contain a considerable number of joins and this would affect performance.
Q from J.M.: What about query strategies?. Join vs. sub-queries...
A: Joins are usually preferable to sub-queries because of the optimisation of subqueries in MySQL. But there may be some situations where subqueries would be another way to break a single query in smaller queries.
Q from John: What is the maximum number of columns for a mysql table?
A: It depends on the storage engine. InnoDB, for example, cannot contain more than 1,000 columns, MyISAM does not have this limit.
Labels: MySQL, webinar performance
Subscribe to:
Posts (Atom)
