by Peter Zaitsev | Jun 8, 2006 | Insight for DBAs
MySQL Server has tons of variables which may be adjusted to change behavior or for performance purposes. They are documented in the manual as well as on new page Jay has created. Still I see constant confusion out where which of variables apply to storage engines only...
by Peter Zaitsev | Jun 8, 2006 | Benchmarks
AnandTech published Intel Woodcrest preview benchmarks which have some numbers for MySQL as well. From these numbers performance looks great and it looks like finally Intel has something to respond to AMD Opterons on Server market. Now competition heats up and...
by Peter Zaitsev | Jun 6, 2006 | Uncategorized
Sometimes I see people thinking about buffers as “larger is always better” so if “large” MySQL sample configuration is designed for 2GB and they happen to have 16, they would simply multiply all/most values by 10 and hope it will work well....
by Vadim Tkachenko | Jun 5, 2006 | Insight for DBAs
InnoDB has a mechanism to regulate count of threads working inside InnoDB. innodb_thread_concurrency is variable which set this count, and there are two friendly variables innodb_thread_sleep_delay and innodb_concurrency_tickets. I’ll try to explain how it...
by Vadim Tkachenko | Jun 4, 2006 | Insight for DBAs
As maybe you know InnoDB uses hard page size 16Kb for datafiles and for buffer pool. However this size can be changed if you need it for your workload. go to file innobase/include/univ.i, lines: /* The universal page size of the database */ #define UNIV_PAGE_SIZE (2 *...
by Vadim Tkachenko | Jun 2, 2006 | Insight for DBAs
MySQL does not always make a right decision about indexes usage. Condsider a simple table: CREATE TABLE `t2` ( `ID` int(11) default NULL, `ID1` int(11) default NULL, `SUBNAME` varchar(32) default NULL, KEY `ID1` (`ID1`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 123456...
by Vadim Tkachenko | May 30, 2006 | Insight for DBAs
There are many questions how InnoDB allocates memory. I’ll try to give some explanation about the memory allocation at startup. Some important constants: NBLOCKS=count of block in innodb_buffer_pool = innodb_buffer_pool_size / 16384 OS_THREADS= if...
by Peter Zaitsev | May 29, 2006 | Benchmarks
We had discussion today which involved benchmarks of Join speed for MyISAM and Innodb storage engines for CPU bound workload, this is when data size is small enough to fit in memory and so buffer pool. I tested very simple table, having with about 20.000 rows in it on...
by Peter Zaitsev | May 29, 2006 | Insight for DBAs
MySQL INSERT ON DUPLICATE KEY UPDATE is very powerful but often forgotten MySQL feature. It was introduced in MySQL 4.1 but I still constantly see people unaware of it. Myself I like this feature big deal because it is designed in truly MySQL style – very...
by Peter Zaitsev | May 27, 2006 | Benchmarks
Sun has now published SpecJAppServer2004 benchmark results with MySQL. The results are pretty good as we can see and it is also good to know some room for tuning remains so we can hope getting even better results this time. Jenny Chen published good MySQL Tuning...
by Peter Zaitsev | May 27, 2006 | Insight for DBAs
Jeremy Cole recently posted very nice post about MySQL Replication Performance. There are however few points I should comment on. Jeremy Speaks about MyISAM and Innodb in the same terms, in fact they are quite different for Replication. As you probably know MyISAM...
by Vadim Tkachenko | May 26, 2006 | Insight for DBAs
As you know MyISAM does not cache data, only indexes. MyISAM assumes OS cache is good enough and uses pread/pwrite system calls for reading/writing datafiles. However OS is not always good in this task, my benchmarks show Linux/Solaris aren’t scalable on...
by Peter Zaitsev | May 22, 2006 | Insight for Developers
In connection to my previous post I was thinking how Web 2.0 with its massive use of Web Services affect LAMP Stack ? Well actually there are serious difference both for client and server application development which you might want to take into account. If...
by Peter Zaitsev | May 21, 2006 | Insight for Developers
Apache Web server could frequently be bottleneck for your LAMP stack, or you might be wasting resources on your web servers. The problem with Apache server comes from “client per process” architecture which you have to stick to at least for dynamic content...
by Vadim Tkachenko | May 19, 2006 | Insight for DBAs
Returning to post Group commit and real fsync I made several experiments: I ran sysbench update_key benchmarks without —log-bin, with —log-bin, and with —log-bin and —innodb-support-xa=0 (default value is 1). Results (in transactions / sec)...
by Peter Zaitsev | May 17, 2006 | Insight for DBAs
Every so often people ask me the question how should they estimate memory consumption by MySQL Server in given configuration. What is the formula they could use. The reasons to worry about memory usage are quite understandable. If you configure MySQL Server so it uses...
by Peter Zaitsev | May 16, 2006 | Insight for DBAs
I’ve now added one more presentation to MySQL Performance Presentations page. This presentation is from MySQL Users Conference 2006 which was presented by Vadim but which we prepared together. Hope you enjoy it. Get it here
by Peter Zaitsev | May 13, 2006 | Insight for DBAs
MyISAM storage engine has key compression which makes its indexes much smaller, allowing better fit in caches and so improving performance dramatically. Actually packed indexes not a bit longer rows is frequent reason of MyISAM performing better than Innodb. In this...
by Vadim Tkachenko | May 12, 2006 | Insight for DBAs
In MySQL 5.0.19 the meaning of innodb_thread_concurrency variable was changed (yeah, again). Now innodb_thread_concurrency=0 means unlimitied count of concurrent threads inside InnoDB. It’s logical, but there was long way. In MySQL versions below 5.0.8 for...
by Peter Zaitsev | May 11, 2006 | Insight for DBAs
Thinking more about the problems I wrote about yesterday I had a question why so ugly workaround and guesses or manual configuration is needed ? The answer seems to be Operation Interfaces just do not provide IO interface which is good enough. The big missing piece is...
