I am not quite sure how to add indexes to tables? But I am interested in getting the spinning circles to go away. If this works in testing is it possibly going to be part of a patch?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Up until now, the standard patches have not been able to modify the database. I think it's time to change this for several reasons:
1. Have a mechanism for laypeople to add these keys
2. Allow addition of new rows/tables to OpenEMR 4.1 (of course, after good testing) since the Certification is for this version (I don't know this, perhaps somebody can weigh in, but will version 4.2 in the future need to be recertified?)
In order to get the new keys in, another function needs to be built into the sql_upgrade.php script:
1. Check if a key exist
2. If key doesn't exist, then add it
Then when have this mechanism, can make a script that can be used in a patch mechanism to deal with database upgrades(the first being the addition of the keys).
Any developers want to to take a stab at adding the mechanism to the sql_upgrade.php script? Guessing it will involve utilization of the mysql SHOW INDEX and CREATE INDEX commands.
-brady
www.open-emr.org
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Adding the ten file indexes sped up things considerably, but it was still a problem so we got a new $1300 server which has 8 MB memory and a really fast CPU. We got one with a heavy duty battery backup system and four hard drives. (We put the first three into a RAID configuration and saved the last one for conventional use.) Now things are so fast we don't notice any delay at all.
As a matter of practicality, I don't think the memory was a problem, as I was able to show before that we were not swapping to and from disk. Also, the RAID drive is just icing on the cake - the real difference was in picking a CPU of 2-4 GHz rather than 300 MHz or so.
Ronald Leemhuis MD
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
MySQL by default may not be configured to optimally use your available memory. Although it wasn't swapping, it might be hitting the hard disk to read data when it could just read from memory instead.
I think you are right that the memory wasn't the fundamental problem, but I think you may be able to better take advantage of the memory you've got.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Every since I upgraded my people to 4.0. The calendar has gotten slower and slower. I have read through this post and done everthing in this posting and then some except the firebug. I have not understood how to install it yet. I have setup the eaccelorator for PHP and indexed all the tables and installed the latest patch 5 and updated the database. It still takes upto a full 20 - 30 seconds to load the calendar at every change. I am getting screamed at to make something happen faster. I don't know what to do. I have 4GB of RAM in the system and 90GB of free space on the hard drive. It is a windows system running server 2003. I am perplexed as to make it go any faster.
Any suggestions would be nice.
Sherwin
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Sherwin,
I suspect the bottleneck is the size of your openemr_postcalendar_* tables. A stop gap may be to delete historical records. However, given that amount of time I'll bet the database is table scanning rather than using the indices.
Best way to figure out the exact problem is to profile the code as I've mentioned in a couple of other threads. In addition if you can look at the query plans for the calendar activity, you can see if it is indeed table scanning as I suspect, or if it's something else.
-Kevin Yeh
kevin.y@integralemr.com
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
thanks for the reply.
I am trying to grasp what you are saying and get onboard. The openemr_postcalendar_events table has 6,588 records in it. Is this what you suspect in slowing down the loading of the calendar? The rest of the postcalendar table have nothing in them. So, this one alone has the most data.
You mentioned "profile the code".
Could you point me to the thread where this is discussed? Is that what FireBug is used for?
Should I delete the records that are over a year old in the postcalendar table?
Sherwin
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hmm… 6000 entries doesn't really seem like that many, but if my suspicion is correct that the database is table scanning instead of using a good index, then the performance is going to be related to the size of that table, so the more rows you can delete, the faster I think it will run.
Firebug can give you info on the "browser side" about how long things take. Profiling with xdebug breaks down the code running on the server side and shows you how long each operation takes.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I took some time to work on this issue again. I finally got firebug loaded to FireFox and ran the profiler on loading the patients demographics page and got this report
Sherwin,
The profiling data that you generated with firebug is looking at potential javascript performance issues in the browser, which is almost certainly not the source of your issue, as it's a server performance problem you are likely dealing with and not a client side (browser) issue.
Xdebug and wincachegrind would probably be the best tools to use, although as I mentioned before, the slow query log in mysql may be somewhat helpful.
I suspect that the index in openemr_postcalendar_events table is less than optimal and correcting that deficit would probably go a long way. The fact that the index is clustered primarily on category_id makes it less useful.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
In trying to setup the slow log file, I was looking for the file creation and was searching for the log name that I had given it.
I did a search and in the search I found the mysql_error.log and decided to look in it. I found these kinds of entries.
110406 5:03:24 Cannot find or open table east/notification_settings from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.
110406 5:03:24 Cannot find or open table east/users_facility from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.
How do I fix these kinds of errors?
Thanks!
Sherwin
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I finally got the slow querry log turned on. I will look at the log at the end of the day to see what is in it. I am hoping it only takes a day of use to see what is going on. I have been doing a lot of reading and I see that there is a log for queries not using indexes and I would like to turn that on as well. It should help if what you suspect is true.
Because I am running a windows server, I had to run the command for the slow query from the Services Control Panel and in the Start Parameter box insert the -slow_query_log_file command, then start up the service again. I was trying to do it from the command line and it was giving me a fit because it kept teill me that I already had mysqld running which was true.
Thanks again for your guidance.
Sherwin
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I believe optimizing "cache hit rate" shoud aim for 100%. My understanding is mysql is getting it's data from cache (faster) rather than having to read the disk bound data base. You might want to look at:
thanks for picking up the conversation. I am hoping that it helps someone else out there that is in production.
I did visit that site and made the adjustments to my buffers as it suggested.
I am still getting a high I/O hit rate even with setting the buffers to the suggestions of that website.
Here is what the graph looks like now of the I/O hit rate https://www.cheapemr.com/ken/myisam.png .
I have been searching the web looking for way to improve the I/O read. I know that the key buffer can go up to 4GB. The server that I am using has 4GB of physical RAM in it. I have dual Xeon processors at 1.6Ghz. According to taskmgr the CPU utilization does not exceed 50% most of the time it is at 5% - 10%. Memory utilization is an even 2GB.
Should I raise the key buffer to 1GB and the InnoDB to 512MB?
Sherwin
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Since you have 100% cache hit rate and more than half of your buffer pool free for InnoDB. Increasing the size is going to do nothing. In fact, hypothetically if you were on a system with less RAM, it could actually hurt performance to increase the size if it made the system swap more. (Not the case given your system configuration, but something to be aware of).
Also, most of the tables including those for the calendar, are by default MyISAM not InnoDB, so unless you've reconfigured your system to use InnoDB instead, you aren't going to get much out of trying to tweak InnoDB related settings.
You WANT a high I/O hit rate. High I/O hit rate means you are getting the data from the cache/memory instead of having to go to the disk which is slower.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
This is how I resolved the issue. Make sure you backup before updating :)
1. Changed all tables to MyISAM.
2. Changed all charset to UTF-8.
3. Added indexes as suggested by Brady. I think patches has this indexes included.
4. Updated server and user PCs Java and OS manually with latest patches.
5. Restart services or simply restart the server.
After these steps it is fast as version 3. Ofcourse, Vitals display will take few seconds.
The above steps should resolve the issue. If not, there is something wrong with hardware…
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Could you pleaes post the syntax for adding these indexes to the MySQL?
Thanks!
Sherwin
Belay my last, too early in the morning. I found how to add the indexes.
Sherwin
I am not quite sure how to add indexes to tables? But I am interested in getting the spinning circles to go away. If this works in testing is it possibly going to be part of a patch?
Hi,
Up until now, the standard patches have not been able to modify the database. I think it's time to change this for several reasons:
1. Have a mechanism for laypeople to add these keys
2. Allow addition of new rows/tables to OpenEMR 4.1 (of course, after good testing) since the Certification is for this version (I don't know this, perhaps somebody can weigh in, but will version 4.2 in the future need to be recertified?)
In order to get the new keys in, another function needs to be built into the sql_upgrade.php script:
1. Check if a key exist
2. If key doesn't exist, then add it
Then when have this mechanism, can make a script that can be used in a patch mechanism to deal with database upgrades(the first being the addition of the keys).
Any developers want to to take a stab at adding the mechanism to the sql_upgrade.php script? Guessing it will involve utilization of the mysql SHOW INDEX and CREATE INDEX commands.
-brady
www.open-emr.org
Hi cverk (and everybody),
The next patch will add these new keys/indexes (should go out sometime over the next week). For details on this, see this thread:
http://sourceforge.net/projects/openemr/forums/forum/202506/topic/4759848
-brady
www.open-emr.org
Adding the ten file indexes sped up things considerably, but it was still a problem so we got a new $1300 server which has 8 MB memory and a really fast CPU. We got one with a heavy duty battery backup system and four hard drives. (We put the first three into a RAID configuration and saved the last one for conventional use.) Now things are so fast we don't notice any delay at all.
As a matter of practicality, I don't think the memory was a problem, as I was able to show before that we were not swapping to and from disk. Also, the RAID drive is just icing on the cake - the real difference was in picking a CPU of 2-4 GHz rather than 300 MHz or so.
Ronald Leemhuis MD
MySQL by default may not be configured to optimally use your available memory. Although it wasn't swapping, it might be hitting the hard disk to read data when it could just read from memory instead.
I think you are right that the memory wasn't the fundamental problem, but I think you may be able to better take advantage of the memory you've got.
Hi,
Every since I upgraded my people to 4.0. The calendar has gotten slower and slower. I have read through this post and done everthing in this posting and then some except the firebug. I have not understood how to install it yet. I have setup the eaccelorator for PHP and indexed all the tables and installed the latest patch 5 and updated the database. It still takes upto a full 20 - 30 seconds to load the calendar at every change. I am getting screamed at to make something happen faster. I don't know what to do. I have 4GB of RAM in the system and 90GB of free space on the hard drive. It is a windows system running server 2003. I am perplexed as to make it go any faster.
Any suggestions would be nice.
Sherwin
PS
It is just a webserver. it does nothing else but run OpenEMR.
Sherwin
Sherwin,
I suspect the bottleneck is the size of your openemr_postcalendar_* tables. A stop gap may be to delete historical records. However, given that amount of time I'll bet the database is table scanning rather than using the indices.
Best way to figure out the exact problem is to profile the code as I've mentioned in a couple of other threads. In addition if you can look at the query plans for the calendar activity, you can see if it is indeed table scanning as I suspect, or if it's something else.
-Kevin Yeh
kevin.y@integralemr.com
Kevin,
thanks for the reply.
I am trying to grasp what you are saying and get onboard. The openemr_postcalendar_events table has 6,588 records in it. Is this what you suspect in slowing down the loading of the calendar? The rest of the postcalendar table have nothing in them. So, this one alone has the most data.
You mentioned "profile the code".
Could you point me to the thread where this is discussed? Is that what FireBug is used for?
Should I delete the records that are over a year old in the postcalendar table?
Sherwin
https://sourceforge.net/projects/openemr/forums/forum/202506/topic/4884045
https://sourceforge.net/projects/openemr/forums/forum/202506/topic/4880879
Hmm… 6000 entries doesn't really seem like that many, but if my suspicion is correct that the database is table scanning instead of using a good index, then the performance is going to be related to the size of that table, so the more rows you can delete, the faster I think it will run.
Firebug can give you info on the "browser side" about how long things take. Profiling with xdebug breaks down the code running on the server side and shows you how long each operation takes.
Hi Kevin,
I click the little check box in PHP myadmin that says profiling and got this information.
Status Time
starting 0.000075
Opening tables 0.000670
System lock 0.000006
Table lock 0.000015
init 0.000087
optimizing 0.000008
statistics 0.000022
preparing 0.000019
executing 0.000005
Sending data 0.002746
end 0.000012
query end 0.000006
freeing items 0.000084
logging slow query 0.000005
cleaning up 0.000009
Does any of this mean anything to you?
Sherwin
Sherwin,
I never use phpMyAdmin.
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
Using the slow query log might help you too though.
Hi,
I took some time to work on this issue again. I finally got firebug loaded to FireFox and ran the profiler on loading the patients demographics page and got this report
.
POST https://web.openmedpractice.com/…/interface/patient_file/summary/stats.php 200 OK 853ms
GET https://web.openmedpractice.com/…./interface/patient_file/summary/pnotes_fragment.php 200 OK 2.71s
GET https://web.openmedpractice.com/…./interface/patient_file/summary/disc_fragment.php 200 OK 2.77s
POST https://web.openmedpractice.com/…../interface/patient_file/summary/clinical_reminders_fragment.php 200 OK 2.6s
GET https://web.openmedpractice.com/…../interface/patient_file/summary/patient_reminders_fragment.php 200 OK 2.93s
GET https://web.openmedpractice.com/…../interface/patient_file/summary/vitals_fragment.php 200 OK 3.01s
I got this information when I profieled the click on the calendar. I ordered it from the longest time to the shortest.
nodeName 319 1.36% 0.573ms 38.611ms 0.121ms 0.037ms 24.841ms jquery….min.js (line 20)
ready 2 0.08% 0.033ms 25.01ms 12.505ms 0.004ms 25.006ms jquery….min.js (line 28)
ready 1 0.02% 0.01ms 24.827ms 24.827ms 24.827ms 24.827ms jquery….min.js (line 28)
ready 1 0.02% 0.009ms 24.817ms 24.817ms 24.817ms 24.817ms jquery….min.js (line 28)
size 17 2.76% 1.163ms 16.641ms 0.979ms 0.085ms 3.857ms jquery….min.js (line 14)
trigger 15 0.22% 0.094ms 16.052ms 1.07ms 0.09ms 3.864ms jquery….min.js (line 27)
ready 14 0.23% 0.097ms 15.976ms 1.141ms 0.096ms 3.871ms jquery….min.js (line 28)
trigger 292 4.35% 1.828ms 14.667ms 0.05ms 0.03ms 0.362ms jquery….min.js (line 27)
jQuery 1 1.7% 0.717ms 14.481ms 14.481ms 14.481ms 14.481ms jquery….min.js (line 12)
unique 294 13.5% 5.68ms 13.063ms 0.044ms 0.025ms 0.214ms jquery….min.js (line 26)
unique 9 2.64% 1.11ms 12.221ms 1.358ms 1.285ms 1.738ms jquery….min.js (line 26)
unique 8 0.56% 0.236ms 10.291ms 1.286ms 0.842ms 1.792ms jquery.js (line 24)
trigger 8 1.45% 0.612ms 10.055ms 1.257ms 0.832ms 1.758ms jquery.js (line 27)
jQuery 37 0.4% 0.167ms 8.933ms 0.241ms 0.01ms 2.707ms jquery….min.js (line 12)
jQuery 37 2.39% 1.006ms 8.766ms 0.237ms 0.004ms 2.7ms jquery….min.js (line 12)
size 9 0.28% 0.119ms 7.71ms 0.857ms 0.267ms 2.684ms jquery….min.js (line 14)
unique 9 0.28% 0.118ms 7.14ms 0.793ms 0.232ms 2.613ms jquery….min.js (line 26)
size 9 0.14% 0.06ms 7.022ms 0.78ms 0.223ms 2.597ms jquery….min.js (line 14)
unique 9 1.1% 0.461ms 6.962ms 0.774ms 0.218ms 2.591ms jquery….min.js (line 26)
trigger 8 0.45% 0.189ms 6.255ms 0.782ms 0.509ms 1.16ms jquery.js (line 27)
extend 8 14.42% 6.066ms 6.066ms 0.758ms 0.5ms 1.134ms jquery.js (line 18)
inArray 18 10.31% 4.335ms 4.335ms 0.241ms 0.003ms 2.258ms jquery….min.js (line 25)
nodeName 793 9.58% 4.028ms 4.028ms 0.005ms 0.003ms 0.05ms jquery….min.js (line 20)
unique 294 8.15% 3.43ms 3.43ms 0.012ms 0.003ms 0.042ms jquery….min.js (line 26)
unique 3 0.29% 0.124ms 3.318ms 1.106ms 0.337ms 1.866ms jquery….min.js (line 26)
trigger 3 0.52% 0.218ms 3.194ms 1.065ms 0.306ms 1.803ms jquery….min.js (line 27)
extend 13 5.83% 2.451ms 2.451ms 0.189ms 0.014ms 0.976ms jquery….min.js (line 19)
unique 9 4.92% 2.07ms 2.07ms 0.23ms 0.071ms 0.448ms jquery….min.js (line 26)
trigger 4 0.36% 0.152ms 2.057ms 0.514ms 0.048ms 1.033ms jquery….min.js (line 27)
unique 8 0.19% 0.082ms 2.027ms 0.253ms 0.192ms 0.37ms jquery.js (line 24)
size 8 0.16% 0.068ms 1.945ms 0.243ms 0.185ms 0.357ms jquery.js (line 14)
setupDirectTime 1 0.04% 0.018ms 1.926ms 1.926ms 1.926ms 1.926ms
calend…lect.js (line 70)
nodeName 12 0.31% 0.132ms 1.877ms 0.156ms 0ms 0.345ms jquery.js (line 19)
unique 8 0.53% 0.222ms 1.745ms 0.218ms 0.165ms 0.318ms jquery.js (line 24)
(?) 4 0.29% 0.124ms 1.67ms 0.418ms 0.286ms 0.638ms encounters.php (line 243)
(?) 4 0.2% 0.083ms 1.368ms 0.342ms 0.253ms 0.49ms encounters.php (line 244)
jQuery 16 0.91% 0.381ms 0.804ms 0.05ms 0ms 0.383ms jquery.js (line 12)
unique 2 0.23% 0.097ms 0.788ms 0.394ms 0.2ms 0.588ms jquery….min.js (line 26)
prop 4 0.94% 0.395ms 0.706ms 0.177ms 0.135ms 0.261ms jquery.js (line 21)
(?) 1 0.07% 0.028ms 0.645ms 0.645ms 0.645ms 0.645ms index…._topic= (line 546)
prop 4 0.37% 0.154ms 0.62ms 0.155ms 0.117ms 0.206ms jquery.js (line 21)
unique 2 0.11% 0.046ms 0.62ms 0.31ms 0.167ms 0.453ms jquery….min.js (line 26)
prop 4 0.93% 0.391ms 0.466ms 0.116ms 0.09ms 0.168ms jquery.js (line 21)
size 9 0.5% 0.209ms 0.451ms 0.05ms 0.023ms 0.104ms jquery….min.js (line 14)
prop 24 0.79% 0.333ms 0.425ms 0.018ms 0.008ms 0.036ms jquery.js (line 21)
jQuery 8 0.68% 0.287ms 0.423ms 0.053ms 0.023ms 0.112ms jquery.js (line 12)
prop 1 0.48% 0.201ms 0.381ms 0.381ms 0.381ms 0.381ms jquery….min.js (line 22)
unique 4 0.16% 0.068ms 0.311ms 0.078ms 0.056ms 0.12ms jquery.js (line 24)
unique 2 0.11% 0.046ms 0.266ms 0.133ms 0.09ms 0.176ms jquery….min.js (line 26)
prop 12 0.21% 0.09ms 0.243ms 0.02ms 0.013ms 0.034ms jquery.js (line 21)
(?) 1 0.04% 0.018ms 0.243ms 0.243ms 0.243ms 0.243ms index…._topic= (line 545)
size 10 0.52% 0.217ms 0.217ms 0.022ms 0.006ms 0.045ms jquery….min.js (line 14)
unique 1 0.1% 0.042ms 0.18ms 0.18ms 0.18ms 0.18ms jquery….min.js (line 26)
prop 7 0.3% 0.127ms 0.171ms 0.024ms 0.014ms 0.042ms jquery….min.js (line 22)
trigger 1 0.02% 0.008ms 0.165ms 0.165ms 0.165ms 0.165ms jquery….min.js (line 27)
trigger 1 0.03% 0.012ms 0.155ms 0.155ms 0.155ms 0.155ms jquery….min.js (line 27)
nodeName 16 0.36% 0.15ms 0.15ms 0.009ms 0.004ms 0.022ms jquery.js (line 19)
bindReady 1 0.1% 0.044ms 0.143ms 0.143ms 0.143ms 0.143ms jquery….min.js (line 28)
prop 4 0.12% 0.049ms 0.138ms 0.035ms 0.031ms 0.042ms jquery….min.js (line 22)
trigger 1 0.03% 0.011ms 0.136ms 0.136ms 0.136ms 0.136ms jquery….min.js (line 27)
trigger 1 0.02% 0.009ms 0.126ms 0.126ms 0.126ms 0.126ms jquery….min.js (line 27)
prop 1 0.05% 0.022ms 0.125ms 0.125ms 0.125ms 0.125ms jquery….min.js (line 22)
trigger 1 0.11% 0.045ms 0.117ms 0.117ms 0.117ms 0.117ms jquery….min.js (line 27)
ready 20 0.24% 0.103ms 0.103ms 0.005ms 0.004ms 0.01ms jquery….min.js (line 28)
prop 1 0.21% 0.089ms 0.103ms 0.103ms 0.103ms 0.103ms jquery….min.js (line 22)
inArray 24 0.22% 0.092ms 0.092ms 0.004ms 0.002ms 0.007ms jquery.js (line 23)
extend 12 0.19% 0.081ms 0.081ms 0.007ms 0.003ms 0.037ms jquery….min.js (line 19)
size 8 0.19% 0.079ms 0.079ms 0.01ms 0.004ms 0.014ms jquery.js (line 14)
extend 8 0.14% 0.057ms 0.057ms 0.007ms 0.004ms 0.01ms jquery.js (line 18)
attr 11 0.12% 0.05ms 0.05ms 0.005ms 0.004ms 0.008ms jquery….min.js (line 24)
inArray 7 0.1% 0.044ms 0.044ms 0.006ms 0.003ms 0.01ms jquery….min.js (line 25)
ready 6 0.08% 0.035ms 0.035ms 0.006ms 0.005ms 0.008ms jquery….min.js (line 28)
unique 6 0.07% 0.029ms 0.029ms 0.005ms 0.004ms 0.007ms jquery….min.js (line 26)
unique 5 0.05% 0.021ms 0.021ms 0.004ms 0.003ms 0.007ms jquery….min.js (line 26)
unique 2 0.04% 0.015ms 0.015ms 0.008ms 0.005ms 0.01ms jquery….min.js (line 26)
attr 1 0.01% 0.004ms 0.004ms 0.004ms 0.004ms 0.004ms jquery….min.js (line 24)
How can I use this to figure out why the calendar is loading so slow?
Sherwin
openmedpractice.com
Sherwin,
The profiling data that you generated with firebug is looking at potential javascript performance issues in the browser, which is almost certainly not the source of your issue, as it's a server performance problem you are likely dealing with and not a client side (browser) issue.
Xdebug and wincachegrind would probably be the best tools to use, although as I mentioned before, the slow query log in mysql may be somewhat helpful.
I suspect that the index in openemr_postcalendar_events table is less than optimal and correcting that deficit would probably go a long way. The fact that the index is clustered primarily on category_id makes it less useful.
Thanks for the response. I will setup the slow query log now.
I will post back when I have that done.
Sherwin
In trying to setup the slow log file, I was looking for the file creation and was searching for the log name that I had given it.
I did a search and in the search I found the mysql_error.log and decided to look in it. I found these kinds of entries.
110406 5:03:24 Cannot find or open table east/notification_settings from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.
110406 5:03:24 Cannot find or open table east/users_facility from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.
How do I fix these kinds of errors?
Thanks!
Sherwin
Ok,
I finally got the slow querry log turned on. I will look at the log at the end of the day to see what is in it. I am hoping it only takes a day of use to see what is going on. I have been doing a lot of reading and I see that there is a log for queries not using indexes and I would like to turn that on as well. It should help if what you suspect is true.
Because I am running a windows server, I had to run the command for the slow query from the Services Control Panel and in the Start Parameter box insert the -slow_query_log_file command, then start up the service again. I was trying to do it from the command line and it was giving me a fit because it kept teill me that I already had mysqld running which was true.
Thanks again for your guidance.
Sherwin
I had to end up getting a commercial tool to do the analizing of the database. It was less frustrating. Anyway. One of the first things I saw was this
MyISAM Key Buffer
0%50%100%Allocated: 1.63MB / 16.00MB 10.16%
Write Ratio: 0.38
Read Ratio: 0.00
MyISAM Key Cache Hit Rate: 99.83%
InnoDB Buffer Pool
05001,0001,500Buffer Pool Size: 16MB
Page Size: 16k
Used Buffers (Pages): 433
Free Buffers (Pages): 591
InnoDB Buffer Cache Hit Rate : 100%
This doesn't look good to me. Should I be concern about the MySIAM cache?
I will have more data end of day. I just loaded the tool and it is 3am.
Sherwin
juggernautsei,
I believe optimizing "cache hit rate" shoud aim for 100%. My understanding is mysql is getting it's data from cache (faster) rather than having to read the disk bound data base. You might want to look at:
http://www.mysqlab.net/knowledge/kb/detail/topic/myisam/id/5114
GP
GP,
thanks for picking up the conversation. I am hoping that it helps someone else out there that is in production.
I did visit that site and made the adjustments to my buffers as it suggested.
I am still getting a high I/O hit rate even with setting the buffers to the suggestions of that website.
Here is what the graph looks like now of the I/O hit rate https://www.cheapemr.com/ken/myisam.png .
Also, I have a slow query chart http://www.cheapemr.com/ken/results-sql-wait.csv. Not sure how to interpret this data yet.
I have been searching the web looking for way to improve the I/O read. I know that the key buffer can go up to 4GB. The server that I am using has 4GB of physical RAM in it. I have dual Xeon processors at 1.6Ghz. According to taskmgr the CPU utilization does not exceed 50% most of the time it is at 5% - 10%. Memory utilization is an even 2GB.
Should I raise the key buffer to 1GB and the InnoDB to 512MB?
Sherwin
Since you have 100% cache hit rate and more than half of your buffer pool free for InnoDB. Increasing the size is going to do nothing. In fact, hypothetically if you were on a system with less RAM, it could actually hurt performance to increase the size if it made the system swap more. (Not the case given your system configuration, but something to be aware of).
Also, most of the tables including those for the calendar, are by default MyISAM not InnoDB, so unless you've reconfigured your system to use InnoDB instead, you aren't going to get much out of trying to tweak InnoDB related settings.
You WANT a high I/O hit rate. High I/O hit rate means you are getting the data from the cache/memory instead of having to go to the disk which is slower.
This is how I resolved the issue. Make sure you backup before updating :)
1. Changed all tables to MyISAM.
2. Changed all charset to UTF-8.
3. Added indexes as suggested by Brady. I think patches has this indexes included.
4. Updated server and user PCs Java and OS manually with latest patches.
5. Restart services or simply restart the server.
After these steps it is fast as version 3. Ofcourse, Vitals display will take few seconds.
The above steps should resolve the issue. If not, there is something wrong with hardware…
If you want to do a quick "sanity" check on your mysql database with out a commercial product try a little perl script call mysqltuner.pl
As root:
wget mysqltuner.pl
Rename it with
mv index.html mysqltunder.pl
I reccomend allways looking at scripts BEFORE you run them with cat or an editor.
Make it executable
chmod +x mysqltuner.pl
Then run it
./mysqltuner.pl #note you may want to be sure mysql has been up and running 24hours plus and redirect to a file
This will at least get you an indea what is going on in the database
The reccomendations are just that and you should do your due diligence before make changes.
GP