Re: mysqldump --single-transaction causes database to become unusable
Hi , --single-transaction will execute the same nature of mysqldump command with begin and end transaction. How ever the table is locked for the backup your site may be slow. -- Praj Ian P. Christian wrote: Recently my one and only slave went down, and stupidly I don't have a dump suitable for reseeding (is that's the right term...) the slave, so need to make a snapshot of the master database again. This time I'll make sure I keep this datafile for future restores should I need to - you live and learn. So... I'm doing a database dump: mysqldump --master-data --single-transaction database dump.sql This database I'm dumping has something like 17 million rows, all but 1 table (which uses FULLTEXT, and only has 3-4k rows) run innodb. There is only one table of any real size, and this table has all but about 100k of the total rows in. My understanding of this command is that the database should not be locked whilst this command is running. However, here's my problem... When the dump starts to read from large table, the database just grinds to a halt - my website running from the database just stops, and the dump (which I was watching progress with a privative `watch ls -la`) slows down a bit. Last time I had to do this (for the first 'seeding' of my slave), I eventually gave up trying to dump from the database whilst the site remained live, and took the site down for 15 minutes whilst the dump ran. As I'm sure you'll understand I'm not too keen on taking the website down again. Any suggestions as to why my database is stopping (could be I/O related maybe? it's on a good RAID setup though), and what I could do about it? Many Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: optimizing mySQL
Also, when a query takes too long I kill it by shutting down the server, is this safe or is there a better alternative (MyISAM tables)? Use show processlist to see the query that taking more time and use kill processid; for terminating the query . -- Praj Surendra Singhi wrote: Hello Chris, Chris [EMAIL PROTECTED] writes: Surendra Singhi wrote: I am using mySQL 5.0 and I have 2 tables with few hundred millions of records. To optimize things, I am using MyISAM tables, using the smallest possible data type and have set indexes. Now, the problem which I am facing is that mySql process is wasting lot of time in disk access the CPU and memory utilization is very low. Most of the table access are simple queries, with very few write operations. What can I do optimize things and make queries faster? My CPU is 64bit Athlon 3000, with 950 MB of RAM running Ubuntu Edgy Eft. I don't mind making mysql eat lot of CPU and RAM. Just want things to be much faster, and loaded into memory instead of slow disk access. The my.cnf file contains: key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K query_cache_limit = 1048576 query_cache_size= 16777216 query_cache_type= 1 [isamchk] key_buffer = 16M I am considering increasing these values, please advise on what should I set them. Also, any other tips will be extremely helpful. Completely depends on the queries you are running and the context of which they are run. A query that runs once a day won't matter if it's slow, a query that runs every time you do something will matter a lot. This page might give you some ideas: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ Enable the slow query log in mysql and triple check that you have the proper indexes in place. Thanks a lot, this link was indeed helpful, and I think I am able to optimize the database much better now. I have an update operation where I am able to update 10 million records in approx 2.5 mins. But when I tried to do the same update on say 40-50 million records, mysql takes forever to finish. Its the same table, and same update operation, i am just changing the range of ids using where clause. Why might this be happening, and how to solve this? Also, when a query takes too long I kill it by shutting down the server, is this safe or is there a better alternative (MyISAM tables)? Thanks.
Re: adding columns to a large table
There must be some thing went wrong with your settings in my.cnf file.For an 3 million records 24 hrs is bad :( |myisam_sort_buffer_size value in my.cnf ? Also you can apporx check the status by doing file size check on the table in database dir and the temoprory table created in the data base dir . -- Praj |Helen M Hudson wrote: let you know shortly or I have a table with about 3million rows and I'm having trouble altering it to add 4 more columns. I'm using the MySQL Administrator and I've left it more than 24hours and its still not finished. I was going to try using a login to the console next, but is there a better way? Any advice much appreciated! Helen
Re: pkgadd error
Not sure iam right . Try without gunzip 'ing the pkg . zehra cagnan wrote: Hi all, I am trying to install mysql-standard-5.0.24. My OS is Sun Solaris 9. I down loaded the binary package mysql-standard-5.0.24-solaris9-sparc.pkg.gz from your web site. I can unzip the file with no problem using gunzip. The file is at /var/spool/pkg. While in this directory I am typing the command: pkgadd mysql-standard-5.0.24-solaris9-sparc.pkg I keep getting the error: pkgadd error no packages were found I tried to save the .pkg file in another directory and to specify the full path to the file, I tried using pkgadd -d command instead. But I keep getting the same error message. Any suggestions about what might be the problem? Thanks Zehra -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to delete all rows....
If its one time .. i will suggest to take a mysqldump of table structure only and drop the database import the backup . [EMAIL PROTECTED] wrote: Hi All, How do I delete all the rows of all the tables(but not table) in the database at one shot. Regards, Ravi K 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 proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how can I access to mysql fro moutside?
U can chk firewall by doing a telnet to the remote host on 3306 ( default ) port . Error message looks like username/password might be wrong . [EMAIL PROTECTED] wrote: hi, I'm trying to access to mysql on dedicated web server (hosted by hosting company) using MySQL Administrator. Right now I'm getting error message 2003 (Can't connect to the server...). On users table I can see in user column 'root' and 'admin' as users and both have for host 'localhost' (root has tough localhost.localdomains too) After some googling I think I have to create a user (e.g. 'admin2') and grant: mysql GRANT ALL PRIVILEGES ON *.* TO 'admin2'@'123.123.123.%' - IDENTIFIED BY 'some_pass' WITH GRANT OPTION; (123.123.123.% means I'll access from IPs that start with 123.123.123 right?) mysql FLUSH PRIVILEGES; Is this all? Do I have to do something else? I do have to restart mysql server, right? Back to top: right now, when I try to access, I'm getting error message 2003. Since I didn't get error Access denied for [EMAIL PROTECTED], I think, even I have correct user/pass, someting else will stop me (firewall, 3306 is blocked,...), right? How can I check this? Thanks for any help. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how can I access to mysql fro moutside?
Okie there might be a entry in mysql.user table having username and password column as blank . That might result in Access denied when u use password . Try this , use mysql ; select * from user where user=''; if so , Delete those entries . [EMAIL PROTECTED] wrote: I can connect using Putty to the server using port 22. When I try to connect to the server using port 3306 and got Connection timed out? Does it mean the port 3306 is blocked? -afan U can chk firewall by doing a telnet to the remote host on 3306 ( default ) port . Error message looks like username/password might be wrong . [EMAIL PROTECTED] wrote: hi, I'm trying to access to mysql on dedicated web server (hosted by hosting company) using MySQL Administrator. Right now I'm getting error message 2003 (Can't connect to the server...). On users table I can see in user column 'root' and 'admin' as users and both have for host 'localhost' (root has tough localhost.localdomains too) After some googling I think I have to create a user (e.g. 'admin2') and grant: mysql GRANT ALL PRIVILEGES ON *.* TO 'admin2'@'123.123.123.%' - IDENTIFIED BY 'some_pass' WITH GRANT OPTION; (123.123.123.% means I'll access from IPs that start with 123.123.123 right?) mysql FLUSH PRIVILEGES; Is this all? Do I have to do something else? I do have to restart mysql server, right? Back to top: right now, when I try to access, I'm getting error message 2003. Since I didn't get error Access denied for [EMAIL PROTECTED], I think, even I have correct user/pass, someting else will stop me (firewall, 3306 is blocked,...), right? How can I check this? Thanks for any help. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myisam primary key with innodb primary key..
Hi Lakshmi , I guess both treated same . But physical structure of storage is different from Myisam and Innodb . Correct me if iam wrong . Lakshmi wrote: Hi, I want to know is there any difference between myisam primary index vs innodb primary index... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find the no of Inserts and selects
mysql show status like 'com_update'; +---+---+ | Variable_name | Value | +---+---+ | Com_update| 0 | +---+---+ 1 row in set (0.00 sec) mysql show status like 'com_insert'; +---+---+ | Variable_name | Value | +---+---+ | Com_insert| 0 | +---+---+ 1 row in set (0.00 sec) mysql show status like 'com_select'; +---+---+ | Variable_name | Value | +---+---+ | Com_select| 32| +---+---+ 1 row in set (0.00 sec) Ratheesh K J wrote: Hello all, I need to find out to some point of accuracy the ratio of Number Inserts/Updates to that of Selects. This is because we are thinking of setting up a Replication sysytem with one master and one slave. Now replication would be optimal only if there if the Select queries are dominating, so how would I determine this figure? Ratheesh Bhat K J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row count discrepancy when converting from MyISAM to InnoDB
Yes your right dilip , but it wont help for INNODB . INNODB , Rows ( show table status\G ) value is an approximation, and may vary from the actual value .Since innodb doesnt keep track on record count For innodb use |SELECT COUNT(*)| to obtain an accurate count.Correct me if iam wrong - Praj Dilipkumar wrote: Hi, Instead of using select count(*) from tablename You can try with show table status like 'tablename' This doesn't takes much longer time. Thanks Regards Dilipkumar - Original Message - From: David Hillman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 25, 2006 11:51 PM Subject: Re: Row count discrepancy when converting from MyISAM to InnoDB On Jul 25, 2006, at 11:55 AM, Frank wrote: Why is the record count so low after conversion to InnoDB? Who should I believe: InnoDB or MyISAM? Any ideas as to what can be done to avoid loss of this many rows? InnoDB doesn't keep a count on number of rows, like MyISAM does. InnoDB only maintains an estimate of the number of rows in each table. This is why select count(*) from table takes a long time on big InnoDB tables. Usually the InnoDB count will be off by 50% or so. http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html -- David Hillman LiveText, Inc 1.866.LiveText x235 ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch the latest updates on Mumbai, with video coverage of news, events, Bollywood, live darshan from Siddhivinayak temple and more, only on www.mumbailive.in Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com
Re: Incremental Backup
Kaushal, Do a flush logs when you do full backup , And copy your Binary log files ( After Full Backup ) for incremental . Correct me if iam wrong . -- Praj Kaushal Shriyan wrote: Hi ALL Can we have incremental backup of MySQL Database and if its there How do we do it Thanks and Regards Kaushal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE
Do chmod -R 755 on datapath Thanks Praj - Original Message - From: Jay Paulson (CE CEN) [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, January 11, 2006 10:47 PM Subject: LOAD DATA INFILE I'm creating a file via PHP after getting information from a log file. I create a new file for data import into a table in MySQL. For some reason now I'm getting this error below. I have no clue what it means. I've checked the file and it is all there and I've even changed the permissions on it so that anyone can do anything with it, but still no luck. Any ideas on what I could do? Can't get stat of './import_file.txt' (Errcode: 13) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: checking for corruption?
Hi , Alternate solution : Do show table status on that database . In comment field you can find info about corrupted table . This will be faster than check table . Thanks Praj - Original Message - From: N.J. Thomas [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 10, 2006 9:00 PM Subject: checking for corruption? After a power outage, we had a large table (~2GiB, 23e6 records) get corrupted. Only a few rows were actually affected, so nobody noticed for a few weeks though. A repair fixed the problem. We are now considering running the check table command regularly (every 15 minutes?) and plugging that into our monitoring system, i.e. if check table reports an error, our script beeps/emails someone. My question is this: is it okay to run check table frequently? What impact will it have on production systems? How quickly will it check very large tables? Is there an alternative (better) solution? thanks, Thomas -- N.J. Thomas [EMAIL PROTECTED] Etiamsi occiderit me, in ipso sperabo -- 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]
Re: Mysql database capacity
Sujay , Tables : 1 (only 1) ::: Innodb or Myisam ? I see lots of updates happening on that table so , how frequent do you defragment the table . -- Thanks Praj On Mon, 9 Jan 2006 01:46:39 -0800 Sujay Koduri [EMAIL PROTECTED] wrote: I am giving the system configuration which we are using to run MySQL. 2-CPU 4G RAM, SAN filesystem. MySQL version : 4.14 (INNODB) OS : RHEL - 3 Amount of Data : 200G No of Rows : 278 million approximately (Every day 2.5-3 million rows gets added) Transaction rate : 300-400 reads/sec, 110-120 updates/sec, 80 inserts/sec Tables : 1 (only 1) This mysql is handling comfortably. So I think this info might help you to get a good idea about planning your system resources. I am not very much sure about the maximum rows/data it can support, but I am sure it can easily handle a billion of rows in a single table. Regards sujay -Original Message- From: vishwas kharajge [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 3:05 PM To: mysql@lists.mysql.com Subject: Mysql database capacity Hi all I am working with startup company. Have some quries about Mysql 1. What is the maximum database storage capacity of mysql 2. What is the maximum row capacity? 3. How much time it will take to search the record if there are consider more than 1 billion rows in a table 4. How many records can i store in a single table. Please help me. Thanks in advance Vishwas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks Praj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
