Chapter 1: An Introduction to InterWorx’s MySQL Integration Up Part I: MySQL administration via NodeWorx Chapter 3: Remote Servers 

2 NodeWorx MySQL Overview Page

The overview page, located in NodeWorx under System Services ▷ MySQL Server ▷ Overview, allows your standard InterWorx Start/Stop/Restart of the MySQL server daemon. In addition, you are able to modify whether the service starts on boot[A][A]This is done on the back-end by setting the service to “on” via chkconfig. Auto-restart MySQL will cause InterWorx to meticulously monitor the mysql service and attempt to start it back up if the service remains down for a predetermined amount of time (this will prevent InterWorx from restarting the daemon in the event the service is already restarting automatically due to log rotation or manual restart). While these are pretty standard for all InterWorx services, what is not standard are the server-specific settings which are configured by modifying the /etc/my.cnf configuration live.
figure images/nodeworx/mysql/mysql-server-control.png
Figure 2.1 The MySQL Overview Page

2.1 Detailed descriptions of Settings found on Overview Screen

Tweaking your MySQL configuration is fairly simple via the MySQL overview screen

2.1.1 Connections(max)

This determines the maximum number of simultaneous client connections. Keep in mind increasing this value also increases the maximum amount of UNIX file descriptors that the server needs. If that statement means nothing to you, just interpret it as “the maximum resources my server will need will increase”. You need more memory to manage all those connections, more CPU time to handle all the concurrent activity. On the other hand, if your MySQL server is not particularly greedy, you may be starving the applications which rely on the server for data storage. According to MySQL’s documentation, the default connection count is 151. Keep in mind that in a typically single page load for something like WordPress, a connection is held open for a fraction of a second. The value of your MySQL connection count should only be raised if you find that your applications are reporting errors of being unable to connect due to the max connection limit being hit (MySQL will tell you this). These errors will typically manifest themselves in the error logs of the SiteWorx account with the application installed.

2.1.2 Connection Errors (max)

This sets the /etc/my.cnf value max_connect_errors. The MySQL documentation says the following:
If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections. You can unblock blocked hosts by flushing the host cache. To do so, issue a FLUSH HOSTS statement or execute a mysqladmin flush-hosts command. If a connection is established successfully within fewer than max_connect_errors attempts after a previous connection was interrupted, the error count for the host is cleared to zero. However, once a host is blocked, flushing the host cache is the only way to unblock it.[B][B]
Basically, the setting is a security setting that is designed to combat brute-force password guessing attacks. Most hosts don’t enforce it because none of the users are permitted remote login, or TCP port 3306 is blocked in the firewall. If you have clients connecting direct to your MySQL server remotely though, you may want to lower this to a reasonable amount to prevent brute-force login attempts. The default is 10.

2.1.3 Connect Timeout

This specifies how long it takes for the server to wait on a connect packet before the server deems that the connection has timed out. This value is normally not that critical but it may be helpful to modify it if clients frequently encounter errors of the form: Lost connection to MySQL server at ’XXX’, system error: errno. The default MySQL value is 10.
What does it mean to wait for a connect packet? A connection to a MySQL server requires 2 things - a TCP connection, and then via MySQL’s protocol, a request to connect. Connect timeout will become a factor only when a remote client has initiated a TCP connection to port 3306 - but has not sent a request to connect to the MySQL server proper. In layman’s terms: the MySQL server is aware that there is some computer connected to it, but it will not consider it a client to it’s service until the remote computer says “I know you are a MySQL server, I want to connect to your service!”. If the remote computer fails to provide this within the connect timeout window, MySQL will drop the connection.

2.1.4 Wait Timeout

This specifies how long a MySQL connection can remain open with nothing happening on it before the server kills the connection. If you for some reason need to remain connected to your MySQL server for long periods of time, you may want to increase this. On the other hand, consider that a connection open is eating up one of your max_connections slots. You may want to lower the timeout to prevent a bunch of users from sitting connected for a lengthy period of time. The default is 28800 seconds.

2.1.5 Key Buffer Size

	userid integer,
	Last_Name varchar(30),
	First_Name varchar(30),
	PRIMARY KEY (userid)
Figure 2.2 An example of creating a table with a primary key
When defining MySQL tables, you occasionally may define one of the columns as a Primary Key, Unique, or Index column. I show an example of this in Figure 2.2↑. You may do it for the ability to put restrictions on what rows you can insert into your table. I.e if I have a column for “userid” I don’t want 2 different users with the same userid, so I would set the column to be a primary key or unique so that the database system doesn’t allow me to accidentally insert a record with an identical ID.
The column key designation also has the additional effect of making data look-ups faster. For example, if I have a column userid in a table users, and I have 1 million users in my database, a query like SELECT * FROM users WHERE userid=10 will be trivial for MySQL to perform because each row in the table will be “optimized” to be looked up by the userid[C][C]This is because on the back-end, the primary key is fed into a hashing algorithm to find the correct row in the file that backs your database. . In order to speed things up even more, commonly used meta data[D][D]index blocks which more than likely contain hash tables for rapid look-ups of specific rows based on keys that is used by the server to find rows quickly is cached in memory. This is known as the Key Buffer or Key cache and you can control it’s size. By default, most users will want to stay with what ever the key buffer size is on operating install. But if you are more adventurous or you have a lot of memory that you can devote to the MySQL server, changing the value may be in your interest. To give more detailed information on modifying this value, we will quote the MySQL documentation again:
The maximum permissible setting for key_buffer_size is 4GB on 32-bit platforms. As of MySQL 5.0.52, values larger than 4GB are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB with a warning). The effective maximum size might be less, depending on your available physical RAM and per-process RAM limits imposed by your operating system or hardware platform. The value of this variable indicates the amount of memory requested. Internally, the server allocates as much memory as possible up to this amount, but the actual allocation might be less.
You can increase the value to get better index handling for all reads and multiple writes; on a system whose primary function is to run MySQL using the MyISAM storage engine, 25% of the machine’s total memory is an acceptable value for this variable. However, you should be aware that, if you make the value too large (for example, more than 50% of the machine’s total memory), your system might start to page and become extremely slow. This is because MySQL relies on the operating system to perform file system caching for data reads, so you must leave some room for the file system cache. You should also consider the memory requirements of any other storage engines that you may be using in addition to MyISAM.[E][E]
It should be noted here that your InterWorx server is not only running MySQL and dedicating 25% of your system’s memory to the MySQL key cache is not recommended. 25% should probably be the maximum memory footprint that the entire MySQL service takes up.

2.1.6 Sort Buffer Size

The sort buffer is a per-session (read: per connection) buffer that is created on demand when sorting or grouping needs to happen in order to process a query. In SQL this translates to when ORDER BY or GROUP BY are used to try and filter/sort data. By default this value is set to 2MB and it is generally recommended to keep this value at default or within 256KB-4MB since this is a per session cache. 100 concurrent sessions doing heavy sorting would eat 200MB of memory solely for sorting (not including various other caches). In general, MySQL recommends that if you have slow queries with ORDER BY or GROUP BY in them, you should investigate better query optimization or improved indexing instead of messing with the sort buffer size first. The maximum size for the sort buffer is 4GB, but again - you don’t want huge sort buffers.

2.1.7 Read Buffer Size

Any MySQL query that needs to do a sequential (read: in order) scan of a given table needs somewhere to store in memory what it has read thus far as it traverses down the table. This is the read buffer and its size is controlled by the read_buffer_size option. By default MySQL sets the value to 131072 bytes, or 128Kbytes. The thing to note about the read buffer size is that the byte size should be a multiple of 4KB or 4096 bytes, the default page size of most operating systems on x86. If the value entered is not a multiple of 4KB, it will be rounded down to the nearest multiple when the server daemon is started. You may want to increase this value if you have extra memory and some of the tables that are getting scanned are quite big.

2.1.8 Maximum Allowed Packet Size

While packets or frames have different meanings in the context of Ethernet and TCP/IP protocols, in MySQL land the communication packet refers to the set of data that a client issues to a server when making an SQL query, or the set of data corresponding to a single table row being sent back to the client. For the most part, the maximum allowed packet size is not something that is typically going to be modified by most hosts since the default of 1MB is sufficient in most cases. On the other hand, certain applications may make use of the BLOB or TEXT column type, which can accept very large data inputs. If you have clients storing large sets of data in their database columns and they are getting errors such as “Packets larger than max_allowed_packet are not allowed” when they attempt to insert or select large table rows, you will want to increase this value to accommodate that data set.

2.1.9 Thread Cache Size

In order to concurrently serve the SQL queries of multiple applications and MySQL clients at once, the MySQL server uses threads. For the non-programmer, a thread is like a mini processes that runs inside the process of the MySQL daemon. It basically operates independent of the execution of other threads, including the one that was started with the program. This allows for the MySQL daemon to concurrently handle multiple connections and queries simultaneously. When a new connection comes in, MySQL (and possibly the operating system) has to expend some CPU time and memory to initialize a new thread to handle the session. The the thread cache allows MySQL to save some threads instead of just letting them die at the end of handling a connection so that the next connection can be serviced quicker.
This variable is typically only really worth modifying if you know you have hundreds of new connections per second and you are seeing a huge performance hit. The default is 0, connection threads are not cached.

2.1.10 Table Cache

This is the number of tables that MySQL can have open concurrently. It is important to note that MySQL will “open” a table once per connection - that means that even if you have a single table in your database, if 100 connections need that table, MySQL will attempt to open that table up to table_cache times.
Table cache is lightly related to the Connections(Max) field as described in section 2.1.2↑. Basically if you are expecting a certain number of connections, let’s say for example 100, you want table cache to be set to 100*N where N is the maximum number of tables you are JOIN-ing together in a given query. Understandably, this value is hard to guess and as such, it is safe to say on a default InterWorx install, you can leave this value unchanged until either the concurrent number of connections on your MySQL server increases, or you notice that queries going through are JOIN-ing a significant number of tables.
Another thing to bear in mind with the Table Cache is that each open table requires an additional Linux file descriptor. Occasionally operating system security, VPS security (if you are inside a VPS), or even MySQL’s open-files-limit can cap the number of file descriptors that are allowed to be open by the MySQL daemon process. If MySQL hits this limit, it will “panic” and disallow any new connections. The MySQL documentation says the following:
“Make sure that your operating system can handle the number of open file descriptors implied by the table_cache setting. If table_cache is set too high, MySQL may run out of file descriptors and refuse connections, fail to perform queries, and be very unreliable. You also have to take into account that the MyISAM storage engine needs two file descriptors for each unique open table. A MyISAM table is opened for each concurrent access. This means the table needs to be opened twice if two threads access the same table or if a thread accesses the table twice in the same query (for example, by joining the table to itself). Each concurrent open requires an entry in the table cache. The first open of any MyISAM table takes two file descriptors: one for the data file and one for the index file. Each additional use of the table takes only one file descriptor for the data file. The index file descriptor is shared among all threads.”[F][F]
The cache of open tables is kept at a level of Table Cache entries. The default value is 64. Note that MySQL may temporarily open more tables than this to execute queries. MySQL closes an unused table and removes it from the table cache under the following circumstances:
  • When the cache is full and a thread tries to open a table that is not in the cache.
  • When the cache contains more than table_cache entries and a table in the cache is no longer being used by any threads.
  • When a table flushing operation occurs. This happens when someone issues a FLUSH TABLES statement or executes a mysqladmin flush-tables or mysqladmin refresh command.
When the table cache fills up, the server uses the following procedure to locate a cache entry to use:
  • Tables that are not currently in use are released, beginning with the table least recently used.
  • If a new table needs to be opened, but the cache is full and no tables can be released, the cache is temporarily extended as necessary. When the cache is in a temporarily extended state and a table goes from a used to unused state, the table is closed and released from the cache.

2.1.11 Query Cache Limit

By default, MySQL will try to cache the results of a SELECT statement so that if the query is issued often and the data backing the query doesn’t change, the expensive computation needed to return the query doesn’t have to be performed a second time. Most applications change their databases less often than how often data is requested from the database. As such, MySQL is able to return the result of query much faster if it has the result already sitting in memory.
The Query Limit Cache controls what the maximum size of a result is that is allowed to go into a cache. By default, this is set to 1MB. You can increase this to improve performance on queries which return large data sets, but you do so at the risk of significantly increasing the memory footprint of the MySQL server.

2.1.12 Query Cache Size

The Query Cache Size variable controls the size to allocate to query caching on the MySQL server. This value must be a multiple of 1024 bytes, and must be at minimum of 40KB to allocate the data structures required for caching. According to MySQL, the default is 0 (as in, no caching will occur). 64-256MB is not unreasonable, though, depending on your resources available.

2.2 Additional features of Overview Page

In addition to manipulating settings found in the /etc/my.cnf MySQL configuration file, this page also allowed you to set the MySQL root password, and view active processes.

2.2.1 The MySQL Server Root Password

A common question InterWorx gets is - what is my MySQL server’s root password? During installation, InterWorx connects to the MySQL database using the MySQL root user since the root user should not have a password after Operating System install. InterWorx creates a special iworx user that has all root privileges which is what InterWorx will use from that point on to interact with the MySQL server. InterWorx then sets a random, scrambled, gibberish password for the MySQL root user so that it is not accessible without a password anymore.
Therefore, by default, no one knows your root MySQL password. Since InterWorx’s iworx user has root privileges, InterWorx is able to set your root password if you wish to access the database by that user. This can be done through MySQL root password box, visible in figure 2.1↑.

2.2.2 Current MySQL Processes

This table will show you the current snapshot of MySQL processes which is done by running SHOW PROCESSLIST as the iworx user (which has root privileges as described in section 2.2.1↑).
Value Meaning
Binlog Dump This is a thread on a master server for sending binary log contents to a slave server.
Change user The thread is executing a change-user operation.
Close stmt The thread is closing a prepared statement.
Connect A replication slave is connected to its master.
Connect Out A replication slave is connecting to its master.
Create DB The thread is executing a create-database operation.
Daemon This thread is internal to the server, not a thread that services a client connection.
Debug The thread is generating debugging information.
Delayed insert The thread is a delayed-insert handler.
Drop DB The thread is executing a drop-database operation.
Error Bad things.
Execute The thread is executing a prepared statement.
Fetch The thread is fetching the results from executing a prepared statement.
Field List The thread is retrieving information for table columns.
Init DB The thread is selecting a default database.
Kill The thread is killing another thread.
Long Data The thread is retrieving long data in the result of executing a prepared statement.
Ping The thread is handling a server-ping request.
Prepare The thread is preparing a prepared statement.
Processlist The thread is producing information about server threads.
Query The thread is executing a statement.
Quit The thread is terminating.
Refresh The thread is flushing table, logs, or caches, or resetting status variable or replication server information.
Register Slave The thread is registering a slave server.
Reset stmt The thread is resetting a prepared statement.
Set option The thread is setting or resetting a client statement-execution option.
Shutdown The thread is shutting down the server.
Sleep The thread is waiting for the client to send a new statement to it.
Statistics The thread is producing server-status information.
Table Dump The thread is sending table contents to a slave server.
Table 2.1 Values for the “Command” field and their meanings
ID is the the “connection identifier”, or how MySQL internally references the connection that issued the query.
User is the MySQL user that is currently connected. This can be particularly if you have many SiteWorx accounts and you are trying to track down particularly resource abusive sites. Users with high process run time and many processes in this list will probably be issuing extremely inefficient queries. Alternatively, their data sets may have grown large enough that some tweaking needs to be performed in order to improve server performance.
Command is the type of command the thread is currently executing. Keep in mind that this is not analogous to what query was executed, but what “internal” procedure the thread is performing. They can take on the values seen in table 2.1↑. Essentially this is the “state” the thread is in. Most of the time, threads are busy in the Query state. With the popularity of using prepared statements, though, you may see other states occasionally on a busy server.
Time is the amount of time in seconds a thread has been in its current state.

2.2.3 RRD Graph

As a web host, you often want to track basic statistics of your server in order to determine if work load or resource usage has changed over time. Also on the MySQL overview page is a graph of the average number of MySQL queries over time, with the X-axis representing time and the Y-axis representing the number of queries. A picture of a (blank) graph can be seen in figure 2.3↓. This can be useful if you notice your MySQL server has been using more resources lately - the graph can demonstrate whether that is due to an increase query workload or not.
figure images/nodeworx/mysql/mysql-server-managment.png
Figure 2.3 The MySQL RRD Graph
 Chapter 1: An Introduction to InterWorx’s MySQL Integration Up Part I: MySQL administration via NodeWorx Chapter 3: Remote Servers 

(C) 2017 by InterWorx LLC