MYSQL Database Alteration, Repair And Restore Takes Huge Time
I am trying to run alteration queries on one of my mysql tables which has more then 22,00,0000 lakh records. Its been 23 hours and the process is still running (I have to close all the sites running on server due to same).
My server specifications:
Red Hat 9.0
Pentium 4 3.0 GHz
2 GB Ram, Burst RAM 5 GB
Running webserver, mail server as well.
Is there a way I can view the minute process details, as what table record is being updated ?
View Complete Forum Thread with Replies
Related Forum Messages:
Query Takes A Long Time
On my site I have a query that searches through 1,7 million. The php-file with the query takes a bit of time to load. Is it possible to show some sort of progress-bar during this time?
View Replies !
Results In Multiple Pages, Takes Too Much Time
I have a table of a million records and wrote a CGI-PERL script to display the results based on the user input. The results might be anywhere from 100 to 1000 per query and presently I am displaying them as 25 results per page. Problem: Each query is taking about 20-30 seconds. My solution: I have tried to optimize the table and also index the table. I have actually converted a MS access database to SQL database, so it wasn't previously indexed. Both optimization and indexing doesn't give any good results. I always get a timeout. ie. it takes longer after indexing and optimizing. 1. I was wondering if someone has a creative solution for this. ie. reduce the time from 20-30 seconds to atleast 10 seconds. 2. I have links of pages of results beneath the first page result. When each of these links are clicked it takes 20-30 seconds again. Is there a way I can reduce the time taken for the subsequent pages are reduced? I cannot use the LIMIT option in mysql, since I have a where clause which has to search through the whole table. I tried using views and using limits, but it takes as much time.
View Replies !
JDBC Result Closing Takes Time
i am reading 1000 Elements from a database (500.000 all together). The problem is reading the data takes only a few miliseconds but closing the ResultSet takes another 22 seconds. My Hard Disk tells me that the ResultSet is probably running to the end of my table row by row. Is there a way to tell mysql not to do this ? Statement tempStmnt = aConnection.createStatement(); tempStmnt = aConnection.createStatement( java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); tempStmnt.setFetchSize(Integer.MIN_VALUE); Date tempStart = new Date(); ResultSet tempRs = tempStmnt.executeQuery("SELECT * FROM ARTIKELSTAMMDATEN"); int tempXx = 0; while (tempRs.next() && tempXx++ < aCount) { tempRs.getString("artikelBezeichnung"); }
View Replies !
Easy SELECT With OR Takes A Very Long Time
We've got a database with about 1000000 books. A query in the table BOOKS for the TITLE 'java' is very fast. We have a fulltext index on the column TITLE. However, if we want to do a exact same query and include the rule that the book with ISBN '0131016210' always should be included - then the query take several seconds to finish: SELECT * FROM C_BOOK WHERE MATCH(NAME) AGAINST ('java') AND (ISBN LIKE '0%' OR ISBN LIKE '1%') OR ISBN = '0131016210' ISBN is the PRIMARY KEY. Is there anything I can do about this?
View Replies !
JDBC Result Closing Takes Ists Time
i am reading 1000 Elements from a database (500.000 all together). The problem is reading the data takes only a few miliseconds but closing the ResultSet takes another 22 seconds. My Hard Disk tells me that the ResultSet is probably running to the end of my table row by row. Is there a way to tell mysql not to do this ? Statement tempStmnt = aConnection.createStatement(); tempStmnt = aConnection.createStatement( java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); tempStmnt.setFetchSize(Integer.MIN_VALUE); Date tempStart = new Date(); ResultSet tempRs = tempStmnt.executeQuery("SELECT * FROM ARTIKELSTAMMDATEN"); int tempXx = 0; while (tempRs.next() && tempXx++ < aCount) { tempRs.getString("artikelBezeichnung"); }
View Replies !
Very Huge Database In MYSql
I am having very huge database (12GB) in mysql, with php i am using this data but i am not able to retrieve it. It’s giving time out every time because of its size. Is there any way to optimize this data?
View Replies !
Is Null Clause Takes A Lot Of Time But Is Not Null Statement Not
i have a query which takes 1 and half minute to fully execute. This query is following which return 2 records select o.id, o.number, o.timest, o.receiptno, o.canedit, o.sessionid, o.voidorder, o.cashchange,p.amount from orders o left join payments p on (o.id=p.oid) where p.amount is null but if i remove the 'not' from where clause then it takes a fraction of seconds. query is following which takes fraction of second and it returns 3920 records select o.id, o.number, o.timest, o.receiptno, o.canedit, o.sessionid, o.voidorder, o.cashchange,p.amount from orders o left join payments p on (o.id=p.oid) where p.amount is not null
View Replies !
Backup With Mysqldump Takes Hours, Database Is Not That Big
Hello, I've been having some problems making database backups. I've searched all around and I notice that to most people it only takes a few minutes to backup huge databases (1+ gb). My database is around 350mb and it can take up to 4-5 hours to backup, which I guess it not normal at all. My server has a dual Xenon with 4gb ram, using mysql 5.0.27-1.fc6 and php 5.1.6-3.6.fc6. I am using Fedora Core6 with Plesk. The database is around 350mb, with around 1.1 million rows. To backup I use mysqldump -uroot -p database > backup.sql As I said, the above takes hours. I guess it should take minutes?
View Replies !
Cannot Restore Mysql Database
My host suddenly went out of business, and deleted all my files without a notice. The only thing I have now is a week old back up Now everytime I'm with a new host, and created a new database, to restore the old one over it, but everytime I try to restore, using ssh, I get that dreadful error message: " ERROR 1062 at line 238892: Duplicate entry '189864' for key 1"
View Replies !
Restore :: Taking Long Time
I've got a table with over 400,000 records. I did a mysqldump to ensure I could restore it in case of problems. Sure enough, problems arose and I needed to do a restore. The mysqldump created the usual create database, create table and one gigantic insert statement, where all 400,000+ records are inserted. I started my restore around 10pm or so yesterday and was surprised to wake up this morning and find it still running. Considering the dump itself took a couple of seconds, why is the restore taking so long? I'm building a website that's going to have tons of traffic. A database restore that's going to take multiple hours seems to be unacceptable. One way to speed up the process would have been to simply copy the actual database files and just stick them back in the data directory.
View Replies !
Database Restore / MySQL 4.1 -- InnoDB
I have got a MySQL 4.1 that we use wit vBulletin.... We had a crash last week and I've been able to restore the data up to September 2005 as it stands. Now, I'm under the impression I could rebuild the data from the logs but I cannot seem to figure out how to do that based on the tools and instructions posted. I've got my ib_log files (ib_log0 and ib_log1) also my ibdata file, which is full of all the data i'm trying to get restored. Opening it up in hex editor, I can see posts and information right up to the moment the database crashed. What steps should I take to restore the data from the log/data files? I've tried several different steps, but I'm goofing it up somewhere I guess. Are there some other tools available to help with this? This site was a 'non-revenue' site, so I didn't exactly make all of the redundancy arrangements that I would normally do for an Oracle/MSSQL or any other live business database. I'm ateast going to enable mirroring this time around and move the backups to another location.
View Replies !
Unable To Restore Mysql Database
I have backed up my database using mysqldump command mysqldump --flush-logs -u root -p OM722 DB1 > db1bkup.sql Now I'm using the Mysql Adminsitrator GUI to restore the backup. It says restored successfully, but no database is created.
View Replies !
MySQL Database Restore Error
I decided to download a db and upload it back to the server just out of curiosity. My site is now missing the top menu and components menu in the backend of Mambo. This site is for a client and I'm in big trouble here. Code:
View Replies !
Repair Tables Of Database In Replication Setup
Recently one of our tables has indexing problem.... queries done through PhpmyAdmin give error, suggesting to repair table. Strangely, the same select queries done through the CLi works fine... you guys encountered such a situation? Next question is.. will the repair result be replicated across to the slave database?
View Replies !
Huge Database Handling
I would like to have some hints about very large database handling with MySQL. I have a big database (10M+ users and their data) and I don't know if it's better to have just one single database or several smaller dbs (partitioned by user) that in any case will be running on the same machine. What is the advised maximun size (if there is) of a single database ?
View Replies !
Huge Database Problem
I have a database with 8.500.000 (aprox.) rows and I want to select a row like this: SELECT *,((DEGREES(ACOS((SIN(RADIANS(36.517)) * SIN(RADIANS(f4)) + COS(RADIANS(36.517)) * COS(RADIANS(f4)) * COS(RADIANS((-4.883)) - RADIANS(f5)))))*69.09)*1.6) as km FROM locdb order by km limit 0,1 (I've divided the lines for better reading). The syntax selects the nearest city acording to the latitude (36.517) and longitude (-4.883). The latiude and longitude colums on my table are f4 and f5, respectively. The problem is that (obviously) it takes about a minute to do it. Has anyone an idea how to improve the table or the syntax to get a better results?
View Replies !
Huge Database With 800 Customers / 3.500 Products
I need for a webshopproject all customers with all products in one file. I have already both informations in two different ones. Pls. find below the relevant info. product ProductID|pricegroup| customer knr|pricegroup|%calc|price It is possible that one product is related to 800 customers with 800 different prices. In my thinking the new DB should look like pricefile ProductID|knr|pricegroup|%calc|price.
View Replies !
A Huge Database Or A Lot Of Small Databases
I have a question regarding database performance. Which of these two designs would be better: -Having one huge database with 50 tables or so (each table having millions of rows) or -Having a lot of databases with the same amount of tables, but each table would have only thousands of rows *The mysqlserver is running on a P4 3.0 ghz with 1 gb in ram, but I may upgrade my server soon. *Privileges to create databases is not a problem since I own the server
View Replies !
MySQL Takes FOREVER To Start Back Up
Need some help here. I am running MySQL 4.1 on a windows 2003 server. We've had the server for about a year and right now the data file for the database is up to 345mb. Its growing week by week. Lately if I had to restart my server once windows comes back up it takes 20min for MySQL to start back up.
View Replies !
Server To Repair Mysql DB
I've got a corrupt 8GB mysql table and am obliged to use myisamchk -r -o -f --safe-recover But this is taking immensely long time, and is going slower and slower The time it would take to repair 2.7 million rows would be far too much than I can make my client wait.is there somewhere a service where I could sync a copy of the db files under /var/lib/mysql/, repair it on their powerful server, and bring it back home?
View Replies !
Repair Mysql Tables
is there some code to repair all the tables in my database in one go? instead of typing in "Repair Table <TableName>;" many times.
View Replies !
Mysql Crash/repair
I've got a couple of directory sites that looks like somehow something's been deleted or crashed (the directory isn't showing any listings). I had a look in the mysql directory, and all I see are a bunch of .frm files. I don't see any MYI or MYD files. Is there any way to recover or repair? I'm assuming I need those files, and that the lack of them is what's causing my problems. We've done a couple of things lately that may have caused the problem like a server move and a hard reset.
View Replies !
Last Time A Mysql Database Updated
Here is my problem, my company has been storing fedex data in a access '97 database (the data is imported into this access database via a program called DirectLink), and because we are mainly a php house and we use mySQL, we need to take that information from the access database and mirror it in the mySQL database. To top things off I have to use ASP to write this code to do this. Now for the Access database I can use a FSO to find out the last time the database was updated. I've had no problem with that. And I've had no problem mirroring the information across to the mySQL database. My problem is, is there a way using the FSO to find out the last time the mySQL database was update. Since mySQL creates several different files for the different databases it holds - is there a specific file I need to look for or is there a query I can run that will tell me?
View Replies !
Cannot Restore A Database
I have a text file that contains the output from mysqldump. I try to restore the database with: Quote: mysql -u root -p <password>1472 database-name < name_01_06_2007.sql and mysql produces a message as if the command is incorrect. The message is: Quote: mysql Ver 14.12 Distrib 5.0.27, for redhat-linux-gnu (i686) using readline 5.0 Copyright (C) 2002 MySQL AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Usage: mysql [OPTIONS] [database] How do I restore a database after mysqldump? I read an article published in devshed and I followed their steps but...
View Replies !
Database Restore *.sql
How may I restore *.sql databases from linux shell? I've read the use of mysql command I don't have installed. This is a remote server with preinstalled Apache-php-mysql.
View Replies !
I Can't Restore My Database...
i have been workin' with MySql recently. I have learned how to backup a database and it worked fine: C:Program FilesMySQLMySQL Server 5.0in>mysqldump -uroot -ptemenos test > backupfile.sql After that i purposely dropped the test database. But when i tried restoring it, it gave me the following error: C:Program FilesMySQLMySQL Server 5.0in>mysql -uroot -ptemenos test < backupfile.sql ERROR 1049 (42000): Unknown database 'test'
View Replies !
Restore Of Database
How do I go about restoring multiple databases at the same time. I know if I needed to restore 1 database I can do something like. mysql db_name < backup-file.sql but what if backup-file.sql consists of multiple databases (ie it was created using mysqldump-all-databases) , which database would I specify in the db_name section?
View Replies !
How To Restore A Database (.bak)
Recently I downloaded a ASP script, so I can use on my website, however in the instructions, it tells me the following : - Restore the bbdf.bak SQL database with SQL 2005 (any edition) Management Tools Now I have the file bbdf.bak, but I am not sure what I am suppose to do. I have downloaded and installed Microsofts SQL 2005, but am totally lost as to what I am suppose to open and type .... ? ? I would of thought it would be easier for them to just give me a .mdb file or something which I could use...
View Replies !
SQL Database Restore
I upgraded to Tiger and it damaged my SQL database. I have a backup file that is about 80 mb. The backup file was created with PHPmyAdmin and is in .SQL format. When I try to use PHPmyAdmin to load this .SQL file and restore my database, I get an error message that says NO SQL QUERY. The file stops executing and NO data is restored. Is this because the file is too big ?? Is there an application that will break the .SQL file down into smaller parts ?? Is there a way to restore the data using the .SQL file and a direct SQL command line entry ??
View Replies !
How To Restore Database
I think i will have to da a full resortation of a database. lets assume old database is deleted. So I go to my hosting control panel and create a new mysql database Use same username and password as used in original open phpmyadmin and do to the new database and select import-then import the file that I have as a backup--i have a file that I exported as a text file-a full databse export. it is about 4 mb so I hope can do it at one time Is that basically how I can do it??
View Replies !
Database Restore Help
im trying to construct a database using a backup file i made aout a year ago when i use phpmyadmin to upload the file i get an error on the first table it trys to create CREATE TABLE `cal` ( `id` bigint( 20 ) NOT NULL AUTO_INCREMENT , `author` varchar( 32 ) NOT NULL default '', `subj` varchar( 64 ) NOT NULL default '', `txt` text NOT NULL , `typ` tinyint( 4 ) NOT NULL default Ɔ', `dmy` varchar( 8 ) NOT NULL default '', `hm` varchar( 4 ) NOT NULL default '', `wday` tinyint( 4 ) NOT NULL default Ɔ', `location` varchar( 64 ) NOT NULL default '', `approved` char( 1 ) NOT NULL default '', `tz` int( 11 ) NOT NULL default Ɔ', `dat` bigint( 20 ) NOT NULL default Ɔ', `mkt` bigint( 20 ) NOT NULL default Ɔ', PRIMARY KEY ( `id` ) , KEY `typ` ( `typ` ) , KEY `wday` ( `wday` ) , KEY `dmy` ( `dmy` ) , KEY `mkt` ( `mkt` ) ) ENGINE = MYISAM DEFAULT CHARSET = latin1; With this msg MySQL said: #1064 - You have an error in your SQL syntax near ') ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 20 this was a backup straight from phpmyadmin some time ago, can someone tell me why it wont restore?
View Replies !
Lost Connection To MySQL Server During Query After Script Takes 60 Secs
I get the error "Lost connection to MySQL server during query" at the mysql_select_db when reaching this part of the code after 60 seconds of script execuation has already passed. Works fine if the script execution time when reaching this is under 60. Tested with Sleep() and I'm 100% sure that the 60 second barrier is causing it. I just don't know what the barrier is.////
View Replies !
Error In Database Restore
I feel there is something wrong in my records which is creating a problem while restoring. I mean some tags or some special characters used in my records which disallows the restoration. Can i know any of such things? The error i get is ERROR 2005 (HY000): Unknown MySQL server host 'bidder' (11001).
View Replies !
Get Trouble With Restore Database
I truly get trouble with my database. I transfer from a shared hosting to another one. Thus, I make a backup file called svduhoc.sql. My database is so large over 160MB. Of course we can not restore database by phpMyAdmin. Therefore, I use another way. Although I have tried many times, I couldnt restore. I even asked for the help from techincal support of my new shared hosting. However they told me that there could be a problem and recommended me to come over here to get your help. Both of us: technical support and me got the same error at line 11923. Whenever we restor, it quits at the same the line 11923. So what should I have to do now? I really get mad and stress because I couldn not lose this database. It is very important to me. If anyone can fix this problem and successfully restore my database, I will pay $50 bucks even though I am poor student now. Because it is very important then I need all your help. You guys can contact me here or pm or email "tony_hai@hotmail.com"
View Replies !
Database Restore Space
i want to increase space of the database.. while restoring.. wen i click import it was '(Max: 2,048KiB)' i want to increase this space to.. higher
View Replies !
Restore Database From Another Disk
I'm wanting to restore my old database by copying the contents of the old /var/lib/mysql folder to the new server, but this isn't working. Is there a documented procedure for doing this? I'm using InnoDB and MyISAM.
View Replies !
Backup And Restore Database
i did a backup of mysql database using the command line utility mysqldump. this is the command i used "mysqldump -u root -h <hostname> --databases <dbname> > <backupfile> when i try to restore this using mysql command line client it gives a error after some time the message says that a error has happened on a line number and it states that the table columns does not match the values in the insert statmenet. i havent done any cahnges to the backup file i just dumped and restored thats all but when i tried this with mysql administrator gui tool it worked fine.
View Replies !
Font Error After Restore Database
I have a forum which is use Vietnamese language and input is utf-8. It work normall in mysql 4.0 but after i upgrade to mysql 4.1 it has some strange error in font. Some character like á à é broken and become ỿ . :( . In the end i found a stupid solutions, that was i copy a /usr/local/mysql/mysql/databasename/ ( a folder which is contain database in mysql ) from mysql 4.0 and put to mysql4.1 folder. Everything work well again. And now a real big problem happen. I backuped my database with : mysqldump data data.sql and drop everything to fix my server, after fixing my server i restore database use : mysql database < database.sql and a old problem appear again :( . Some character broken and apear as ỿ . I dont know what to do now becauase that was my only backup at this time. I'v google a week already and could not find any solutions.
View Replies !
Error 1044 On Database Restore
I'm trying to move a database from one machine to another. I dumped the database and successfully loaded it on one machine, but I can't do it on another machine. I have the database set up. I created the user to access the database, and gave all privs with grant option for that database. But when I go to run the command to restore the database, I get the following error: $ mysql -u user -p database < database.sql Enter password: ERROR 1044 at line 26: Access denied for user: 'user@localhost' to database 'database' I don't understand. Looking at the sql file, the line is: /*!40000 ALTER TABLE accidents DISABLE KEYS */; LOCK TABLES accidents WRITE; UNLOCK TABLES; /*!40000 ALTER TABLE accidents ENABLE KEYS */; Why can I do this on one machine but get an error on the other? The users are set up the same. Actually, using mysqlcc, I can't even get the user to have the lock tables priv, even though I'm root granting the priv, it just refuses to take. Yet, the machine it worked on seems to be set up the same way and I didn't have a problem. Can someone point me in the right direction to a solution?
View Replies !
InnoDB Constraints And Database Restore
We use InnoDB tables and foreign key constraints extensively. The mysqldump backs up the database tables in alphabetical order with foreign key constraints defined in the create statement of each table. These foreign key constraints are violated at the time of restore. We have tried the following two solutions. (1) We have tried to backup the database tables in the order of their dependencies. This works but the backup scripts need to be maintained carefully as new tables are added/removed from the database. (2) phpMyAdmin export does the database dump and puts the table constraints as ALTER statements at the end of the dump. Unfortunately, the phpMyAdmin dumps cannot be automated and I have not been able to push constraints to the end of the backup using mysqldump.
View Replies !
/var/lib/mysql - Huge Files
Directory /var/lib/mysql has 100's of very large files everyday, I have sub directories which are my databases for example /var/lib/mysql/mydb What what are all these files in the /var/lib/mysql directory -rw-rw---- 1 mysql mysql 261976048 Dec 11 05:58 #sql_36a6_0.MYD All the files are like the one above Many of these get written per day, isn't that excessive IO? Considering I can delete them , therefor they are not needed, why are they written then? What are they? I had 50GB of these files
View Replies !
Import Huge Csv To Mysql?
I have a huge csv file ( 480mb).phpmyadmin of xampp can import maximum 12mb csv file as mysql db.so how can I import this huge csv as mysql db? the ms excel even cant open a 480mb completely
View Replies !
Huge Problem With Mysql Locking Up
I was hoping for some urgent help. One of my websites is having big problems with MySQL. Its a popular website and MySQL hasn't been able to cope. The site recently froze, here is a processlist from the time it started to freeze, its very messy: http://www.trailblasters.co.uk/sitepoint/main.htm The query that shows up the most has been removed and the site is running ok again but I sense it will freeze again at some point. UPDATES are run very regularly, could this be the problem? How can I overcome this issue?
View Replies !
|
TRACKER
