Tag MySQL_70

Asking the question is already answering to it ;) I had a very long query that was even locking the table during the select on innodb (select into, that's due to the binlog). To avoid that query I decided to replace the select into statement by a select into outfile and add a second statement to perform the load data infile. The query was running for 18 hours !! It was impossible to change it, but the amout of data was huge, more than 100M rows (>1.2GB of returned data). I was able to reduce the running time by 9 : now the query runs in 1h50 ! How ? by adding SQL_BIG_RESULT to my select and by modifying two MySQL variables : set global join_buffer_size=67108864; set global query_cache_size=67108864;

Yesterday with Zipkid[1], we went to the MySQL(/Sun) Conference in Paris[2]. We took the TGV in Lille to Paris, then the metro (with too many stairs...) and a nice walk before we found the right place. We received a nice bag, good ! We arrived just some minutes late during the introduction by Bertrand Matthelié (MySQL) and Bruno Hourdel (Sun). That was a nice presentation and Bruno clarify some points on the bought of MySQL by Sun. Robin Schumacher made a nice presentation about the future features of MySQL, very interesting and MySQL Enterprise becomes more and more interesting with the monitoring and the new MysQL Query Analyzer[3]. We were a bit surprised that those presentations where on M$ office and not using OO.org or StarOffice ;-) We followed then the presentation of Serge Frezefond about Web2.0 and memcached, first part was quite common, the second one was much more attractive for me. The topic of the next presentation (HA and loadbalancing with MySQL Proxy) was maybe the most important reason of my presence in Paris... but I was disappointed by the presentation of Omar Zemrag, his inexperience as speaker was too obvious... pity. The lunch was very good, isn't it Zipkid ? Before every presentation we could "enjoy" the famous song of The Beatles : "here comes the sun" We got a thunder sponsors presentation, one minute per sponsors, that was just enough. The LEMUG[4] is doing now what we were already doing in Belgium with technical meeting between MySQL users, but they also provide stuffed mysql dolfins, but we didn't see or get one :-( We attended the presentation of Miriam Tuerk about datawarehouse with MySQL and Infobright[5] where we heard about good, bad and ugly data packs :-) This is something I should check soon. After this we saw a customer case : Virgin Mobile (Xavier Nicolle). It was not easy to understand him, his voice wasn't good with the accoustic system it seems and he was "eating" his words. The MySQL backup strategy presentation from Max Mether was too slow (it would be certainly nicer in English) and the content was an overview of the basis (mysqldump, copy files), nothing new in fact, I expected some overview of zamanda. Scale Up, Scale Out, Virtualization by Serge Frezefond. He gave a nice summary of the benefits of the different virtualization method concerning MySQL. For high performance and critial applications, full virt or para virt are of course not advised. He advised for MySQL the OS virtualization like zones on Solaris, OpenVZ or VSever on Linux. We also saw nice slides summarizing the load on different levels : network, cpu, ram/caching, i/o, space... It seems that when we reach some limitations in an intensive write application, scale out is the solution, but that scale out can happen on the same physical machine using multiple instances especially on machines with a large number of cores... (I don't have yet enough return of experience on this topic). This is called "instance scale-out". Serge illustrated this with a benchmark's results (DBT2, mysqlslap). In conclusion, currently is stupid to run only one instance of MySQL on a machine having more than 8 cores. This is then a green approach :-) But having more instances will also consume more diskspace if they need the same data... I was globally happy with this conference, but I'd prefer to have some technical high level tracks. Would be nice if MySQL organize some event like SambaXP. PS: for my Sun lover colleagues, I've learn that Sun has something called MySQL coolstack: it's binary of MySQL optimized for Sun platforms. [1] http://www.zipkid.com/ [2] http://www.mysql.fr/news-and-events/european-conferences/2008/ [3] http://www.mysql.com/products/enterprise/query.html [4] http://www.lemug.fr/ [5] http://www.infobright.org/Open-Source/Home