by Peter Zaitsev | Oct 3, 2006 | Insight for DBAs
I’ve written and spoke a lot about using short PRIMARY KEYs with Innodb tables due to the fact all other key will refer to the rows by primary key. I also recommended to use sequential primary keys so you do not end up having random primary key BTREE updates...
by Vadim Tkachenko | Sep 22, 2006 | Uncategorized
Not so long ago Solid released solidDB for MySQL Beta 3 so I decided now is time to take a bit closer look on new transactional engine for MySQL. While my far goal is the performance and scalability testing before I wanted to look at basic transactional properties...
by Vadim Tkachenko | Sep 7, 2006 | Insight for DBAs
InnoDB uses its own mutexes and read-write locks instead of POSIX-mutexes pthread_mutex*, the main reason for that is performance, but InnoDB’s implementation isn’t ideal and on modern SMP boxes can cause serious performance problems. Let’s look on...
by Peter Zaitsev | Aug 21, 2006 | Insight for DBAs
If someone asks me about MySQL Backup advice my first question would be if they have LVM installed or have some systems with similar features set for other operation systems. Veritas File System can do it for Solaris. Most SAN systems would work as well. What is...
by Peter Zaitsev | Aug 6, 2006 | Insight for Developers
Baron wrote a nice article comparing locking hints in MySQL and SQL Server. In MySQL/Innodb SELECT LOCK IN SHARE MODE and SELECT FOR UPDATE are more than hints. The behavior will be different from normal SELECT statements. Here is a simple example: SESSION1: mysql>...
by Peter Zaitsev | Aug 4, 2006 | Insight for DBAs
One of very interesting techniques Innodb uses is technique called “doublewrite” It means Innodb will write data twice when it performs table space writes – writes to log files are done only once. Why is Doublewrite is needed? It is needed to archive...
by Peter Zaitsev | Jul 30, 2006 | Insight for DBAs
MySQL is known for its stability but as any other application it has bugs so it may crash sometime. Also operation system may be flawed, hardware has problems or simply power can go down which all mean similar things – MySQL Shutdown is unexpected and there...
by Vadim Tkachenko | Jul 28, 2006 | Insight for DBAs
I’m again returning to InnoDB scalability and related bug #15815 as it hurts many users and customers using multi-cpu servers. Short intro into problem: On 4-CPU box 1 thread executes full-table scan select query for 8 sec, but with 4 threads – each thread...
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 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 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 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 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
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 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 | 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...
