Not the answer you need?
Register and ask your own question!

simple performance question

csakiscsakis EntrantInactive User Role Beginner
I have a table with ~1.4 billion records. I want to check how many records are exactly there.
I have a Percona 5.5 server running on Ubuntu 12.04. The server has 80 GB of memory.
The table itself is ~100 GB.
I issue a simple command

Select count(*) from table

I then, monitor the INNODB engine. I get a reading of 35-45,000 reads/second
If my calculations are correct, it will need 9-18 hours just to do this simple command.
I thought that using databases would improve the speed of handling vast amounts of data. Is this realistic? Can I make my server faster?
I used the Percona my.cnf wizard to set all my variables.

Any help would appreciated.

Csaba

Comments

  • csakiscsakis Entrant Inactive User Role Beginner
    I am trying to execute this statement


    select `Distance`, count(`Distance`) from `hamming_q22_sd6_11` group by `Distance`</pre>


    My table has approximately 300 million rows.
    When I look at the process list it says

    copying to tmp table</pre>

    and it stays like that for hours. Something is not right. These things should take seconds not hours. I am really desperate, what am I doing wrong?

    Csaba
  • jcesariojcesario Contributor Current User Role Beginner
    Can you post the output of:

    SHOW ENGINE INNODB STATUS\G
    SHOW GLOBAL VARIABLES;
    SHOW GLOBAL STATUS;

    please describe the hardware you are using.

    Can you also post the EXPLAIN output for the affected queries as well as the schema for involved tables.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.