Question 8080 in PHP Versions Question posted in
You can find a list of all versions here.

When the user activity increases the query time increases and application hangs – PHP Versions

PHP Version: 7.3.19
mysql version: 10.4.13 (Maria DB- Community Edition)
I am running an application on Linux, using PHP and Mysql. We have multiple queries which runs perfectly fine when 20-25 people are using the application. But when the users are more than 30 users the query becomes very slow. there are more than 500 users which want to use this application. If one of the query takes 0.188844 seconds to execute, but when there are many users attempting to do the same activity at the same time, the processes becomes very slow and takes about 21.017460seconds to execute sometimes even more.

below is some of examples from slow query log:

===============# Time: 220601  7:42:44
# User@Host: root[root] @ WIN-MN0PM3TE06L [10.15.51.252]
# Thread_id: 41274  Schema: mahabhumi_prod8  QC_hit: No
# Query_time: 0.188844  Lock_time: 0.000154  Rows_sent: 0  Rows_examined: 51848
# Rows_affected: 0  Bytes_sent: 10337
SET timestamp=1654049564;
SELECT *
FROM (`kcc_requests`)
WHERE `aadhar_num` =  'XXXXX';

===============# Time: 220601 14:12:13
# User@Host: root[root] @ WIN-MUQCJ3KLLE2 [10.15.51.250]
# Thread_id: 81466  Schema: mahabhumi_prod8  QC_hit: No
# Query_time: 21.017460  Lock_time: 0.000071  Rows_sent: 0  Rows_examined: 52692
# Rows_affected: 0  Bytes_sent: 10337
SET timestamp=1654072933;
SELECT *
FROM (`kcc_requests`)
WHERE `aadhar_num` =  'YYYYY';
=================

We have put the indexes in all frequently queried columns.

is there a setup issue? is any configuration to be done to allow more users to use the application?

Tags:

2

Answers


  1. 0

    For starters, you need

    INDEX(aadhar_num)

    After you add that index, you may find other queries bubbling up to the surface as "slow". Let’s see SHOW CREATE TABLE and the main queries.

    Login or Signup to reply.
  2. 0

    The new index DOES NOT EXIST that started with aadhar_num + other columns.

    Please create it again.

    Please consider testing when your system has more than 25 users
    the query of

    SELECT *
    FROM (kcc_requests)
    WHERE aadhar_num = ‘XXXXX’;

    changed to

    SELECT *
    FROM (kcc_requests)
    WHERE aadhar_num = nnnnnnn;

    to AVOID alphanumeric to numeric wasted CPU time before accessing your table’s index.

    If this does not make your query faster, you may have a corrupted index.

    To create current indexes, you have two choices.
    ANALYZE TABLE kcc_requests; least time consuming
    or
    OPTIMIZE TABLE kcc_requests; to recreate indexes and minimize data_free space used in the table.

    PS, What you recently posted is the results of SHOW CREATE TABLE – and does NOT have the new INDEX you created later. Please recreate your index as suggested.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search