OP running slow after power outage

Submitted by pyevet on Fri, 15/07/2016 - 12:15

General User's Discussion

Hello everybody,

We have had 2 blackouts in 2 days & the server (which is running OP) was not shut down properly each time - we think the battery in the UPS needs replacing. Since then, OP has been running incredibly slowly. We have reset the Tomcat server. Where would the potential sources of the problem lie, so we can get it back up to speed by next Monday?

Thanks, Mary

Re: OP running slow after power outage

Submitted by tanderson on Fri, 15/07/2016 - 12:51.

Assuming you are on Windows, if you go into Task Manager, are there any processes using a lot of resources?

Are there any errors in the MySQL error file at or after the time of the power outages? The file is located in the MySQL data directory e.g.:

c:\ProgramData\MySQL\MySQL Server 5.1\Data\<hostname>.err

If you have MySQL Workbench installed, you can view it on the Server -> Server Logs menu.

Re: OP running slow after power outage

Submitted by pyevet on Fri, 15/07/2016 - 13:44.

Sorry Tim only Linux here, but /var/log/mysqld.log has these as the last entries:

Version: '5.1.73'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
160712 19:40:36 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160712 19:40:36  InnoDB: Initializing buffer pool, size = 8.0M
160712 19:40:36  InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
160712 19:40:36  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
160712 19:40:38  InnoDB: Started; log sequence number 0 2676802222
160712 19:40:38 [Note] Event Scheduler: Loaded 0 events
160712 19:40:38 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.73'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
160713 17:12:45 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160713 17:12:46  InnoDB: Initializing buffer pool, size = 8.0M
160713 17:12:46  InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
160713 17:12:46  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
160713 17:12:47  InnoDB: Started; log sequence number 0 2676802608
160713 17:12:47 [Note] Event Scheduler: Loaded 0 events
160713 17:12:47 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.73'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
[simon@serv0 ~]$

Simon

Simon Slater

Registered Linux User #463789 @ http://linuxcounter.net

Re: OP running slow after power outage

Submitted by tgething (not verified) on Fri, 15/07/2016 - 12:59.

Mary - as a brute force fix attempt, I would shut down and restart the machine running the openvpms server.

As a more focused approach, I would have a look at what is going on on the server. If it is a Windows machine, use task manager; if a linux machine, use top and iostat [if I remember my linux stuff correctly].

How do you do backup? The reason for my asking is that I know my machine runs like a dog between 0600 and 0630 when the backup is running. I am wondering if things got screwed and and it is running backups when you do not expect.

Next - hardware changes - if it possible that as part of the power problems, you lost some memory on the server. What does (assuming you have a linux server)

grep MemTotal /proc/meminfo

say? (see http://www.cyberciti.biz/faq/check-ram-in-ubuntu/ )

Finally - what does Simon say? or is he away?

Regards, Tim G

Re: OP running slow after power outage

Submitted by pyevet on Fri, 15/07/2016 - 14:01.

From last to first Tim,

We also had a network link go down between the 2 buildings, so I have been working on that and had Mary send the initial post.

Next, this looks about right:

[simon@serv0 ~]$ grep MemTotal /proc/meminfo
MemTotal:        3854732 kB

(I'm used to e-mail forums not web ones and I think I'm not formatting these snippets correctly - changing "normal" to "formatted" but it doesn't look right).

When submitting a query, say selecting a different patient from the customer screen, it takes about a minute, but top shows java as tomcat running for a couple of seconds and mysqld for 1 second, load is very low. On the client, Firefox is not working hard either. Output of iostat:

simon@serv0 ~]$ iostat
Linux 2.6.32-573.12.1.el6.centos.plus.x86_64 (serv0.pyevet.mynet)       15/07/16   _x86_64_     (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.37    0.01    0.26    0.79    0.00   98.56

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda               3.22        15.67        30.34    2465148    4771866
sdb               3.00         2.63        29.66     413453    4665496
md0               3.91        17.78        23.84    2795780    3750008
dm-0              0.04         0.77         0.11     121474      16664
dm-1              0.46         0.75         2.93     117954     460368
dm-2              0.81         0.87         5.65     137186     888008
dm-3              0.00         0.02         0.00       3394         56
dm-4              0.37         5.58         0.70     877354     110584
dm-5              2.01         9.78        14.46    1537650    2274328

with no difference while "Please wait..."ing

Backups not an issue. A reset was a last option in resolving the networking issue, but was waiting 'till after knock-off for that.

Simon

Simon Slater

Registered Linux User #463789 @ http://linuxcounter.net

Re: OP running slow after power outage

Submitted by tanderson on Fri, 15/07/2016 - 14:33.

Is mysqld on the same server as Tomcat?

Can you rule out a slow network?

Re: OP running slow after power outage

Submitted by pyevet on Fri, 15/07/2016 - 15:11.

Yes, Tim, both mysqld and Tomcat are on the same server.

As to the slow network, the same hub is allowing acceptable internet connectivity ... and was about to verify video streaming, but now have nfs trouble. Must be time for a complete restart.

This hub is the one I'm having trouble reconnecting to the switch in the other building. It may be the hub is broken also.

Regards Simon

Simon Slater

Registered Linux User #463789 @ http://linuxcounter.net

Re: OP running slow after power outage

Submitted by pyevet on Fri, 15/07/2016 - 16:33.

Okay, after a complete restart, selecting a different patient from the customer screen, it takes about 4s now. Does that sound about right?

Simon Slater

Registered Linux User #463789 @ http://linuxcounter.net

Re: OP running slow after power outage

Submitted by tgething (not verified) on Fri, 15/07/2016 - 16:51.

Simon - that feels a little slow to me - but this is immediately after a MySql restart so it has nothing in its buffers. If you look up Fido Smith then Rover Bloggs and then Fido Smith again, I would hope that the response is better.

How much memory does MySQL have? I would have thought that you should be able to fit most of the database in memory. See also the last part of http://www.openvpms.org/documentation/csh/1.9/reference/tuning

Regards, Tim G

Re: OP running slow after power outage

Submitted by pyevet on Fri, 15/07/2016 - 17:45.

That might be it Tim. From that link:

innodb_buffer_pool_size                 | 8388608

query_cache_limit                       | 1048576

query_cache_size                        | 0

I'll up these and see what happens. There is still nfs trouble to sort out and the hub to switch link which may be adversely affecting the system in general too, but the query cache should get things moving.

Thanks.

Simon Slater

Registered Linux User #463789 @ http://linuxcounter.net

Re: OP running slow after power outage

Submitted by tgething (not verified) on Fri, 15/07/2016 - 18:13.

Simon - the query_cache is not as important as the buffer pool size. The query cache simply cached query results - so if you run the same query multiple times, it uses the cached results.

The innodb_buffer_pool_size is the important one. Yours is only 8MB odd - you should be able to push this to 800MB on a 4GB machine, or 8000MB on a 16GB machine. Ideally this is set to the same size as your database so everything runs in memory. However, for OpenVPMS systems (where there is normally a substantial amount of space taken up by images which are hardly ever accessed), buffer_pool_size == database size is an overkill.

Regards, Tim G

Re: OP running slow after power outage

Submitted by pyevet on Fri, 15/07/2016 - 18:39.

I've up'd the pool size to 500M for now and set the others to the values in the link. The query time is still about the same. Occasionally it is still over 1 minute, but is ready with a page refresh.

I'll move back to fixing the network issues - swap that old hub out for a switch and see how things go.

Simon Slater

Registered Linux User #463789 @ http://linuxcounter.net

Re: OP running slow after power outage

Submitted by pyevet on Sat, 16/07/2016 - 16:28.

That hub was the problem I think. As I was checking out the connection between that hub and a switch, other ports on the hub started failing. Swapped it for an old 10Base hub that was lying around and OP is faster than it was before. Mind you, everything else is going slower with that old hub. Will order some new kit over the weekend.

Thanks again

Simon Slater

Registered Linux User #463789 @ http://linuxcounter.net

Re: OP running slow after power outage

Submitted by Ben_Charlton on Tue, 19/07/2016 - 16:57.

There are some really good mysql memory buffer optimization applications that can be used to optimize a linux mysql server.

I am looking through my notes to find the one I used.

Additionally ...I have never found CPU usage with Tomcat to be a huge issue on Linux UNLESS the OpenOffice thread is blocked...in which case it chugs to a stop.

I use a neat little Tomcat App called Probe to monitor via a webpage the instance but you can do the same with Zabbix. We can view nice little charts like

I guess what I am seeing above is the need to be able to isolate a Server hardware/software issue from a network issue.

I use Kperf to do the testing and try and isolate network segments that I want to test.

http://forums.rptools.net/viewtopic.php?t=2607

I think I still have a copy if you want it but perhaps only for windows machines ..but there are other tools on linux that can test network bandwidth and latency