headers
Jeff Roberson | 1 Jul 2007 13:00

Re: mysql scalability.

On Thu, 14 Jun 2007, Sunny Bains wrote:

> Jeff,
>
> Jeff Roberson wrote:
>> Firstly, has anyone examined using pthread standard locks in place of
>> your home-rolled spinlocks?
>
> I assume you mean the InnoDB mutex implementation. I've tested the pthread
> locks under Solaris 10 and it does help there. I'm interested in your
> observations and your comments are most welcome.

I have produced a graph of mysql writes on linux and bsd:

http://people.freebsd.org/~jeff/mysqlwrite.png

This is using the sysbench oltp benchmark.  I see long periods with no 
disk activity and almost no CPU activity where all mysql threads are 
blocked on a condition variable.  This can last for a second or two.  Has 
anyone investigated it?  Is this an understood problem?

The difference between the blue and green lines on FreeBSD is a patch to 
reduce kernel contention related to fcntl().  However, the most 
significant sources of contention by far are in mysql.

Thanks,
Jeff

>
> Regards,
(Continue reading)

Permalink | Reply | 2 comments |
headers
Konstantin Osipov | 1 Jul 2007 21:28
Picon
Favicon

Re: mysql scalability.

* Jeff Roberson <jroberson <at> chesapeake.net> [07/07/01 15:30]:
> > Jeff Roberson wrote:
> >> Firstly, has anyone examined using pthread standard locks in place of
> >> your home-rolled spinlocks?
> >
> > I assume you mean the InnoDB mutex implementation. I've tested the pthread
> > locks under Solaris 10 and it does help there. I'm interested in your
> > observations and your comments are most welcome.
> 
> I have produced a graph of mysql writes on linux and bsd:
> 
> http://people.freebsd.org/~jeff/mysqlwrite.png
> 
> This is using the sysbench oltp benchmark.  I see long periods with no 
> disk activity and almost no CPU activity where all mysql threads are 
> blocked on a condition variable.  This can last for a second or two.  Has 
> anyone investigated it?  Is this an understood problem?

I have a suspicion what may be causing this, but from such a brief
description I can't recognize anything in particular.

Could it be Bug#15815 "Very poor performance with multiple
queries running concurrently"?

Are any DDL statements issued during the load (CREATE/DROP TABLE,
ALTER, FLUSH TABLES, etc?)

> The difference between the blue and green lines on FreeBSD is a patch to 
> reduce kernel contention related to fcntl().  However, the most 
> significant sources of contention by far are in mysql.
(Continue reading)

Permalink | Reply | 0 comments |
headers
Sunny Bains | 2 Jul 2007 01:59
Picon
Favicon

Re: mysql scalability.

Jeff,

Jeff Roberson wrote:
 > I have produced a graph of mysql writes on linux and bsd:
> 
> http://people.freebsd.org/~jeff/mysqlwrite.png
> 
> This is using the sysbench oltp benchmark.  I see long periods with no
> disk activity and almost no CPU activity where all mysql threads are
> blocked on a condition variable.  This can last for a second or two. 
> Has anyone investigated it?  Is this an understood problem?

This is very interesting, as I haven't observed this during my testing.
Please post the the output of pstack (or stack trace after attaching
to the running process) during this pause.

Regards,
-sunny

--

-- 
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe:    http://lists.mysql.com/internals?unsub=gcdmd-internals <at> m.gmane.org
Permalink | Reply | 0 comments |
headers
Rick James | 3 Jul 2007 19:45
Picon
Favicon

RE: Blob data

I gave up on putting large blobs in Mysql -- too many limits around 16MB.

Instead I broke blobs into pieces, inserting them with a sequence number.

Added benefit:  Does not clog up replication while huge single-insert is
being copied over network and reexecuted on slaves. 

> -----Original Message-----
> From: Paul McCullagh [mailto:paul.mccullagh <at> primebase.com] 
> Sent: Wednesday, June 27, 2007 2:57 AM
> To: Ann W. Harrison
> Cc: MySQL List; MySQL Internal
> Subject: Re: Blob data
> 
> Hi Ann,
> 
> Currently, the thoughts on how to make the BLOB references secure go  
> like this:
> 
> The BLOB reference consists of 2 components: The first component is  
> basically an index used to find the BLOB on the server. The second  
> component is a random number generated when the BLOB is created.
> 
> The random number acts as an "authorization code", and is checked  
> when the BLOB is requested. So if the authorization code supplied in  
> the BLOB reference does not match the code stored by the server for  
> that BLOB, then the BLOB is not returned.
> 
> If the authorization code is a 4-byte number, then the chances of  
> getting the correct code for any particular BLOB is 1 in 4 billion.
(Continue reading)

Permalink | Reply | 2 comments |
headers
Ann W. Harrison | 3 Jul 2007 20:43
Picon
Favicon

Re: Blob data

Rick James wrote:
> 
> Instead I broke blobs into pieces, inserting them with a sequence number.

Understanding the underlying problem, that still seems like an
unnatural way to store pictures and documents.

> Added benefit:  Does not clog up replication while huge single-insert is
> being copied over network and reexecuted on slaves. 

The design of blobs that Jim did at DEC included the ability to send
them across the network in chunks of a client specified size.  In 1982
it was quite common to have blobs that were larger than physical memory.
What he did more recently was add a "blob repository" separate from the
active tablespace that allowed the backup function to skip unchanged
blobs while backing up active data.  It also allows replicants to share
a single copy of blobs, if appropriate.

There are lots of ways of making large blobs work better in relational
databases.

Regards,

Ann

--

-- 
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe:    http://lists.mysql.com/internals?unsub=gcdmd-internals <at> m.gmane.org
(Continue reading)

Permalink | Reply | 0 comments |
headers
Krunal Bauskar | 4 Jul 2007 06:11
Picon

MySQL table scan bug in DML UPDATE context

MySQL Bug:
==========

I am hitting a bug in MySQL (mysql-5.1.19) while using UPDATE.
This is in context of new storage engine we are developing,
but the bug also appears for the CSV storage engine.
Please let me know if anyone has a workaround on this.

What is the bug:
===============
- BUG occurs as part of DML Update Execution.
- Example Schema:
    create table t (i int, f float, c char(20)) engine= CSV;
  Update Statement:
     update t set i = 10 where f > 2.0 LIMIT 2;
- MySQL reports to SE that it just needs second column (f) for
  evaluation.
  This is done using bitmap (table->read_set).
  (Call to rnd_next has table->read_set bitmap updated to
  reflect selected column information)
- But for evaluation of where condition MySQL internally
  goes and compare complete record and not the *only*
  value/column read.
- This is cause of the problem:
  MySQL internally should only compare loaded values but unfortunately it
  compares complete record. This is not in sync with the bitmap passed by
  MySQL.

Why can't storage engine return complete record ?
=================================================
(Continue reading)

Permalink | Reply | 3 comments |
headers
Sergei Golubchik | 4 Jul 2007 09:31
Picon
Favicon

Re: MySQL table scan bug in DML UPDATE context

Hi!

On Jul 04, Krunal Bauskar wrote:
> MySQL Bug:
> ==========
> 
> I am hitting a bug in MySQL (mysql-5.1.19) while using UPDATE.
> This is in context of new storage engine we are developing,
> but the bug also appears for the CSV storage engine.
> Please let me know if anyone has a workaround on this.
> 
> What is the bug:
> ===============
> - BUG occurs as part of DML Update Execution.
> - Example Schema:
>    create table t (i int, f float, c char(20)) engine= CSV;
>  Update Statement:
>     update t set i = 10 where f > 2.0 LIMIT 2;
> - MySQL reports to SE that it just needs second column (f) for
>  evaluation.
>  This is done using bitmap (table->read_set).
>  (Call to rnd_next has table->read_set bitmap updated to
>  reflect selected column information)
> - But for evaluation of where condition MySQL internally
>  goes and compare complete record and not the *only*
>  value/column read.
> - This is cause of the problem:
>  MySQL internally should only compare loaded values but unfortunately it
>  compares complete record. This is not in sync with the bitmap passed by
>  MySQL.
(Continue reading)

Permalink | Reply | 0 comments |
headers
Ingo Strüwing | 4 Jul 2007 09:42
Picon
Favicon

Re: MySQL table scan bug in DML UPDATE context

Hi Krunal,

Krunal Bauskar wrote:
> MySQL Bug:
> ==========
> 
> I am hitting a bug in MySQL (mysql-5.1.19) while using UPDATE.
> This is in context of new storage engine we are developing,
> but the bug also appears for the CSV storage engine.
> Please let me know if anyone has a workaround on this.

Please look at Bug#28971 (ALTER TABLE followed by UPDATE for a CSV table
make server crash): http://bugs.mysql.com/bug.php?id=28971
It might be related to this problem. The fix will go into one of the
next 5.1 releases.

> 
> What is the bug:
> ===============
> - BUG occurs as part of DML Update Execution.
> - Example Schema:
>    create table t (i int, f float, c char(20)) engine= CSV;
>  Update Statement:
>     update t set i = 10 where f > 2.0 LIMIT 2;
> - MySQL reports to SE that it just needs second column (f) for
>  evaluation.
>  This is done using bitmap (table->read_set).
>  (Call to rnd_next has table->read_set bitmap updated to
>  reflect selected column information)
(Continue reading)

Permalink | Reply | 1 comment |
headers
Alex Pilchin | 4 Jul 2007 21:58
Picon

write sets and read sets

Hi,
Could someone please tell me some of the ways one can extract the read
and write sets for a query or a transaction.   I understand using
Triggers might be one approach.  

What's the most efficient way to extract this information.  

Thanks,
Alex

--

-- 
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe:    http://lists.mysql.com/internals?unsub=gcdmd-internals <at> m.gmane.org
Permalink | Reply | 10 comments |
headers
Alex Pilchin | 4 Jul 2007 22:09
Picon

Checkpointing and Checkpointing hooks

Hi,
I need find a way to get notification that a checkpoint has occurred in
MySql.  Is this functionality available somewhere (eg. as a patch or
somewhere in the MySql code)? 

Also, it would be nice to know which transactions have been executed
after the last checkpoint. Is this at all possible w/o parsing and
scanning the log at the moment? 

Is there any capability at the moment to support user triggered
check-pointing? 

Thanks,
Alex

--

-- 
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe:    http://lists.mysql.com/internals?unsub=gcdmd-internals <at> m.gmane.org
Permalink | Reply | 4 comments |

Gmane