Welcome, Guest. Please login or register.
September 24, 2017, 12:19:36 PM
Home Help Search Calendar Login Register
News: All new members are manually verified so don't waste your time if your intent is to post spam in this forum!

+  Collections MAX - Debt Collection Software Forum
|-+  General Information
| |-+  Support
| | |-+  Speeding up MySQL and the software
« previous next »
Pages: [1] Print
Author Topic: Speeding up MySQL and the software  (Read 4374 times)
admin
Administrator
Hero Member
*****
Posts: 898


View Profile
« on: May 11, 2013, 07:57:12 AM »

A few agencies have expressed concerns about the software being slow.  The following will give you a bit more insight into MySQL, how to speed it up on your server and other options you have to increase speed.  Many times MySQL will not be tuned optimally right after it has been installed.....keep reading as I will disclose some important speed up tips to keep your server humming along.

MySQL was chosen as the database for Collections MAX back in 2003 when I first started coding it for a few reasons.

1.  It was faster and easier to install than PostgreSQL.  At the time PostgreSQL did not have a windows binary and was limited to Linux.
2.  The MySQL community edition is free.  Microsoft SQL server and Oracle is expensive (especially Oracle)
3.  MySQL has Innodb which is an ACID compliant data engine that recovers from crashes 99% of the time automatically and performs row level locking (like Oracle) and uses transactions (like a banking system).  Collections MAX uses Innodb exclusively.
4. The Microsoft Desktop Engine (MSDE), the free version of MSSQL Server at the time was severely limited with a small 2GB of data restriction and a governor that slowed access when more than eight connections was being made.
5.  Other free databases wasn't designed for multiple concurrent users (except for Firebird...which didn't have a .net connector at the time)

There are quite a few other data engines included with MySQL...notably MyISAM.  However while MyISAM is faster because it doesn't have any transactional overhead it is prone to crashes and doesn't support transactions which is critical for any financial business application.

MySQL used to be called the Geo Metro of databases because it wasn't that speedy under load (for business apps...it was always more than enough for websites) and it was very economical (Free).  You could say that Oracle is like a Ferrari when it comes to speed....however in the software world  it seems to cost as much as a Ferrari (around $50K per year for an Enterprise license per server + a full time Oracle DBA)...this is why high priced...high speed databases didn't fit the business model of budget friendly debt collection software.  Can you imagine our sales pitch when explaining to users how we can save them money? "That will be $1,799.95 for 10 user licenses and $50,000 for the database"....um...not happening!



MySQL has been steadily improved over the last year or two making it easier to scale...however it needs a few tweaks to get your server "tuned" properly as the default settings are usually too conservative.

In Linux you have a my.cnf file and in Windows you have a my.ini file that contains the configuration settings of MySQL.  

The first setting you need to add (or make sure it is there) is under the [mysqld] section you need to add the skip-name-resolve directive.

Quote
[mysqld]

skip-name-resolve

The skip-name-resolve directive will bypass DNS lookups which account for about 50% of the speed issues that we see.  You might need to recreate your user names after this has been added to your configuration file because MySQL will only accept connections from ip addresses and not hostnames.  This can speed up the system considerably if your DNS is bottle-necking.


The other parameter you need to adjust is your innodb buffer pool size (innodb_buffer_pool_size)  so MySQL can hold everything in memory.  If you are using a dedicated server you need to set this to 70-80% of your available RAM (if over 2GB on a 64 bit machine and 64 bit mysql)  as dictated in the following webpage.  If your server is used for running additional programs you will need to set the value less to account for it.
http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

If you change your innodb buffer pool size to more than 1 GB you will also want to change your innodb_buffer_pool_instances to multiples of 1GB.  So for instance if you set your  innodb buffer pool size to 4GB you would want to set the innodb_buffer_pool_instances to 4.
https://kb.askmonty.org/en/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances


There are other innodb parameters and this page will explain them to you.
http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html

If you are unsure about tuning the parameters yourself....you can have an online wizard do it for you and create your tuned my.ini or my.cnf file.

Percona's MySQL tuning wizard.
https://tools.percona.com/wizard


Make sure to reboot your server after making any configuration file changes.



Now...there are also other options as far as MySQL goes.  You can get an additional 25% gain in performance if you use a drop in replacement for MySQL called Percona or Maria DB.  These are forks of MySQL with additional performance enhancements.

The Percona server is higher performance server software MySQL replacement and is Linux based.  Maria DB was also designed for high performance and has both Windows and Linux variants that also contain the Percona patches.  Percona originally created a high performance version of InnoDB and named it XtraDB which both Percona and Maria DB has instead of Innodb.  This extra performance can be obtained simply by changing out MySQL and replacing it with one of these.

Percona Server - Linux Only
http://www.percona.com/software/percona-server

Maria DB - Windows and Linux
https://mariadb.org/



Now of course...hardware has a lot to do with the speed of your server.  You ALWAYS want to go with something that is 64 bit as 32 bit machines can only use 2GB of RAM for MySQL.

You have to think of MySQL this way.....the MySQL server software was most likely provided for free(community edition) so you will need to throw more powerful hardware at it than you would other more expensive database software (Oracle and Microsoft SQL)  in order to achieve respectable speed.  You make it up one way (by paying high license fees to the other database vendors) or the other (by throwing speedy hardware at it).  Since hardware is much cheaper...that is the route we need to take.

This is what I suggest.....

You want a very fast processor....like an Intel Core I7

64 bit operating system (Did you know Windows 7 shares the same kernel with Windows Server 2008?)

You also want fast hard disks.  Many agencies are migrating to the SSD (Solid State Disks) with flash memory due to it's low latency.

The second biggest performance gain (other than additional RAM) will be the addition of a SSD drive (SATA III interface)  for the server for both the Operating System (Windows, Linux) and MySQL (MySQL, MariaDB, Percona).   A good fast SSD (like the Samsung 840 PRO) can be had for under $300!  A single SSD drive is a GREAT MAGNITUDE faster than twin 15,000 RPM drives striped at RAID 0 for Random Reads.....which Collections MAX has a lot of.  Most standard hard drives can sustain about 100-200 IOPS (Input Output/ Operations Per Second) while a SSD drive can do  5,000 - 8,000 + IOPS per second.  This is a TREMENDOUS difference!

Disk I/O access time is also something to consider.  Disk access time is determined via the sum of several variables: spin-up, seek, rotational delay, and transfer time.  7,200 -15,000 RPM hard drives will have millisecond access times for each MySQL query while a SSD drive will have microsecond access times.  Since there are roughly 15 or so queries made to populate the Collections MAX Professional screen each time a new account is selected you can see why the millisecond latency of standard  hard drives equals SECONDS (when you add up the latency of 15 queries X millisecond latency) and with SSD drives the microsecond latency with the same queries will equal MILLISECONDS .
If you want millisecond speed you simply need a SSD drive in your server regardless if it is hosted or on site.

You need RAM.  Lots of it for MySQL if you have a sizable office.  The more the merrier.

64 bit versions of the latest MySQL, Percona or Maria DB.  Make sure to upgrade your SQL server software as older versions scaled horribly on multiple core computers.

You also should upgrade to at least gigabit Ethernet if you plan on putting in a server with 25 or more collectors and running voice over ip (VOIP) on the same network.  VOIP will saturate a 100mbps network fairly quickly (depending on the VOIP codec that is being used) as usually the maximum bandwidth you can get on 100mbps is around 8MB / second.  Gigabit Ethernet will also reduce latency across the network (by sending packets quicker) speeding up response times from the client software to the server....and making everything go faster.

Here is a site with some good tips about keeping your network up to speed.
http://www.techrepublic.com/article/10-tips-for-troubleshooting-slowdowns-in-small-business-networks/6157505

I hope this gives you a little bit more insight in what is needed to make your server hum.  If you still are having issues you can also try to use the Collections MAX Proxy server which puts a stronger native MySQL GPL connector in between the client applications and the database.
http://sourceforge.net/projects/cmaxproxy/

And finally...if your server seems to slow down for no apparent reason all of a sudden simply try to reboot it after everyone is off of it and reboot all of your network switches, routers and modems.  This usually fixes the problem 90% of the time.  If it keeps slowing down you might need to check your network (routers, nic cards, cables, switches, modems), reconfigure your MySQL settings, add more RAM, or change out the hardware....in that order.


Respectfully,

Frank Coukos
President and Senior Software Engineer
Decca Software Company
« Last Edit: December 21, 2014, 08:23:05 PM by admin » Logged
Pages: [1] Print 
« previous next »
Jump to:  


Login with username, password and session length

Powered by MySQL Powered by PHP Powered by SMF 1.1.11 | SMF © 2006-2009, Simple Machines LLC Valid XHTML 1.0! Valid CSS!