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:
Debian
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)
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?
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 :(:(
