Copying DB to new structure
We have a moderately sized database, more than 5GB in size, several million rows and 70 tables. We're running MySQL 5.22 and the database uses innodb throughout with multiple foreign keys in use. During development the structure of several tables has been changed many times, such that we now have a number of rendundent columns. We've created a new, empty database with our proposed new structure and I'm now looking for the most efficient way to get our existing data into this new structure, dropping any data in columns that no longer exist. Can anyone propose a sensible way to go about this? Because we're using innodb, dropping columns one at a time takes an age as every index is rebuilt. Just laoding the database from a mysqldump file takes about five hours so I'm pretty sure we'll want to load data from our old database into the new db with the new structure - if anyone can recommend a strategy to do that, or suggest an alternative, I'd be most appreciative! Thanks, Russell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
**** Probably Spam (6.1) **** Slow performance - any suggestions?
Spam detection software, running on the system marmalade.snp.org, has identified this incoming email as possible spam. The original message has been attached to this so you can view it (if it isn't spam) or label similar future email. If you have any questions, see [EMAIL PROTECTED] for details. Content preview: Hi, I'm seeing what to me appears to be very slow performance when inserting/ updating / deleting into a 15M row innodb table. I'm using mysql 5.0 on Solaris 10 with 8GB RAM. MySQL is compiled 32bit so has 4GB available. [...] Content analysis details: (6.1 points, 5.2 required) pts rule name description -- -- 0.1 FORGED_RCVD_HELO Received: contains a forged HELO 1.1 SPF_FAIL SPF: sender does not match SPF record (fail) [SPF failed: Please see http://www.openspf.org/why.html?sender=mysqllists%40albanach.comip=65.40.219.158receiver=localhost] 0.9 J_CHICKENPOX_44BODY: 4alpha-pock-4alpha 0.1 TW_HK BODY: Odd Letter Triples with HK -0.2 BAYES_40 BODY: Bayesian spam probability is 20 to 40% [score: 0.2484] 2.0 RCVD_IN_SORBS_DUL RBL: SORBS: sent directly from dynamic IP address [65.40.219.158 listed in dnsbl.sorbs.net] 1.9 RCVD_IN_NJABL_DUL RBL: NJABL: dialup sender did non-local SMTP [65.40.219.158 listed in combined.njabl.org] ---BeginMessage--- Hi, I'm seeing what to me appears to be very slow performance when inserting/ updating / deleting into a 15M row innodb table. I'm using mysql 5.0 on Solaris 10 with 8GB RAM. MySQL is compiled 32bit so has 4GB available. The table structure is as follows: f_id (int6) | v_id (int9) The primary key is an index on f_id and v_id There's also an index on v_id An insert of about 50,000 rows takes about 15 seconds which seems long - I'd have expected most of this to be happening in memory. A small sql script that reads and updates the table shows the following output when run with time from the command line: real0m53.457s user0m0.035s sys 0m0.038s Any suggestion why the real time is so much higher than user sys time? Below is the output from show innodb status and the innodb section from my.cnf As indicated in the my.cnf file below, My innodb data is all in its own partition loaded with forcedirectio Thanks for any suggestions! Russell = 060829 0:10:46 INNODB MONITOR OUTPUT = Per second averages calculated from the last 8 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 1534, signal count 1513 Mutex spin waits 212, rounds 20947, OS waits 101 RW-shared spins 22911, OS waits 529; RW-excl spins 47142, OS waits 904 TRANSACTIONS Trx id counter 0 74514 Purge done for trx's n:o 0 74512 undo n:o 0 0 History list length 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 11 MySQL thread id 3, query id 19 localhost root SHOW INNODB STATUS FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 1220 OS file reads, 177 OS file writes, 89 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, is empty Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 7470761, used cells 371755, node heap has 375 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 3 1075228472 Log flushed up to 3 1075228472 Last checkpoint at 3 1075228472 0 pending log writes, 0 pending chkp writes 61 log i/o's done, 0.00 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 2109874514; in additional pool allocated 5845504 Buffer pool size 115200 Free buffers 112987 Database pages 1838 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 1320, created 518, written 1206 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread id 9, state: waiting for server activity Number of rows inserted 114100, updated 1, deleted
Innodb import tuning on Sun T2000
Folks, I'm trying to import a sql dump of a database which is taking an age. I've disabled foreign key constraints, unique checks and set autocommit to 0 but this is still slow. My data file has a number of tables, one of which has circa 3.5 million tuples taking up about 500MB of data with 900MB of indexes. This seems to be where we are slowing down. Most the other tables are much smaller. The server is a Sun T2000 with 6 cores and 8GB of RAM. We're using the local disks. I'm using the mysql.conf file from http://media.zilbo.com/img/feh/mysql/my.cnf though I've increased innodb_buffer_pool_size to 3G Is there anything else I can do to speed up these operations, or should I resign myself to the import taking several hours each time it's required? TIA, Russell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Looking for advice on how to store and query some data
Hi, I'm storing data against a bunch of people and want to track how it changes. So, I have a person table where everyone has a person ID and a results table a bit like this: | personID | classification | date | | 1| 0 | 2005-11-10 | | 2| 3 | 2005-11-10 | | 3| 1 | 2005-11-10 | | 4| 0 | 2005-11-10 | | 1| 3 | 2005-12-01 | | 4| 2 | 2005-12-03 | | 1| 2 | 2005-12-23 | | 5| 1 | 2006-01-03 | | 2| 2 | 2006-12-03 | This lets me see how things change as a pattern, for example comparing a a SELECT classification WHERE DATE '2006-01-01' GROUP BY classification and comparing it to: SELECT classification WHERE DATE = '2006-01-01' AND DATE = '2006-01-31' GROUP BY classification But is there a way I can select every personID's most recently expressed preference? I hope this makes sense - in the table above, person 1 had a classification of 0 at 10th November, but this changed to 2 on 23rd Decembner. Can I write a query to select personID once together with their latest preference, or indeed their preference as expressed at a specific point in time? Thanks as ever for any suggestions. Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table comments truncated at 80 characters
Since upgrading from 4.x to 5.x we've found table comments to be truncated at 80 characters. Is this a configurable option somewhere and I just can't find it in the manual? Thanks, Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select records added in last hour
I have a table containing a timestamp field, `insert_time` The manual is clear when it comes to selecting records from the past week or month, I can use: SELECT COUNT(*) FROM statistics WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) = `insert_time`; What I am less sure about is how I would select records that had been inserted to thee table in the previous hour. I understand the date functions will ignore the hh:mm:ss - will the time functions ignore the date? Can anyone suggest what function I need for this select? Thanks, Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select records added in last hour
On Thu, 2006-01-19 at 11:49 -0800, Devananda wrote: Rather than the CURDATE() function, just use NOW(). This is perfect. Thanks. Russell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Matching phone numbers to addresses
I have a couple of tables like so: FName FInitial SName SName Address1Address1 Address2Address2 Address3Address3 Zip Zip Phone I need to match the phone numbers to the addresses in table 1. Trouble is, Table 1 has data like: Joe, Bloggs, 25 Frontier St,, BigTown, 12345 Mary, Bloggs, 25 Frontier St,, BigTown, 12345 James, Bloggs, 25 Frontier St,, BigTown, 12345 Table 2 would only have Joe, Bloggs, 25 Frontier St,, BigTown, 12345, (111) 555-1234 So... I need to get any matches on table 1 and then populate them to the rest of the family at the address. So in the example above, Mary would also get a phone number - I only have a first initial in table 2, so that would hot Joe and James. Any thoughts on a strategy to do this? If I need to go outside MySQL to do some programming I'd be using php5. Thanks for any pointers. Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Combining tables
I'm having difficulty writing a query as follows. I'm unsure if I need a subquery, a union or if this isn't actually possible without using temporary tables. I have two tables for members. Table 1 (members) Table 2 (payments) +--++ +-+--+ | memno| group | | memno | payment_type | +--++ +-+--+ |1 | a | | 1 | cash | |2 | b | | 2 | cash | |3 | a | | 3 | creditcard | |4 | a | | 4 | check| |5 | c | | 5 | creditcard | ... ... I'd like a query that returns the total number in each group, together with the number paying by credit card. Obviously I can build two queries, and use a temporary table, but is there a way to get a table like that below in a single query? +--++--+ | group| members | pay_by_card | +--++--+ |a | 5| 3 | |b | 26 | 18 | ... This will be using MySQL 5 if that helps Thanks! Russell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Combining tables
Thanks for the two incredibly fast responses, they were perfect - problem solved. Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Joining tables - restricting selected records
This must have come up before, but I've not found it using a google search. I have two tables customer and purchases customer: customerID customerName purchases: purchaseID customerID purchaseDate purchaseValue Is it possible in MySQL to join the tables so I only get the value of the latest purchase? Or is this something that's better done in PHP, say select all my customers and then one at a time do a query to select the value of their last purchase? Thanks! Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cumulative Totals
I have a pretty simple table with a list of payments, not much more than: paymentID | amount | paymentDate 1 | 123| 2005-01-10 2 | 77 | 2005-01-13 3 | 45 | 2005-02-16 4 | 13 | 2005-02-17 I can get totals per month using a query like: SELECT SUM(amount) , DATE_FORMAT( `paymentDate` , '%Y-%m' ) FROM `payments` GROUP BY DATE_FORMAT( payments . date , '%Y-%m' ) That would give me: amount | paymentDate 200| 2005-01 58 | 2005-02 Is there any way to get a running cumulative total directly from mysql? Something like: amount | paymentDate 200| 2005-01 258| 2005-02 Thanks, Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: turning off binary logging
I just found that mysql 4.0.18 is doing binary logging. How can I turn this off? SNIP I do not see a nead for this, plus I fear that it might fill up the file system, plus I think it is reducing performance. The binary log is primarily there to let you restore data. Say you backup every night at 04:00 and your database crashes at 15:00 you can restore from your backup but what about all those changes in the 11 hours after the backup was made? Instead you can use mysqlbinlog to run all the changes made in those 11 hours and get your data back to pretty much exactly where it was before the crash. According to the manual, the performance hit is about 1% - that's peanuts in exchange for the ability to recover your data. The other function of the binary log is to store statements that will be replicated on slave servers. That might not be relevant to you at the moment, but will perhaps be something you need later? If you really want to disable it, read the manual page at: http://dev.mysql.com/doc/mysql/en/Binary_log.html Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Set Password [SOLVED]
The man page says /usr/bin/mysqladmin -u user -p somepassword Not here it doesn't. My man page says: mysqladmin [-#|--debug= logfile] [-f|--force] [-?|--help] [--character-sets-dir=directory] [-C|--compress] [-h|--host=[#]] [-p[pwd]] [--password=[pwd]] [-P|--port= pnum] [-i|--sleep= sec] [-E|--vertical] [-s|--silent] [-S|--socket= #] [-r|--relative] [-t|--timeout= #] [-u|--user= uname][-v|--verbose][-V|--version] [-w|--wait[=retries]] Which means you would user either: /usr/bin/mysqladmin -uuser -psomepassword or /usr/bin/mysqladmin --user=user --pass=somepassword Both of which work from my command line. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select inside a subquery
I ahve a problem with updating a row using a field from another row in the same table. The mySQL manual says: Error 1093 (ER_UPDATE_TABLE_USED) SQLSTATE = HY000 Message = You can't specify target table 'x' for update in FROM clause This error will occur in cases like this: UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1); It's okay to use a subquery for assignment within an UPDATE statement, since subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table, in this case table t1, for both the subquery's FROM clause and the update target. So what is the best way to do something like UPDATE accounts SET balance = balance + (SELECT balance FROM accounts WHERE userID = 100) WHERE userID = 101 Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update using fields from another table
I haev three tables: Table 1 id_2 | date Table 2 id_1 | id_2 Table 3 id_1 I want to set the table1.date = '2004-03-18' for each record in table3. Any ideas? Thanks, Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Bash script to MySql
Why not use another language that has mysql hooks built in? For example, from version 4.3.0 PHP comes with SAPI to allow scripting from the command line, ideal for cron style tasks and it's mysql support is very mature. -- Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL question: Finding duplicates
row | foo 1 | a 2 | c 3 | b 4 | c 5 | a 6 | d the statement would return me rows 1, 2, 4, and 5. CREATE TEMPORARY TABLE temptable SELECT * FROM test GROUP BY foo HAVING COUNT( * ) 1 ORDER BY foo ASC ; SELECT * FROM test, temptable WHERE test.foo = temptable.foo ORDER BY test.row ASC; -- Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Advise on High Availability configuration
Andrew Braithwaite wrote: Each slave keeps a heartbeat to the master and in the event of a failure, changes it's master to master2. So how does this bit work? If one master falls over and slaves move to master two, how do you rebuild master one without downtime? Don't the slaves try and use Master 1 as soon as it's back online even though its data is out of sync? What if one of the slaves loses its heartbeat with master one and falls over to master two when other slaves can still see master one. Don't you then have inconsistencies in your data? Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can't connect from localhost using 4.1.1 alpha
Also, if I (mysql -u root -p root) from localhost, then it prompts me for a password. I enter password 'root' and then I get this error: That's what's supposed to happen: You use ~ mysql -u username -p database_name Then you are prompted for the password for root and it will start in database 'database_name' If you want to enter the root password on the command line use: ~ mysql -u username -prootpass database_name Where root pass is the root password. Note no space after the -p If you don't want to select a database right away, just leave off database_name from either of those two commands. Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
