mysql: What actions or practices contribute to the high CPU and RAM usage in SQL databases?

I'm curious about what common practices or actions contribute to peaks or the high CPU and RAM usage in SQL databases. I am trying to estimate the resources that I would assign to a new MySQL database (vertical update), based on the current database resource. The use and how much it is estimated that this use will increase in the future.

For example:
– "Highly complex search and combination queries can increase CPU utilization, because …"

mysql: how to reduce the time of a query that has 4 combinations

I'm new to CakePHP and I'm working on the CakePHP version 3.8. There is a union query that takes almost 10 -12 seconds to retrieve data. I do not know how to optimize this query. This query is written in CakePHP 3.8 format.

SELECT Coach.email AS `Coach__email`,
Coach.name AS `Coach__name`,
Coach.account_id AS `Coach__account_id`,
Coach.id AS `Coach__id`,
Coach.last_login AS `Coach__last_login`,
Coach.subscription AS `Coach__subscription`,
Coach.linked_agency AS `Coach__linked_agency`,
A.name AS `A__name`,
Find_in_set (2, U.activate) AS `active`,
(Count (C.id)) AS `count`,
(Count (CA.id)) AS `countactive`
FROM jsb_coach Coach
INNER JOIN jsb_accounts U
ON U.id = Coach.account_id
JOIN LEFT jsb_client C
ON C.coach_id = Coach.account_id
JOIN LEFT jsb_client CA
IN CA.id = C.id
And CA.status LIKE 1
JOIN LEFT jsb_agency A
ON Coach.agency_id = A.account_id
GROUP BY Coach.id
ORDER BY Coach.last_login DESC 

mysql – I have to store image field data in image table and category field data in category table from a single form using php

Stack exchange network

The Stack Exchange network consists of 176 question and answer communities that include Stack Overflow, the largest and most reliable online community for developers to learn, share their knowledge and develop their careers.

Visit Stack Exchange

java – Problem to send data from mysql to control select in jsp using servlet

Dear,
I'm trying to send the data of an sql query to an html select control using jsp and servlet and ajax, the problem that when sending the data by ajax, it does not bring me the combo box with the data and the console does not throw me error either, just It tells me successful and serious connection.

I have an sql query that shows some data through a parameter in the FoliosDao class.
the parameter is the rut, through this parameter I get the mysql data.
In the servlet I call the FoliosDao class to access the method and thus take the data. the parameter sent from the jsp form using ajax.
When pressing the button, the system does not show the result, nor does it show me errors in the console nor in netbeans.
your help please.

enter the description of the image here

Here I leave the code.

Ajax function


Form with button

Ruth:

Div container where the result should be shown

Servlet class

                protected void processRequest (HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException, SQLException {
    response.setContentType ("text / html; charset = UTF-8");


    Folio fl = new Folio ();
    User rutUs = new User ();

    FoliosDao fodao = new FoliosDao ();

try {
    Connection conn = ConnectionMysqlCargomove_db.getInstance (). GetConnection ();
    rutUs.setRutPersona (request.getParameter ("rutUserJS"));

    ResultSet res = (ResultSet) fodao.listarFoliosReportReservasPorRut (rutUs);
    ArrayList numberFolio = new ArrayList();

    if (res == null)
    {
        String error = "No data found";
        request.getSession (). setAttribute ("error", error);

    }

    else
    {

            while (res.next ())
            {
                numberFolio.add (new Folio (res.getInt ("cod_seq_awb"), res.getString ("awb")));
            }
            request.getSession (). setAttribute ("numberFolio", numberFolio);
            ArrayList foli = (ArrayList) request.getSession (). getAttribute ("numberFolio");
            PrintWriter out = response.getWriter ();



                try {


                            / * ALL output your page here. You may use following sample code. * /
            out.println ("");
                            out.println ("");
                            out.println ("");
                            out.println ("");
                            out.println ("");


                            out.println ("");
                            out.println ("");
                            out.println ("");


                } finally {
                    out.close ();
                }




    }

}
catch (java.lang.NullPointerException ex)
    {
            ex.getMessage ();
    }
}

FoliosDao class method class

                public ResultSet listFoliosReporteReservasPorRut (User usr)
{
    try {
        Connection conn = ConnectionMysqlCargomove_db.getInstance (). GetConnection ();
        st = (Statement) conn.createStatement ();
        String SQL = "";

        SQL = "select a.cod_seq_awb, a.awb from awb a, rvas r  n" +
            "where a.cod_seq_awb = r.awb_cod_seq_awb"
                + "and r.rut_client = '" + usr.getRutPersona () + "';";
        res = st.executeQuery (SQL);


    } catch (SQLException ex) {
        java.util.logging.Logger.getLogger (FoliosDao.class.getName ()). log (Level.SEVERE, null, ex.getStackTrace ());
    }
    return res;
}

enter the description of the image here

enter the description of the image here

Your help please.

Why did a mysql rds report two rebooted events?

enter the description of the image here

I changed the name of a mysql RDS database.

When I checked your record, the database shows the following

There are two messages of & # 39; DB instance restarted & # 39;.

I want to know why it happens? Is it indicative of any potential problem?

Get two data from a table creating col with your information (mysql)

I want to make a query that has two cols with the employee's information.
I do not know how to do it,
My code:

select * from employees
inner join history on empleados.id_em = historia.id_em;

Boards
enter the description of the image here

Obtaining the error ER_SP_DOES_NOT_EXIST in the MySQL db function, when the YES function exists

I have a server with ~ 100 databases with the same structure for each client. The clients manipulate the data that comes to the node.js script and after writing to the database (I am using the mysql-node nodejs module). The last time I saw in the logs that nodejs did, the error # ER_SP_DOES_NOT_EXIST: FUNCTION database_name.avgWeightPrice does not exist & # 39; but this function really exists. The function that was not found (in the opinion of the MySQL server) may be different (I use stored procedures actively) and a one-time error may occur, but most are fine. These errors began to appear recently as the number of customer databases increased. So what is the problem? What can cause this behavior?
The server has 4Gb of RAM, the average size of the database is approximately 100mb. 99% of the tables is InnoDB and innodb_buffer_pool_size is now set to 1Gb.

server – Percona installation error for MySQL

Percona Server is distributed with several useful UDFs (user-defined functions) from the Percona Toolkit. Execute the following commands to create these functions:

mysql -e "CREATE FUNCTION fnv1a_64 RETURN INTEGER SONAME & libfnv1a_udf.so & # 39;"
mysql -e "CREATE FUNCTION fnv_64 RETURN INTEGER SONAME & libfnv_udf.so & # 39;"
mysql -e "CREATE FUNCTION murmur_hash RETURN INTEGER SONAME & libmurmur_udf.so & # 39;" See http://www.percona.com/doc/percona-server/8.0/management/udf_percona_toolkit.html for more details

This is the output I got after installing Percona Server for MySQL. I have put the commands in MySQL, but this message keeps coming up. I can not update sudo, because every time this message appears, the process will be blocked. Does anyone know how to solve this?

php – the MySQL query takes too long in codeigniter

I have done this query

SELECT id, name, iupac_name, inchi_key, molecular_weight, molecular_formula, exact_molecular_mass
Of the compound
WHERE name LIKE &% 39;% quer% & # 39;
Or iupac_name I LIKE &% 39;% quer% & # 39;
Or inchi_key LIKE &% 39;% quer% & # 39;
OR inchi_key IN (SELECT inchi_key FROM compound_synonyms WHERE name LIKE &% 39;% quer% & # 39;)

that took 0.3563 seconds to show the result through phpmyadmin on my local machine and it took 0.1117 seconds on digital oceanic vps.

But the same query took more than 24 minutes on another vps.

Can someone help me discover the problem?

MySQL Simple dynamic table with MAX (), GROUP BY and only_full_group_by

I'm having trouble using GROUP BY with only_full_group_by.

Here is my employee_work table:

+ ---- + ------------- + -------- + ------------ +
| id | employee_id | job_id | created_at |
+ ---- + ------------- + -------- + ------------ +
| 1 | 2 | 10 | 2019-01-01 |
| 2 | 3 | 20 | 2019-01-01 |
| 3 | 3 | 21 | 2019-02-01 |
| 4 | 3 | 22 | 2019-03-01 |
| 5 | 4 | 30 | 2019-01-01 |
| 6 | 4 | 35 | 2019-02-01 |
+ ---- + ------------- + -------- + ------------ +

I would like to select only the last lines by Employee ID, which I think gives me:

SELECT *, MAX (created_at)
FROM `employee_job`
GROUP BY employee_id;

The thing is, with only_full_group_by, which I can not disable, I get an error:

# 1055 - Expression # 1 of the SELECT list is not in the GROUP BY clause and contains the column & # 39; XXX.employee_job.id & # 39; not added that does not depend functionally on the columns in the GROUP BY clause; this is incompatible with sql_mode = only_full_group_by

Well … I tried to read about that, and it seems I do not understand the error. Of course, if I add other fields to the GROUP BY, the result still contains the same id several times.

Can someone explain to me how to group my results, maybe if GROUP BY Is not that the best way to do it, please?