by Peter Zaitsev | Jul 24, 2006 | Insight for Developers
One nice feature added for EXPLAIN statement in MySQL 4.1 is EXTENDED keyword which provides you with some helpful additional information on query optimization. It should be used together with SHOW WARNINGS to get information about how query looks after transformation...
by Peter Zaitsev | Jul 24, 2006 | Insight for Developers
Running EXPLAIN for problematic queries is very powerful tool for MySQL Performance optimization. If you’ve been using this tool a lot you probably noticed it is not always provide adequate information. Here is list of things you may wish to watch out. EXPLAIN...
by Vadim Tkachenko | Jul 19, 2006 | Insight for DBAs
We are going to release several patches which are not included in official MySQL releases. First one is automatically stack trace for x86_64 systems. Currently MySQL resolves stack in crash only for x86 boxes. You can download patch for 5.0.22 source tree here. How to...
by Peter Zaitsev | Jul 17, 2006 | Insight for DBAs
Many people asked me to publish a walk through SHOW INNODB STATUS output, showing what you can learn from SHOW INNODB STATUS output and how to use this info to improve MySQL Performance. What is SHOW INNODB STATUS To start with basics, SHOW INNODB STATUS is a command...
by Peter Zaitsev | Jul 17, 2006 | Benchmarks, Insight for Developers
Have you seen recent eWeek benchmarks which test OpenSource stacks and compare them to .NET ? Here is what was compared and here are results. Results make me ask number of questions – why WAMP would perform 6 times better than LAMP ? Why Python would be faster...
by Peter Zaitsev | Jul 13, 2006 | Insight for DBAs
After playing yesterday a bit with INSERT … SELECT I decided to check is Innodb locks are relly as efficient in terms of low resource usage as they are advertised. Lets start with a bit of background – in Innodb row level locks are implemented by having...
by Peter Zaitsev | Jul 12, 2006 | Insight for DBAs
Everyone using Innodb tables probably got use to the fact Innodb tables perform non locking reads, meaning unless you use some modifiers such as LOCK IN SHARE MODE or FOR UPDATE, SELECT statements will not lock any rows while running. This is generally correct,...
by Peter Zaitsev | Jul 7, 2006 | Insight for DBAs
Brian Aker recently published good write up about using MySQL replication. The piece I find missing however is good description of warning about limits of this approach as well as things you need to watch out for. You can call me person with negative mind but I tend...
by Peter Zaitsev | Jul 6, 2006 | Benchmarks, Hardware and Storage
Partha Dutta posted pretty interesting post about iSCSI vs SCSI performance using SysBench. This is nice to finally see some iSCSI benchmarks done with MySQL – something we were planning to do for a while but never ended up doing, mainly due to lack of hardware...
by Peter Zaitsev | Jul 3, 2006 | Insight for DBAs
If you’re doing significant amount of writes to Innodb tables decent size of innodb_log_file_size is important for MySQL Performance. However setting it too large will increase recovery time, so in case of MySQL crash or power failure it may take long time...
by Peter Zaitsev | Jun 30, 2006 | Hardware and Storage
This post is not exactly about MySQL Performance or about Performance at all, but I guess it should be interested to many MySQL DBAs and other people involved running MySQL In production. Recently I’ve been involved in troubleshooting Dell Poweredge 2850 system...
by Peter Zaitsev | Jun 29, 2006 | Insight for DBAs
“What cache hit rate is good for optimal MySQL Performance” is typical question I’m asked. It could by MyISAM key_buffer or Innodb innodb_buffer_pool it does not really matter. In both cases trying to come up with constant “good” hit rate...
by Vadim Tkachenko | Jun 26, 2006 | Insight for Developers
by Peter Zaitsev | Jun 26, 2006 | Insight for DBAs
As we know build in full text search is currently limited only to MyISAM search engine as well as has few other limits. Today Sphinx Search plugin for MySQL was released which now provides fast and easy to use full text search solution for all storage engines. This...
by Peter Zaitsev | Jun 23, 2006 | Benchmarks, Insight for DBAs
I frequently get questions along the lines of “how many transactions per second MySQL can do” or “how many servers I need to handle 100.000 users” or “which hardware would be enough to handle my 40GB” database. There are two things...
by Peter Zaitsev | Jun 17, 2006 | Insight for DBAs
There were recently number of posts about MyISAM, for example Arjen wrote pretty nice article about MyISAM features so I thought I would share my own view on using MyISAM in production. For me it is not only about table locks. Table locks is only one of MyISAM...
by Vadim Tkachenko | Jun 15, 2006 | Benchmarks
I’m continuing my experiments with different OS and today I tested FreeBSD 6.0 on my box. (more details about box and benchmark see here https://www.percona.com/blog/2006/06/13/quick-look-at-ubuntu-606/). Initially I was very pessimistic about FreeBSD, as...
by Vadim Tkachenko | Jun 13, 2006 | Benchmarks, Insight for DBAs
Arjen posted a good note about MyISAM concurrent-insert features, though I should mention concurrent-insert can be cause of scalablity and peformance problems on SMP boxes, especially on queries processing range-queries. The reason of problems is POSIX read-write...
by Vadim Tkachenko | Jun 13, 2006 | Benchmarks
There are a lot of talks around new coming Ubuntu 6.06, so I decided to make quick benchmarks. I used sysbench 0.4.6 oltp-read-only workload with 1000000 rows against InnoDB and MyISAM tables. Such workload is CPU-bound and allows to compare CPU / OS if we are using...
by Peter Zaitsev | Jun 9, 2006 | Insight for DBAs, MySQL
If you’ve been reading enough database-related forums, mailing lists, or blogs you have probably heard complains about MySQL being unable to handle more than 1,000,000 (or select any other number) rows by some of the users. On the other hand, it is well known...
