Database Performance Blog

Indexes in MySQL

Vadim Tkachenko | June 2, 2006 | Posted In: 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

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

SELECT COUNT(*) FROM t2

1	SELECT COUNT(*) FROM t2

; 250001 (V1)

SELECT COUNT(*) FROM t2 WHERE ID1=1

1	SELECT COUNT(*) FROM t2 WHERE ID1=1

; 83036 (V2) (execution time = 110 ms) That is index selectivity by condition (ID1=1) is V2/V1 = 0.3321 or 33.21% It is said (e.g. book “SQL Tuning”) if selectivity over 20% then a full table […]

InnoDB memory usage

Vadim Tkachenko | May 30, 2006 | Posted In: 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 (innodb_buffer_pool_size >= 1000Mb) = 50000 else if (innodb_buffer_pool_size >= 8Mb) = 10000 else = 1000 (it’s true for *nixes, for Windows […]

Join performance of MyISAM and Innodb

Peter Zaitsev | May 29, 2006 | Posted In: 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 32bit Linux. The columns “id” “i” […]

MySQL INSERT ON DUPLICATE KEY UPDATE and counters

Peter Zaitsev | May 29, 2006 | Posted In: 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 efficient solution for frequent task while keeping it beautiful […]

MySQL SpecJAppServer2004 results

Peter Zaitsev | May 27, 2006 | Posted In: 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 writeup so I would not repeat it. It was […]

Jeremy Cole on MySQL Replication

Peter Zaitsev | May 27, 2006 | Posted In: 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 uses buffered IO so if master crashes you’re out of sync anyway […]

MyISAM mmap feature (5.1)

Vadim Tkachenko | May 26, 2006 | Posted In: 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 intensive pread calls (I believe the same for Windows, but I did not test […]

How Web Services world affect LAMP Stack

Peter Zaitsev | May 22, 2006 | Posted In: 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 you’re developing Web Service Server application you will most likely […]

Speedup your LAMP stack with LigHTTPD

Peter Zaitsev | May 21, 2006 | Posted In: 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 served by PHP or ModPerl. With “client per process” you end up […]

Group commit and XA

Vadim Tkachenko | May 19, 2006 | Posted In: 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) threads without —log-bin —log-bin —log-bin and —innodb_support-xa=0 1 1218.68 614.94 1010.44 4 2686.36 667.77 1162.60 16 3993.59 666.14 1161.56 64 […]

Indexes in MySQL

InnoDB memory usage

Join performance of MyISAM and Innodb

MySQL INSERT ON DUPLICATE KEY UPDATE and counters

MySQL SpecJAppServer2004 results

Jeremy Cole on MySQL Replication

MyISAM mmap feature (5.1)

How Web Services world affect LAMP Stack

Speedup your LAMP stack with LigHTTPD

Group commit and XA

Upcoming Webinars

PERCONA

SERVICES

SOFTWARE

RESOURCES

CONTACT US

COMMUNITY

CONFERENCES

ABOUT