MyISAM concurrent insert

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 locks, which are used to protect key_buffer from concurrent changes and called for each processed row. More info […]

Read more

Why MySQL could be slow with large tables ?

If you’ve been reading enough database related forums, mailing lists or blogs you 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 other hand it is well known with customers like Google, Yahoo, LiveJournal,Technocarati MySQL has installations with many […]

Read more

MySQL Server Variables – SQL layer or Storage Engine specific.

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 and which are used on […]

Read more

Intel Woodcrest vs AMD Opteron for MySQL

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 we’ll see what AMD will have to respond. As Opteron […]

Read more

Are larger buffers always better ?

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.
Obviously it does not. The least problem would be […]

Read more

InnoDB thread concurrency

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 works.
MySQL has pluginable architecture which divides work between mysql common code
(parser, optimizer) and storage engine. From storage engine’s point of view it […]

Read more

Indexes in MySQL

MySQL does not always make a right decision about indexes usage.
Condsider a simple table:

Shell

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

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

Shell

SELECT COUNT(*) FROM t2

1

SELECT COUNT(*) FROM t2

;
250001 […]

Read more

InnoDB memory usage

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 (innodb_buffer_pool_size >= 1000Mb) = 50000
else if (innodb_buffer_pool_size >= 8Mb) = 10000
else = 1000 (it’s true for *nixes, for Windows there is a bit […]

Read more