sql performance with a large number of rows in a table.

hi,
I am currently using MySQL 5.0.16 on a dual pentium 4-2G system with
9G of ram. I have a number of machines that connect to the MySQL
server, doing INSERTS, SELECTS, and UPDATES on 1 table, and generating
about 1500-3000 queries/sec.

Now, when the table it's writing to is empty, things are fine....very
fast, the DB is able to handle all of the queries. Also, when the
table starts and it's empty, most of the things it's doing are SELECTS
and INSERTS. As this table fills up, that will change to SELECTS and
UPDATES.

Also, typically there are about 10 threads open at any given time.

Somewhere around 60 million rows, the # of queries/sec goes down to
about 500-1500, and the number of threads starts to grow until it
reaches the server max. (which is set to 90)

Here is my my.cnf file:

log-long-format
skip-locking
max_allowed_packet=8M
key_buffer_size=2048M
sort_buffer_size=2M
read_buffer_size=2M
myisam_sort_buffer_size=128M
query_cache_size=128M
table_cache=220
thread_concurrency=4
bulk_insert_buffer_size=64M
max_connections=90
thread_cache=10
wait_timeout=1800
myisam_max_sort_file_size=3000000000

does anybody have any suggestions or know why the mysql server is
exhibiting this behavior?


thanks in advance!!

sawoad wrote:

> hi,
> I am currently using MySQL 5.0.16 on a dual pentium 4-2G system with
> 9G of ram. I have a number of machines that connect to the MySQL
> server, doing INSERTS, SELECTS, and UPDATES on 1 table, and generating
> about 1500-3000 queries/sec.
>
> Now, when the table it's writing to is empty, things are fine....very
> fast, the DB is able to handle all of the queries. Also, when the
> table starts and it's empty, most of the things it's doing are SELECTS
> and INSERTS. As this table fills up, that will change to SELECTS and
> UPDATES.
>
> Also, typically there are about 10 threads open at any given time.
>
> Somewhere around 60 million rows, the # of queries/sec goes down to
> about 500-1500, and the number of threads starts to grow until it
> reaches the server max. (which is set to 90)
>
> Here is my my.cnf file:
>
> log-long-format
> skip-locking
> max_allowed_packet=8M
> key_buffer_size=2048M
> sort_buffer_size=2M
> read_buffer_size=2M
> myisam_sort_buffer_size=128M
> query_cache_size=128M
> table_cache=220
> thread_concurrency=4
> bulk_insert_buffer_size=64M
> max_connections=90
> thread_cache=10
> wait_timeout=1800
> myisam_max_sort_file_size=3000000000
>
> does anybody have any suggestions or know why the mysql server is
> exhibiting this behavior?


It cannot perform magic. You are most likely exceeding the capabilities of
your hardware. You will need faster hardware or multiple machines with
load balancing.



--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png

"sawoad" <sawoad@yahoo.com> wrote:

> I am currently using MySQL 5.0.16 on a dual pentium 4-2G system with
> 9G of ram. I have a number of machines that connect to the MySQL
> server, doing INSERTS, SELECTS, and UPDATES on 1 table, and generating
> about 1500-3000 queries/sec.

I am not familiar with Intel model names, so what does 'pentium 4-2G'
mean? What operating system are you running? Is it a 32- or 64-bit
system? Probably lot of your RAM is just useless. To really *use* more
than ~3GB of RAM you should switch to a 64-bit system.

> Now, when the table it's writing to is empty, things are fine....very
> fast, the DB is able to handle all of the queries. Also, when the
> table starts and it's empty, most of the things it's doing are SELECTS
> and INSERTS. As this table fills up, that will change to SELECTS and
> UPDATES.
>
> Also, typically there are about 10 threads open at any given time.
>
> Somewhere around 60 million rows, the # of queries/sec goes down to
> about 500-1500, and the number of threads starts to grow until it
> reaches the server max. (which is set to 90)

Details!

What's the READ/WRITE ratio in your queries? Is it READs or WRITEs that
get slow? What state are the threads in (SHOW FULL PROCESSLIST) ?
Do you experience locking problems (threads in state 'Locked') ?
What storage engine (table type) are you using?

After all, performance degradation by factor 2 from 0 rows to 60 Mio
rows is quite OK. Having a growing backlog (that's the number of
threads) is also normal in this situation.

> Here is my my.cnf file:
>
> key_buffer_size=2048M

This is by far the most important setting if you use only/mostly MyISAM
tables. As a rule of thumb all your active indexes should fit into this
buffer space. However, if you're on a 32-bit platform, 2GB is the max.
setting. For 60 Mio rows this would allow you ~16 bytes index per row.
Not much.

> query_cache_size=128M

This might be wasted. Depends on your application. Are there (literally)
identical SELECT statements? Are there only few WRITEs to your tables
(that will invalidate query cache entries) ?


More interesting detail: what is the utilization of your servers
resources when it shows performance degradation? Most important:
is it CPU bound or I/O bound? In most cases I/O is the more
precious resource.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Hi Axel,

I made a mistake regarding the CPU's. They are actually a little bit
beefer than originally stated..anyways, linux states:

Intel P4/Xeon Extended MCE MSRs
Intel(R) Xeon(TM) CPU 3.20GHz stepping 01

The exact operating system is Red Hat Enterprise Linux ES release 4
(Nahant) It is not a 64bit system.

As for the ram, I don't know what's using it all, but there's currently
only 10M free. At the current moment, there are 55 million rows in the
table, and the entire DB looks like it's handling everything fine.

Once this one table is somewhat filled, the ratio becomes SELECTS vs.
UPDATES, and it breaks down to 55% selects, 45% inserts.

I should also say that...most of the actions to this DB occur in 2
MyISAM tables. Reads/Writes/Updates occur in both tables, however,
table 1 is 35 million rows while table 2 is 60+ million rows. Also,
upon deleting table 2, the DB is able to keep up with all the
connections being made to it...(table 1 has never been deleted...), and
that is, until table 2 reaches 55-60 million rows.

I have the DB set so that it will log slow queries. however, it's not
really reads or writes that are slow. In fact, they do not even show
up in the log. However, going back to what I was saying, what ends up
happening is the DB server is not able to handle all the requests from
all the servers, and the number of connections start to grow until it
reaches the system max.

As of this moment, I am not suffering any locking problems. (also, the
DB is entirely myisam. This table in particular is a myisam table with
delay_key_write enabled. (it speeds things up quite a bit...however, I
was starting to wonder if that was the cause of the problems?)

You are correct regarding the query cache size. There aren't any
identical select statements, and there are tons of update statements
that happen. So - I guess I can lower that option considerably.

As for the system, here are the stats on the CPU (as of now with 55
million rows..)

Cpu(s): 8.4% us, 2.7% sy, 0.0% ni, 44.1% id, 44.2% wa, 0.1% hi,
0.6% si


UPDATE :

so there are now about 63 million rows in this one particular table,
and the slowdown is occuring. There are currently 69 open connections.

Here is the same information as above now:

Cpu(s): 13.8% us, 3.3% sy, 0.0% ni, 50.6% id, 31.2% wa, 0.1% hi,
0.8% si

Again, 10M of memory is all that remains free.


Thanks for your time...and let me know if you have any other questions!
:-)

"sawoad" <sawoad@yahoo.com> wrote in message
news:1142436439.363614.163070@i39g2000cwa.googlegr oups.com...
> As for the ram, I don't know what's using it all, but there's currently
> only 10M free.

I would focus my efforts here if I were you. Figure out what's taking all
that RAM, and figure out what must be done to reduce it. That's 10MB out of
9GB? In other words, about 99.89% of memory is occupied. It's likely that
the actual demand on memory is higher, and your system is going crazy trying
to page active memory to disk.

Regards,
Bill K.

sawoad wrote:

> The exact operating system is Red Hat Enterprise Linux ES release 4
> (Nahant) It is not a 64bit system.
>
> As for the ram, I don't know what's using it all, but there's currently
> only 10M free.


That sounds quite normal. OS file-system caching will use up as much RAM as
you can throw at it in most cases.

Try this:

$ free
total used free shared buffers cached
Mem: 4149316 2992228 1157088 0 80188 2258512
-/+ buffers/cache: 653528 3495788
Swap: 2048276 144 2048132


This is from a machine with 4GB RAM. Apparently it has 1157MB of free RAM,
but in fact 2258MB of the RAM that is used is file-system caching, so the
total free RAM in 3495MB.

If your Swap:used is low (like 144k as above) then you have plenty of RAM.


--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png

Hi Brian,

you are correct - here is my output from free:

total used free shared buffers
cached
Mem: 9288304 9283688 4616 0 19708
7008448
-/+ buffers/cache: 2255532 7032772
Swap: 12289488 1884 12287604

sawoad wrote:

> Hi Brian,
>
> you are correct - here is my output from free:
>
> total used free shared buffers
> cached
> Mem: 9288304 9283688 4616 0 19708
> 7008448
> -/+ buffers/cache: 2255532 7032772
> Swap: 12289488 1884 12287604


You certainly have no memory usage issue then.



--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png

"sawoad" <sawoad@yahoo.com> wrote:
>
> The exact operating system is Red Hat Enterprise Linux ES release 4
> (Nahant) It is not a 64bit system.

I'm still not aware if your CPUs support the EM64T extension that make
it possible to run a 64-bit Linux kernel. Probably it would be of
advantage to switch to 64-bit Linux. Especially if you could use more
than 2GB of key_buffer. Read below for reasoning.

> Once this one table is somewhat filled, the ratio becomes SELECTS vs.
> UPDATES, and it breaks down to 55% selects, 45% inserts.

INSERTs or UPDATEs? INSERTs are fast as long as your datafile has no
"holes". In that case INSERTs and SELECTs can be done in parallel. If
you have holes in your datafile (this happens when you DELETE rows or
you have variable length rows and do UPDATEs that change the length of
a row) then READ and WRITE requests become mutually exclusive. In this
case I would expect to see many threads in the "Locked" state.

> I have the DB set so that it will log slow queries. however, it's not
> really reads or writes that are slow. In fact, they do not even show
> up in the log.

You can lower the slow-query threshold from the 10 second default.
However I don't think you have single slow queries. It's just that
MySQL cannot handle the throughput from your clients. That leads to
a backlog of SQL statements in form of worker threads.

> As for the system, here are the stats on the CPU (as of now with 55
> million rows..)
>
> 8.4% us, 2.7% sy, 0.0% ni, 44.1% id, 44.2% wa, 0.1% hi, 0.6% si

> so there are now about 63 million rows in this one particular table,
> and the slowdown is occuring. There are currently 69 open connections.
>
> 13.8% us, 3.3% sy, 0.0% ni, 50.6% id, 31.2% wa, 0.1% hi, 0.8% si

This is quite clear. Your CPUs spend 44% resp. 31% of their time
waiting for some I/O device to get ready. You're certainly not CPU-
bound but I/O-bound.

Next step would be to find out, if you could spare I/O by making better
use of the available memory.

> Again, 10M of memory is all that remains free.

You already know by now, that Linux uses excess memory to buffer I/O
to the mass storage. So in fact the memory *is* used for good. However
the buffer cache of Linux caches *all* active pages, that is

- text segments (code) of running applications
- all kinds of open files, including
* MyISAM datafiles
* MyISAM indexfiles

So after all MyISAM index data is buffered twice, first in MySQLs
key_buffer, second in Linux' page cache. MyISAM data files are cached
only in the page cache. From the DBMS' standpoint that's OK, index
data is (should be) dramatically more often accessed than row data.

However this may not be true for certain types of workload. If you do
lots of INSERTs and/or UPDATEs, you clearly have to a) write row data
to disk, b) update index data. This puts more stress on the I/O system.


So far for theory. Back to practice:


It's possible that your I/O subsystem is just too slow. Means you need
faster hard disks, preferrably in RAID configuration. You see this
problem only with high data volume because the page cache reduces I/O
demands for lower data volume.

OTOH it's still possible that index operations slow you down. To
evaluate this, you should look at server statistics:

SHOW STATUS LIKE 'key_read%'

This gives you 'Key_read_requests' and 'Key_reads'. You can calculate
the cache-hit-ratio for the key_buffer as

100% * (1 - Key_reads/Key_read_requests)

For a properly configured key_buffer you will see 95% and more. If your
key_buffer starts to become too small, it will drop. You then have to
increase your key_buffer. In your case that would include to upgrade
your operating system to 64 bits.


HTH, XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/