Databases play key role in dynamic websites.
Whether be of any type, like RDBMS, NoSQL, Distributed file system or simple CSV notepad file type database, they are undoubtedly backbone of application data.
In this text, we are going to narrow our focus on world class, most widely used and free digital database namely MySQL.
Administration related work like
- Installing MySQL database server and creating a database
- Attaching database to web application
are routine tasks for most software folks.
“So, what you can do in Microsoft Word is what Bill Gates has decided. What you can do in Oracle Database is what Larry Ellison and his crew have decided.”
Your money is respected only when software company allocates DBA resource
There are many software vendors and dealers who have NOT gone much past after executing above 2 tasks except writing few liberal SQL queries.
They executed these tasks as part of standard SDLC process and to pack the product to make it sale-able to customer. Otherwise many groups do NOT possess dedicated resource for digital database management.
Few are so small companies running with bare minimum of 3-4 software engineers to save their money, they no further look into once website is delivered to customer and their bank account is credited.
In this situation, they can’t resolve database performance related issues, but neither they are ready to make any deduction in costs charged to clients. Clients are often un-aware of these technical matters, and even if they are, generally don’t know what is offered to them and what are industry standard practices for fees and related skilled work.
Let us breathe frequently needed several important options and related values in MySQL database.
- Query caching
- The MyISAM and InnoDB storage engines
- The Percona DB and Percona XtraDB storage engines
- MySQL performance monitoring tools
- Redis
- Memcached
While being free and open source, MySQL is NOT dumb. Neither it means that it is defectful.
Rather, MySQL, ( hence MariaDB and Percona too – being forks of MySQL ) is a feature rich software product employed by many enterprise businesses and institutions.
Before applying these techniques, also remember that poorly designed database’s performance tuning will be a prolonged process, for it has fundamental level, i.e. design side problems as opposed to configuration settings problems.
- Query caching:
Many OLTP systems as well as enterprise ERP systems like those in banks daily execute tons of transactions, but only few transactions are with different semantics.
These similar or identical transactions run with help of SQL queries behind the scenes. Only values change with each SQL statement while patterns remain same.
If semantics of these SELECT statements can be fetched from physical memory as opposed to from disk space or disk swap space, response time to end user can be much lesser.
Also, it will consume less CPU cycles thereby increasing CPU life and thus, it will reduce costs of hardware.
Query caching feature can enable this kind of functionality.
To check whether it is already implemented or not, go to MySQL prompt and run following command.
MySQL> SHOW VARIABLES LIKE ‘have_query_cache’;
If preceding command displays ‘NO’ in the output in result set, enable query cache by uncommenting below code lines from the my.cnf file.
query_cache_type = 1
query_cache_size = 128MB
query_cache_limit = 1MB
Re-enter SHOW VARIABLES statement and see output this time, it should be altered to ‘YES’.
- Storage engines
Storage engines reflects table types and their architecture in specific engine.
There are many MySQL storage engines, but in practice 2 are most widely used.
- MyISAM
- InnoDB
Both these storage engines are robust and tested for years.
Although both have their own pros and cons, InnoDB is prioritized and has become standard due to its technological advanced features.
It is default engine packed in MySQL server database instances starting from 5.5
To gain performance benefits from tables created with InnoDB style structure, you need to take for below 3 parameters at bare minimum, all resided in my.cnf configuration file.
- innodb_buffer_pool_size– Set this parameter to absorb 50-70% memory from what is available and leave remaining to operating system program.
- innodb_buffer_pool_instances
This feature is NOT widely known or practised but it is recommended.
It enables multiple buffer pool instances.
All instances will work together to reduce the chances of memory contentions, especially on a 64-bit system.
You may need to set a large value for innodb_buffer_pool_size parameter while working with this parameter.
- innodb_log_fle_size
Larger log size for each database transaction compensate better diagnosis information. Set it from any values between 1 GB to 4 GB.
“Oracle’s latest database, version 12c, was specifically designed for the cloud. Oracle 12c makes all your Oracle applications multitenant applications without you having to make any changes whatsoever to your applications.”
Continue exploring more at.. http://www.blisswebsolution.com
Simply to read more articles on Web application development areas or related to open source databases and technologies, navigate to http://www.blisswebsolution.com/blog/
If you are a tech person or MySQL update related news interests you, you can attend our discussion forum without any obligation, just drop an e-mail at enquiry@blisswebsolution.com.