|
Home > Archive > MySQL ODBC Connector > September 2005 > MyISAM to InnoDB
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| Hey all,
I've got a production database that made up of all MyISAM tables. I'd
like to change some of the more heavily written to tables to InnoDB to
take advantage of the record level locking and thus improve write
performance of our applications.
I currently have a second db server that is replicating from the current
production system but not in production yet. I'd like to try to convert
it to InnoDB. MySQL version is 4.0.16. It it as symple as just issuing
the modify table query or are there problems I should be aware of when
doing this?
Also are there known problems replicating from A -> B -> A (circular
replication) when A had Table1= InnoDB and B has Table1=MyISAM?
Thanks,
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Bruce Dembecki 2005-09-22, 11:23 am |
| You will need to make sure you have innodb configured in the my.cnf
file and you have enough space built for it in the shared table
space. InnoDB also needs it's own memory pool, so make sure you give
it enough memory. For day to day issues there is no problem doing
innodb/myisam replication, with a couple of small caveats... an
"ALTER TABLE" would replicate and thus... may change the table type
from myisam to innodb or vice versa depending on which server the
ALTER TABLE came from. To go with that the original conversion from
myisam to InnoDB would also need to be done in such a way as to not
be replicated.
Remember that an ALTER TABLE that could have an impact could be as
simple as adding or dropping an index... although usually very simple
alter table statements like that can be done without defining the
table engine, some GUIs may however insert that for you on even the
simplest ALTER TABLE commands.
Best Regards, Bruce
On Sep 22, 2005, at 7:59 AM, Jeff wrote:
> Hey all,
>
> I've got a production database that made up of all MyISAM tables. I'd
> like to change some of the more heavily written to tables to InnoDB to
> take advantage of the record level locking and thus improve write
> performance of our applications.
>
> I currently have a second db server that is replicating from the
> current
> production system but not in production yet. I'd like to try to
> convert
> it to InnoDB. MySQL version is 4.0.16. It it as symple as just
> issuing
> the modify table query or are there problems I should be aware of when
> doing this?
>
> Also are there known problems replicating from A -> B -> A (circular
> replication) when A had Table1= InnoDB and B has Table1=MyISAM?
>
> Thanks,
>
> Jeff
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?
> unsub=bruce@liveworl
d.com
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
| > -----Original Message-----
> From: Bruce Dembecki & #91;mailto:bruce@liv
eworld.com]
> Sent: Thursday, September 22, 2005 11:41
> To: Jeff
> Cc: mysql@lists.mysql.com
> Subject: Re: MyISAM to InnoDB
>
>
> You will need to make sure you have innodb configured in the my.cnf
> file and you have enough space built for it in the shared table
> space. InnoDB also needs it's own memory pool, so make sure you give
> it enough memory. For day to day issues there is no problem doing
> innodb/myisam replication, with a couple of small caveats... an
> "ALTER TABLE" would replicate and thus... may change the table type
> from myisam to innodb or vice versa depending on which server the
> ALTER TABLE came from. To go with that the original conversion from
> myisam to InnoDB would also need to be done in such a way as to not
> be replicated.
>
True, is there a way to tell a slave to not replicate certain queries
like alter table or would I need to get creative and stop replication
and all writes to the main database, then issue the alter table
statement, then restart replication with a set global
slave_sql_skip_count
er=1 so that it skips the alter statemtent?
>
> Remember that an ALTER TABLE that could have an impact could be as
> simple as adding or dropping an index... although usually
> very simple
> alter table statements like that can be done without defining the
> table engine, some GUIs may however insert that for you on even the
> simplest ALTER TABLE commands.
>
If I understand what you're saying here, some MySQL front end gui
software will add onto any "Alter table" statement you submit a
statement specifying the type of table like myisam automatically. So if
you used that gui and tried to issue an alter statement to say add an
index to a InnoDB table it would add on a table type = MyISAM and cause
havoc?
Normally I don't rely on gui tools to do my serious quiries like
altering tables or adding indexes etc. I'll do them logging directly
into mysql server on the linux box itself. In this case there shouldn't
be a problem correct?
> Best Regards, Bruce
>
> On Sep 22, 2005, at 7:59 AM, Jeff wrote:
>
> tables. I'd
> to InnoDB to
[color=darkred]
> aware of when
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Bruce Dembecki 2005-09-22, 8:24 pm |
|
On Sep 22, 2005, at 11:46 AM, Jeff wrote:
>
> True, is there a way to tell a slave to not replicate certain queries
> like alter table or would I need to get creative and stop replication
> and all writes to the main database, then issue the alter table
> statement, then restart replication with a set global
> slave_sql_skip_count
er=1 so that it skips the alter statemtent?
>
>
On a single connection use:
SET SQL_LOG_BIN = 0
It's a connection variable, the default is 1, which means queries
altering the data get written to the bin log... changing this to 0
means data altering commands from this specific connection do not get
written to the binary log... It's best not to leave a connection
lying around with this setting because it's the sort of thing you
forget about and later end up with data inconsistencies. However
short term use by turning it off, doing your thing, and turning it on
again usually works without trouble... eg:
SET SQL_LOG_BIN = 0;
ALTER TABLE ....some stuff here....;
SET SQL_LOG_BIN = 1;
Not all users have permission to issue such a command.
> If I understand what you're saying here, some MySQL front end gui
> software will add onto any "Alter table" statement you submit a
> statement specifying the type of table like myisam automatically.
> So if
> you used that gui and tried to issue an alter statement to say add an
> index to a InnoDB table it would add on a table type = MyISAM and
> cause
> havoc?
>
> Normally I don't rely on gui tools to do my serious quiries like
> altering tables or adding indexes etc. I'll do them logging directly
> into mysql server on the linux box itself. In this case there
> shouldn't
> be a problem correct?
>
>
Some GUI's take simple steps and write them out into their full long
SQL format... whereas adding a table's engine or type to an alter
table is optional in MySQL, officially it is suppose to be there...
so some GUI's put it there... typically if you haven't told it to
change the table type it will just use whatever table type it is
now... but the end result in the binary log will still go to the
other server and potentially change something there.
There shouldn't be a problem using the mysql command line client...
but I'm going to emphasize "shouldn't" here... when you have two
different table types on master and slave you need to be **really**
sure you don't mess that up.
Best Regards, Bruce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
|
|
| > -----Original Message-----
> From: Devananda & #91;mailto:karnah805
@yahoo.com]
> Sent: Thursday, September 22, 2005 16:14
> To: Jeff
> Cc: mysql@lists.mysql.com
> Subject: Re: MyISAM to InnoDB
>
>
> Jeff wrote:
> certain queries
> replication
>
> There's a much easier way - issue the statement "SET
> SQL_LOG_BIN = 0;"
> before issuing any ALTER TABLE statements. This will cause all
> statements for the duration of that session to not be written to the
> binlog. See http://dev.mysql.com/doc/mysql/en/set-option.html
> for more
> information.
>
First off, thanks for the help to you and Bruce both!
When you say here, "for the duration of that session" does that mean
that only queries I issue with my connection skip the binlog? Or do all
queries during that time skip the binlog. In other words, when I SET
SQL_LOG_BIN = 0; should I first stop all applications writing to the
database to prevent missing data in the slaves?
>
> You may want to look at a few pages in the docs, for
> information about
> InnoDB / MyISAM differences. If your code relies on one table
> type (or
> features only available with that table type, like transactions for
> InnoDB or "SELECT COUNT(*)" for MyISAM), you may run into
> some problems.
> Here are a couple links to try to help.
> http://dev.mysql.com/doc/mysql/en/i...sql-replication
..html
http://dev.mysql.com/doc/mysql/en/c...-to-innodb.html
http://dev.mysql.com/doc/mysql/en/i...ent-column.html
http://dev.mysql.com/doc/mysql/en/r...n-features.html (towards the
bottom it talks about replication of transactions and MyISAM engine)
Best Regards,
Devananda vdv
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Devananda 2005-09-22, 8:24 pm |
| Jeff wrote:
>
>
> First off, thanks for the help to you and Bruce both!
>
You're quite welcome, Jeff :)
> When you say here, "for the duration of that session" does that mean
> that only queries I issue with my connection skip the binlog? Or do all
> queries during that time skip the binlog. In other words, when I SET
> SQL_LOG_BIN = 0; should I first stop all applications writing to the
> database to prevent missing data in the slaves?
>
It only affects that connection. Bruce wrote a response at about the
same time I did; his covers this topic as well. SQL_LOG_BIN is a session
variable, meaning that it only affects the current session (connection).
So, any applications running at the same time will not be affected by a
change to this variable, and if you close your client and reconnect, you
will have to set the variable again. As Bruce suggested, it's best to
set it only when you need it and unset it immediately afterwards (as a
precaution against operator error, not because it affects the server).
I do want to point out that while the commands you issue (after setting
SQL_LOG_BIN to 0) will not be written to the binlog (thus will not run
on any slave reading from this server), they may affect other running
processes on the server. If, for example, you run an ALTER TABLE on a
table currently in MyISAM format, the table will be locked and all
processes running on that server that read from / write to that table
will wait until that ALTER finishes. Setting SQL_LOG_BIN to 0 doesn't
affect this in any way - it _only_ affects whether statements from that
specific session are recorded in the binary log.
Side question - you've stated that you are planning to migrate to
InnoDB, but you haven't said anything to the list about how much data
you have. Just be aware that it can take a lot of time and disk space
for MySQL to transfer all your data from one format to the other (of
course depending on how much data you have) and if anything goes wrong
during that time, the results will probably not be what you expect, or
want. I would advise you to at least investigate an alternate approach
if you have a lot of data - take the server you are going to migrate out
of the 'cluster' and make sure it is not processing any data / no
clients are connecting to it; dump all your data to text files,
preferably separating your data definition statements (ie CREATE TABLE
statements) from your actual data; modify the CREATE statements to
specify the InnoDB engine; lastly load all the data from the text files
into MySQL, and bring this server back into the 'cluster'.
If you don't have a _lot_ of data, then it may not be worth all that
work. Of course, "a lot" is subjective; I'd say, based purely on my own
experiences with this, that if you are going to migrate 1G of data, you
will probably be better off exporting / alter the text files /
importing. If you have 10's or 100's of G of data, I would strongly
recommend that you do it this way. And regardless of how much data you
have, it is, IMHO, safer to export/import. If you're interested, I would
be happy to talk more about a method to automate this process over many
tables / lots of data.
Best Regards,
Devananda vdv
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
| > -----Original Message-----
> From: Devananda & #91;mailto:karnah805
@yahoo.com]
> Sent: Thursday, September 22, 2005 19:03
> To: Jeff
> Cc: mysql@lists.mysql.com
> Subject: Re: MyISAM to InnoDB
>
>
> Jeff wrote:
> written to the
> You're quite welcome, Jeff :)
>
> that mean
> binlog? Or do
> words, when I
> writing to
>
>
> It only affects that connection. Bruce wrote a response at about the
> same time I did; his covers this topic as well. SQL_LOG_BIN
> is a session
> variable, meaning that it only affects the current session
> (connection).
> So, any applications running at the same time will not be
> affected by a
> change to this variable, and if you close your client and
> reconnect, you
> will have to set the variable again. As Bruce suggested, it's best to
> set it only when you need it and unset it immediately
> afterwards (as a
> precaution against operator error, not because it affects the server).
>
> I do want to point out that while the commands you issue
> (after setting
> SQL_LOG_BIN to 0) will not be written to the binlog (thus
> will not run
> on any slave reading from this server), they may affect other running
> processes on the server. If, for example, you run an ALTER TABLE on a
> table currently in MyISAM format, the table will be locked and all
> processes running on that server that read from / write to that table
> will wait until that ALTER finishes. Setting SQL_LOG_BIN to 0 doesn't
> affect this in any way - it _only_ affects whether statements
> from that
> specific session are recorded in the binary log.
>
Thanks, that answer my question regarding SQL_LOG_BIN varialbe.
> Side question - you've stated that you are planning to migrate to
> InnoDB, but you haven't said anything to the list about how much data
> you have. Just be aware that it can take a lot of time and disk space
> for MySQL to transfer all your data from one format to the other (of
> course depending on how much data you have) and if anything
> goes wrong
> during that time, the results will probably not be what you
> expect, or
> want. I would advise you to at least investigate an alternate
> approach
> if you have a lot of data - take the server you are going to
> migrate out
> of the 'cluster' and make sure it is not processing any data / no
> clients are connecting to it; dump all your data to text files,
> preferably separating your data definition statements (ie
> CREATE TABLE
> statements) from your actual data; modify the CREATE statements to
> specify the InnoDB engine; lastly load all the data from the
> text files
> into MySQL, and bring this server back into the 'cluster'.
>
> If you don't have a _lot_ of data, then it may not be worth all that
> work. Of course, "a lot" is subjective; I'd say, based purely
> on my own
> experiences with this, that if you are going to migrate 1G of
> data, you
> will probably be better off exporting / alter the text files /
> importing. If you have 10's or 100's of G of data, I would strongly
> recommend that you do it this way. And regardless of how much
> data you
> have, it is, IMHO, safer to export/import.
Well like you say a "lot" of data is subjective. Our situation is this;
we currently have a DB01 up and running and in production. We're moving
to a more redundant data center and have purchased new hardware to
migrate the database to (new server will be DB03). The speed of the
database directly affects our profitability. This being the case I've
suggested to our developers that we take the opertunity to migrate the
tables involved in heavy write actions from MyISAM to InnoDB on the new
DB03 server before we put it into production. Currently I have DB03 up
and running (all MyISAM) at the new datacenter and doing circular
replication with DB01 over VPN. No apps or users are currently writing
or even reading from DB03 yet.
Even if we migrate selected tables in DB03 to InnoDB I'd like to keep
the downstream (one way) replicated servers using MyISAM as most of the
activity on those is read only.
As for the size of the tables in question, they are currently;
2gig Table1.MYD
200M Table1.MYI
14k Table1.frm
422k Table2.MYD
114k Table2.MYI
11k Table2.frm
Our lead developer here has made the statement, "why not just convert
the entire db to InnoDB for the sake of simplicity and ease?" As far as
I'm concerned, simplicity and ease would be to leave it as is but we're
looking to eek out as much speed in our transactions as possible so
simplicity, ease and performance don't always go together.
If I understand what your suggesting above I should:
1) Stop all writes/read to the table (just replication at this point)
2) Issue a "show create table" statement for Table1 and Table2, save
these results and modify the engine type to InnoDB for later use.
3) Dump all data from Table1 and Table2 to text files.
4) Setup the db for InnoDB use (add variables to my.cnf file as needed
to support InnoDB).
5) Restart the database (make sure replication doesn't start)
6) Issue a SQL_LOG_BIN=0
7) Drop the old Table1 and Table2
8) Use the previousely created "create table" queries to rebuild Table1
and Table2 in InnoDB
9) Re-import the data from the text files into the new Table1 and Table2
10) Issue a SQL_LOG_BIN=1
11) Restart replication.
If you're
> interested, I would
> be happy to talk more about a method to automate this process
> over many
> tables / lots of data.
>
>
> Best Regards,
> Devananda vdv
>
I'd be very interested to discuss that. Even if I don't use it in this
particular situation, knowledge never hurts! ;o)
Thanks!
jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| SGreen@unimin.com 2005-09-23, 9:23 am |
| --=_alternative 004AB64085257085_=
Content-Type: text/plain; charset="US-ASCII"
Sorry to butt in but I wanted to make sure you didn't do actually do what
you proposed to do, yet. More responses interspersed...
"Jeff" <jsmforum@optonline.net> wrote on 09/23/2005 08:32:57 AM:
>
> Thanks, that answer my question regarding SQL_LOG_BIN varialbe.
>
>
> Well like you say a "lot" of data is subjective. Our situation is this;
> we currently have a DB01 up and running and in production. We're moving
> to a more redundant data center and have purchased new hardware to
> migrate the database to (new server will be DB03). The speed of the
> database directly affects our profitability. This being the case I've
> suggested to our developers that we take the opertunity to migrate the
> tables involved in heavy write actions from MyISAM to InnoDB on the new
> DB03 server before we put it into production. Currently I have DB03 up
> and running (all MyISAM) at the new datacenter and doing circular
> replication with DB01 over VPN. No apps or users are currently writing
> or even reading from DB03 yet.
>
> Even if we migrate selected tables in DB03 to InnoDB I'd like to keep
> the downstream (one way) replicated servers using MyISAM as most of the
> activity on those is read only.
>
> As for the size of the tables in question, they are currently;
>
> 2gig Table1.MYD
> 200M Table1.MYI
> 14k Table1.frm
>
> 422k Table2.MYD
> 114k Table2.MYI
> 11k Table2.frm
>
> Our lead developer here has made the statement, "why not just convert
> the entire db to InnoDB for the sake of simplicity and ease?"
The tables in the mysql database cannot be InnoDB. InnoDB does not yet
support fulltext searching. Other than those two major restrictions, a
full InnoDb setup is possible.
> As far as
> I'm concerned, simplicity and ease would be to leave it as is but we're
> looking to eek out as much speed in our transactions as possible so
> simplicity, ease and performance don't always go together.
>
> If I understand what your suggesting above I should:
>
> 1) Stop all writes/read to the table (just replication at this point)
STOP SLAVE should be all you need. You don't need to worry about reads
affecting either your data or your structures.
http://dev.mysql.com/doc/mysql/en/stop-slave.html
>
> 2) Issue a "show create table" statement for Table1 and Table2, save
> these results and modify the engine type to InnoDB for later use.
>
> 3) Dump all data from Table1 and Table2 to text files.
You are doing this more as a backup measure than as a step in the
conversion process.
>
> 4) Setup the db for InnoDB use (add variables to my.cnf file as needed
> to support InnoDB).
This is a great time for you to decide if you want to use
file-per-tablespace or the single tablespace model of InnoDB. There are
advantages to each. Pick which one works best for your situation.
>
> 5) Restart the database (make sure replication doesn't start)
>
> 6) Issue a SQL_LOG_BIN=0
Good, this means that the binlog won't get the next few commands.
>
> 7) Drop the old Table1 and Table2
>
> 8) Use the previousely created "create table" queries to rebuild Table1
> and Table2 in InnoDB
>
> 9) Re-import the data from the text files into the new Table1 and Table2
Nope. That's overkill. All you need to do is to issue ALTER TABLE
statements where you change ENGINE=myISAM to ENGINE=InnoDB. Looks
something like this:
ALTER TABLE mytable1 ENGINE=InnoDB;
MySQL will take care of copying over the column definitions and moving the
data from one set of files (the MyISAM files) into the appropriate InnoDB
structures.
http://dev.mysql.com/doc/mysql/en/alter-table.html
http://dev.mysql.com/doc/mysql/en/create-table.html
>
> 10) Issue a SQL_LOG_BIN=1
>
> 11) Restart replication.
>
> If you're
>
>
> I'd be very interested to discuss that. Even if I don't use it in this
> particular situation, knowledge never hurts! ;o)
>
> Thanks!
>
> jeff
>
>
Yep, that sounds like it will work.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 004AB64085257085_=--
| |
|
| > -----Original Message-----
> From: SGreen@unimin.com & #91;mailto:SGreen@un
imin.com]
> Sent: Friday, September 23, 2005 09:40
> To: Jeff
> Cc: mysql@lists.mysql.com
> Subject: RE: MyISAM to InnoDB
>
>
> Sorry to butt in but I wanted to make sure you didn't do
> actually do what
> you proposed to do, yet. More responses interspersed...
>
Nope nothing yet, I don't rush things when I'm unsure... ;o)
> "Jeff" <jsmforum@optonline.net> wrote on 09/23/2005 08:32:57 AM:
>
> alter table
> SQL_LOG_BIN =
> will cause all
> at about the
> it's best to
> the server).
> other running
> ALTER TABLE on a
> locked and all
> to that table
> to 0 doesn't
> how much data
> and disk space
> the other (of
> statements to
> worth all that
> would strongly
> This being
> opertunity
> MyISAM to
> VPN. No apps
> like to keep
> just convert
>
> The tables in the mysql database cannot be InnoDB. InnoDB
> does not yet
> support fulltext searching. Other than those two major
> restrictions, a
> full InnoDb setup is possible.
>
Shawn,
What do you mean by "The tables in the mysql database cannot be InnoDB?"
You kinda lost me there...
> as possible
> this point)
>
> STOP SLAVE should be all you need. You don't need to worry
> about reads
> affecting either your data or your structures.
> http://dev.mysql.com/doc/mysql/en/stop-slave.html
>
> Table2, save
>
> You are doing this more as a backup measure than as a step in the
> conversion process.
Yes, you are correct but it was suggesed by Devananda that this was a
"safer" approach.
I'm in no rush so safer is better.
>
> file as needed
>
> This is a great time for you to decide if you want to use
> file-per-tablespace or the single tablespace model of InnoDB.
> There are
> advantages to each. Pick which one works best for your situation.
Actually it's not a choice right now as the MySQL version running on
these servers is 4.0.16 which I believe doesn't support single
tablespace. I do plan on moving to that however after upgrades as it
seems a more easily managed InnoDB setup. I suspect backups of a InnoDB
db with a single tablespace model would be easier as well?
>
> Good, this means that the binlog won't get the next few commands.
>
>
> Nope. That's overkill. All you need to do is to issue ALTER TABLE
> statements where you change ENGINE=myISAM to ENGINE=InnoDB. Looks
> something like this:
>
> ALTER TABLE mytable1 ENGINE=InnoDB;
>
> MySQL will take care of copying over the column definitions
> and moving the
> data from one set of files (the MyISAM files) into the
> appropriate InnoDB
> structures.
> http://dev.mysql.com/doc/mysql/en/alter-table.html
> http://dev.mysql.com/doc/mysql/en/create-table.html
>
> it in this
>
> Yep, that sounds like it will work.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| SGreen@unimin.com 2005-09-23, 11:23 am |
| --=_alternative 004EE5B785257085_=
Content-Type: text/plain; charset="US-ASCII"
"Jeff" <jsmforum@optonline.net> wrote on 09/23/2005 09:57:06 AM:
>
> Nope nothing yet, I don't rush things when I'm unsure... ;o)
>
>
> Shawn,
>
> What do you mean by "The tables in the mysql database cannot be InnoDB?"
> You kinda lost me there...
Yes, I meant exactly that. Within each MySQL server is a "special"
database called `mysql`. That is the database that contains the tables of
all of the user login and permission information for the server (and
several other important bits of system-wide metadata). None of the tables
in that database can be converted to InnoDB. That would be a "bad thing".
The tables of every OTHER database on the server (including yours) are
eligible for InnoDB conversion so long as you do not want to use fulltext
searching. If you need a FT index, you have to keep that table as MyISAM
for now (they are working on enabling FT indexes in InnoDB but there is no
release date yet)
>
>
> Yes, you are correct but it was suggesed by Devananda that this was a
> "safer" approach.
> I'm in no rush so safer is better.
Yes, it is safer because you have a backup. Should the auto-conversion
fail, you have an extra method of recovery. If you decide to move from
4.0 to 4.1 or 5.x, this is definitely the method you should use. "Going to
text" is the most portable means of moving data between versions and will
avoid any version-to-version incompatibilities.
Remember to tell mysqldump how big the max_allowed_packet setting will be
on the destination server. If you do not, it can (and will) create
extended insert statement too big to be processed as you attempt to
restore from the dump file. Trust me, I know :-)
I still believe that because your are only performing a table type
conversion, this step acts more as a safety net than it plays a role as
part of the conversion process.
>
>
> Actually it's not a choice right now as the MySQL version running on
> these servers is 4.0.16 which I believe doesn't support single
> tablespace. I do plan on moving to that however after upgrades as it
> seems a more easily managed InnoDB setup. I suspect backups of a InnoDB
> db with a single tablespace model would be easier as well?
I realize I may have said it backwards. The newer option (4.1+) is to have
InnoDB create a separate tablespace per each table (
http://dev.mysql.com/doc/mysql/en/m...ablespaces.html). I think you
understood what I meant, though, based on your comment.
>
(I meant to apply the next comment to all 3 steps above (7, 8, and 9), not
just to the last)
[color=darkred]
>
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 004EE5B785257085_=--
| |
|
| > -----Original Message-----
> From: SGreen@unimin.com & #91;mailto:SGreen@un
imin.com]
> Sent: Friday, September 23, 2005 10:25
> To: Jeff
> Cc: mysql@lists.mysql.com
> Subject: RE: MyISAM to InnoDB
>
>
> "Jeff" <jsmforum@optonline.net> wrote on 09/23/2005 09:57:06 AM:
>
> alter statemtent?
> http://dev.mysql.com/doc/mysql/en/set-option.html
> issue (after
> to migrate
> any data / no
> text files,
> text files /
> situation is
> production.
> purchased new
> DB03). The
> MyISAM as most
>
> Yes, I meant exactly that. Within each MySQL server is a "special"
> database called `mysql`. That is the database that contains
> the tables of
> all of the user login and permission information for the server (and
> several other important bits of system-wide metadata). None
> of the tables
> in that database can be converted to InnoDB. That would be a
> "bad thing".
>
Yep, I knew that ;o)
> The tables of every OTHER database on the server (including
> yours) are
> eligible for InnoDB conversion so long as you do not want to
> use fulltext
> searching. If you need a FT index, you have to keep that
> table as MyISAM
> for now (they are working on enabling FT indexes in InnoDB
> but there is no
> release date yet)
>
Is that the only diff (other than the "select count(*)" thing) between
InnoDB and MyISAM? Aren't select statements faster from MyISAM tables
than from InnoDB's?
There's also been a statement from our lead developer that having a db
with mixed tables (some InnoDB and some MyISAM) will make life harder on
them because it makes development of application more difficult. I do
quite a bit of php and some perl programming that interacts with MySQL
and I can't think of any major problems created by a mixed engine type
environment. Sure "select count(*) from table" won't be as fast in an
InnoDB table and obviously full text indexes won't be there but other
than that, I don't see this as causing any "difficulties" for a
programmer. I'm also under the belief that it's not the world's job to
make the programmer's life easier, it's the programmers job to make the
world's life easier.
Does anyone have any input on that?
<<<<<Truncated thread, getting too big>>>>>>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| SGreen@unimin.com 2005-09-23, 11:23 am |
| --=_alternative 0058E4C785257085_=
Content-Type: text/plain; charset="US-ASCII"
"Jeff" <jsmforum@optonline.net> wrote on 09/23/2005 11:36:01 AM:
<<rest of thread snipped>>
>
> Is that the only diff (other than the "select count(*)" thing) between
> InnoDB and MyISAM? Aren't select statements faster from MyISAM tables
> than from InnoDB's?
>
> There's also been a statement from our lead developer that having a db
> with mixed tables (some InnoDB and some MyISAM) will make life harder on
> them because it makes development of application more difficult. I do
> quite a bit of php and some perl programming that interacts with MySQL
> and I can't think of any major problems created by a mixed engine type
> environment. Sure "select count(*) from table" won't be as fast in an
> InnoDB table and obviously full text indexes won't be there but other
> than that, I don't see this as causing any "difficulties" for a
> programmer. I'm also under the belief that it's not the world's job to
> make the programmer's life easier, it's the programmers job to make the
> world's life easier.
>
> Does anyone have any input on that?
>
> <<<<<Truncated thread, getting too big>>>>>>
>
>
I would think that life would become easier on the developers because now,
in order to provide transactional security, they will no longer need to
issue LOCK TABLE and UNLOCK TABLE statements but rather START TRANSACTION
and either COMMIT or ROLLBACK statements. Much friendlier from the
developer's standpoint. I guess if you were using a mixed-mode table
(part of one record is held in an InnoDB table while the fields that
needed FT indexes were in a MyISAM table) that would be a bit harder to
work with but the overall performance gains and the stability provided by
the transactional structure should outweigh any developmental overhead.
The data structure should be relatively independent from the application
design. Your data needs to be stored in a manner that is both logically
correct and efficient to access and maintain. The application needs to
work with that design, not the other way around. It's only when the
database design is so complex that practical factors (like memory size or
a really large or complex join) begin to limit the speed of certain SQL
statements that you need to consider compromising between a theoretically
correct design and performance.
For instance, it is possible to normalize a data structure to the point
that it becomes slower to manage than one that is slightly denormalized.
However, start from theory and work backwards. Break an optimal design
only if it creates a significant or required performance increase. Most of
the times, you can gain performance by small shifts in the application
layer (use two smaller queries instead of one larger, more complex one,
use equality matching rather than LIKE,...) or by tuning your index
structures (watch the slow query log and look for patterns of unindexed
column usage; create indexes to fit, consider building covering indexes
for some of your most frequently executed query patterns,...).
IMHO, a good logical data design will promote better code design and will
enhance overall performance by improving the performance of your data
persistence layer. I think your developers need to have their code
reviewed if switching to InnoDB is going to be a big hassle for them. A
complaint like that sends up a red flag for me. I have to wonder what are
they currently doing that isn't going to be supported by the new
format....
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 0058E4C785257085_=--
| |
| Devananda 2005-09-23, 1:23 pm |
| I see that the thread is getting too long, so I'm cutting out parts that
I'm not responding to. Hope no one minds.
MySQL allocates memory to MyISAM and InnoDB separately, so if you want
to 'eek out' as much performance as possible from the InnoDB side of
things, you will probably want to reduce the amount of memory allocated
to MyISAM (on that server). For this reason, I would recommend changing
all tables (except those in the 'mysql' database, as Shawn explained) to
InnoDB, and essentially dedicating that server to InnoDB. You do have to
leave some memory for MyISAM (8M is enough if your only tables in MyISAM
are the ones in the 'mysql' database).
The configuration settings which determine memory allocation are still
somewhat confusing to me (I'm sure others on the list have a clearer
understanding than I), but the main ones are key_buffer_size for MyISAM
and innodb_buffer_pool_s
ize for InnoDB.
links to the docs:
http://dev.mysql.com/doc/mysql/en/s...-variables.html
http://dev.mysql.com/doc/mysql/en/innodb-start.html
[color=darkred]
> Is that the only diff (other than the "select count(*)" thing) between
> InnoDB and MyISAM? Aren't select statements faster from MyISAM tables
> than from InnoDB's?
There are cases when each one performs better than the other. I'll go
over a couple examples from my own experiences... If your table is being
written to very frequently, then InnoDB will yield faster reads because
of table locking restrictions on MyISAM tables. And, if you have
commonly repeated queries, turning on (or turning up) the query cache
will have a more noticeable difference on speed than anything else.
However, for very large tables that are primarily read from (without
repeating the same questions such that they could be cached), I do
believe MyISAM will give better performance.
>
> There's also been a statement from our lead developer that having a db
> with mixed tables (some InnoDB and some MyISAM) will make life harder on
> them because it makes development of application more difficult. I do
> quite a bit of php and some perl programming that interacts with MySQL
> and I can't think of any major problems created by a mixed engine type
> environment. Sure "select count(*) from table" won't be as fast in an
> InnoDB table and obviously full text indexes won't be there but other
> than that, I don't see this as causing any "difficulties" for a
> programmer. I'm also under the belief that it's not the world's job to
> make the programmer's life easier, it's the programmers job to make the
> world's life easier.
>
> Does anyone have any input on that?
If your developers need to use transactions to modify multiple tables at
once, then all those tables must be in InnoDB for the transaction to
work. However, besides the few points already mentioned, it really is
transparent to the programmers what storage engine you use. I'd ask them
how they think it will make life "harder on them"; chances are they are
basing this on misconceptions.
While I was writing this, I see that Shawn has responded to this
particular question with a lot more clarity than I would have, so I'll
just agree with what he wrote and stop here :)
Regards,
Devananda vdv
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
| > Cut orignal thread because it was too long
Ok so I'm about to convert two tables in my database from MyISAM to
InnoDB. They are currently:
14K Sep 15 13:15 Table1.frm
2.1G Sep 28 14:15 Table1.MYD
198M Sep 28 14:15 Table1.MYI
11K Sep 20 08:45 Table2.frm
424K Sep 28 14:15 Table2.MYD
110K Sep 28 14:15 Table2.MYI
The system is only used as a database server, it's a dual processor
system with 2gig of ram.
As you can see, Table1's MyISAM data file is quite large at 2.1 gig.
Taking this into account what size InnoDB data files should I configure
in my my.cnf file?
I was thinking of this:
My.cnf
<snip>
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin
server-id=70
port = 3306
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_s
ize = 64M
thread_cache = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
set-variable= max_connections=500
### InnoDB setup ###
# use default data directory for database
innodb_data_home_dir
= /DATA/dbdata/
innodb_data_file_pat
h =
/ibdata/ibdata1:2G;/ibdata/ ibdata2:50M:autoexte
nd:max:2G
innodb_log_group_hom
e_dir = /DATA/dbdata/ibdata/iblogs
innodb_buffer_pool_s
ize = 1G
innodb_additional_me
m_pool_size = 20M
innodb_log_files_in_
group = 3
innodb_log_file_size
= 500M
innodb_log_buffer_si
ze = 8M
innodb_buffer_pool_s
ize = 1.5G
innodb_additional_me
m_pool_size = 2M
innodb_file_io_threa
ds = 4
</snip>
But what happens if the ibdata2 fills up to the max of 2G?
I've got 50 gig available on the partition where the db data is stored.
Is there anything else here that looks incorrect?
Thanks,
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Sujay Koduri 2005-09-28, 11:23 am |
|
If you think your storage requiremnets will increase in future, try to
estimate how much you will be needing in the future in the worst case and
try allocating that much of disk space now itself (Any way you have good
amount of disk space left).
Try creating a different partition for storing the log files. This will
increase the performance
Even if you don't do this and run out of space, you just have to add more
add data files and a restart the server.
And for 2G RAM, its better to limit the innodb_bufferpool_si
ze to 1G. You
can also look at the query_cache_size parameter and try tuning that by
running some load tests.
Apart from that everything is looking fine for me
sujay
-----Original Message-----
From: Jeff & #91;mailto:jsmforum@
optonline.net]
Sent: Wednesday, September 28, 2005 8:22 PM
To: mysql@lists.mysql.com
Cc: SGreen@unimin.com
Subject: RE: MyISAM to InnoDB
> Cut orignal thread because it was too long
Ok so I'm about to convert two tables in my database from MyISAM to InnoDB.
They are currently:
14K Sep 15 13:15 Table1.frm
2.1G Sep 28 14:15 Table1.MYD
198M Sep 28 14:15 Table1.MYI
11K Sep 20 08:45 Table2.frm
424K Sep 28 14:15 Table2.MYD
110K Sep 28 14:15 Table2.MYI
The system is only used as a database server, it's a dual processor system
with 2gig of ram.
As you can see, Table1's MyISAM data file is quite large at 2.1 gig.
Taking this into account what size InnoDB data files should I configure in
my my.cnf file?
I was thinking of this:
My.cnf
<snip>
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin
server-id=70
port = 3306
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_s
ize = 64M
thread_cache = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency thread_concurrency = 4
set-variable= max_connections=500
### InnoDB setup ###
# use default data directory for database innodb_data_home_dir
=
/DATA/dbdata/ innodb_data_file_pat
h =
/ibdata/ibdata1:2G;/ibdata/ ibdata2:50M:autoexte
nd:max:2G
innodb_log_group_hom
e_dir = /DATA/dbdata/ibdata/iblogs
innodb_buffer_pool_s
ize = 1G
innodb_additional_me
m_pool_size = 20M
innodb_log_files_in_
group = 3
innodb_log_file_size
= 500M
innodb_log_buffer_si
ze = 8M
innodb_buffer_pool_s
ize = 1.5G
innodb_additional_me
m_pool_size = 2M
innodb_file_io_threa
ds = 4
</snip>
But what happens if the ibdata2 fills up to the max of 2G?
I've got 50 gig available on the partition where the db data is stored.
Is there anything else here that looks incorrect?
Thanks,
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=sujayk@andale.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
| > -----Original Message-----
> From: Sujay Koduri & #91;mailto:SujayK@an
dale.com]
> Sent: Wednesday, September 28, 2005 11:04
> To: Jeff; mysql@lists.mysql.com
> Cc: SGreen@unimin.com
> Subject: RE: MyISAM to InnoDB
>
>
>
> If you think your storage requiremnets will increase in
> future, try to estimate how much you will be needing in the
> future in the worst case and try allocating that much of disk
> space now itself (Any way you have good amount of disk space left).
> Try creating a different partition for storing the log files.
> This will increase the performance
>
Well currently MySQL is set up in the default dir of /var/lib/mysql and
soft links to the database data residing on another partition
/DATA/<dbname>.
Should I maybe specify:
innodb_log_group_hom
e_dir = /var/lib/mysql/iblogs/
I have about 9 gig available on /var so 1.5 gig of logs shouldn't be too
bad.
> Even if you don't do this and run out of space, you just have
> to add more add data files and a restart the server.
>
> And for 2G RAM, its better to limit the
> innodb_bufferpool_si
ze to 1G. You can also look at the
> query_cache_size parameter and try tuning that by running
> some load tests.
>
> Apart from that everything is looking fine for me
>
> sujay
>
> -----Original Message-----
> From: Jeff & #91;mailto:jsmforum@
optonline.net]
> Sent: Wednesday, September 28, 2005 8:22 PM
> To: mysql@lists.mysql.com
> Cc: SGreen@unimin.com
> Subject: RE: MyISAM to InnoDB
>
>
> Ok so I'm about to convert two tables in my database from
> MyISAM to InnoDB. They are currently:
>
> 14K Sep 15 13:15 Table1.frm
> 2.1G Sep 28 14:15 Table1.MYD
> 198M Sep 28 14:15 Table1.MYI
>
> 11K Sep 20 08:45 Table2.frm
> 424K Sep 28 14:15 Table2.MYD
> 110K Sep 28 14:15 Table2.MYI
>
> The system is only used as a database server, it's a dual
> processor system with 2gig of ram.
>
> As you can see, Table1's MyISAM data file is quite large at
> 2.1 gig. Taking this into account what size InnoDB data files
> should I configure in my my.cnf file?
>
> I was thinking of this:
>
> My.cnf
>
> <snip>
>
> [mysqld]
>
> datadir=/var/lib/mysql
> socket=/var/lib/mysql/mysql.sock
> log-bin
> server-id=70
> port = 3306
> skip-locking
> key_buffer = 384M
> max_allowed_packet = 1M
> table_cache = 512
> sort_buffer_size = 2M
> read_buffer_size = 2M
> myisam_sort_buffer_s
ize = 64M
> thread_cache = 8
> query_cache_size = 32M
> # Try number of CPU's*2 for thread_concurrency
> thread_concurrency = 4 set-variable= max_connections=500
>
> ### InnoDB setup ###
>
> # use default data directory for database
> innodb_data_home_dir
= /DATA/dbdata/
> innodb_data_file_pat
h =
/ibdata/ibdata1:2G;/ibdata/ ibdata2:50M:autoexte
nd:max:2G
> innodb_log_group_hom
e_dir = /DATA/dbdata/ibdata/iblogs
>
> innodb_buffer_pool_s
ize = 1G
> innodb_additional_me
m_pool_size = 20M
> innodb_log_files_in_
group = 3
> innodb_log_file_size
= 500M
> innodb_log_buffer_si
ze = 8M
> innodb_buffer_pool_s
ize = 1.5G
> innodb_additional_me
m_pool_size = 2M
> innodb_file_io_threa
ds = 4
>
> </snip>
>
> But what happens if the ibdata2 fills up to the max of 2G?
> I've got 50 gig available on the partition where the db data
> is stored.
>
> Is there anything else here that looks incorrect?
>
> Thanks,
>
> Jeff
>
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> unsub=sujayk@andale.com
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Sujay Koduri 2005-09-28, 11:23 am |
|
One more thing, noneed to give so much space for each logfile. The combined
size of all log files should be around 25-50% of innodb_buffer_pool size. So
you can reduce the size of each log file to 100M.
You can specify that for storing log files.
sujay
-----Original Message-----
From: Jeff & #91;mailto:jsmforum@
optonline.net]
Sent: Wednesday, September 28, 2005 9:04 PM
To: 'Sujay Koduri'; mysql@lists.mysql.com
Subject: RE: MyISAM to InnoDB
> -----Original Message-----
> From: Sujay Koduri & #91;mailto:SujayK@an
dale.com]
> Sent: Wednesday, September 28, 2005 11:04
> To: Jeff; mysql@lists.mysql.com
> Cc: SGreen@unimin.com
> Subject: RE: MyISAM to InnoDB
>
>
>
> If you think your storage requiremnets will increase in future, try to
> estimate how much you will be needing in the future in the worst case
> and try allocating that much of disk space now itself (Any way you
> have good amount of disk space left).
> Try creating a different partition for storing the log files.
> This will increase the performance
>
Well currently MySQL is set up in the default dir of /var/lib/mysql and
soft links to the database data residing on another partition
/DATA/<dbname>.
Should I maybe specify:
innodb_log_group_hom
e_dir = /var/lib/mysql/iblogs/
I have about 9 gig available on /var so 1.5 gig of logs shouldn't be too
bad.
> Even if you don't do this and run out of space, you just have
> to add more add data files and a restart the server.
>
> And for 2G RAM, its better to limit the
> innodb_bufferpool_si
ze to 1G. You can also look at the
> query_cache_size parameter and try tuning that by running
> some load tests.
>
> Apart from that everything is looking fine for me
>
> sujay
>
> -----Original Message-----
> From: Jeff & #91;mailto:jsmforum@
optonline.net]
> Sent: Wednesday, September 28, 2005 8:22 PM
> To: mysql@lists.mysql.com
> Cc: SGreen@unimin.com
> Subject: RE: MyISAM to InnoDB
>
>
> Ok so I'm about to convert two tables in my database from
> MyISAM to InnoDB. They are currently:
>
> 14K Sep 15 13:15 Table1.frm
> 2.1G Sep 28 14:15 Table1.MYD
> 198M Sep 28 14:15 Table1.MYI
>
> 11K Sep 20 08:45 Table2.frm
> 424K Sep 28 14:15 Table2.MYD
> 110K Sep 28 14:15 Table2.MYI
>
> The system is only used as a database server, it's a dual
> processor system with 2gig of ram.
>
> As you can see, Table1's MyISAM data file is quite large at
> 2.1 gig. Taking this into account what size InnoDB data files
> should I configure in my my.cnf file?
>
> I was thinking of this:
>
> My.cnf
>
> <snip>
>
> [mysqld]
>
> datadir=/var/lib/mysql
> socket=/var/lib/mysql/mysql.sock
> log-bin
> server-id=70
> port = 3306
> skip-locking
> key_buffer = 384M
> max_allowed_packet = 1M
> table_cache = 512
> sort_buffer_size = 2M
> read_buffer_size = 2M
> myisam_sort_buffer_s
ize = 64M
> thread_cache = 8
> query_cache_size = 32M
> # Try number of CPU's*2 for thread_concurrency
> thread_concurrency = 4 set-variable= max_connections=500
>
> ### InnoDB setup ###
>
> # use default data directory for database
> innodb_data_home_dir
= /DATA/dbdata/
> innodb_data_file_pat
h =
/ibdata/ibdata1:2G;/ibdata/ ibdata2:50M:autoexte
nd:max:2G
> innodb_log_group_hom
e_dir = /DATA/dbdata/ibdata/iblogs
>
> innodb_buffer_pool_s
ize = 1G
> innodb_additional_me
m_pool_size = 20M
> innodb_log_files_in_
group = 3
> innodb_log_file_size
= 500M
> innodb_log_buffer_si
ze = 8M
> innodb_buffer_pool_s
ize = 1.5G
> innodb_additional_me
m_pool_size = 2M
> innodb_file_io_threa
ds = 4
>
> </snip>
>
> But what happens if the ibdata2 fills up to the max of 2G?
> I've got 50 gig available on the partition where the db data
> is stored.
>
> Is there anything else here that looks incorrect?
>
> Thanks,
>
> Jeff
>
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> unsub=sujayk@andale.com
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Devananda 2005-09-28, 1:23 pm |
| Jeff wrote:
>
>
> The system is only used as a database server, it's a dual processor
> system with 2gig of ram.
>
> As you can see, Table1's MyISAM data file is quite large at 2.1 gig.
> Taking this into account what size InnoDB data files should I configure
> in my my.cnf file?
>
> I was thinking of this:
>
> My.cnf
>
> <snip>
>
> [mysqld]
>
> datadir=/var/lib/mysql
> socket=/var/lib/mysql/mysql.sock
> log-bin
> server-id=70
> port = 3306
> skip-locking
> key_buffer = 384M
> max_allowed_packet = 1M
> table_cache = 512
> sort_buffer_size = 2M
> read_buffer_size = 2M
> myisam_sort_buffer_s
ize = 64M
> thread_cache = 8
> query_cache_size = 32M
> # Try number of CPU's*2 for thread_concurrency
> thread_concurrency = 4
> set-variable= max_connections=500
>
> ### InnoDB setup ###
>
> # use default data directory for database
> innodb_data_home_dir
= /DATA/dbdata/
> innodb_data_file_pat
h =
> /ibdata/ibdata1:2G;/ibdata/ ibdata2:50M:autoexte
nd:max:2G
> innodb_log_group_hom
e_dir = /DATA/dbdata/ibdata/iblogs
>
> innodb_buffer_pool_s
ize = 1G
> innodb_additional_me
m_pool_size = 20M
> innodb_log_files_in_
group = 3
> innodb_log_file_size
= 500M
> innodb_log_buffer_si
ze = 8M
> innodb_buffer_pool_s
ize = 1.5G
((( duplicate setting, later-occurring one will take precedence )))
> innodb_additional_me
m_pool_size = 2M
> innodb_file_io_threa
ds = 4
>
> </snip>
>
> But what happens if the ibdata2 fills up to the max of 2G?
> I've got 50 gig available on the partition where the db data is stored.
>
> Is there anything else here that looks incorrect?
>
> Thanks,
>
> Jeff
>
I agree with what Sujay suggested: you can set the innodb_log_file_size
much smaller, and will get the same performance with better start-up
time. 100M x 3 log_files_in_group should be fine. Also I recommend
setting up your ibdata files large enough to anticipate need initially.
If, or once, they are full, you will not be able to write to tables in
InnoDB, so make sure that does not happen!
However, I see a potential problem - you said your system only has 2G
RAM. Here's the formula for how much RAM MySQL can (worst case) use,
taken from http://dev.mysql.com/doc/mysql/en/i...figuration.html
innodb_buffer_pool_s
ize
+ key_buffer_size
+ max_connections*(sor
t_buffer_size+read_b
uffer_size+binlog_ca
che_size)
+ max_connections*2MB
1024M ((( assuming you meant 1G and not 1.5G )))
+ 384M
+ 500 * (2M + 2M + ??)
+ 500 * 2M
According to your config, this results in a minimum of 1408M + 6M *
current_connections. That doesn't leave much RAM for the underlying OS
and any other processes running. And, far worse, if your application
servers attempted to establish more than 100 connections, MySQL could
not allocate enough memory for them, and would either crash or deny new
connections.
You need to adjust something in the formula - reduce max_connections if
that is possible, or reduce the key_buffer_size if you do not need to be
working with MyISAM tables on this server, or allocate less memory to
innodb_buffer_pool_s
ize.
Best Regards,
Devananda
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
| > -----Original Message-----
> From: Devananda & #91;mailto:karnah805
@yahoo.com]
> Sent: Wednesday, September 28, 2005 13:06
> To: Jeff
> Cc: mysql@lists.mysql.com
> Subject: Re: MyISAM to InnoDB
>
>
> Jeff wrote:
> 2.1 gig.
> thread_concurrency = 4
> ((( duplicate setting, later-occurring one will take precedence )))
> I've got 50
>
> I agree with what Sujay suggested: you can set the
> innodb_log_file_size
> much smaller, and will get the same performance with better start-up
> time. 100M x 3 log_files_in_group should be fine. Also I recommend
> setting up your ibdata files large enough to anticipate need
> initially.
> If, or once, they are full, you will not be able to write to
> tables in
> InnoDB, so make sure that does not happen!
>
> However, I see a potential problem - you said your system only has 2G
> RAM. Here's the formula for how much RAM MySQL can (worst case) use,
> taken from http://dev.mysql.com/doc/mysql/en/i...figuration.html
>
> innodb_buffer_pool_s
ize
> + key_buffer_size
> +
> max_connections*(sor
t_buffer_size+read_b
uffer_size+binlog_ca
che_size)
> + max_connections*2MB
>
> 1024M ((( assuming you meant 1G and not 1.5G )))
> + 384M
> + 500 * (2M + 2M + ??)
> + 500 * 2M
>
> According to your config, this results in a minimum of 1408M + 6M *
> current_connections. That doesn't leave much RAM for the
> underlying OS
> and any other processes running. And, far worse, if your application
> servers attempted to establish more than 100 connections, MySQL could
> not allocate enough memory for them, and would either crash
> or deny new
> connections.
>
> You need to adjust something in the formula - reduce
> max_connections if
> that is possible, or reduce the key_buffer_size if you do not
> need to be
> working with MyISAM tables on this server, or allocate less memory to
> innodb_buffer_pool_s
ize.
>
>
> Best Regards,
> Devananda
>
Since this server will have InnoDB and MyISAM tables, 100+ connections,
I'll need to reduce the innodb_buffer_pool_s
ize. I can possibly drop
the max_connections to 250 as well.
Thanks!
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
| > -----Original Message-----
> From: Devananda & #91;mailto:karnah805
@yahoo.com]
> Sent: Wednesday, September 28, 2005 13:06
> To: Jeff
> Cc: mysql@lists.mysql.com
> Subject: Re: MyISAM to InnoDB
>
>
> Jeff wrote:
> 2.1 gig.
> thread_concurrency = 4
> ((( duplicate setting, later-occurring one will take precedence )))
> I've got 50
>
> I agree with what Sujay suggested: you can set the
> innodb_log_file_size
> much smaller, and will get the same performance with better start-up
> time. 100M x 3 log_files_in_group should be fine. Also I recommend
> setting up your ibdata files large enough to anticipate need
> initially.
> If, or once, they are full, you will not be able to write to
> tables in
> InnoDB, so make sure that does not happen!
>
> However, I see a potential problem - you said your system only has 2G
> RAM. Here's the formula for how much RAM MySQL can (worst case) use,
> taken from http://dev.mysql.com/doc/mysql/en/i...figuration.html
>
> innodb_buffer_pool_s
ize
> + key_buffer_size
> +
> max_connections*(sor
t_buffer_size+read_b
uffer_size+binlog_ca
che_size)
> + max_connections*2MB
>
> 1024M ((( assuming you meant 1G and not 1.5G )))
> + 384M
> + 500 * (2M + 2M + ??)
> + 500 * 2M
>
> According to your config, this results in a minimum of 1408M + 6M *
> current_connections. That doesn't leave much RAM for the
> underlying OS
> and any other processes running. And, far worse, if your application
> servers attempted to establish more than 100 connections, MySQL could
> not allocate enough memory for them, and would either crash
> or deny new
> connections.
>
> You need to adjust something in the formula - reduce
> max_connections if
> that is possible, or reduce the key_buffer_size if you do not
> need to be
> working with MyISAM tables on this server, or allocate less memory to
> innodb_buffer_pool_s
ize.
>
>
> Best Regards,
> Devananda
>
Ugh...
mysqladmin -uroot -ptelaurus processlist | grep -c "Sleep"
And it returned 200 sleeping connections, all persistant connections
from our app servers and 4 threads_running
Also a show status gave me a max_used_connections
of 236.
If that's the case then I can probably only set it to about 250 which
means if I set my innodb_buffer_pool_s
ize = 100M and dropping my
key_buffer_size to 250, I'll need 1884M of ram according to the formula
above, which is dangerously close to the 2G limit specified in the
warning on the link above.
Currently the key_reads to Key_reads_requests is about 1:1970 with the
key_buffer_size of 384M, so I guess I can safely drop this to 250M
Even if I changed the entire DB over to InnoDB, and pushed the
key_buffer_size down really low it wouldn't drop the total memory usage
below 1600M.
So what is this telling me? I need more ram or less connections or I
should just stay with MyISAM?
Thanks,
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| SGreen@unimin.com 2005-09-28, 8:24 pm |
| --=_alternative 00680FBB8525708A_=
Content-Type: text/plain; charset="US-ASCII"
"Jeff" <jsmforum@optonline.net> wrote on 09/28/2005 02:24:48 PM:
>
> Ugh...
>
> mysqladmin -uroot -ptelaurus processlist | grep -c "Sleep"
>
> And it returned 200 sleeping connections, all persistant connections
> from our app servers and 4 threads_running
>
> Also a show status gave me a max_used_connections
of 236.
>
> If that's the case then I can probably only set it to about 250 which
> means if I set my innodb_buffer_pool_s
ize = 100M and dropping my
> key_buffer_size to 250, I'll need 1884M of ram according to the formula
> above, which is dangerously close to the 2G limit specified in the
> warning on the link above.
>
> Currently the key_reads to Key_reads_requests is about 1:1970 with the
> key_buffer_size of 384M, so I guess I can safely drop this to 250M
>
> Even if I changed the entire DB over to InnoDB, and pushed the
> key_buffer_size down really low it wouldn't drop the total memory usage
> below 1600M.
>
> So what is this telling me? I need more ram or less connections or I
> should just stay with MyISAM?
>
> Thanks,
>
> Jeff
>
>
236 concurrent connections is not that bad, however the fact that 200 of
them are currently sleeping made me wince.
During this changeover, I would also think about your application(s)
design(s). Can you somehow reduce the number of concurrent connections
from the application side? Do all of those connections really need to be
persistent? Making and breaking a MySQL connection is a much "lighter"
activity (faster, fewer resources) than doing the same thing with Oracle
or some other database servers. You might see improved performance if you
only make a connection when you need one and drop it as soon as your are
through.
For instance, many developers of most stand-alone (desktop) applications
(like Java or VB apps) and some web-based applications might think it to
be more efficient to open a connection once when the user starts the app
(logs into the site) and close it only as the app is shutting down (leaves
the site/logs out). Yes, it limits connection "churn" in the application
but if the majority of the time is spent waiting on user response (filling
in forms, clicking on buttons, analyzing data, etc) then you are not
really making full use of your database server's capacity, are you? Those
connections are really only necessary when the application needs to
communicate with the server and it may work better for you if you design
you app to connect only when needed. "Connect last - disconnect early" is
a design philosophy I frequently follow.
Now, if you have a block of code that does several separate database
actions in a row, DO NOT make and break connections between each action.
Make as few connections as you need at the beginning of the block and
close them at the end of the block. It's when the application is waiting
on the user that it is usually advantageous to be disconnected.
I know I made a fairly generalized statement. I also know that my advice
will not work better for all application designs. However, it is still a
design option to consider in order to optimize the availability of
database server-side resources.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 00680FBB8525708A_=--
| |
| Devananda 2005-09-28, 8:24 pm |
| Jeff wrote:
>
> Ugh...
>
> mysqladmin -uroot -ptelaurus processlist | grep -c "Sleep"
>
> And it returned 200 sleeping connections, all persistant connections
> from our app servers and 4 threads_running
>
> Also a show status gave me a max_used_connections
of 236.
>
> If that's the case then I can probably only set it to about 250 which
> means if I set my innodb_buffer_pool_s
ize = 100M and dropping my
> key_buffer_size to 250, I'll need 1884M of ram according to the formula
> above, which is dangerously close to the 2G limit specified in the
> warning on the link above.
>
> Currently the key_reads to Key_reads_requests is about 1:1970 with the
> key_buffer_size of 384M, so I guess I can safely drop this to 250M
>
> Even if I changed the entire DB over to InnoDB, and pushed the
> key_buffer_size down really low it wouldn't drop the total memory usage
> below 1600M.
>
> So what is this telling me? I need more ram or less connections or I
> should just stay with MyISAM?
>
> Thanks,
>
> Jeff
>
I would suggest taking a hard look at why your application servers are
creating 200 sleeping connections, and if that is necessary. You may
also be able to reduce sort_ and read_buffer_size to 1M each, but I
couldn't tell you how that might affect your application, so you may not
want to do that. (Does anyone on the list have experience modifying these?)
I think the biggest issue will be the system's RAM - the 2G limit on
MySQL's total allocated RAM is a per-process hard limit on 32-bit
architecture, but most 32-bit systems benefit greatly from having more
than 2G total RAM (the OS may use the rest for disk caching, etc). If,
say, your server had 4G RAM, then you could safely configure MySQL to
use very close to 2G, and performance should fly. With only 2G in the
system, setting MySQL to use as much RAM as possible would leave next to
nothing for the OS or other processes, and that is the problem (as I see
it).
However, that said, more RAM is not always the answer. You may get much
more of a performance increase by modifying your application code so
that it doesn't "waste" so many connections (thus allowing you to
allocate plenty of RAM to the innodb_buffer_pool).
Of course, you can do both (just to play it safe, right?). ;)
Best Regards,
Devananda vdv
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Bruce Dembecki 2005-09-29, 3:23 am |
| On Sep 28, 2005, at 5:21 PM, Devananda wrote:
> Jeff wrote:
> Lots of stuff goes in here...
So without going into specifics here... your sort_buffer and
read_buffer become pretty much unimportant if you move everything to
InnoDB... keeping in mind the earlier advice to leave the mysql
database intact in myisam. So if you went that route you can set them
to like.. 32k or something trivial and even the 250 x multiplier
doesn't hurt you much here. Once you do that of course you should
give InnoDB most of the memory.
On the disk side the one comment I didn't see is that if you have
2.2G of data already, you need 4.5G of InnoDB table space to put it
all together. InnoDB needs (as a rough guideline) twice as much table
space as there is data, so it can do things like indexes, and undo
logs and so on... Your 2 x 2G files isn't going to cut it as a place
to put your 2.2G of data. In your case for these guys I'd throw 3 x
2G files at it and a fourth auto extending file - more if you move
your other tables... most of our database instances had 20 x 2G files
in their InnoDB table space before we moved to innodb_file_per_tabl
e
where it stopped being an issue.
Best Regards, Bruce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
|
> Jeff wrote:
> connections
> 250 which
> specified in
> 1:1970 with the
> connections or I
>
> I would suggest taking a hard look at why your application
> servers are
> creating 200 sleeping connections, and if that is necessary. You may
> also be able to reduce sort_ and read_buffer_size to 1M each, but I
> couldn't tell you how that might affect your application, so
> you may not
> want to do that. (Does anyone on the list have experience
> modifying these?)
>
> I think the biggest issue will be the system's RAM - the 2G limit on
> MySQL's total allocated RAM is a per-process hard limit on 32-bit
> architecture, but most 32-bit systems benefit greatly from
> having more
> than 2G total RAM (the OS may use the rest for disk caching,
> etc). If,
> say, your server had 4G RAM, then you could safely configure MySQL to
> use very close to 2G, and performance should fly. With only 2G in the
> system, setting MySQL to use as much RAM as possible would
> leave next to
> nothing for the OS or other processes, and that is the
> problem (as I see
> it).
>
> However, that said, more RAM is not always the answer. You
> may get much
> more of a performance increase by modifying your application code so
> that it doesn't "waste" so many connections (thus allowing you to
> allocate plenty of RAM to the innodb_buffer_pool).
>
> Of course, you can do both (just to play it safe, right?). ;)
>
Well the applications with persistant connections is a touchy subject.
Our apps send and rec data over satelite links which are very expensive.
The shorter the duration of the link the less it costs us. So the
pervailing theory is that with persistant connections the apps will
spend less time re-connecting/dis-connecting from the db. Even
fractions of a second counts when you're talking about thousands of
connections a day and we are charged by the second for airtime. That's
the whole driving force behind wanting to switch over to InnoDB. The
thought is it would give us faster writes when we have a hundred apps
trying to write at or very near the same time because of the record
level locking as opposed to the MyISAM Table level locking during writes
and updates.
Now, the question is, if we need to drop the persistant connections in
order to move to an InnoDB engine, will the speed benefit of record
level locking outweigh what is lost by not having persistant
connections?
That being said and having just looked at our connections for the past
60 minutes during what is our roughly our peak time I only see about 350
which is roughly one every 10 seconds with a rough avg connection time
of about 28 seconds most of which is transfer of data and not db
read/write/updates. So, I believe, from that information I can make an
educated guess that the MyISAM table locking is not the real bottleneck
here and therefore it's probably not going to do us a lot of good to
switch to InnoDB, especially with our current hardware and application
behavior. Thoughts?
At some point however, as our traffic grows we probably will hit a point
where the db read/write/updates will start to become a bottleneck and
we'll need to look at moving to a 64bit arch, >2gig ram and the InnoDB
engine. What status variables should I be looking at to see if we have
a lot of read/write/updates being delayed?
Thanks,
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| SGreen@unimin.com 2005-09-29, 9:24 am |
| --=_alternative 004DD7F48525708B_=
Content-Type: text/plain; charset="US-ASCII"
"Jeff" <jsmforum@optonline.net> wrote on 09/29/2005 08:47:52 AM:
>
>
> Well the applications with persistant connections is a touchy subject.
> Our apps send and rec data over satelite links which are very expensive.
> The shorter the duration of the link the less it costs us. So the
> pervailing theory is that with persistant connections the apps will
> spend less time re-connecting/dis-connecting from the db. Even
> fractions of a second counts when you're talking about thousands of
> connections a day and we are charged by the second for airtime.
And all of those sleeping connections are costing you how much in unused
air time? Compared with many other databases, the cost (time and data) of
making and breaking a MySQL connection is cheap. Try a small set of test
cases and see for yourself. Maybe you could move 10 of your normal clients
from using your persistent connections into a connect-as-needed model and
see what that does to your air-time, sleeping connection counts, and total
throughput.
The only way to know for certain is to try it in your environment but I
know that in the world of web development (where connections are also
precious and throughput is king) that being connected only when necessary
usually works much better than trying to stay connected all of the time.
By minimizing the communications overhead imposed on the server by
maintaining unused open connections, the server should be able to respond
better. You should not only have less "dead air" but each connection
itself will take less time as the server will be more responsive.
Remember, I recommend making and breaking connections around blocks of
execution not per-statement. Let's say you have a "lookup" routine that
uses 6 queries and massages the data into something useful client-side. It
makes no sense to flip a connection 6 times for those 6 queries as they
are all part of one larger process. Prepare your SQL statements as much as
possible, make one connection, run the 6 queries, cache the results, drop
the connection, process the results from cache. Another trick to
maximizing connection usage is to make a few trips to the server as
necessary. Using the same scenario I just described, if 4 of those
queries did not contain data useful to the user but were used primarily to
build the results of the final 2 queries, you may be able to cache the
results of the first queries server-side, minimizing the # of frames sent
across your satellite link.
>That's
> the whole driving force behind wanting to switch over to InnoDB. The
> thought is it would give us faster writes when we have a hundred apps
> trying to write at or very near the same time because of the record
> level locking as opposed to the MyISAM Table level locking during writes
> and updates.
It sounds as though you do a lot of "burst" processing. Your client apps
collect information from the user then interacts with the database and
waits for more user input. It's that "dead air" time (waiting on the live
people to do something) that is costing you a small fortune in unused
connection time and consuming valuable server-side resources.
>
> Now, the question is, if we need to drop the persistant connections in
> order to move to an InnoDB engine, will the speed benefit of record
> level locking outweigh what is lost by not having persistant
> connections?
Dropping the persistent connections are not necessary to move to InnoDB. I
think many of us believe that 200 sleeping connections out of 236 total
are worrisome no matter which storage engine you are using.
>
> That being said and having just looked at our connections for the past
> 60 minutes during what is our roughly our peak time I only see about 350
> which is roughly one every 10 seconds with a rough avg connection time
> of about 28 seconds most of which is transfer of data and not db
> read/write/updates. So, I believe, from that information I can make an
> educated guess that the MyISAM table locking is not the real bottleneck
> here and therefore it's probably not going to do us a lot of good to
> switch to InnoDB, especially with our current hardware and application
> behavior. Thoughts?
The three primary benefits of InnoDB: Row level locking (more concurrency
for tables with heavy writes or updates), transactional support (for
action atomicity, data consistency, and process isolation), and foreign
keys (also consistency). If you need any of those features for your
application design, that would also be a compelling reason to change
engines.
>
> At some point however, as our traffic grows we probably will hit a point
> where the db read/write/updates will start to become a bottleneck and
> we'll need to look at moving to a 64bit arch, >2gig ram and the InnoDB
> engine. What status variables should I be looking at to see if we have
> a lot of read/write/updates being delayed?
>
>
> Thanks,
>
> Jeff
>
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 004DD7F48525708B_=--
| |
| Jeff McKeon 2005-09-29, 11:23 am |
| > "Jeff" <jsmforum@optonline.net> wrote on 09/29/2005 08:47:52 AM:
>=20
> "Sleep" And it=20
> dropping my
> this to 250M
> total memory=20
> necessary. You may=20
> each, but I=20
> 2G limit on
> configure MySQL to=20
> only 2G in the=20
> application code so=20
> touchy subject.=20
> costs us. =20
> connections the apps=20
> db. Even=20
>=20
> And all of those sleeping connections are costing you how=20
> much in unused=20
> air time?=20
I think there's a missunderstanding here. The applications run on
servers in our datacenter and wait for client connections to call in.
The "client" which is another server on the other end of the sat link,
transfers the data to our apps and our apps send data to it (depending
on whether or not data is waiting for it, one of the db queries tells
the local app this) and the local apps in turn write the connection
information to the database.
>Compared with many other databases, the cost (time=20
> and data) of=20
> making and breaking a MySQL connection is cheap. Try a small=20
> set of test=20
> cases and see for yourself. Maybe you could move 10 of your=20
> normal clients=20
> from using your persistent connections into a=20
> connect-as-needed model and=20
> see what that does to your air-time, sleeping connection=20
> counts, and total=20
> throughput.
>=20
> The only way to know for certain is to try it in your=20
> environment but I=20
> know that in the world of web development (where connections are also=20
> precious and throughput is king) that being connected only=20
> when necessary=20
> usually works much better than trying to stay connected all=20
> of the time.=20
> By minimizing the communications overhead imposed on the server by=20
> maintaining unused open connections, the server should be=20
> able to respond=20
> better. You should not only have less "dead air" but each connection=20
> itself will take less time as the server will be more responsive.
>=20
> Remember, I recommend making and breaking connections around=20
> blocks of=20
> execution not per-statement. Let's say you have a "lookup"=20
> routine that=20
> uses 6 queries and massages the data into something useful=20
> client-side. It=20
> makes no sense to flip a connection 6 times for those 6=20
> queries as they=20
> are all part of one larger process. Prepare your SQL=20
> statements as much as=20
> possible, make one connection, run the 6 queries, cache the=20
> results, drop=20
> the connection, process the results from cache. Another trick to=20
> maximizing connection usage is to make a few trips to the server as=20
> necessary. Using the same scenario I just described, if 4 of those=20
> queries did not contain data useful to the user but were used=20
> primarily to=20
> build the results of the final 2 queries, you may be able to=20
> cache the=20
> results of the first queries server-side, minimizing the # of=20
> frames sent=20
> across your satellite link.=20
>=20
Again, there are no "over the sat link" queries going on here. The
local apps do all the db work. If data files come in from the remote
systems, the local apps collect the files and then write records into
the database to record the information about those files so that we can
bill for them later. Likewise, when a remote connects and identifies
itself, the local apps query the database to see if there are any data
files waiting to be sent to the remote system. Then once they are sent,
the local apps again update the database with the appropriate
information about the transaction so we can bill. So basically, the
local apps just stand there "at the ready" to receive a connection and
then try to get the data transfers done as quickly as possible. Part of
the transaction is reading and writing to the local DB. =20
> InnoDB. The =20
> hundred apps =20
>=20
> It sounds as though you do a lot of "burst" processing. Your=20
> client apps=20
> collect information from the user then interacts with the=20
> database and=20
> waits for more user input. It's that "dead air" time (waiting=20
> on the live=20
> people to do something) that is costing you a small fortune in unused=20
> connection time and consuming valuable server-side resources.
>=20
No live people here, just server to server transactions. Moving data
over a sat link cost effectively is tricky. You can't use packet
acknowlegements like with TCP/IP because it just uses too much time.
We're talking connections of 2400bps max here.
> connections in=20
>=20
> Dropping the persistent connections are not necessary to move=20
> to InnoDB. I=20
> think many of us believe that 200 sleeping connections out of=20
> 236 total=20
> are worrisome no matter which storage engine you are using.
>=20
I agree here but convincing our developers of this is another story.
> for the past=20
> see about=20
> connection=20
> and not db=20
> can make=20
> us a lot=20
> hardware and=20
>=20
> The three primary benefits of InnoDB: Row level locking (more=20
> concurrency=20
> for tables with heavy writes or updates), transactional support (for=20
> action atomicity, data consistency, and process isolation),=20
> and foreign=20
> keys (also consistency). If you need any of those features for your=20
> application design, that would also be a compelling reason to change=20
> engines.=20
>=20
> arch, >2gig ram=20
> looking at=20
>=20
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>=20
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
| Hi,
2005/9/23, SGreen@unimin.com <SGreen@unimin.com>:
> "Jeff" <jsmforum@optonline.net> wrote on 09/23/2005 09:57:06 AM:
>
>
> Yes, I meant exactly that. Within each MySQL server is a "special"
> database called `mysql`. That is the database that contains the tables of
> all of the user login and permission information for the server (and
> several other important bits of system-wide metadata). None of the tables
> in that database can be converted to InnoDB. That would be a "bad thing".
>
> The tables of every OTHER database on the server (including yours) are
> eligible for InnoDB conversion so long as you do not want to use fulltext
> searching. If you need a FT index, you have to keep that table as MyISAM
> for now (they are working on enabling FT indexes in InnoDB but there is n=
o
> release date yet)
>
And GIS as well, IIRC:
http://dev.mysql.com/doc/mysql/en/s...s-in-mysql.html
(just to be picky)
--
Pooly
Webzine Rock : http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Devananda 2005-09-29, 8:23 pm |
| Jeff wrote:
>
> Well the applications with persistant connections is a touchy subject.
> Our apps send and rec data over satelite links which are very expensive.
> The shorter the duration of the link the less it costs us. So the
> pervailing theory is that with persistant connections the apps will
> spend less time re-connecting/dis-connecting from the db. Even
> fractions of a second counts when you're talking about thousands of
> connections a day and we are charged by the second for airtime. That's
> the whole driving force behind wanting to switch over to InnoDB. The
> thought is it would give us faster writes when we have a hundred apps
> trying to write at or very near the same time because of the record
> level locking as opposed to the MyISAM Table level locking during writes
> and updates.
>
> Now, the question is, if we need to drop the persistant connections in
> order to move to an InnoDB engine, will the speed benefit of record
> level locking outweigh what is lost by not having persistant
> connections?
The only way to know is to test it in your environment. I don't believe
anyone on the list could answer that question with certainty.
Just out of curiosity, I wrote a couple scripts in perl to very loosely
test this.
------------------
[deva@o1 - test]# cat loop.sh
#!/bin/bash
for x in `seq 1 10`; do
$1
done
----------------------
[deva@o1 - test]# cat con.pl
#!/usr/bin/perl
use strict;
use warnings;
require DBI;
print "Start\n";
my $con = DBI->connect("DBI:mysql::db3-p", 'user', 'pass')
or die("Failed to connect!");
print "Connected!\n";
exit;
----------------------
[deva@o1 - test]# cat nocon.pl
#!/usr/bin/perl
use strict;
use warnings;
require DBI;
print "Start\n";
#my $con = DBI->connect("DBI:mysql::db3-p", 'user', 'pass')
# or die("Failed to connect!");
print "Skipped Connecting!\n";
exit;
-------------------
time ./loop.sh ; time ./loop.sh ./nocon.pl >/dev/null; time ./loop.sh
../con.pl >/dev/null
((( bash script overhead )))
real 0m0.004s
user 0m0.002s
sys 0m0.002s
((( perl script with no connection )))
real 0m0.595s
user 0m0.520s
sys 0m0.057s
((( same perl script with connection )))
real 0m0.781s
user 0m0.682s
sys 0m0.064s
Now, I know this is *far* from an accurate test, and doesn't demonstrate
any of the specifics of your servers, but it does show that, on my
servers, with perl, there is roughly a 0.02sec real and 0.007sec sys
overhead to make and close the connection. Take that for what you will.
>
> That being said and having just looked at our connections for the past
> 60 minutes during what is our roughly our peak time I only see about 350
> which is roughly one every 10 seconds with a rough avg connection time
> of about 28 seconds most of which is transfer of data and not db
> read/write/updates. So, I believe, from that information I can make an
> educated guess that the MyISAM table locking is not the real bottleneck
> here and therefore it's probably not going to do us a lot of good to
> switch to InnoDB, especially with our current hardware and application
> behavior. Thoughts?
With one connection every 10 seconds, I don't understand how table lock
contention is a concern, unless your queries are so large that they lock
the table for *that* long. If so, are they properly indexed?
It doesn't sound like that is your problem though, so that's not a
reason to move to InnoDB.
>
> At some point however, as our traffic grows we probably will hit a point
> where the db read/write/updates will start to become a bottleneck and
> we'll need to look at moving to a 64bit arch, >2gig ram and the InnoDB
> engine. What status variables should I be looking at to see if we have
> a lot of read/write/updates being delayed?
>
See http://dev.mysql.com/doc/mysql/en/internal-locking.html
and http://dev.mysql.com/doc/mysql/en/show-status.html
" Table_locks_immediat
e
The number of times that a table lock was acquired immediately. This
variable was added as of MySQL 3.23.33.
Table_locks_waited
The number of times that a table lock could not be acquired immediately
and a wait was needed. If this is high, and you have performance
problems, you should first optimize your queries, and then either split
your table or tables or use replication. This variable was added as of
MySQL 3.23.33."
For example, this is from our MyISAM server (uptime 200days, 7% selects,
very un-optimized but still performs well enough),
mysql> show status like 'table%';
Table_locks_immediat
e 12810013
Table_locks_waited 306450
Hope that helps!
Devananda vdv
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
| > -----Original Message-----
> From: Devananda & #91;mailto:karnah805
@yahoo.com]
> Sent: Thursday, September 29, 2005 14:56
> To: Jeff
> Cc: mysql@lists.mysql.com
> Subject: Re: MyISAM to InnoDB
>
>
> Jeff wrote:
> touchy subject.
> costs us.
> connections the apps
> db. Even
> we have a
> because of
> level locking
> connections in
>
> The only way to know is to test it in your environment. I
> don't believe
> anyone on the list could answer that question with certainty.
>
> Just out of curiosity, I wrote a couple scripts in perl to
> very loosely
> test this.
> ------------------
> [deva@o1 - test]# cat loop.sh
> #!/bin/bash
>
> for x in `seq 1 10`; do
> $1
> done
> ----------------------
> [deva@o1 - test]# cat con.pl
> #!/usr/bin/perl
>
> use strict;
> use warnings;
> require DBI;
>
> print "Start\n";
> my $con = DBI->connect("DBI:mysql::db3-p", 'user', 'pass')
> or die("Failed to connect!");
> print "Connected!\n";
> exit;
> ----------------------
> [deva@o1 - test]# cat nocon.pl
> #!/usr/bin/perl
>
> use strict;
> use warnings;
> require DBI;
>
> print "Start\n";
> #my $con = DBI->connect("DBI:mysql::db3-p", 'user', 'pass')
> # or die("Failed to connect!");
> print "Skipped Connecting!\n";
> exit;
> -------------------
> time ./loop.sh ; time ./loop.sh ./nocon.pl >/dev/null; time ./loop.sh
> ./con.pl > | | |