MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:October 4 2010 12:55pm
Subject:Re: Some MySQL Concerns
View as plain text  
On Mon, Oct 4, 2010 at 3:03 AM, monloi perez <mlp_folder@stripped> wrote:

> 1)      While inserting and connection lost, what will happen? Is the query
> going to be there forever?

No. Depending on the timing of the connection loss, the statement may
complete or be rolled back. it will not just hang there. Transactions will
guarantee that not only the current insert, but the entire transaction is
rolled back cleanly in case of a disconnect.

> 2)      There are two engines for MySQL tables: InnoDB and MyISAM. It is
> said
> that the skip locking config is not working 100% for MyISAM tables and it's
> better to use InnoDB. Is this true?

You shouldn't usually touch the skip-locking config :)

> 3)      What is the impact if we switch engine for production tables from
> to InnoDB? I tried this before and seems like you can just simply alter the
> table and change its storage engine, yet I just wanted to confirm.

The very very VERY simplified answer is that if you're going to be using
transactions, you must use InnoDB. If you're not using transactions, MyISAM
may be slightly faster.

Altering the engine on-the-fly is not a problem, indeed (keep in mind you're
locking the table during the operation, though); but going from InnoDB to
MyISAM will remove all defined foreign key relations; going from MyISAM to
InnoDB requires you to remove any fulltext indices first.

> 4)      MySQL thread_concurrency config: to split the queries into multiple
> CPUs. Does this really help a lot since currently we have a server with 8
> cpu's
> and one with 16.

MySQL is known to be not very good with multiple cores, although
improvements keep being added, of course. A single query also executes
always on a single CPU, no splitting happens.

The Percona guys have done some excellent work making their XtraDB
multithread very well, I hear; I'm not aware of wether they've done anything
with query splitting.

> 5)      MySQL wait_timeout config: the max time the server waits for
> activity on
> a noninteractive connection before closing it. What happens if an INSERT
> query
> exceeds max time. Will this corrupt our table since I guess the insert will
> be
> killed? or does the max time only work on selects?

While a statement is running, your connection is not considered inactive.

> Sorry if its a lot, we just need some confirmations before we try to
> optimize
> our servers.

Sounds like you want a DBA :-p

Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Some MySQL Concernsmonloi perez4 Oct
  • Re: Some MySQL ConcernsJohan De Meersman4 Oct