quinta-feira, 21 de agosto de 2014

Automation & Management of MariaDB Galera Clusters: New European Webinars with SkySQL - The MariaDB Company - Jean-Jerome Schmidt


August 21, 2014
By Severalnines

MariaDB Galera Cluster involves more effort and resource to administer than standalone MariaDB systems. If you would like to learn how to better manage your MariaDB cluster, then this webinar series is for you. 


We will give you practical advice on how to introduce clusters into your MariaDB / MySQL  environment, automate deployment and make it easier for operational staff to manage and monitor the cluster using ClusterControl.


Language, Date & Time: 


English - Tuesday, September 30th @ 11am CEST: Management & Automation of MariaDB Galera Clusters

French - Tuesday, October 7th @ 10am CEST: Gestion et Automatisation de Clusters Galera pour MariaDB

German - Wednesday, October 8th @ 10am CEST: Verwaltung und Automatisierung von MariaDB Galera Cluster


High availability cluster configurations tend to be complex, but once they are designed, they tend to be duplicated many times with minimal variation. Automation can be applied to provisioning, upgrading, patching and scaling. DBAs and Sysadmins can then focus on more critical tasks, such as performance tuning, query design, data modeling or providing architectural advice to application developers. A well managed system can mitigate operational risk, that can result in significant savings and reduced downtime. 




MariaDB Roadshow - London


And if you’re in London this September, do join us at the MariaDB Roadshow event on Thursday, September 18th. We’ll be talking about Automation & Management of Database Clusters there as well and would love to talk to you in person! 


read more

from Planet MariaDB http://ift.tt/1lvLCrP

quarta-feira, 20 de agosto de 2014

Free PHP MYSQL Hosting with CPanel? Hospedagem de PHP e MySQL gratuita com CPANEL?

I'm doing some tests and experiments with php and mysql and Your3Host was a great option among so many on the web. 10 GB of space available, CPanel and a bunch of amazing features:

Full Features List

  • Completely free
  • Disk space 10GB
  • Bandwidth 100GB
  • Free domains Unlimited
  • No ads
  • Email accounts Unlimited
  • FTP accounts 5
  • MySQL database 5
  • Free domains Unlimited
  • Parked domains Unlimited
  • Free Website builder & Publisher
  • Web SSH console
  • CPanel
  • Hotlinking protection
  • htaccess support
  • Customizable Error Pages
  • Zend Optimizer
  • E-mail Hosting features
  • POP3 Accounts
  • WebMail Access (RoundCube, SquirellMail)
  • E-mail alias
  • Spam Assasin Protection
  • Free Profesional Web Design Templates
  • No contract!
  • Free / Instant setup
  • Help Center & Support
  • Knowledge Base & FAQ
  • Weekly backups
  • 99.9% Uptime
  • Support International Domain names
  • Supported Technical Features And Databases
  • FTP Access
  • 2 Web File Managers
  • MySQL 5 Database Server
  • PHP 5.2, PHP 5.3, PHP 5.4
  • ionCube Loaders
  • WordPress Hosting
  • Joomla Hosting
  • Drupal Hosting
  • Concrete 5 Hosting
  • Instant Guestbook
  • Instant counter
  • Instant FormMail
  • Redirect URL
  • Password protected directories
  • E-Commerce features
  • osCommerce auto installer
  • Zen Cart auto installer
  • OpenCart auto installer
  • PrestaShop auto installer
  • Mail forwarding features
  • IMAP Support
  • Free Hosting CPanel features & scripts
  • PhpMyAdmin
  • 1 click Web site Installer
  • IP deny Manager
  • Instant Blogs
  • Instant Portals
  • Instant PHPnuke
  • Instant Forums
  • Free SEO tools, Search Engine submitter
  • Multimedia features
  • Streaming Audio / Video
  • Real Audio and Video support
  • DHTML Support
  • Flash Support
  • Shockwave Support
  • MIDI File Support
  • Javascript Support
  • Add custom MIME Types
  • New generation hosting technology
  • Dual Quad Processor Performance Servers
  • Secure Linux OS
  • Optimized Apache Web Server
  • 24/7 Network Monitoring
  • Multiple 10 Gigabit Ethernet Connections
  • cURL
  • Custom Cron jobs

Check it out here!

Check out my new book on MariaDB http://www.amazon.com/dp/B00MQC06HC

New Book "MariaDB - Beginners Guide"

New book "MariaDB - Beginners Guide" http://ift.tt/1l8na5e

Using resource monitoring to avoid user service overload


Tue, 2014-08-19 11:18

With MariaDB, as with any service, you must monitor user resource usage to ensure optimal performance. MariaDB provides detailed statistics for resource usage on per-user basis that you can use for database service monitoring and optimization. User statistics are especially useful in shared environments to prevent a single gluttonous user from causing server-wide performance deterioration. If you detect abnormal use, you can apply fine-grained limits, as we'll see.

To enable user statistics in MariaDB, edit the server configuration file /etc/my.cnf.d/server.cnf. In the [mysqld] section, add userstat = 1, then restart the service.

Now MariaDB will gather and store usage statistics in the table USER_STATISTICS in the database information_schema. USER_STATISTICS uses the Memory engine and does not preserve information upon service restarts, so statistics are reset when you restart the MariaDB service. You can also reset statistics manually with the command FLUSH USER_STATISTICS;.

Retrieving user statistics

To see all the user statistics, use the command SHOW USER_STATISTICS. It returns all the information about all the users, and gives you an overall look at resource usage. The output can help you spot inappropriately high usage by one user compared to others.

You can get more summarized information by filtering your query and retrieving information directly from the database with a command such as select CPU_TIME from information_schema.USER_STATISTICS where USER='test1';. This command shows the cumulative CPU time in seconds spent on serving user test1's connections.

Understanding user statistics

An example output of all the user statistics for user test1 might look like this:

  MariaDB [(none)]> select * from information_schema.USER_STATISTICS where USER='test1' \G  *************************** 1. row ***************************                    USER: test1       TOTAL_CONNECTIONS: 105  CONCURRENT_CONNECTIONS: 0          CONNECTED_TIME: 0               BUSY_TIME: 0.10427200000000013                CPU_TIME: 0.028732600000000018          BYTES_RECEIVED: 8190              BYTES_SENT: 86520    BINLOG_BYTES_WRITTEN: 0               ROWS_READ: 630               ROWS_SENT: 735            ROWS_DELETED: 0           ROWS_INSERTED: 0            ROWS_UPDATED: 0         SELECT_COMMANDS: 210         UPDATE_COMMANDS: 0          OTHER_COMMANDS: 0     COMMIT_TRANSACTIONS: 105   ROLLBACK_TRANSACTIONS: 0      DENIED_CONNECTIONS: 15        LOST_CONNECTIONS: 0           ACCESS_DENIED: 15           EMPTY_QUERIES: 0  1 row in set (0.00 sec)  

The names of the fields explain what information they hold. The most important ones are:

  • TOTAL_CONNECTIONS, CONCURRENT_CONNECTIONS, and CONNECTED_TIME – If any or all of these are high, you may see errors such as 'Too many connections.' By default, in MariaDB the maximum number of connections to the servers is just 151, which aggressive users can easily exhaust.
  • BUSY_TIME and CPU_TIME – BUSY_TIME indicates for how long there was activity on the user connections, while CPU_TIME indicates the CPU time spent on servicing the user connections. The latter is more important, as it shows the direct user impact on CPU utilization.
  • BYTES_RECEIVED and BYTES_SENT – These two indicators are useful for monitoring network traffic that originates with MariaDB users. Usually high traffic is not a problem with databases, but in times of service overload the traffic statistics could help spot the base problem faster.
  • BINLOG_BYTES_WRITTEN – This indicator may help spot abnormal activity in the binary logs, which are used for replication or backup purposes. If your binary log starts growing unexpectedly, check this indicator first.
  • ROWS_READ, ROWS_SENT, ROWS_DELETED, ROWS_INSERTED, SELECT_COMMANDS, UPDATE_COMMANDS, OTHER_COMMANDS, COMMIT_TRANSACTIONS – These indicators give detailed information about a user's SQL work. Along with BUSY_TIME and CPU_TIME, they can give a full picture of the user's impact on the system's load.
  • ROLLBACK_TRANSACTIONS – An unusually high number or peaks in this indicator may show problems in the front-end application. A high number of rollback transactions may cause overload, because the front-end application is usually supposed to try recreating the information or query, thus causing additional load for every rolled-back transaction.
  • DENIED_CONNECTIONS and ACCESS_DENIED – These two indicators are useful mostly for security purposes and for troubleshooting application problems with incorrect logins. When a user is denied a connection, the attempt goes to DENIED_CONNECTIONS. A denied connection usually indicates incorrect privileges to establish the connection in the first place. ACCESS_DENIED, on the other hand, usually appears when a user has already established a successful connection but has been denied access to certain resource (database or table).

Taking action

Once you detect abnormally high activity from a user, you can take action to limit the resources allocated to the user by using the account resource limits feature. For instance, for the test1 user, you could run the query update mysql.user set max_connections=10,max_updates=100,max_questions=1000 where user='test1';. To make this change take effect, also run the command flush privileges;. The specified user's resources will be limited, and your server's performance should return to normal.

As you can see, MariaDB resource statistics and limits are useful for maintaining optimal service performance.


About the Author

anatoliydimitrov's picture
Anatoliy Dimitrov

Anatoliy Dimitrov is an open source enthusiast with substantial professional experience in databases and web/middleware technologies. He is as interested in technical writing and documentation as in practical work on complex IT projects. His favourite databases are MariaDB (sometimes MySQL) and PostgreSQL. He is currently graduating his master's degree in IT and aims to a PhD in Bionformatics in his home town University of Sofia.

from MariaDB blogs http://ift.tt/1vgGLRg

How to use MySQL Global Transaction IDs (GTIDs) in production - Peter Zaitsev


Reconfiguring replication has always been a challenge with MySQL. Each time the replication topology has to be changed, the process is tedious and error-prone because finding the correct binlog position is not straightforward at all. Global Transaction IDs (GTIDs) introduced in MySQL 5.6 aim at solving this annoying issue.

The idea is quite simple: each transaction is associated with a unique identifier shared by all servers in a given replication topology. Now reconfiguring replication is easy as the correct binlog position can be automatically calculated by the server.

Awesome? Yes it is! However GTIDs are also changing a lot of things in how we can perform operations on replication. For instance, skipping transactions is a bit more difficult. Or you can get bitten by errant transactions, a concept that did not exist before.

Percona MySQL webinarsThis is why I will be presenting a webinar on Aug. 27 at 10 a.m. PDT: Using MySQL Global Transaction IDs in Production.

You will learn what you need to operate a replication cluster using GTIDs: how to monitor replication status or to recover from replication errors, tools that can help you and tools that you should avoid and also the main issues that can occur with GTIDs.

This webinar is free but you can register today to reserve your seat. And a recording will be available afterwards. See you next week!

The post How to use MySQL Global Transaction IDs (GTIDs) in production appeared first on MySQL Performance Blog.

from Planet MariaDB http://ift.tt/1lvLCrP