Does innodb buffer pool cache indexes and data in sub sets or in entirety?
I've heard people mention the buffer pool allocation is dependent on
the size of your tables and indexes.
Kyong
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
Another thing to keep in mind is to make sure all your foreign keys
are re-created if you have any. We had a similar accident in our
prod box a few years back and converting MyIsam to InnoDB won't
necessarily re-create the foreign keys.
Kyong
On Mon, Nov 22, 2010 at 6:39 AM, Johan De Meersman
I couldn't find much information on innodb_autoinc_lock_mode and
implications on mixed mode replication. Does the same caution for
innodb_autoinc_lock_mode=2 and statement-based replication apply to
mixed mode replication?
Kyong
--
MySQL General Mailing List
For list archives:
I've been going through the 5.1 manual and exploring the new features.
To add a secondary index to an existing table, InnoDB scans the table,
and sorts the rows using memory buffers and temporary files in order
by the value(s) of the secondary index key column(s). The B-tree is
then built in
Are there any known issues or challenges implementing MMM?
We're currently focused on MMM but just kinda wanted to keep our eyes open.
Kyong
On Thu, Jul 22, 2010 at 11:19 PM, Rob Wultsch wult...@gmail.com wrote:
On Thu, Jul 22, 2010 at 8:42 PM, Kyong Kim kykim...@gmail.com wrote:
Has anyone
Has anyone used this in production?
We're looking at this as part of our sharding/scale strategy and
wanted some insight into real world experience.
Are there alternatives out there?
Kyong
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
This isn't true for innodb.
I think the only requirement is that you need to have a unique index
on the auto increment column.
We created a composite primary key + auto_increment to take advantage
of clustering by primary key while satisfying unique constraint for
the primary key. It worked out
On Tue, Jun 8, 2010 at 10:57 PM, Machiel Richards machi...@rdc.co.za wrote:
Good morning all
I would like to try and find out how you can see what is using the
query cache.
My reason for asking is the following:
On one of our client databases, the
on the list of things to scrutinize.
Kyong
On Wed, Jun 9, 2010 at 12:12 PM, Johan De Meersman vegiv...@tuxera.be wrote:
On Wed, Jun 9, 2010 at 8:04 PM, Kyong Kim kykim...@gmail.com wrote:
If the memory is available, why not use it? It seems like the default
buffer pool size out of the box was just
I can see how having innodb_support_xa set to 1 can have write
performance impact due to additional flushes to disk.
Can this impact read performance as well?
Kyong
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
Also, if you have read heavy workload, you might want to try using and
tuning your query cache.
Start off with something like 32M and incrementally tune it.
You can monitor some query cache related server variables.
Kyong
On Sat, Apr 10, 2010 at 4:28 PM, Rob Wultsch wult...@gmail.com wrote:
On
Yeah. One of the telltale signs of something amiss is excessive swap activity.
You're not going to be happy with the performance when the swap space
is actually in use heavily.
Kyong
On Tue, Apr 13, 2010 at 8:15 PM, Dan Nelson dnel...@allantgroup.com wrote:
In the last episode (Apr 13), Joe
We've seen good results throwing more RAM to the buffer pool.
It is true that InnoDB data never gets accessed directly on disk.
The only downside I know of with a larger buffer pool is slower restarts.
The load speed depends on the order of the inserts.
Random inserts or updates to primary key
Also depends on your data access pattern as well.
If you can take advantage of clustering my primary key for your
selects, then InnoDB could do it for you.
My suggestion would be to write some queries based on projected
workload, build 2 tables with lots and lots of data, and do some
isolated
Also depends on your data access pattern as well.
If you can take advantage of clustering my primary key for your
selects, then InnoDB could do it for you.
My suggestion would be to write some queries based on projected
workload, build 2 tables with lots and lots of data, and do some
isolated
KEY UPDATE, I think this will solve
the problem with one statement.
Rodrigo Ferreira
--- On *Wed, 3/24/10, Johnny Withers joh...@pixelated.net* wrote:
From: Johnny Withers joh...@pixelated.net
Subject: Re: SELECT and INSERT if no row is returned
To: Kyong Kim kykim...@gmail.com
Cc: mysql
I need to INSERT a row into a table only if it does not exist to
insure that there won't be duplicate rows.
Due to the the usage case and the design of the primary key,
non-unique id + auto increment, I don't think insert ignore is an
option.
What would be simplest and cheapest way to make sure
I'm not positive if the general log captures all invalid queries but
it does capture at least some.
I was asked the same question a few months back and checking to make
sure that manually issued invalid queries are logged (IIRC).
Could it be that the queries are never even making it to the
On Tue, Jan 26, 2010 at 11:23 AM, Keith Murphy bmur...@paragon-cs.com wrote:
You absolutely *should not* convert the mysql database to InnoDB.
Read the above sentence again :)
All others, unless you had a specific reason not to do so, yes, I would
convert them.
keith
On Tue, Jan 26, 2010
I think you can use update replace.
UPDATE table SET column=REPLACE(column,'$','');
Kyong
On Thu, Nov 5, 2009 at 1:35 PM, Tim Legg kc0...@yahoo.com wrote:
Hello,
I am importing CSV data from a proprietary database into a table in MySQL.
Due to a flaw in the proprietary software's export
the documentation. Where on earth
did you learn to code like this? A one-liner at that, even on an 80-column
terminal.
Thank you very much!
Tim Legg
--- On Thu, 11/5/09, Kyong Kim kykim...@gmail.com wrote:
From: Kyong Kim kykim...@gmail.com
Subject: Re: Removing 1st character of string for all entries
Raj,
Yup. It's that bug.
I got the row size to below 8K and the insertion takes place fine.
Thanks for pointing me in the right direction.
Kyong
On Wed, Oct 14, 2009 at 10:31 AM, Raj Shekhar rajl...@rajshekhar.net wrote:
Kyong Kim kykimdba at gmail.com writes:
For sure all of our columns
We have an InnoDB table on MySQL 5.0.
We recently encountered an this error during a multirow insert(200 rows).
We identified the data causing it and it's a a series of long strings
exceeding the VARCHAR(255) columns into which they're being inserted.
I've been looking at the InnoDB restriction
Is the status information correct?
mysql Ver 14.12 Distrib 5.0.27, for Win32 (ia32)
Are you using some sort of vm?
Kyong
On Mon, Sep 21, 2009 at 12:23 PM, Lawrence Robertson
lawrobert...@yahoo.com wrote:
Hi.
We have some MySql servers in a circular replication, and one of the servers
is
Be careful about burying list type of data in a column.
I've seen poor performance issues parsing lists and XML type data
buried in columns.
A lot depends on your application and how and what you need to query
from those lists.
I've seen a case where a submitted documents were stored in a column
A) You would probably want to populate the Article.Article_Type column
with Article_Type.ID. You probably wouldn't need Article_Type table if
you're going to store Article_Type value directly.
I would also consider the use of natural primary key vs surrogate
primary key. We've seen good results
Michael,
We're counting on batch inserts of constant 2 leftmost columns of the
primary key.
We would be selecting within constant values for the leftmost columns as well.
For example, our primary key is
country_id, city_id, auto_inc, ...
We'll always be looking for data from within the same
We have a multi-column primary key with an auto-increment column as
the 3rd column in the primary key in InnoDB.
Is there a requirement to have the auto-increment column as the
leftmost column in the primary key in order for InnoDB to cluster by
the multi-column primary key?
I don't believe this
Michael,
Yeah. We're trying to maximize the benefits of clustering and had to
sacrifice on the length of the primary key.
And we got fairly good results from query profiling using maatkit.
One thing that shocked me was the overhead of random inserts primary
key updates.
It's definitely a tradeoff.
We have a composite primary key consisting of
column a, column b, column c. We don't have a lot of variation on
column a and it makes sense for us to cluster by a.
Our queries are
SELECT column c FROM table WHERE column a=something and column e=something.
By creating a composite secondary index on
It's often said that NOT NULL column is preferable in terms of index
performance.
I was wondering exactly why and how this is so specifically to InnoDB.
It would be great if someone can shed light on this matter in some detail.
Kyong
--
MySQL General Mailing List
For list archives:
users permissions?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=kimky...@fhda.edu
Kyong Kim
Instructional Multimedia/Web Programmer
Foothill College
12345 El Monte Rd
3601
Los Altos Hills, CA 94022
650-949-7091
table.
Any insight would be much appreciated.
Kyong
kimky...@fhda.edu (Kyong Kim) writes:
I was wondering about a scale out problem.
Lets say you have a large table with 3 cols and 500+ million rows.
Would there be much benefit in splitting the columns into different
tables
based on INT type
That's why you really need to be more precise in the data structures
you are planning on using. This can change the results significantly.
So no, I don't have any specific answers to your questions as you don't
provide any specific information in what you ask.
Yeah. Let me see if I can
I was wondering about a scale out problem.
Lets say you have a large table with 3 cols and 500+ million rows.
Would there be much benefit in splitting the columns into different tables
based on INT type primary keys across the tables? The split tables will be
hosted on a same physical instance
=(
list of time stamps).
Clearly above query accepts only one item in the list. Is there a way to
do
this for multiple items in the list ??? I can't think of anything at this
moment.
Thanks,
-Abhi
--
Barney Boisvert
bboisv...@gmail.com
http://www.barneyb.com/
Kyong Kim
://lists.mysql.com/mysql?unsub=kimky...@fhda.edu
Kyong Kim
Instructional Multimedia/Web Programmer
Foothill College
12345 El Monte Rd
3601
Los Altos Hills, CA 94022
650-949-7091
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com
://lists.mysql.com/mysql?unsub=kimky...@fhda.edu
Kyong Kim
Instructional Multimedia/Web Programmer
Foothill College
12345 El Monte Rd
3601
Los Altos Hills, CA 94022
650-949-7091
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com
38 matches
Mail list logo