Percona IO very slow with 500k+ records

  • Filter
  • Time
  • Show
Clear All
new posts

  • Percona IO very slow with 500k+ records

    Hello guys,

    At my company we have a web application that deal with a lot of I/O within a second.

    But as we installed new servers, I'm facing some real slowness on these queries that are being selected when it reaches above 100k+ records, which if I take a look at the process the database uses around 250% of it, which is pretty high. I tried configuring the my.cnf, but no luck again.

    I would really appreciate if someone could help me on configuring on the right way the percona database.

    Our server specs are:

    Linux 2.6.32-5-amd64 on x86_64
    Common KVM processor, 16 cores (VPS)
    20 GB of RAM

    The strange thing is that in our older server with more data in it, it selects faster, which is an older mysql version as well (not percona)

    Thanks in advance.

  • #2
    You say "250% of it", does "it" refer to the "100k+" records?

    Does the data fit in memory? For disk i/o the underlying system matters a lot. VPSs are usually not suitable for this job. Is your older server also a VPS?

    Maybe you can find a way that only a subset of the data is frequently accessed. What is the select...group by query you mentioned on twitter?


    • #3

      when I say 200 - 250% is the CPU Used by Mysql (Percona), while working..

      Yes it fits, the disk is 15K, VPS is because we have a HP Blade Server C7000, and we created virtualization to use it for other systems as well, but not as demanding as this one.

      The query is "select max(Timestamp) from table where imei in (some imeis) group by imei (imei is a serial number from the device).

      Now, whereas these imeis has around 500k+ the query takes like 5-8 seconds, and most of the time aborts.

      This is very frustrating, whereas the older server is slower/older and not updated it uses like mysql 5.0, and still process these queries faster



      • #4
        Can you give the output of SHOW CREATE TABLE? I think we can make that query run much quicker and with less CPU usage by creating an index on (imei,timestmap).


        • #5

          I have indexes on imei, timestamp, and some other fields, I need some kind of help to configure the my.cnf.


          • #6
            No no, you need to learn about multicolumn indices and how they can greatly improve the running time of this query. Depending on how many imei's you look up at once and depending on your data, this query can easily run 100 times as fast.


            • #7
              ok, could you give me some advice on how to create a multicolumn index ?

              right now the indexes are:

              index name - fields:
              1.index - Timestamp, id, X, Y, Speed, io1, io2, io199, io3, io4
              2.timestamp_index - Timestamp_full (Timestamp with miliseconds)
              3. IMEI_INDEX - IMEI

              as far as I know index key is a multicolumn, what I did right now delete the other two, and I saw a bit of increase, but not satisfied yet ...


              • #8
                You seem to have multicolumn indices, could you provide the SHOW CREATE TABLE output? That is more readable than your current overview.


                • #9

                  here you go:

                  | avl | CREATE TABLE `avl` (
                  `id` bigint(11) NOT NULL AUTO_INCREMENT,
                  `IMEI` bigint(15) DEFAULT NULL,
                  `Priority` int(1) DEFAULT NULL,
                  `X` decimal(15, DEFAULT NULL,
                  `Y` decimal(15, DEFAULT NULL,
                  `Speed` smallint(3) DEFAULT NULL,
                  `Angle` smallint(4) DEFAULT NULL,
                  `Altitude` smallint(4) DEFAULT NULL,
                  `Satellites` tinyint(4) DEFAULT NULL,
                  `io1` tinyint(4) DEFAULT NULL,
                  `io2` tinyint(4) DEFAULT NULL,
                  `io199` int(4) DEFAULT NULL,
                  `Timestamp` int(20) DEFAULT NULL,
                  `EventSource` tinyint(4) DEFAULT NULL,
                  `io3` tinyint(11) DEFAULT NULL,
                  `io4` tinyint(11) DEFAULT NULL,
                  `io5` int(11) DEFAULT NULL,
                  `io6` int(11) DEFAULT NULL,
                  `io7` int(11) DEFAULT NULL,
                  `io8` int(11) DEFAULT NULL,
                  `io9` int(11) DEFAULT NULL,
                  `io10` int(11) DEFAULT NULL,
                  `companyid` int(11) DEFAULT NULL,
                  `Timestamp_full` bigint(11) DEFAULT NULL,
                  PRIMARY KEY (`id`),
                  KEY `index2` (`Timestamp_full`,`IMEI`,`Timestamp`,`id`,`X`,`Y`, `Speed`,`i o1`,`io2`,`io199`,`io3`,`io4`),
                  KEY `IMEI` (`IMEI`)
                  ) ENGINE=InnoDB AUTO_INCREMENT=3261312402 DEFAULT CHARSET=latin1 |

                  1 row in set (0.00 sec)


                  • #10
                    The order of the columns in a multicolumn index matter. Add the key as I suggested before and change `index2` to something more useful.

                    See http://dev.mysql.com/doc/refman/5.0/en/multiple-column-index es.html


                    • #11
                      yes, I know it matter, and the query firsts gets the max Timestamp_full than based on those Timestamp_full return selects the imeis.

                      Any other suggestion on mysql configuration ?