Godaddy, mysql, very slow update 1 record
I am sorry to bother everyone but I am a broken man. I have spent two days (all day) with hundreds of google searches trying to figure this out. I purchased this product with a plan to use Microsoft Access as a database using VBA Code which I do know but found out that Digital File Pro currently does not use .asp page as per a few thread here which I assume is the only way to use Access. Anyways, go with the flow I say so I decided to learn mysql. So I installed it on my window xp system and installed the admin part of it. Being a computer guy...like all of you...I look for a short cuts so I took the .mdb database provided my webassist and change the data for the items and used a mysql migration program and created a quick mysql database with my data and then did a backup which I uploaded up to the server.
The cart program and the dataassist worked like a charm (great product and save a lot of time). So then I upload everthing to the server except the database since I was not to sure about that. Ok, now on to my server. I host with godaddy and have a virtual private server. I log on remotely and like an idiot started mysql server instance not knowing that I was suppose to go through plesk and that godaddy already had it set up. Of course it did not work. Then I went to plesk made a blank database and then reload the backup. My mysql did not work but then I assumed my.ini was goofed up so I copied a backup in that folder data called my-medium.ini and pasted it in my.ini. Everything seemed to work. I could created new database and everything seem fine.
I go to login...works perfect. Do a search...perfect and fast. Look at an item details...perfect and fast. Delete an item...perfect and fast. Now, I go to add and item or update and item and then it seems to takes forever and after about 10 minutes it looks like it reloads the page. Now, if I hit update and immediately do a search for the item...it is updated in the database but the page still just sits there. I even change the redirect page after completed to the index page and it still would not move.
So this is where I started searching for the problem.
Things I noticed:
Handler_read_rnd is 366
Now I am not sure but it seems high and every few minutes goes up another 1,000.
I started looking into it and it was indicated that their could be leaking so InnoDB engine might be better. I go to change the engine and after working on that for several hours come to the conclusion I must have deleted the text either in the my.ini file or the .cnf file (which by the way I could not find). Now I can only assume that it was on their in the beginning since I search the log and found the following in the first several lines of the log:
090603 6:48:06 InnoDB: Started; log sequence number 0 43655
090603 6:48:07 [Note] C:\Parallels\Plesk\Databases\MySQL\bin\mysqld-nt.exe: ready for connections.
Version: '5.0.45-community-nt' socket: '' port: 3306 MySQL Community Edition (GPL)
090603 6:48:07 [Note] C:\Parallels\Plesk\Databases\MySQL\bin\mysqld-nt.exe: Normal shutdown
090603 6:48:09 [Warning] C:\Parallels\Plesk\Databases\MySQL\bin\mysqld-nt.exe: Forcing close of thread 1 user: 'admin'
090603 6:48:09 InnoDB: Starting shutdown...
090603 6:48:10 InnoDB: Shutdown completed; log sequence number 0 43655
090603 6:48:10 [Note] C:\Parallels\Plesk\Databases\MySQL\bin\mysqld-nt.exe: Shutdown complete
InnoDB: The first specified data file C:\MySQL InnoDB Datafiles\ibdata1 did not exist:
InnoDB: a new database to be created!
090723 13:54:47 InnoDB: Setting file C:\MySQL InnoDB Datafiles\ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Error: log file .\ib_logfile0 is of different size 0 10485760 bytes
InnoDB: than specified in the .cnf file 0 63963136 bytes!
090723 13:54:48 [ERROR] Default storage engine (InnoDB) is not available
090723 13:54:48 [ERROR] Aborting
Now, my search engine is MyISAM and I can't change it. I don't know if this is the problem or something I did incorrect with the set up of dataassist. Hopefully someone can look at this and see a remedy. Like I said, when I hit update or insert with datassist it does work but does not leave the page and the progress bar moves very slowly and never completes although it does update the table. The only thing I can think of is the Handler_writes which is the requests to insert a row in a table is the problem as I just added another 1,000 request just writing these two paragraphs.
I tried to be detailed as much as possible...sorry it is so long.
Oh Yeah...I have essential suite.
Update: Next day
Now what worries me is that the Handler_Write is the number of request to insert a row in a table. The Handler_read_rnd_next is the number of request to read the next row in the data. Now this is the first mysql database and it is private so those reads must be coming from the standard mysql table that hold database names, users, permissions....etc.