;
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Sunday, November 25, 2007 10:03 PM
To: Edoardo Serra
Cc: mysql@lists.mysql.com
Subject: Re: MyISAM vs InnoDB - Index choice and Huge performance difference
just want to take a note on 4Gbytes
What
Tnx for your interest
# uname -a
Linux corona 2.6.18-5-amd64 #1 SMP Thu May 31 23:51:05 UTC 2007 x86_64
GNU/Linux
64 bit shouldn't have problems in using 4gb of ram .. right ?
[EMAIL PROTECTED] ha scritto:
just want to take a note on 4Gbytes
What kernel u use?
4Gbytes or bigger means
: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Sunday, November 25, 2007 10:03 PM
To: Edoardo Serra
Cc: mysql@lists.mysql.com
Subject: Re: MyISAM vs InnoDB - Index choice and Huge performance difference
just want to take a note on 4Gbytes
What kernel u use?
4Gbytes or bigger means nothing
-30 23:59:59'
)
GROUP BY day, disposition;
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Sunday, November 25, 2007 10:03 PM
To: Edoardo Serra
Cc: mysql@lists.mysql.com
Subject: Re: MyISAM vs InnoDB - Index choice and Huge performance difference
just want
Hi everybody,
I have a MySQL database with MyISAM tables.
As we're experiencing a lot of locking-related problems I decided to
migrate to InnoDB.
Our database is composed by a lot of small tables (1.000 - 10.000 rows)
and a huge table containing 7.000.000 rows, this big table is a
just want to take a note on 4Gbytes
What kernel u use?
4Gbytes or bigger means nothing on your MySQL, because if your kernel
is not compiled using correct patch or simply use CentOS/RHEL, then
your MySQl will limited to use up to 2Gbytes only, so 4Gbytes --
2Gbytes is useless
On 11/25/07,
U might want to try seting you index to calldate, disposition
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Sunday, November 25, 2007 10:03 PM
To: Edoardo Serra
Cc: mysql@lists.mysql.com
Subject: Re: MyISAM vs InnoDB - Index choice and Huge performance
Hi! Comments inline.
Edoardo Serra wrote:
SELECT sum(usercost) FROM cdr WHERE calldate BETWEEN '2007-06-01
00:00:00' AND '2007-06-30 23:59:59'
If I run it on the MyISAM table, MySQL choose the right index (the one
on the calldate column) and the query is fast enough
If I run it on the
Hi guys,
I'm moving a database to InnoDB because I need some transaction related
features but I'm having big problems with perrformances.
I have a big table with 5mln rows on which I need to run some SELECTs.
It's the Call Detail Record of a telco, so each record has a 'calldate'
field with
On 11/6/06, Leandro Guimarães Faria Corcete DUTRA wrote:
Em Thu, 02 Nov 2006 10:22:18 -0800, Jochem van Dieten escreveu:
PostgreSQL supports 2 phase commit. IIRC except for transaction
interleaving, join and suspend/resume it supports XA. I think that puts it
about on par with Ingres and
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 7 Nov 2006, at 12:35, Jochem van Dieten wrote:
On 11/6/06, Leandro Guimarães Faria Corcete DUTRA wrote:
Em Thu, 02 Nov 2006 10:22:18 -0800, Jochem van Dieten escreveu:
PostgreSQL supports 2 phase commit. IIRC except for transaction
Em Fri, 03 Nov 2006 09:18:21 +0100, Martijn Tonies escreveu:
On two-phase commits? I guess it's the IB 6 docs where you have to read
that, or get a copy of Helen Borries Firebird book. Get a copy of the
IBPhoenix CD that includes docs.
The Firebird project itself has no full documentation
Em Thu, 02 Nov 2006 10:22:18 -0800, Jochem van Dieten escreveu:
PostgreSQL supports 2 phase commit. IIRC except for transaction
interleaving, join and suspend/resume it supports XA. I think that puts it
about on par with Ingres and Firebird.
I would have to analyze better, but I think
On two-phase commits? I guess it's the IB 6 docs where you have to read
that, or get a copy of Helen Borries Firebird book. Get a copy of the
IBPhoenix CD that includes docs.
The Firebird project itself has no full documentation yet - it's being
worked on.
Hm, do you mean 2PC are
InterBase had two-phase commits ages ago, Firebird inherited it.
If there's anything specific you want to know, ask
I *am* asking — where is the specific piece of documentation?
On two-phase commits? I guess it's the IB 6 docs where you have
to read that, or get a copy of Helen Borries
Em Wed, 01 Nov 2006 09:34:05 -0600, mos escreveu:
At 05:56 AM 11/1/2006, Leandro Guimarães Faria Corcete DUTRA wrote:
Always use a DBMS, and MySQL is no (proper) DBMS without a
transactional backend. There are InnoDB, which is not completely free (needs
a proprietary backup tool);
Always use a DBMS, and MySQL is no (proper) DBMS without a
transactional backend. There are InnoDB, which is not completely free
(needs
a proprietary backup tool); BDB, which is deprecated until further
notices;
and SolidDB, which is still β.
Ok, so your solution is to use
At 08:32 AM 11/2/2006, you wrote:
Always use a DBMS, and MySQL is no (proper) DBMS without a
transactional backend. There are InnoDB, which is not completely free
(needs
a proprietary backup tool); BDB, which is deprecated until further
notices;
and SolidDB, which is still β.
Is there a better open source database out there for that amount of
data?
Several. MySQLâ?Ts own MaxDB, PostgreSQL, Firebird if you are
into
Borland stuff, Ingres if you need XA distributed transactions.
Firebird isn't Borland :-)
I usually recommend PostgreSQL, or Ingres if
Em Thu, 02 Nov 2006 15:32:06 +0100, Martijn Tonies escreveu:
Several. MySQL’s own MaxDB, PostgreSQL, Firebird if you are into
Borland stuff, Ingres if you need XA distributed transactions.
Firebird isn't Borland
Granted. But it is (even more) attractive if you are already a
Several. MySQL’s own MaxDB, PostgreSQL, Firebird if you are into
Borland stuff, Ingres if you need XA distributed transactions.
Firebird isn't Borland
Granted. But it is (even more) attractive if you are already a Borland
shop.
I usually recommend PostgreSQL, or Ingres if
On 11/2/06, Leandro Guimarães Faria Corcete DUTRA wrote:
Em Wed, 01 Nov 2006 09:34:05 -0600, mos escreveu:
Is there a better open source database out there for that amount of data?
Several. MySQL's own MaxDB, PostgreSQL, Firebird if you are into
Borland stuff, Ingres if you need XA
Em Thu, 02 Nov 2006 17:30:14 +0100, Martijn Tonies escreveu:
Falcon has a transactional storage engine, including Foreign
Keys (Jim wouldn't do a database without em)
Obviouſly.
MGA
Ma ze?
--
Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel)
Administrador
Em Thu, 02 Nov 2006 17:40:44 +0100, Martijn Tonies escreveu:
InterBase had two-phase commits ages ago, Firebird inherited it.
If there's anything specific you want to know, ask
I *am* asking — where is the specific piece of documentation?
Because if you don’t read MySQL’s
Em Tue, 31 Oct 2006 15:24:44 -0500, Francis escreveu:
MyISAM vs InnoDB ? What is the best to use
Always use a DBMS, and MySQL is no (proper) DBMS without a transactional
backend. There are InnoDB, which is not completely free (needs a proprietary
backup tool); BDB, which is deprecated
At 07:56 AM 11/1/2006, Leandro Guimarães Faria Corcete DUTRA wrote:
snip .. further notices; and SolidDB, which
is still β.
Choose your evil.
--
Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel)
Administrador de (Bases de) Dados +55 (11) 2122 0302 (com)
Miles Thompson [EMAIL PROTECTED] wrote:
At 07:56 AM 11/1/2006, Leandro Guimarães Faria Corcete DUTRA wrote:
snip .. further notices; and SolidDB, which
is still β.
Help this poor English-speaker - what's the symbol you use to describe
SolidDB?
I assume it is a beta character, since
On Nov 1, 2006, at 12:56 PM, Leandro Guimarães Faria Corcete DUTRA
wrote:
Em Tue, 31 Oct 2006 15:24:44 -0500, Francis escreveu:
MyISAM vs InnoDB ? What is the best to use
Always use a DBMS, and MySQL is no (proper) DBMS without a
transactional
backend. There are InnoDB, which
At 05:56 AM 11/1/2006, Leandro Guimarães Faria Corcete DUTRA wrote:
Em Tue, 31 Oct 2006 15:24:44 -0500, Francis escreveu:
MyISAM vs InnoDB ? What is the best to use
Always use a DBMS, and MySQL is no (proper) DBMS without a
transactional
backend. There are InnoDB, which
MyISAM vs InnoDB ? What is the best to use
Always use a DBMS, and MySQL is no (proper) DBMS without a
transactional
backend. There are InnoDB, which is not completely free (needs a
proprietary
backup tool); BDB, which is deprecated until further notices; and SolidDB,
which
is still Î
Francis wrote:
Question about MyISAM vs InnoDB ? What is the best to use, I have
a large table contain around 10 millons of records. What is the best
for me ? Use MyISAM or InnoDB ?
Depends VERY much on your application. If any concurrency and/or
durability is required then I would
Francis wrote:
Question about MyISAM vs InnoDB ? What is the best to use, I have
a large table contain around 10 millons of records. What is the best
for me ? Use MyISAM or InnoDB ?
Depends VERY much on your application. If any concurrency and/or
durability is required then I would
At 09:35 AM 11/1/2006, Martijn Tonies wrote:
MyISAM vs InnoDB ? What is the best to use
Always use a DBMS, and MySQL is no (proper) DBMS without a
transactional
backend. There are InnoDB, which is not completely free (needs a
proprietary
backup tool); BDB, which is deprecated until
On 11/1/06, mos wrote:
Sure, I've thought of those too. But has anyone gotten Firebird to
store 700-800gb tables? Can you split Firebird's .gdb file across drives?
The main problem with tables of that size is maintaining the index. My
upper limit for MySQL is 100 million rows. After
At 02:27 PM 11/1/2006, Jochem van Dieten wrote:
On 11/1/06, mos wrote:
Sure, I've thought of those too. But has anyone gotten Firebird to
store 700-800gb tables? Can you split Firebird's .gdb file across drives?
The main problem with tables of that size is maintaining the index. My
On 11/1/06, mos wrote:
At 02:27 PM 11/1/2006, Jochem van Dieten wrote:
What is the big deal of a TB? Now, if you get past 20 TB you might
want to team up with one of the commercial PostgreSQL supporters
(Fujitsu, EnterpriseDB, Greenplum etc.), but Sun even sells appliances
for 100 TB
Hi list,
Question about MyISAM vs InnoDB ? What is the best to use, I have a
large table contain around 10 millons of records. What is the best for me ?
Use MyISAM or InnoDB ?
Ty for reply ☺
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
/storage-engines.html
Thanks,
Jimmy Guerrero
MySQL, Inc
-Original Message-
From: Francis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 31, 2006 2:25 PM
To: mysql@lists.mysql.com
Subject: MyISAM vs InnoDB
Hi list,
Question about MyISAM vs InnoDB ? What is the best
Hello.
innodb_log_file_size=10M
innodb_log_buffer_size=1M
These variables have too small values, increase them. Follow
other recomendations from:
http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html
Andrew stolarz [EMAIL PROTECTED] wrote:
hello, here are my current
Hello List,
When I do a bulk import into a MyIsam engine database, I can reach about 2-3
thousand records imported per second.
However when I use the InnoDB engine, I am only importing about 30-50
records per second?
Am I missing something here?
its a P4 3 Ghz machine with 1024mb ram. running
Hello.
Without seeing at least your configuration it is difficult to say
what's going on. Please, provide your config file.
Andrew stolarz [EMAIL PROTECTED] wrote:
When I do a bulk import into a MyIsam engine database, I can reach about 2-3
thousand records imported per second.
hello, here are my current setttings:
# MySQL Server Instance Configuration File
# --
# Generated by the MySQL Server Instance Configuration Wizard
#
#
# Installation Instructions
#
Is your database connection auto-commit? MyISAM commits everything at
once, where InnoDB you can commit whenever you want. You might want to
commit at the end of your batch.
Also, look at your indexes - indexes make selects fast, but slow down
inserts and deletes, and can slow down updates
Hello.
Usually only benchmarks will show a real picture
for you. Create foreign keys, perform some tests. Then
temporary disable FKs using SET FOREIGN_KEY_CHECKS=0 and
repeat the performance measurement. Super Smack is a good
tool for such kind of analysis. See:
Here's some thing I've been thinking about.
I want to use INNODB without FKs. I don't need or want referential integrity
in my app (due to a schema and performance issue).
Basically I just create FKs in my OR layer and my app enforces the rules.
The column is still an _ID column so I visually
We have a table containing just one column that we use for
unique IDs:
CREATE TABLE `id_sequence` (
`id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
) TYPE=MyISAM
Watching 'SHOW FULL PROCESSLIST' and reading the slow query
log shows the occasional backlog of locks. Has
Eamon Daly [EMAIL PROTECTED] wrote on 08/24/2005 12:40:55 PM:
We have a table containing just one column that we use for
unique IDs:
CREATE TABLE `id_sequence` (
`id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
) TYPE=MyISAM
Watching 'SHOW FULL PROCESSLIST' and
, 2005 12:05 PM
Subject: Re: MyISAM vs. InnoDB for an AUTO_INCREMENT counter table
Eamon Daly [EMAIL PROTECTED] wrote on 08/24/2005 12:40:55 PM:
We have a table containing just one column that we use for
unique IDs:
CREATE TABLE `id_sequence` (
`id` int(10) unsigned NOT NULL auto_increment
: Re: MyISAM vs. InnoDB for an AUTO_INCREMENT counter table
Eamon Daly [EMAIL PROTECTED] wrote on 08/24/2005 12:40:55
PM:
We have a table containing just one column that we use for
unique IDs:
CREATE TABLE `id_sequence` (
`id` int(10) unsigned NOT NULL auto_increment,
PRIMARY
Hello.
If you have lots of concurrent updates and selects on the
same table, InnoDB usually has better performance. Use the
benchmarks to determine what configuration is preferred.
Super-smack for example allows you to write very flexible tests.
Be aware of different behavior of
Eamon Daly wrote:
We have a table containing just one column that we use for
unique IDs:
CREATE TABLE `id_sequence` (
`id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
) TYPE=MyISAM
Watching 'SHOW FULL PROCESSLIST' and reading the slow query
log shows the occasional backlog
Hello.
mysql Ver 14.3 Distrib 4.1.1-alpha, for pc-linux (i686)
You have an old MySQL version which contains lots of bugs (it's
an alpha!). I strongly recommend you to upgrade to the latest release
(4.1.12 now) and use official binaries.
Hi,
In a table of 20,000 records I am
Praveen KS [EMAIL PROTECTED] writes:
In a table of 20,000 records I am frequented with this error:
Error 1034:
Incorrect key file for table: ''; try to repair it
Frequency of this error: Three or four times a week.
I am logging the data it was trying to insert or update. After I,
Hi,
In a table of 20,000 records I am frequented with this error:
Error 1034:
Incorrect key file for table: ''; try to repair it
Frequency of this error: Three or four times a week.
I am logging the data it was trying to insert or update. After I,
repair table tablename
if, I try to
At 06:37 PM 12/20/2004, you wrote:
I'm new to MySQL and I was wondering which storage
engine is the best choice for heavily-indexed,
read-mostly data.
From skimming over the documentation, it seems that
MyISAM is a better choice since it doesn't have the
transactional overhead. Yet I'm worried
Thanks Mike for the information. Yes, Emmett mentioned
the same thing in a private message, and it seems that
MyISAM is exactly what I'm looking for: a
heavily-indexed large table that will be also indexed
for full-text search and built off-line -- no updates
whatsoever.
However, I will be
Homam S.A. wrote:
I'm new to MySQL and I was wondering which storage
engine is the best choice for heavily-indexed,
read-mostly data.
From skimming over the documentation, it seems that
MyISAM is a better choice since it doesn't have the
transactional overhead. Yet I'm worried that it's
becoming
At 04:00 PM 12/21/2004, Homam S.A. wrote:
Thanks Mike for the information. Yes, Emmett mentioned
the same thing in a private message, and it seems that
MyISAM is exactly what I'm looking for: a
heavily-indexed large table that will be also indexed
for full-text search and built off-line -- no
Thanks Mike. I think testing ultimately determines how
efficient heterogeneous engine joins are. I just
wanted to know if someone had issues with them in a
heavy-load environment.
--- mos [EMAIL PROTECTED] wrote:
At 04:00 PM 12/21/2004, Homam S.A. wrote:
Thanks Mike for the information. Yes,
I'm new to MySQL and I was wondering which storage
engine is the best choice for heavily-indexed,
read-mostly data.
From skimming over the documentation, it seems that
MyISAM is a better choice since it doesn't have the
transactional overhead. Yet I'm worried that it's
becoming depricated and
I thought that only InnoDB tables could be joined -
and only if they had foreign keys. But it sounds like
any kind of table can be joined, and it doesn't need a
foreign key.
Can someone explain InnoDB, MyISAM and foreign keys in
plain English? If I understand correctly, foreign keys
simply help
I thought that only InnoDB tables could be joined -
and only if they had foreign keys. But it sounds like
any kind of table can be joined, and it doesn't need a
foreign key.
The ability to join a bunch of tables in a query is different from foreign
keys. A foreign key is a relationhip
On Thu, 13 May 2004 10:34:37 -0700 (PDT)
David Blomstrom [EMAIL PROTECTED] wrote:
I thought that only InnoDB tables could be joined -
and only if they had foreign keys. But it sounds like
any kind of table can be joined, and it doesn't need a
foreign key.
Exactly, you can do a join with any
A quick question for the hardcore MySQL experts out there.
I have a simple table;
---
ID varchar (PK)
DATA longblob
---
This table is a simple persistence cache for one of our servers. It
regularly INSERTs and SELECTs into this table data of approximately 2KB
-
Alan Williamson wrote:
A quick question for the hardcore MySQL experts out there.
I have a simple table;
---
ID varchar (PK)
DATA longblob
---
This table is a simple persistence cache for one of our servers. It
regularly INSERTs and SELECTs into this table data
Thanks for that Chris, interesting thoughts.
For clarification, there is *NO* UPDATEs running on this table. Not a
single one! :) Many more SELECTs than INSERTs
Chris Nolan wrote:
Alan Williamson wrote:
A quick question for the hardcore MySQL experts out there.
I have a simple table;
Hi Alan,
Thanks for that Chris, interesting thoughts.
For clarification, there is *NO* UPDATEs running on this table. Not a
single one! :) Many more SELECTs than INSERTs
If you value your data, and these INSERTs are part of
a multi-insert batch of related data, go with the table-type
that
Hi all,
I'm using mysql 4.0.17 and I have this problem with speed of innodb
database:
I have simple command like select count(*) from table1, or select field1,
field2 from table1. The table1 and has more than cca 10.000 rows (most of
the fields are integer, only several varchars and several
I think count(*) is a special case: MyISAM holds a record count which it
can access instantly, InnoDB has to count rows. Does the time difference
persist for real queries?
Alec
Ji Matjka [EMAIL PROTECTED] wrote on 18/02/2004 10:38:13:
Hi all,
I'm using mysql 4.0.17 and I have
COUNT(*) is a special case for MyISAM. However, you'll find that
anything that has a WHERE clause that takes advantage of an index is
pretty quick for both MyISAM and InnoDB tables.
For instance:
SELECT COUNT(*) FROM table;
Is slow as all buggery on InnoDB, but:
SELECT COUNT(id) FROM table
Unfortunately it persists also for real queries. Eg. query like select
field1 from table1 where field3=xx and InnoDB is cca 10times slower than
MyISAM.
I wonder whether there is not some error or problem in my.ini settings, I
use following settings:
innodb_additional_mem_pool_size 1048576
Now I found one more strange thing - if I use show tables to get table
properties, then if table is MyISAM the number of rows is correct and if it
is InnoDB number of rows is around 2000 lower... And the innodb table looks
8 times bigger than myisam table (field data_length in show table status
From: Jiri Matejka [EMAIL PROTECTED]
Now I found one more strange thing - if I use show tables to get table
properties, then if table is MyISAM the number of rows is correct and if
it
is InnoDB number of rows is around 2000 lower... And the innodb table
looks
8 times bigger than myisam table
Jiri Matejka [EMAIL PROTECTED] wrote:
Now I found one more strange thing - if I use show tables to get table
properties, then if table is MyISAM the number of rows is correct and if it
is InnoDB number of rows is around 2000 lower... And the innodb table looks
8 times bigger than myisam table
Is it just not the case that InnoDB table have to do more as they have more
functionality and so take more time?
Simon
- Original Message -
From: Ji Matjka [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, February 18, 2004 10:38 AM
Subject: Speed of MyISAM vs. InnoDB
Hi all
PROTECTED]
Sent: Wednesday, February 18, 2004 10:38 AM
Subject: Speed of MyISAM vs. InnoDB
Hi all,
I'm using mysql 4.0.17 and I have this problem with speed of innodb
database:
I have simple command like select count(*) from table1, or select field1,
field2 from table1. The table1 and has more than cca
It is my understanding that at least through 4.0.14, INNODB does not
support using autoincrement on the last field in a multi field primary
key.
i.e. if a table has a primary key of three fields like
cpny_ID, acct_ID, list_ID
in MYISAM you can add the autoincrement attribute to
Can anyone either summarize for me a comparison between the MyISAM and InnoDB MySQL
table type?
I am getting ready to upgrade from MySQL 3.23.42 to 4.0.13 in the coming week and
started reading the upgrade documents on the www.mysql.com site. Never had even
thought about using another table
78 matches
Mail list logo
