Wednesday, January 10, 2018

MySQL: Too many connection errors.

When user trying to connect MySQL Database, he got below error

ERROR 1040 (hy000): Too many connections
By looking the error we can see that the max_connections got exhausted. You can check the connection info by running below commands.

-- To find max_connections value run below command
SHOW VARIABLES LIKE '%max_connections%';

-- To see the all processes and connections run below command

Note that when you get this error you will be able to login using root. Becoz My SQL by default will consider maximum allowed connections as MAX_CONNECTIONS +1 for super user.

But if you have used root to connect to any other app you will not able to connect to run the above commands. That means you should use root for only Administration purpose only.

You can run below command to change the MAX_CONNECTIONS

SET GLOBAL max_connections = 200;
But note that you need to find out is there any application issue which is creating more connections and causing this issue and fix that later.

Satishbabu Gunukula, Oracle ACE

Best practices for running Tableau on a VM (Virtual Machine)

Tableau Server is resource-intensive and latency-sensitive, that’s why if you are running Tableau on a VM then dedicated resources required.

1.       Dedicated vCPU
100% dedicated vCPU allocation – should not be pooled or shared.
Core count is based on "physical" cores. Physical cores can represent actual server hardware or cores on a virtual machine (VM). Hyper-threading is ignored.
2.       Dedicated RAM
Fully (100%) pre-allocated, no pooling or sharing, no dynamic RAM.
Contiguous on the host server.
Suggested 128GB RAM per 8 core for Enterprise Deployments.
3.       Disk
Write speed is often the bottle neck, faster the better!
150MB/s or less = BAD
400MB/s to 1GB/s+ = GREAT
250MB/s Write & 1GB/s Read = Good performance
Tiered SAN: Tableau should be on higher tier with better IO than typical storage level tier.
No underlying network attached storage (NAS) for disk.

Always note that Poorly tuned VM’s results “Poor performance” and Wasted $$ on core purchases

VM Tuning Tips Directly From VMWare
        See page 17 for recommended BIOS setting
       Deploying Extremely Latency-Sensitive Applications in vSphere 5.5 (
        See page 15 for best practices for latency-sensitive applications
       Upgrade network adapter driver in VM cluster

Other things needs to consider

       Virus Scan can impact performance
       Network latency between worker machines should be less than 10ms.
       Single network hop between servers (contiguous network access).

Satishbabu Gunukula, Oracle ACE