Index question
Howdy all, I've noticed some strange behavior with the way that mysql is choosing indexes with a particular query I'm trying to optimize. First off, I'm using MySQL 4.0.24 on MAC OSX. I've got a table that I'm searching on based upon a set of preferences. From one query to the next the set of preferences may change (as well as the values of the preferences). However, there are a couple of basic preferences that all queries will have, so I created a composite index on that set of preferenes (with the least selective pref being the left most column in the index and getting more restrictive going to the right). I also have another index on the leftmost column mentioned above because that field is a FK and MySQL wouldn't let me use the comp index for the FK. So, there are times when I actually want the single column index to be used and other times the composite key, based on how broad the preferences are. So far so good. I added another index, to see if I could speed things up even more and the query performance took a nose dive (about a factor of 7 worse). When I ran the explain I noticed that mysql changed the index that it was using, but not to the new index (the third one). Adding a fourth index made mysql select the orginal index and performance was restored. My question is, why is mysql choosing differet indexes based on the presence of these new indices (that it chooses not to use in place to the old indices)? I got things back on track by just adding and removing indices until things were working the way that I wanted them to, but it seems really strange. Thanks, Tripp __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table full
Mike, Thanks for the insight. The "sent" table has about 7 million records. The other tables involved have tens of thousands of records or there abouts. Not your 100 million size but certainly worth exploring. Thanks again, Tripp --- mos <[EMAIL PROTECTED]> wrote: > Tripp, > This problem may occur if your table is > quite large (several gb in > size). The update may make the table too large to > address using > conventional MySQL pointers. You may need to modify > the table so it has a > "Max Rows=" option where "" is the max rows > you expect the table to > have and this forces MySQL to use a larger table > pointer. When my tables > === message truncated === __ Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table full
Mathias, Here's the query: UPDATE customer_indicator INNER JOIN customer_listing_pref ON customer_listing_pref.customer_id = customer_indicator.customer_id AND customer_listing_pref.store_id = customer_indicator.store_id AND customer_listing_pref.store_id = @OLD_STORE_ID LEFT JOIN contact_log ON contact_log.customer_id = customer_indicator.customer_id AND contact_log.store_id = @OLD_STORE_ID LEFT JOIN sent ON sent.pref_id = customer_listing_pref.pref_id SET customer_indicator.employee_id = @NEW_EMPLOYEE_ID, customer_indicator.store_id = @NEW_STORE_ID, customer_listing_pref.store_id = @NEW_STORE_ID, sent.store_id = @NEW_STORE_ID, contact_log.store_id = @NEW_STORE_ID WHERE customer_indicator.employee_id = @OLD_employee_id AND customer_indicator.store_id = @OLD_STORE_ID AND customer_indicator.customer_id BETWEEN 2 AND 23000; ++--+ | Variable_name | Value| ++--+ | lower_case_table_names | 0| | max_heap_table_size| 16777216 | | max_tmp_tables | 32 | | table_cache| 64 | | table_type | MYISAM | | tmp_table_size | 33554432 | ++--+ I don't explicitly create any tables for this operation. I'm just trying to run the query. If I make the range in the BETWEEN condition of the WHERE claus sufficiently small the query runs. Otherwise I get the table is full error. So it seems that MySQL is doing some table creation behind the scenes. I pretty certain that I have enough disk space to perform the operation (I have about 10GB free). Thanks, Tripp --- [EMAIL PROTECTED] wrote: > sorri it's tmp_table_size. > > mysql> show variables like '%table%'; > ++--+ > | Variable_name | Value| > ++--+ > | innodb_file_per_table | OFF | > | innodb_table_locks | ON | > | lower_case_table_names | 1| > | max_heap_table_size| 16777216 | > | max_tmp_tables | 32 | > | table_cache| 256 | > | table_type | InnoDB | > | tmp_table_size | 9437184 | > ++--+ > 8 rows in set (0.00 sec) > > > What are : > show create table toto; > the count(*) ? > the query ? > > > > Mathias > Selon Emmett Bishop <[EMAIL PROTECTED]>: > > > Mathias, > > > > Thanks for the reply. I couldn't find a server > > variable named "max_temp_table_size" but I did > find > > one named "max_heap_table_size". Is that what you > > meant? BTW, I forgot to mention that I'm using > MySQL > > 4.0.20. Could it be that this variable that you > > mention is only in later versions? > > > > Basically, what I'm trying to do is a multi-table > > update statement. If I select too many rows I get > the > > "Table #sql-123 is full" error. If I bite off a > small > > enough chunk, the query works. > > > > Thanks again, > > > > Tripp > > > > --- [EMAIL PROTECTED] wrote: > > > > > hi, > > > seems to be a temp table (sybase notation). > > > see max_temp_table_size > > > > > > Mathias > > > Selon Emmett Bishop <[EMAIL PROTECTED]>: > > > > > > > Howdy all, I have a question about a SQL > statement > > > > that I'm trying to execute. When I execute the > > > > statement I get the following error: The table > > > > '#sql_bd6_3' is full. > > > > > > > > What does this mean exactly? > > > > > > > > Thanks, > > > > > > > > Tripp > > > > > > > > > > > > > > > > __ > > > > Yahoo! Mail Mobile > > > > Take Yahoo! Mail with you! Check email on your > > > mobile phone. > > > > http://mobile.yahoo.com/learn/mail > > > > > > > > -- > > > > MySQL General Mailing List > > > > For list archives: > http://lists.mysql.com/mysql > > > > To unsubscribe: > > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > > > > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > __ > > Do You Yahoo!? > > Tired of spam? Yahoo! Mail has the best spam > protection around > > http://mail.yahoo.com > > > > > __ Do you Yahoo!? Make Yahoo! your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table full
Mathias, Thanks for the reply. I couldn't find a server variable named "max_temp_table_size" but I did find one named "max_heap_table_size". Is that what you meant? BTW, I forgot to mention that I'm using MySQL 4.0.20. Could it be that this variable that you mention is only in later versions? Basically, what I'm trying to do is a multi-table update statement. If I select too many rows I get the "Table #sql-123 is full" error. If I bite off a small enough chunk, the query works. Thanks again, Tripp --- [EMAIL PROTECTED] wrote: > hi, > seems to be a temp table (sybase notation). > see max_temp_table_size > > Mathias > Selon Emmett Bishop <[EMAIL PROTECTED]>: > > > Howdy all, I have a question about a SQL statement > > that I'm trying to execute. When I execute the > > statement I get the following error: The table > > '#sql_bd6_3' is full. > > > > What does this mean exactly? > > > > Thanks, > > > > Tripp > > > > > > > > __ > > Yahoo! Mail Mobile > > Take Yahoo! Mail with you! Check email on your > mobile phone. > > http://mobile.yahoo.com/learn/mail > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table full
Howdy all, I have a question about a SQL statement that I'm trying to execute. When I execute the statement I get the following error: The table '#sql_bd6_3' is full. What does this mean exactly? Thanks, Tripp __ Yahoo! Mail Mobile Take Yahoo! Mail with you! Check email on your mobile phone. http://mobile.yahoo.com/learn/mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Analyze Table
Howdy all, Quick question about Analyze table. I just deleted 3+ million records from a table that gets a lot of insert activity throughout the day. It seems like now would be a good time to run optimize table and/or analyze table. I saw in the docs that analyze table puts a read lock on the table. This will prevent inserts/updates/deletes while the table is being analyzed, correct? For a table with 5 million records (the table has 5 int fields, 1 decimal, 1 timestamp) I would assume that this might take a while. I don't want to tie this table up for a long period of time during the day/evening. Just wanted to see if, in anyone's experience, this is generally a fast or slow thing. Cheers, Tripp __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
default values
Howdy all, is it possible to force a field to be NOT NULL but not have any default value (I.E the insert statement must explicitly provide data for the field in question)? Cheers, Tripp __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
list of error codes
I looked around and didn't see documentation of MySQL error codes. I did find a short list of INNODB codes but nothing comprehensive. Is there such a page? Thanks, Tripp __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help interpreting SHOW INNODB Status Message
Heikki, Thanks for the input. Right now we can't move to 4.1, we're stuck with 4.0.20 for sometime. So, it there someway that through improving the schema these issues could be addressed? I added an index on these tables reflecting the way that data is usually looked up (always by session_guid and label). Every user has a unique session_guid that looks something like: 7E6E4C38-4819-11D9-9802-84B0A828F8C4 What I don't understand is what the gap is. I can understand in the example on the mysql site (select * from table where id > 100 for update) how that would lock all records with id > 100. How does it work in the case of a string based index? Suppose there were two different users, one with guid 7E6E4C38-4819-11D9-9802-84B0A828F8C4 and the other with guid 5G4F4H10-4819-11D9-9802-84B0A828F8C4 (note, these two guids are fairly representative in form). The first user was performing a delete and the second was performing an insert what gap would be created? What kind of schema change might reduce/eliminate the contention? Thanks, Tripp --- Heikki Tuuri <[EMAIL PROTECTED]> wrote: > Emmett, > > ok, trx (1) is trying to lock gap G, but trx (2) > already has a lock on it. > And trx (2) is trying to insert a new record into > that same gap G. > > Even a waiting lock request on a gap prevents new > inserts into it => a > deadlock. > > In 4.1.7, you might avoid this deadlock. In 4.1.7, a > waiting gap lock does > not need to wait just because someone else has a > granted lock on the same > gap. But, of course, your transactions might > deadlock a bit later. DELETEs > and INSERTs in the same gap very easily lead to a > deadlock. > > In 4.1, you can also set > > innodb_locks_unsafe_for_binlog > > in my.cnf. That removes almost all gap locking. > > Best regards, > > Heikki Tuuri > Innobase Oy > Foreign keys, transactions, and row level locking > for MySQL > InnoDB Hot Backup - a hot backup tool for InnoDB > which also backs up MyISAM > tables > http://www.innodb.com/order.php > > > - Original Message - > From: "Emmett Bishop" <[EMAIL PROTECTED]> > Newsgroups: mailing.database.myodbc > Sent: Tuesday, December 07, 2004 5:44 PM > Subject: Re: Help interpreting SHOW INNODB Status > Message > > > > Heikki, > > > > We're using MySQL 4.0.20. Here's the full output: > > > > = > > 041207 8:33:26 INNODB MONITOR OUTPUT > > = > > Per second averages calculated from the last 15 > > seconds > > -- > > SEMAPHORES > > -- > > OS WAIT ARRAY INFO: reservation count 49506785, > signal > > count 48823098 > > Mutex spin waits 963371106, rounds 3338424450, OS > > waits 2853796 > > RW-shared spins 80886102, OS waits 38728129; > RW-excl > > spins 2488320, OS waits 302033 > > > > LATEST FOREIGN KEY ERROR > > > > 041206 23:52:41 Transaction: > > TRANSACTION 0 10799828, ACTIVE 2 sec, process no > > 31424, OS thread id 2942950320 inserting, thread > > declared inside InnoDB 500 > > mysql tables in use 1, locked 1 > > 6 lock struct(s), heap size 1024, undo log entries > 1 > > MySQL thread id 88051, query id 14858689 host ip > user > > update > > INSERT INTO s_filter (fname, lname, email, > > eff_oldest_dt, eff_latest_dt, move_oldest_dt, > > move_latest_dt, agent_id, no_agent_sw, > > exclude_holds_sw, custom_message_sw, pics_only_sw, > > price, wiz_city_name, subdivision, > tot_finished_sqft, > > bedrooms, garage_spaces, bathrooms, area, subarea, > > year, property_t > > Foreign key constraint fails for table > > `ahf_test/s_filter`: > > , > > CONSTRAINT `s_filter_ibfk_1` FOREIGN KEY > > (`session_guid`) REFERENCES `s_session` > > (`session_guid`) > > Trying to add in child table, in index > > `idx_session_guid` tuple: > > DATA TUPLE: 2 fields; > > 0: len 36; hex > > > 3745364534482d343831392d313144392d393830322d383442304138323846384334; > > asc 7E6E4C38-4819-11D9-9802-84B0A828F8C4;; 1: len > 6; > > hex 0048a79b; ascH ;; > > > > But in parent table `ahf_test/s_session`, in index > > `PRIMARY`, > > the closest match we can find is record: > > PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; > info > > bits 32 > > 0: len 30; hex > > > 37454139364146452d343743442d313144392d393830322d383442304138; > > asc 7EA96AFE-47CD-11D9-9802-84B0A8;...(truncated); > 1: > > len 6; hex 00a49aaf; asc ;
Re: Help interpreting SHOW INNODB Status Message
Heikki, We're using MySQL 4.0.20. Here's the full output: = 041207 8:33:26 INNODB MONITOR OUTPUT = Per second averages calculated from the last 15 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 49506785, signal count 48823098 Mutex spin waits 963371106, rounds 3338424450, OS waits 2853796 RW-shared spins 80886102, OS waits 38728129; RW-excl spins 2488320, OS waits 302033 LATEST FOREIGN KEY ERROR 041206 23:52:41 Transaction: TRANSACTION 0 10799828, ACTIVE 2 sec, process no 31424, OS thread id 2942950320 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 6 lock struct(s), heap size 1024, undo log entries 1 MySQL thread id 88051, query id 14858689 host ip user update INSERT INTO s_filter (fname, lname, email, eff_oldest_dt, eff_latest_dt, move_oldest_dt, move_latest_dt, agent_id, no_agent_sw, exclude_holds_sw, custom_message_sw, pics_only_sw, price, wiz_city_name, subdivision, tot_finished_sqft, bedrooms, garage_spaces, bathrooms, area, subarea, year, property_t Foreign key constraint fails for table `ahf_test/s_filter`: , CONSTRAINT `s_filter_ibfk_1` FOREIGN KEY (`session_guid`) REFERENCES `s_session` (`session_guid`) Trying to add in child table, in index `idx_session_guid` tuple: DATA TUPLE: 2 fields; 0: len 36; hex 3745364534482d343831392d313144392d393830322d383442304138323846384334; asc 7E6E4C38-4819-11D9-9802-84B0A828F8C4;; 1: len 6; hex 0048a79b; ascH ;; But in parent table `ahf_test/s_session`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32 0: len 30; hex 37454139364146452d343743442d313144392d393830322d383442304138; asc 7EA96AFE-47CD-11D9-9802-84B0A8;...(truncated); 1: len 6; hex 00a49aaf; asc ;; 2: len 7; hex 0340082073; asc@ s;; 3: len 8; hex 8000123a34f916a6; asc:4 ;; LATEST DETECTED DEADLOCK 041207 7:32:14 *** (1) TRANSACTION: TRANSACTION 0 10812524, ACTIVE 0 sec, process no 31424, OS thread id 2916936624 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 320 MySQL thread id 89288, query id 15070554 host ip user updating DELETE FROM s_customer WHERE session_guid = '0B10634A-485A-11D9-9802-84B0A828F8C4' AND label = 'CustomerDTO' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 6682 n bits 120 index `idx_session_label` of table `ahf_test/s_customer` trx id 0 10812524 lock_mode X locks gap before rec waiting Record lock, heap no 13 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 32 0: len 30; hex 32354435463135432d343835382d313144392d393830322d383442304138; asc 25D5F15C-4858-11D9-9802-84B0A8;...(truncated); 1: len 11; hex 437573746f6d657244544f; asc CustomerDTO;; 2: len 6; hex 0048b228; ascH (;; *** (2) TRANSACTION: TRANSACTION 0 10812523, ACTIVE 0 sec, process no 31424, OS thread id 2926173104 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 7 lock struct(s), heap size 1024, undo log entries 1 MySQL thread id 89289, query id 15070556 host ip user update INSERT INTO s_customer (customer_id, address_id, agent_id, pref_id, lname, fname, email, home_phone, work_phone, comments, house_for_sale, address1, city, state, zip, subdivision_list, price_lower, price_upper, tot_finished_sqft, bedrooms, bathrooms, garage_spaces, style, eff_dt, move_dt, customer_i *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 6682 n bits 120 index `idx_session_label` of table `ahf_test/s_customer` trx id 0 10812523 lock_mode X locks gap before rec Record lock, heap no 13 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 32 0: len 30; hex 32354435463135432d343835382d313144392d393830322d383442304138; asc 25D5F15C-4858-11D9-9802-84B0A8;...(truncated); 1: len 11; hex 437573746f6d657244544f; asc CustomerDTO;; 2: len 6; hex 0048b228; ascH (;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 6682 n bits 120 index `idx_session_label` of table `ahf_test/s_customer` trx id 0 10812523 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 13 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 32 0: len 30; hex 32354435463135432d343835382d313144392d393830322d383442304138; asc 25D5F15C-4858-11D9-9802-84B0A8;...(truncated); 1: len 11; hex 437573746f6d657244544f; asc CustomerDTO;; 2: len 6; hex 0048b228; ascH (;; *** WE ROLL BACK TRANSACTION (1) TRANSACTIONS Trx id counter 0 10819864 Purge done for trx's n:o < 0 10819842 undo n:o < 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 31424, OS thread id 2925570992 MySQL thread id 89423, query id 15136606 host ip user show innodb sta
Help interpreting SHOW INNODB Status Message
Howdy all, We're having concurrency problems with a table in our database and I'm not sure if I'm interpreting the following chunk of output from SHOW INNODB STATUS correctly. >From what I gather, the row could not be inserted because the table was locked. I think that this insert was a victim of the next key locking stategy used by INNODB based on statement "lock_mode X locks gap before rec". We don't use SELECT FOR UPDATE statements so I'm wondering how there could be a lock. If someone was performing a regular SELECT statement (they would be using the index session_guid, label to retrieve the records) would that cause the insert statement to get locked out? What confuses me is how there could be two or more different guids involved in any one query (the session guid is always in the where clause of any select statement against this table, as is the label, though the labels are almost all the same, a value of "SelectedCustomer"). Any ideas as to how I can clean this up so that the contention is reduced? BTW, we're using a transaction isolation level of REPEATABLE READ. Thanks in advance, Tripp LATEST DETECTED DEADLOCK 041206 17:13:50 *** (1) TRANSACTION: TRANSACTION 0 10790587, ACTIVE 151 sec, process no 31424, OS thread id 2949241776 inserting mysql tables in use 1, locked 1 LOCK WAIT 46 lock struct(s), heap size 5504, undo log entries 158 MySQL thread id 85684, query id 14714501 host ip user update INSERT INTO s_contact_log (log_id, customer_id, entry, author, log_dt, log_type, office_id, session_guid, label, sort_id) VALUES (228072,38755, 'test entry' - *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 2263 n bits 264 index `idx_session_label` of table `ahf_test/s_contact_log` trx id 0 10790587 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 130 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 32 0: len 30; hex 35384143373041392d343745312d313144392d393830322d383442304138; asc 58AC70A9-47E1-11D9-9802-84B0A8;...(truncated); 1: len 16; hex 53656c6563746564437573746f6d6572; asc SelectedCustomer;; 2: len 6; hex 004880b1; asc H ;; Here's the structure of the table: CREATE TABLE `s_contact_log` ( `session_guid` varchar(36) NOT NULL default '', `label` varchar(50) NOT NULL default '', `log_id` int(11) NOT NULL default '0', `office_id` int(11) NOT NULL default '0', `customer_id` int(11) NOT NULL default '0', `entry` text NOT NULL, `author` varchar(60) NOT NULL default '', `log_dt` date NOT NULL default '-00-00', `sort_id` int(11) NOT NULL default '0', `log_type` int(11) NOT NULL default '0', KEY `idx_session_guid` (`session_guid`), KEY `idx_session_label` (`session_guid`,`label`), CONSTRAINT `s_contact_log_ibfk_1` FOREIGN KEY (`session_guid`) REFERENCES `s_session` (`session_guid`) ) TYPE=InnoDB __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locking question
Howdy all, quick question about how INNODB handles locks. If autocommit is off and I perform a select statement without then issuing a commit, will INNODB remove any read locks that it issued? I would assume that the locks would be removed when the statement finished. Just want to verify that this is/isn't the case. Cheers, Tripp __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transaction Deadlocks
Howdy all, I've got a series of tables that are used to store a user's session information. The main table is a very simple table that stores a GUID and a last access data-time value. All other tables use the guid as a FK back to this main table. The main table's schema is as follows: CREATE TABLE s_session ( session_guid varchar(36) NOT NULL default '', last_access_dt datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (session_guid) ) TYPE=InnoDB; What we're seeing is that we get trx deadlocks when trying to delete rows and occasionally when simply updating a row (for example, when updating the last_access_dt when the user moves to a new page in the app). Is there any reason that using a GUID as the PK would cause problems? It seems not, but I really can't figure out why I would be getting deadlocks. I'm causing these in a development environment where I'm the only user! BTW, I'm using mySQL 4.0.20 on Mac OS X. Cheers, Tripp __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
archived versions of MySQL
I need to snag a copy of MySQL 4.0.20 for Windows. Is there somewhere on the MySQL I can grab older versions? Thanks, T __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transaction Deadlocks
Howdy all, We have a set of tables in a database that maintain session information of users of a website. Each user's session is identified by a GUID that we generate when they login. As the users perform various operations records are inserted/updated/deleted from these session tables. Some of the tables change frequently as users go about their business. One thing that I'm seesing is that we're getting deadlocks when records are being removed. Here's the structure of the table: CREATE TABLE `s_customer_identifier` ( `session_guid` varchar(36) NOT NULL default '', `label` varchar(50) NOT NULL default '', `customer_id` int(11) NOT NULL default '0', `pref_id` int(11) NOT NULL default '0', `office_id` int(11) NOT NULL default '0', `sort_id` int(11) NOT NULL default '0', KEY `idx_session_guid` (`session_guid`) ) TYPE=InnoDB The records are referenced for deletion by the session_guid and label: DELETE FROM s_customer_identifier WHERE session_guid = '5E6ED651-3725-11D9-96C0-FE7645265844' AND label = 'selected_customers'; is a typical example. A typical log statement we get when this happens is: General error, message from server: "Deadlock found when trying to get lock; Try restarting transaction" Is there some obvious cause based on what I've provided? Thanks, Tripp __ Do you Yahoo!? The all-new My Yahoo! - Get yours free! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error 2013
Howdy all, I got this error while running a routine mysqldump last night: Error 2013: Lost connection to MySQL server during query when dumping table `contact_log` at row: 41245 I didn't find any good information on this error at mysql. Is there a page there that lists all error codes? I found one such page just for INNODB errors, but that doesn't help me much. Google didn't seem to have much either (mainly people asking the same question I'm asking now). Any insight on how to trouble shoot this error would be greatly appreciated. Cheers, Tripp __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign Key Question
Howdy all, Quick question about foreign keys. If I have a database with foreign keys setup, then drop one of the tables (which is referenced by many of the others) and re-add the table, will the existing FKs work? I'm seeing errors in SHOW INNODB STATUS under the LATEST FOREIGN KEY ERRORS section that claim that the referenced table (the one I dropped and readded) doesn't exist. It does, I can query it and join to it, but I can't do inserts into any table that references the table. Any ideas? Cheers, Tripp ___ Do you Yahoo!? Win 1 of 4,000 free domain names from Yahoo! Enter now. http://promotions.yahoo.com/goldrush -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select non-matching fields
You're making the assumption that he's using 4.1.x. He didn't state which version he's using so your solution may be of no use to him. -- T --- Matt Warden <[EMAIL PROTECTED]> wrote: > On Fri, 06 Aug 2004 13:17:42 -0400, Michael Stassen > <[EMAIL PROTECTED]> wrote: > > You need a LEFT JOIN: > > > >SELECT ticket_number > >FROM purchased_items LEFT JOIN purchases > >ON purchased_items.ticket_number = > purchases.ticket_number > >WHERE purchases.ticket_number IS NULL; > > > No, actually he doesn't. > > SELECT ticket_number > FROM purchased_items > WHERE ticket_number NOT IN > (SELECT ticket_number FROM purchases); > > The above will most certainly be faster than any > join, because it is > only a couple projections and a simple selection > over ticket_number > (which is almost certainly indexed). > > Although, I suppose if this is only a maintenance > query (I suspect it > is), then it probably doesn't matter. But, the > bottom line is: if you > can avoid join, do it. There's only so much the > query optimizer can > do. > > > > > -- > > Matt Warden > Berry Neuroscience Lab > Department of Psychology > Miami University > > > > This email proudly and graciously contributes to > entropy. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: distinct based on two fields
And I have a table like this click send on accident? A Don't leave me hangin'!! -- Tripp --- Claire Lee <[EMAIL PROTECTED]> wrote: > I have a table like this > > name price type > A > > > > __ > Do you Yahoo!? > New and Improved Yahoo! Mail - Send 10MB messages! > http://promotions.yahoo.com/new_mail > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DISTINCT?
Paul, What about the case where column A is a foreign key? In that case would you be forced to keep the index on column A or could you use the A,B index since A is the left most prefix? Tripp --- Paul DuBois <[EMAIL PROTECTED]> wrote: > At 19:26 -0700 7/7/04, John Mistler wrote: > >I am not certain from the documentation whether it > is advisable to create a > >unique multi-column index on two columns that are > already individually > >indexed. The individual indexes I assume I need > for when I do a SELECT on > >those particular columns. The multi-column one I > need for the reasons > >discussed below. Any one know? > > If you have indexes on column A and column B, then > if you create > a multiple-column unique index on (A,B), you could > remove the index > on A. The reason for this is that MySQL can use a > leftmost prefix > of a multiple-column index as if you had an index on > just the leftmost > columns. > > In other words, an index on (A, B) can be use when > you search for combinations > of A and B, or when you search for just A. > > You cannot remove the index that you have on just B, > because B is not > a leftmost index of (A, B). > > http://dev.mysql.com/doc/mysql/en/Multiple-column_indexes.html > > > > >Thanks, > > > >John > > > >on 7/7/04 2:21 PM, Joshua J. Kugler at > [EMAIL PROTECTED] wrote: > > > >> Certainly, it's called making a unique index on > the field(s) you > >>want to keep > >> unique. > >> > >> Hope that helps. > >> > >> j- k- > >> > >> On Wednesday 07 July 2004 12:48 pm, John Mistler > said something like: > >>> Is there a way to do an INSERT on a table only > if no row already exists > >>> with the same info for one or more of the > columns as the row to be > >>> inserted? That is, without using a method > outside SQL? > >>> > >>> Thanks, > >>> > >>> John > > > > > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > Paul DuBois, MySQL Documentation Team > Madison, Wisconsin, USA > MySQL AB, www.mysql.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Administrator question
Howdy all. If this isn't the right listserv for this question, my appologies. What I'd like to do is set up a graph under the "Health" section of the mysql administrator so that I can easliy keep tabs on the INNODB Buffer pool. The problem is that I don't know which variables to use to calculate the buffer usage or the hit rate, which would also be nice to have. The mysql site doesn't seem to have a place where the innodb status variables are explained and the administrator doesn't explain them either (most of the other variables are explained, but no the innodb ones). We're running mysql 4.0.16 and mysql administrator 1.0.5 Beta. Cheers, Tripp __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compound Primary Key question
Quick question. In general, is it better to create compound primary keys or use an auto increment field to uniquely identify each record? --T __ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25¢ http://photos.yahoo.com/ph/print_splash -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tuning Table cache and temp table allocations
Howdy all, I've got a few questions about the table cache and the tmp_table_size. First off, a little background info. The server has been running now for 193 hours. I've got 3 GB of RAM on my box and I'm running RH linux. The MySQL installation is version 4.0.13. After taking a look at Show Status I noticed that the Opened Tables value was 10188. Does this qualify as big? It seems like it but I'm not sure. The table cache is set to the default value (64). My max connections is 100 and the largest set of joins I do is 6. Furthermore the max number of concurrent connections is only 9, at least for now. So should I bump the table cache to 600 like the docs say? I assume that linux can handle that no problem, but I don't know linux well so perhaps that's not true. Second, show status showed that Created_tmp_disk_tables = 111223. Again, I'm assuming that this is big and that I should adjust the tmp_table_size server variable. The tmp_table_size is set to the default (33554432). So, if I'm reading the docs correctly, MySQL will create the temp table on disk if the table will exceed ~33MB in size? That seems awfully large. Does that suggest that there are some queries that are doing full joins or something to that effect? Is there any guideline on how large I should make this value (something similar to the guidelines on innodb_buffer_pool_size or key_cache_size)? Does anyone know if the new book High Performance MySQL covers serving tuning in detail? There seems to be a lot that I could do, but most of the documentation that I've come across seems to be lacking in explanation of the basic concepts. Thanks for any advice, Tripp __ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25¢ http://photos.yahoo.com/ph/print_splash -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB SHOW STATUS
Arun, Unfortunately, that link contains the exact same info as does the mysql.com page. There's almost no explaination there of what any of the output means. I'm sure that it's really useful stuff but I don't have a starting point. Can someone point me in the right direction (a book or website) or explain briefly what I'm seeing in the BUFFER POOL AND MEMORY section? Brief intro, I set the innodb_buffer_pool_size to 128M in the my.cnf file. A quick peek at show variables confirms this value... -- BUFFER POOL AND MEMORY -- Total memory allocated 152389988; in additional pool allocated 1048576 Buffer pool size 8192 Free buffers 0 Database pages 7947 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 20345325, created 9857, written 763089 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 Why does it say the buffer pool size is only 8M? Shouldn't it be 128M? Also, could someone explain the hit rate? I remember seeing in someone's recent post that the 1000/1000 is good, but I don't know what that means. Cheers, Tripp __ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25¢ http://photos.yahoo.com/ph/print_splash -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INNODB SHOW STATUS
Howdy all, Quick question about what I'm seeing in the BUFFER POOL AND MEMORY section... I've configured the innodb_buffer_pool_size to be 128M and when I do a show variables like 'innodb%' I see | innodb_buffer_pool_size | 134217728 | So that looks good. However, I see the following in the BUFFER POOL AND MEMORY section of the output from the innodb monitor: -- BUFFER POOL AND MEMORY -- Total memory allocated 152389988; in additional pool allocated 1048576 Buffer pool size 8192 Free buffers 0 Database pages 7947 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 20345325, created 9857, written 763089 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 Why does it say the buffer pool size is only 8M? Shouldn't it be 128M? Also, could someone explain the hit rate? I remember seeing in someone's recent post that the 1000/1000 is good, but I don't know what that means. Can someone suggest a good resouce that explains the contents of Innodb show status in detail. The page on www.mysql.com gives a very cursory overview of the output. Cheers, Tripp __ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25¢ http://photos.yahoo.com/ph/print_splash -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
key_buffer_size and INNODB
Is the key_buffer_size server variable useful for tuning databases that only have innodb tables or do I need to use the innodb_buffer_pool_size variable for this? Thx, Tripp __ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25¢ http://photos.yahoo.com/ph/print_splash -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble Tuning SLOW query
Howdy all,
I've got a query which selects the count all listings
that meet meet a customer's search criteria. In
addition, there is a sent table that keeps track of
which listings have already been sent to that
customer. There are roughly 30,000 listings and 1.2
million sent records. As is stands right now, the
query can take as long as 15 seconds to run, which
seems like a ridiculously long time.
CREATE TABLE listing (
listing_id int(11) NOT NULL default '0',
mls_id int(11) NOT NULL,
price decimal(11,2) default NULL,
property_type varchar(50) default NULL,
city varchar(30) default NULL,
subdivision varchar(30) default NULL,
area varchar(20) default NULL,
tot_finished_sqft int(11) default NULL,
sqft int(11) default NULL,
bedrooms varchar(20) default NULL,
baths varchar(20) default NULL,
garage_spaces int(11) default NULL,
year smallint default NULL,
PRIMARY KEY (mls_id, listing_id),
KEY idx_price (price),
KEY idx_city (city),
KEY idx_area (area),
INDEX idx_tot_sqft (tot_finished_sqft),
INDEX idx_sqft (sqft),
INDEX idx_mls (mls_id),
FOREIGN KEY (mls_id) REFERENCES mls(mls_id)
) TYPE=INNODB;
CREATE TABLE sent (
customer_id int(11) NOT NULL,
pref_id int(11) NOT NULL,
listing_id int(11) NOT NULL,
mls_id int(11) NOT NULL,
office_id int(11) NOT NULL,
price decimal(11,2) NOT NULL default '0.00',
sent_dt datetime NOT NULL,
PRIMARY KEY (customer_id,pref_id,listing_id,
mls_id,price,sent_dt),
INDEX idx_price (price),
INDEX idx_customer(customer_id),
FOREIGN KEY (customer_id) REFERENCES
customer(customer_id),
INDEX idx_listing(listing_id, mls_id),
INDEX idx_office(office_id),
FOREIGN KEY (office_id) REFERENCES
office(office_id),
INDEX idx_pref(pref_id),
FOREIGN KEY (pref_id) REFERENCES
customer_listing_pref(pref_id),
) TYPE=INNODB;
Here's the query:
SELECT COUNT(listing.listing_id) AS listing_count FROM
listing LEFT JOIN sent ON sent.customer_id = 28080 AND
sent.pref_id = 28483 AND sent.office_id = 12 AND
sent.listing_id = listing.listing_id AND sent.mls_id =
listing.mls_id AND sent.price = listing.price WHERE
sent.listing_id IS NULL AND listing.price <= 25.0
AND listing.price >= 89000.0 AND city IN ('Arvada')
AND area IN ('JNC','JFN') AND tot_finished_sqft >=
1000 AND baths >= 2.0 AND bedrooms >= 4 AND
garage_spaces >= 0 AND property_type IN ('RES') AND
year <= 32767 AND baths <= 99.0 AND bedrooms <= 99 AND
sqft <= 999 AND ns <= 999 AND ew <= 999;
the explain shows that a range scan is being performed
on the listing table and that sent join type is ref.
The idx_city index is being used to lookup listings.
and the idx_listing index is being used on the sent
table. I wanted to include more of the explain but it
wasn't very readable.
Any help speeding this query up would be greatly
appreciated.
Thanks,
Tripp
__
Do you Yahoo!?
Yahoo! Tax Center - File online by April 15th
http://taxes.yahoo.com/filing.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REPLACE query
I ran into this problem when I installed 4.0.18. All
of the tables in my database are INNODB and the
REPLACE statement was failing on tables that had
foreign key constraints. I just rolled back to 4.0.16
and the problems went away. Not much of a solution,
but it's buying me a little time. Will I have to get
rid of all of the REPLACE INTO statements and replace
them with INSERT/UPDATE statements or is there some
configuration setting that needs to be changed to make
it work?
Cheers,
Tripp
--- Victoria Reznichenko
<[EMAIL PROTECTED]> wrote:
> Kevin Carlson <[EMAIL PROTECTED]> wrote:
> > I have a table with four columns, the first three
> of which are combined
> > into a unique key:
> >
> >
> > create table Test {
> > cid int(9) NOT NULL default '0',
> > sid int(9) NOT NULL default '0',
> > uid int(9) NOT NULL default '0',
> > rating tinyint(1) NOT NULL default '0',
> > UNIQUE KEY csu1 (cid,sid,uid),
> > KEY cid1 (sid),
> > KEY sid1 (sid),
> > KEY uid1 (sid),
> > } TYPE=InnoDB;
> >
> >
> > I am using a REPLACE query to insert a row if it
> doesn't exist and
> > replace an existing row if one does exist:
> >
> > REPLACE into TEST (cid, sid, uid, rating) values
> (580, 0, 205, 1)
> >
> > In the case of this particular row, a row already
> exists with the
> > concatenated key of 580-0-205 and I am getting a
> duplicate key error. I
> > thought REPLACE was supposed to actually replace
> the contents of the row
> > if one exists. Does anyone have any ideas as to
> why this would be
> > causing a duplicate key error?
> >
>
> Works fine for me. The above CREATE TABLE statement
> has some syntax errors. What exactly does CREATE
> TABLE look like?
> What version of MySQL do you use?
>
>
> --
> For technical support contracts, goto
> https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.net
> http://www.ensita.net/
>__ ___ ___ __
> / |/ /_ __/ __/ __ \/ /Victoria Reznichenko
> / /|_/ / // /\ \/ /_/ / /__
> [EMAIL PROTECTED]
> /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
><___/ www.mysql.com
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
>
http://lists.mysql.com/[EMAIL PROTECTED]
>
__
Do you Yahoo!?
Yahoo! Tax Center - File online by April 15th
http://taxes.yahoo.com/filing.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User variables + SUM + GROUP BY = strange behavior
Vadim, if I'm not mistaken, you can't set a variable then use it in the same statement. See http://dev.mysql.com/doc/mysql/en/Variables.html A little ways down the page... "The general rule is to never assign and use the same variable in the same statement." -- Tripp --- "Vadim P." <[EMAIL PROTECTED]> wrote: > > Sorry, the message got garbled, here is a more > digestible look: > > -Original Message- > > Hello all, > > Could anyone comment on User Variable behavior in > the example below? > > Thanks, > Vadim. > > = > > mysql> SELECT > ->LEFT(CallTime,10) AS CallDate, > ->@a := SUM(Charge), > ->@b := SUM(Cost), > ->@a - @b, > ->@a, > ->@b > -> FROM Calls > -> GROUP by CallDate > -> ORDER BY CallDate DESC; > > ++--++-++- > | CallDate | @a:= SUM(Charge) | @b:= SUM(Cost) | > @a - @b | @a | @b > ++--++-++- > ... > | 2004-03-01 | 621.059 |249.310 | > 30.882 | 39.512 | 8.63 > | 2004-02-29 | 54.620 | 17.660 | > 30.882 | 39.512 | 8.63 > | 2004-02-28 | 205.581 | 17.460 | > 30.882 | 39.512 | 8.63 > | 2004-02-27 | 622.282 |248.920 | > 30.882 | 39.512 | 8.63 > | 2004-02-26 | 607.274 |277.100 | > 30.882 | 39.512 | 8.63 > | 2004-02-25 | 709.698 |308.580 | > 30.882 | 39.512 | 8.63 > | 2004-02-24 | 783.210 |298.560 | > 30.882 | 39.512 | 8.63 > | 2004-02-23 | 799.764 |252.890 | > 30.882 | 39.512 | 8.63 > ... > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: security reason for not using load data infile local?
Ginger, can't speak to the log file issue but check out this link for the dynamic server variables: http://dev.mysql.com/doc/mysql/en/Dynamic_System_Variables.html Best O'luck, Tripp --- Ginger Cheng <[EMAIL PROTECTED]> wrote: > Hello, MySQL gurus, > Sometimes the 'local' option of 'load data infile' > is disabled for > security reasons (that is what I got from web). What > could be the security > problem? I have another question, is there any way > to change a variable of > mysql server without shutting it down? > Thank you for help > ginger > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too many server instances
Howdy all, I am having trouble configuring my server parameters with my.cnf because there seems to be several mysql server instances (mysqld processes) running on my linux box. What I would like to do is bump up the innodb_buffer_pool_size on the server to 512M (the box has 3GB of RAM). The problem is that there seem to be several instances of mysql server running concurrently on the box. Each one of them allocates 512M for the buffer pool and the box grinds to a halt, completely out of RAM to do anything! I've included a snippet of the top command display (I hope that you can read it easily). PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM CTIME CPU COMMAND 18913 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:01 3 mysqld 18914 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:00 1 mysqld 18915 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:01 1 mysqld 18916 mysql 15 0 30160 29M 2944 S 0.0 0.9 83:48 0 mysqld 18917 mysql 15 0 30160 29M 2944 S 0.0 0.9 6:25 1 mysqld 18918 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:00 3 mysqld 18919 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:32 0 mysqld 18920 mysql 15 0 30160 29M 2944 S 0.0 0.9 12:05 1 mysqld 18921 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:00 3 mysqld 31277 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:24 3 mysqld 31807 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:00 0 mysqld 31808 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:02 0 mysqld 32211 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:00 0 mysqld 32212 mysql 15 0 30160 29M 2944 S 0.0 0.9 0:00 2 mysqld In windows when I run mysql as a service there is only one mysql process running. The configuration settings have proven to work quite well there because there is never more than one mysql process. So, how do I have only one instance of the server running at any given time under linux? I'm starting mysql using the mysqld_safe script through the command: service mysql start I'm pretty new to linux so perhaps that is where my problem lies. Any ideas as to what I'm doing incorrectly? Thanks, Tripp __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
