Performance | Forum profile

This is possibly excessive: innodb_log_file_size = 1500M and may cause hiccups when it is flushing it. To assist in analyzing slow SELECTs, please provide * SHOW CREATE TABLE tbl\G -- engine, indexes -- works better than DESC * SHOW TABLE STATUS LIKE 'tbl'\G -- sizes * EXPLAIN SELECT ...\G -- clues of inefficiencies * SHOW VARIABLES LIKE '%buffer%'; -- cache size * SHOW VARIABLES LIKE 'innodb%'; -- various...

>>>>> CONTINUATION >>>>> mysql> show processlist; +----------+-----------------+---------------------------+----------------------+---------+------+-----------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info |...

All tables are InnoDB, so yes InnoDB is used a lot. Regarding connections: Number of connections is pretty constant, at the current level although it jumps to around 200 during peak hours. thread_cache_size is 8.

Update: Well, if I change the date value to a higher date such as 20090301, then it'll finally use the index and the rows shown is much better. But, why isn't it using it to begin with? There are about 300,000 records and only a third will match based on the 20080101 criteria, but it refuses to recognize this in the explain...

I guess i am crazy... but i already have the same problem! :-(

Hello, I improved those slow queries, we will see after next week (because we want to test before put them in production)... I'm still trying to get more information about mysql tuning and queries optimization... I am still trying to see what happened the night before this... but I see nothing special logged. we have a lot of crons scanning a lot of big tables... more than one time a night We don't backup automatically yet... I do it...

Hi All, I have an application in which I am creating dynamic shops from a huge database of products. For achieving performance we are expanding our server architecture. Now we have a separate database server on which is dedicated for hosting the data of created shops. When a new shop will be created, we will create a database with the name of the shop on this database server and create its core tables. Now we have to copy the data for each...

Hi Rick, The manual says changes can be made immediately if the change is only to the table's metadata. I had assumed that the NOT NULL constraint would be part of the metadata, so I guess my question should really have been "Why can't the NOT NULL constraint for a column stored as part of a table's metadata?" Thanks, Ben

I have came across a situation where app generates around 50 M records each year.. We are using mysql 4.1 and its not possible to upgrade at this time.. Can any one provide inputs, ideas on how this situation can be handled.. As far as I know 4.1 does not support partitioning... The table structure is very simple with few columns.. The problem is how to handle this many records in one table and still have decent performance.. Table structure...

Hi, I have a query designed to update a series of rows from x to the end, so if 30 rows match the criteria I may want to update the last 20 rows leaving the first 10 as they are. I have a query to achieve this: UPDATE queue SET processed=1 WHERE id IN (SELECT * FROM (SELECT id FROM queue WHERE probid=1 AND id <> 2 AND processed <> 1 ORDER BY id DESC LIMIT 10, 18446744073709551615) alias) This works in a test...