Some MySQL Concerns

--0-309047440-1286154182=:59069
Content-Type: text/plain; charset=us-ascii

All,

Sorry if this is the wrong mailing list to post yet please help me out or
redirect me to the right one.

Here are some concerns we PHP Developers have over our MySQL Database.

1) While inserting and connection lost, what will happen? Is the query
going to be there forever?
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?
3) What is the impact if we switch engine for production tables from MyISAM
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.
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.
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?

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

Thanks,
Mon



--0-309047440-1286154182=:59069--

Re: Some MySQL Concerns

--0050450140b5d0733c0491ca0e34
Content-Type: text/plain; charset=ISO-8859-1

On Mon, Oct 4, 2010 at 3:03 AM, monloi perez 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
> MyISAM
> 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

--0050450140b5d0733c0491ca0e34--