Peter Zaitsev:
Now I work as Independent MySQL Consultant specializing in MySQL Performance
Previously I was employed as Manager of High Performace Group at MySQL Inc,
I specialise MySQL Server performance as well as in performance of application stacks using MySQL, especially LAMP. Web sites handling millions of visitors a day dealing with terabytes of data and hundreds of servers is king of applications I love the most.
As part of my work at MySQL I’m working on performance analyses of MySQL Server, our groups looks for bottlenecks and helps Development team to fix them. We also work with MySQL partners helping them to ensuree MySQL performs best with Hardware or Software solution.
We’re also involved in MySQL Support activities - if there is complex MySQL Performance related support case, it will likely land on our plate.
I’m also doing MySQL Performance consulting engagements. During last few years I’ve been to many big web shops and enterprises as well as worked remotely with about same number.
I also enjoy speaking on the conferences. You will find slides for most of my talks on this site.
You can learn more about Peter Zaitsev on our company team page.
Vadim Tkachenko:
I partner with Peter on our MySQL Consulting Practice
Before that I was employed by MySQL Inc as Performance Engineer, working in Peters group working on MySQL Performance and Scalability issues. My primary tasks were benchmarks, profiling, finding bottlenecks. I’m an expert in multi threading programming and synchronization stuff.
While working for MySQL I took part in many performance optimization projects, including DVD Store Benchmark for CT competition, where I’ve implemented most of optimizations under Peter’s guidance.
I also worked on number features for MySQL Performance monitoring and tuning - SHOW MUTEX STATUS, Tuning Innodb mutexes and concurrency control, MMAP IO for MyISAM tables, Innodb Auto-increment optimizations.
My recent work in my spare time was finding a way to get Innodb to scale with multiple CPUs and I have a prototype patch which seems to help a lot on certain workloads.
Before MySQL I used to develop web-applications, so LAMP is my native area. Currently I support my two open source projects: PHPExcelReader, php class for reading/writing excel documents and PefrProf, performance profiler, which I use as replacement of gprof and oprofile.
To learn more about Vadim Tkachenko please visit our company team page.
We have published biographies of other MySQL Performance Blog contributers at Percona Team Page
42 Comments
Hi Peter, can you contact me when you have a chance or send me your email address? I have a question about INNODB table locking and can’t seem to find any good resources on this topic. Thanks! Leon
Comment :: May 30, 2006 @ 7:32 am
Hi Peter,
What is the process if we’d like to use you for performance consulting engagement to look at our MySQL MyISAM tables performance?
We are located in Silicon Valley.
You can drop me an email directly
Thanks
ananth
Comment :: June 20, 2006 @ 5:59 pm
Ananth,
Sorry I did not notice you are looking for performance consulting services, In other thread I thought you’re looking for some free advice.
If you would like to order performance consulting from MySQL you can contact our profession services department. Here are our base offers: http://www.mysql.com/consulting/packaged/. You can than request me as consultant if you would like me to perform the job.
Comment :: June 23, 2006 @ 7:49 am
Добрый день, Петр.
Пожалуй?та ?в?жите?ь ?о мной по email’у. Хотело?ь бы задать Вам не?колько вопро?ов ?в?занных ? mysql.
Comment :: July 3, 2006 @ 1:50 pm
Hi Peter, love the blog, I have it as an RSS Feed on my.yahoo.com
Comment :: July 12, 2006 @ 3:11 pm
Hi Dathan,
Thank you for your note. It is good to get some feedback to know who is actually reading the blog. As I can see only few people comment, most just read so it is hard to get to know people
Comment :: July 13, 2006 @ 12:56 am
CPU Max’s out at 100% when a query is run.
We have a Windows 2000 Server SP4+ System with MySQL 4.1.20 running an application. When this was originally installed (MySQL 4.1.1) on a P4 2.4GB 512MB Server there were no problems. However when the system was upgraded P4 2.8Ghz 2GB RAM when a query was run the CPU would max out hanging the system. We were advised by MyBugs to upgrade to 4.1.20 which we duly did and hey presto the problem seemed to go away. Then 3 days later back to square one, the CPU max’s out on the querys. We have tried this on a variety of hardware with the same problem. Any ideas?
Comment :: July 17, 2006 @ 7:11 pm
Dave,
It is hard to tell why 4.1.1 worked OK while 4.1.20 causes troubles. It could be some behavior changes, different server settings or it could be changed data distribution or statistics. I would suggest to run SHOW PROCESSLIST to see what queires are causing problems and run EXPLAIN for them.
If you still have old server running explain on old system and comparing them would help.
Comment :: July 18, 2006 @ 4:56 am
Hi Peter,
I like your Blog, I’m very interested in MySQL Performance Tuning and Optimization. You provide some deep view on performance aspects with pros and cons, which elsewhere is often explained shortly and show only the surface. I still have to read your presentations
Good speed!
Comment :: August 3, 2006 @ 2:18 am
Thank you Soeren,
Yes it is my goal to provide some deep insights and production experiences in this blog. I would not just rephase the manual
Comment :: August 3, 2006 @ 3:06 am
Hello Peter,
I just picked your name off of the MySQL blog list because you were at the top - I took that intuitively to mean something, hehe! My question is really simple and if you want to direct me to a source for further simple questions that’s fine. I will have more interesting and complex questions later.
I tried to get the Administrator to connect to my server and it said I had an obsolete version of mysql running. Do I have to back up all my data, tables, etc. before installing a new version or will that happen seamlessly and leave everything intact? I’m using it with Ruby-on-Rails. We will be expecting some very large numbers of subscribers so that’s when I will have some performance test-type questions for you. Thanks for any help you can give. RT
Comment :: August 9, 2006 @ 10:09 am
Hi Rob,
When you’re upgrading MySQL Server it is recommended to take backup of your data just in case. However upgrade should happen semlessly in most cases. Also I would check all tables after upgrade if you moved to nex next major version - sometimes sort order and similar things may display tables as corrupted.
If you have further questions please use newly created forums: http://forum.mysqlperformanceblog.com/
Comment :: August 11, 2006 @ 9:00 am
Hey Peter,
Just one quick question regarding dbt2 (the tpcc-like benchmark from osdl). How do you clean the database after an experiment? How do you get rid of the changes made during the experiment?
Thanks.
Comment :: October 25, 2006 @ 11:44 am
Hi Peter,
You have a greate blog with so many valuable articles. May I translate some of them into Chinese and put those Chinese articles on my blog? Of course there will be URLs of your original articles in my translation.
Regards,
Gu Lei —- one of MySQL fans in China
Comment :: November 18, 2006 @ 8:34 pm
Sure. Please feel free to do so.
Comment :: November 21, 2006 @ 10:43 am
Hi, i am no able to gain the same performance as in MSSQL while migrating to MySQL 5.
queries with simple selcts and inserts run efficiently.
But when it comes to complex queries with sub-queries the performance is like 1 record read /sec. Usually it is more than a second.
Comment :: November 28, 2006 @ 12:58 am
Murtaza,
Please ask unrelated questions on forum.mysqlperformanceblog.com
But basically you’re right. Some subqueries are poorly optimized in MySQL as of version 5.0
Comment :: November 28, 2006 @ 3:19 am
I am new to MySQL and was hoping you might be able to give me some tips for optimizing it. I am using MySQL for data mining (i.e. reads, no inserts, updates etc.) and have it installed on a dual core intel processor (2* 3.0Ghz) with 4Gb RAM using SUSE Linux. The database is large with tables upto 30Gb and use a mixture of InnoDB and MyISAM. I am the only person querying the data and am finding queries take a long time (12-24hrs).
Any tips?
Ps. Its indexed.
Comment :: December 13, 2006 @ 1:23 pm
Have you gotten my email yet ? any chance to meet up and talk about on site engagement ? my aim sn is fbamylin.
Thanks.
Amy
Comment :: April 23, 2007 @ 11:06 am
thx a lot. my forum and bog will be faster in the future!
http://wiki.mobbing-gegner.de/Linux/MySql/optimieren
Comment :: April 24, 2007 @ 2:55 pm
so great blog!
This is what I have been finding for a long time.
Comment :: June 15, 2007 @ 3:46 am
Peter - I have a question about backing up InnoDB tables, namely: I have a very very large database, on the order of 400,000,000,000 records in various InnoDB tables. Is there a faster way to back this up then a lengthy mysqldump to a text file? This also is extremly large, and even compressed with tar && gzip -9 can’t fit on one DVDR. Any other thoughts / suggestions ?
Comment :: June 15, 2007 @ 6:47 am
Hi Peter
I am impressed with your knowledge and would be interested to speak to you about job opportunities.
Thank you!
-Melissa
Comment :: June 26, 2007 @ 7:32 am
Peter, we acquire shares of privately-held, venture backed corporations such as MySQL. Would you be interested in a possible liquidity solution for all or a portion of your shares in MySQL? We have worked with many executives and Founders of high quality venture-backed companies. Please let me know if you’d like to learn more.
Best regards,
Max
Comment :: June 28, 2007 @ 9:16 am
Greetings,
I invite you to evaluate a free (Not for Resale) version of FileMaker Pro 9 (http://filemaker.com/products/fmp/features.html), now offering live SQL connections.
Please contact me if you would like to evaluate the software.
Many thanks.
Kevin Mallon
Kevin J. Mallon
Senior Manager, Public Relations
FileMaker, Inc.
Comment :: July 18, 2007 @ 5:00 pm
Dear Petr,
If by any chance you could be interested in Senior Engineering Positions in Google Russia please let me know. Thank you.
Kind regars,
Natalia Korobova
Google EMEA
E-mail: nkorobova@google.com
Comment :: October 11, 2007 @ 2:18 am
День добрый
Я хотел бы задать один вопрос. У меня есть БД на InnoDB размером от 1 до 1.8GB на выделеном сервере с 4GB ОЗУ. К базе необходима максимальная скорость доступа на чтение. Различные оптимизации нужного результата не дали, потому я включил режим хранения данных в .ibd файлах и разместил их на RAM-диске размером 2GB. В настройках MySQL поставил умеренное использование памяти и небольшой размер лога (128 MB). Скорость работы меня устраивает, но одолевают сомнения - можно ли достичь таких же показателей по скорости разными настройками и выделив MySQL ту память, что я использую под RAM-диск.
Если у Вас есть желание и возможность - проведите несколько сравнительных тестов и напишите статью с опровержением или подтверждением этого метода (использования RAM-диска в качестве хранилища для данных).
Заранее спасибо
Comment :: October 17, 2007 @ 1:58 am
Hi,Peter
I am postgraduate of grade one in China,i am really excited when i find this Blog,because i show much interest on Mysql .Thanks for your files,free to all of us.
Comment :: January 22, 2008 @ 11:09 pm
[...] Peter Zaitsev’s advice on faking a reverse index, I decided to sidestep our problem by creating a separate reverse [...]
Pingback :: June 28, 2008 @ 10:03 pm
[...] avoir discuté avec Peter Zaitsev et Heikki Tuuri, il semblerait que “innodb_file_per_table” qui est apparu avec MySQL [...]
Pingback :: June 29, 2008 @ 4:33 am
asa ako ate karon
Comment :: July 15, 2008 @ 4:12 am
dear peter,
asa akong ate karon….dugay lge ni uli..
Comment :: July 15, 2008 @ 4:14 am
Peter,
Doing some searching on MySQL statistics and saw this site come up. I had read
your book (and recommended it to others), it is excellent. I work at a Company
that just released an extensive set of Open Source patches for MySQL to improve
statistical gathering — SHOW STATUS on steroids! We are also trying to get it
accepted as a change to the SQL standard — we are on the INCITS H2 committee (the
U.S. standards body for SQL). I know you all must be very busy, but if you have
time your thoughts are welcome — more details below:
I work at a Web Hosting company and we were frustrated by our
inability to account for user activity on a busy MySQL server. We
could see gross spikes in activity by SHOW STATUS, but couldn’t tell
where they were coming from in an easy manner. We modified the server
so our DBAs could account for activity by user, db, and connecting
host. We love it!
We decided to better document our changes and developed an extensive
set of patches for MySQL and recently released them as Open Source (we
hope it will get picked up in the main MySQL distribution soon!). We
are next thinking of working on PostgreSQL.
http://freshmeat.net/projects/extsql/
As a couple of examples below show — it became easy for us to track
high usage patterns. We were surprised to see that a lot of activity
was coming from web sites we had never expected to be ‘high users’.
Show number of select, updates, and total queries from all
users@client host machines where more than 10,000 queries were issued:
sql> SHOW STATISTICS Com_select, Questions, Com_update FROM conuser
WHERE Questions > 10000;
+—————————-+————-+———–+————+
| conuser (user@host) | Com_select | Questions | Com_update |
+——— ——————+————-+———–+————+
| bandala@db2.adomain.com | 8306726 | 23163320 | 3439850933 |
| sandymao@db2.adomain.com | 1704040 | 3831803 | 3365501841 |
| ponnetli@lathe.adomain.com | 24920 | 95662 | 156529077 |
Show number of select, updates, and total queries for DB bandala for
the past three minutes:
sql> SHOW STATISTICS Com_select, Questions, Com_update FROM db
LIKE ‘bandala’ HISTORY LIMIT 3;
+———+————-+————+———–+————+
| db | minutes | Com_select | Questions | Com_update |
+———+————-+————+———–+————+
| bandala | 11/20 13:56 | 216 | 382 | 318343 |
| bandala | 11/20 13:55 | 642 | 1618 | 1386347 |
| bandala | 11/20 13:54 | 280 | 699 | 646855 |
Comment :: October 7, 2008 @ 10:26 am
Hi Peter,
Just a litle question: where can I use views in Mysql?
The great part of this blog’s articles said that this feature may cause performance’s loss.
I’m working on several reports in my system, and all this reports will need to some data
distributed in more than 20 tables. It will be a good reason to use views?
Thank you
Comment :: October 14, 2008 @ 11:06 am
Hello Peter,
Thanks for wonderful blog.Presentations are really helpful. For the first time, I have read so much about mysql performance & optimization & still reading . I am feeling lucky
Regards,
Sandip.
Comment :: October 21, 2008 @ 3:13 am
Congratulations for HPMS v2!!!! It’s totally amazing. Very interesting, one of my favourites IT books (although I’m not exactly a MySQL supporter).
Comment :: October 21, 2008 @ 6:06 am
[...] Peter Zaitsev will be giving his excellent presentation “Scaling MySQL powered Web Sites by Sharding and Replication” to the upcoming SF MySQL 6:00pm on November 3rd @ the CBS Interactive (formerly CNET Networks) building in SOMA San Francisco (235 2nd St). [...]
Pingback :: October 23, 2008 @ 4:28 pm
Hi guys,
I have just ordered your book,
You guys rock!
Cheers
Claudio
Comment :: February 12, 2009 @ 3:41 pm
[...] The MySQL Performance Blog, and this post in particular, The perils of InnoDB with Debian and startup scripts, by Baron Schwartz answered our question. [...]
Pingback :: March 27, 2009 @ 9:36 am
Hi Peter,
I know this is quiet a bad forum to post such a question though I’m hoping you gain a quick point of view from you or someone else from Percona with regards to Continuent’s Tungston replicator as a drop in replacement for MySQL standard master / slave.
http://www.continuent.com/solutions/mysql-solutions/master-slave-replication
In a scenario where theres no funky binlog excludes to purge out DROPS or what not from slaves, as far as I can tell, the only real benefit is the ability to have a multiple slave configuration and the ability to promote a slave to a master and have those other slaves be fully aware of where I am at this point of time with regards the new master.
I am wondering what your thoughts are on Continuent’s Tungston replicator? Have you had many customers use it?
Thanks and again I find these posts and book most informative.
Kind regards,
Trent
Comment :: April 16, 2009 @ 12:26 am
Is there any way we can contact you for a suggestion on an article to appear on your blog? I’m struggling with something a lot of people probably are, and haven’t been able to find many resources on the subject. Thanks !
Comment :: April 17, 2009 @ 12:44 am
You can contact us via http://www.percona.com/contacts.html
Comment :: April 17, 2009 @ 6:08 am