Hi,
Thank you. I have a doubt, you mentioned one equation as
Memory Used By MySQL = Innodb_buffer_pool_size + key_buffer_size +
max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) +
max_connections*2MB
Is it against mysqld or for each mysql prompt?.
Could you please confirm it?
Thanks,
Narasimha
From: David Griffiths [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 04, 2005 10:19 PM
To: Gleb Paharenko
Cc: mysql@lists.mysql.com
Subject: Re: Slow queries, why?
Yes, indexes slow down inserts (or updates that change the value of a
column that is indexed).
Also, remember that MySQL only uses one index per per table in a query.
So if there are some columns in your table that are indexed, but,
1) Have poor cardinality (number of distinct values - low cardinality
means there aren't many distinct values)
2) Are only used in a where clause with another column that has good
cardinality
then they are an excellent candidate for removal.
While "EXPLAIN" is great for queries, it won't help much with an insert;
it might be useful for figuring out what indexes are used, and which
ones aren't.
Use "show innodb status" to get an idea of what's going on (Gleb
suggested it in the link to the innodb monitor).
You should also post the relevant parts of your my.cnf file; have you
seen this equation before:
Memory Used By MySQL = Innodb_buffer_pool_size + key_buffer_size +
max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) +
max_connections*2MB
Use it to calculate how much memory you are using.
Finally, read up on phantom reads:
http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/InnoDB_Next-key_
locking.html
This might be what's happening.
David
Gleb Paharenko wrote:
>Hello.
>
>
>
>
>
>>We're running MySQL 4.11 on a machine with 2GB memory, the table is
>>
>>
>
>
>
>>InnoDB with a compound primary key, and additional indexes on all rows
>>
>>
>
>
>
>>with searchable options in the API. Any generic advice or admin tools
>>
>>
>
>
>
>>would be great.....
>>
>>
>
>
>
>Use EXPLAIN to determine how efficient your indexes are. Using a lot of
>
>keys could slow down the INSERT operations but fasten the SELECTs.
>
>InnoDB monitors might be helpful in your case as well. See:
>
> http://dev.mysql.com/doc/mysql/en/explain.html
>
> http://dev.mysql.com/doc/mysql/en/innodb-monitor.html
>
>
>
>
>
>
>
>Joseph Cochran <[EMAIL PROTECTED]> wrote:
>
>
>
>>So here's my situation: we have a database that has a table of about 5
>>
>>
>
>
>
>>million rows. To put a new row into the table, I do an INSERT ...
>>
>>
>
>
>
>>SELECT, pulling data from one row in the table to seed the data for
>>
>>
>
>
>
>>the new row. When there are no active connections to the DB other than
>>
>>
>
>
>
>>the one making the INSERT, it runs like a charm. But during normal
>>
>>
>
>
>
>>daytime operation, when we run around 50 connections (most sleeping at
>>
>>
>
>
>
>>any one time), it takes up to two minutes to do, and ends up locking
>>
>>
>
>
>
>>any other inserts or updates against that table for the entire time.
>>
>>
>
>
>
>
>
>
>>I'll get into more specifics if they're required, but I wanted to ask
>>
>>
>
>
>
>>in general if MySQL has tools to diagnose this, or if anyone has had
>>
>>
>
>
>
>>general situations like this. In SQL Server (which is where I have
>>
>>
>
>
>
>>most of my experience) I could use the trace tool and the Query
>>
>>
>
>
>
>>Analyzer to tell what the execution plan for the query was and thus
>>
>>
>
>
>
>>what's stalling it (an index gone bad, a weird locking situation,
>>
>>
>
>
>
>>etc).
>>
>>
>
>
>
>
>
>
>>We're running MySQL 4.11 on a machine with 2GB memory, the table is
>>
>>
>
>
>
>>InnoDB with a compound primary key, and additional indexes on all rows
>>
>>
>
>
>
>>with searchable options in the API. Any generic advice or admin tools
>>
>>
>
>
>
>>would be great.....
>>
>>
>
>
>
>
>
>
>>-- Joe
>>
>>
>
>
>
>
>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
Confidentiality Notice
The information contained in this electronic message and any attachments to
this message are intended
for the exclusive use of the addressee(s) and may contain confidential or
privileged information. If
you are not the intended recipient, please notify the sender at Wipro or [EMAIL
PROTECTED] immediately
and destroy all copies of this message and any attachments.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
