MySQL performance issues on Linux system 4

[Phaethar]

Hello,

I'm having some problems with a new Linux system running MySQL 4.0.17. This box is going to be my main database server, so it needs to be fast. It is a dual 2.66 Ghz Xeon system with 2GB of ECC memory, running a RAID-5 SCSI array (tried LSI MegaRAID 320-1 and Adaptec 2120S adapters). Everything installs just fine, and as a test of the speed capabilities, we're trying to set up a few other machines on the network to start inserting new records into the tables through a Python script. What happens is that the machines running the script to do the inserts starts stopping and starting repeatedly. It almost seems like we are filling out a buffer of some kind and we're waiting for it to clear out. The pauses happen about every 5 seconds, during which time the CPU utilization on the machines drop to almost 0 before picking up again. Utilization on the server itself never goes above 10-15% for the most part.

The really strange thing about this is that running the exact same system under Windows 2000 Server does not exhibit the same problem. We can have multiple systems doing inserts, all they all max out their utilization with no hiccups. So, I'm a little stumped as to what in Linux is causing the problem. I've tried tweaking the my.cnf file (using the my.huge as the base), and adding 'flush' will help things somewhat, but the speed hit makes it not worth it.

So, I'm hoping someone can help me out with this. I'm not sure if it's a Linux problem (tried Redhat 9, Fedora, Mandrake, and currently running Redhat Enterprise Server), or if it's something with MySQL, or if it's something between the 2 of them. I've tried the 2.4 and 2.6 kernels to no avail. It's difficult to watch this powerhouse of a server continue to pause repeatedly however, so we're definitely hitting some kind of bottleneck somewhere... I just don't know where else to look.

If any other system information is needed, just ask.

Thanks.

 

[KarveR]

I have no input for you I'm aafraid, but I have similar systems planned for later this year, so I'd really appreciate any follow up info you may have in due course.

Good luck with it.

Karv

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[tshot]

though not much of idea but to start with 'top' to see all the process at thta time. also chek for indexes and try and rebuild them if possible




----------------
ur feedback is a very welcome desire

 

[tshot]

also look for all the variables. it could be thta ur earlie system was optimised




----------------
ur feedback is a very welcome desire

 

[sleipnir214]

On what filesystem are MySQL's table files being stored?

I'm thinking that you may be running afoul of ext3's journaling mechanism.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[Phaethar]

Tshot-

Thanks for the advice. I went through all variables and changed them and watched the results in real time. The only on that made any noticable difference was turning flush on, and that slowed everything waaaaay down.

Sleipnir214-

You are correct, we are storing the tables on an ext3 partition. Is there an easy way to disable the journaling? Is it safe? Or, should I reinstall and use a different filesystem? Reiser perhaps?

 

[sleipnir214]

ReiserFS is also a journaling filesystem.

I don't know of a way to disable journaling on ext3. Generally, if I want no journaling on that type of filesystem, I format the filesystem as ext2. You might ask in the Linux server forum.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[Chacalinc]

Phaetar,

Did you take a look to the array stats? RAID 5 has not a very good performance for writes. It suits very well for OLTP, where -from stats- the I/O rates are 80% read and 20% writes, but the script you are running seems to me that is 100% write, comparable to a datawarehouse load.

What kind of SCSI are you using? LVD/SE/HVD?

Cheers.

 

[Phaethar]

Chacalinc-

Yeah, I thought about the RAID-5 speeds today too, so I blew away the array and picked 2 drives and created a RAID-0 array. I was still met with the same results after setting everything up. However, per sleipnir214's advice, I just reinstalled using the ext2 filesystem, and so far it's been running great. No drop-outs, no stutters.. just as it should be. I'm hoping that this will continue to work and we can get back to our RAID-5 array in just a bit. Once this goes into production, it won't be doing as many writes as it is now. We're just moving a lot of data in all at once for the time being.

Also, this is running on a LSI MegaRAID 320-1 currently, which I believe is LVD SCSI. 1 adapter, 1 channel, and 1 logical drive.

 

[Chacalinc]

It sounds you get the solution (good one sleipnir!)

I think if you have no problem now, you will have no problem with the RAID 5... may be performance could be less than the current RAID 0, but in production you will have a very good performance.

Good for you. Good luck!

 

[tshot]

Something i got in mail - MySql News Letter. Should have relevance to you

How to Monitor MySQL's performance

Here are some ideas, how you can monitor the
database performance of your MySQL installation.
Monitoring is always an iterative and continuous
process. You need to learn what patterns are OK
for your database and what are the signs of slight
problems or even dangerous situations.

Below are the main items you can use to monitor
your system:

- mysqladmin extended (absolute values)
- mysqladmin extended -i10 -r (relative values)
- mysqladmin processlist
- mysql -e "show innodb status"
- OS data. vmstat/iostat
- MySQL error log
- InnoDB tablespace info.

1) mysqladmin extended (absolute values)

The values making most sense to monitor are:

* Slave_running: If the system is a slave
replication server, this is an indication of the
slave's health.

* Threads_connected: The number of clients
currrently connected. This should be less than
some preset value (like 200), but you can also
monitor that it is larger than some value to
ensure that clients are active.

* Threads_running: If the database is overloaded
you'll get an increased number of queries running.
That also should be less than some preset value
(20?). It is OK to have values over the limit for
very short times. Then you can monitor some other
values, when the Threads_running was more than the
preset value and when it did not fall back in 5
seconds.

2) mysqladmin extended (counters)

The idea is that you store the performance counter
value and compute the difference with the new
values. The interval between the recordings should
be more than 10 seconds. The following values are
good candidates for checking:

* Aborted_clients: The number of clients that were
aborted (because they did not properly close the
connection to the MySQL server). For some
applications this can be OK, but for some other
applications you might want to track the value, as
aborted connects may indicate some sort of
application failure.

* Questions: Number of queries you get per second.
Also, it's total queries, not number per second.
To get number per second, you must divide
Questions by Uptime.

* Handler_*: If you want to monitor low-level
database load, these are good values to track. If
the value of Handler_read_rnd_next is abnormal
relative to the value that you normally would
expect, it may indicate some optimization or index
problems. Handler_rollback will show the number of
queries that have been rolled back. You might want
to wish to investigate them.

* Opened_tables: Number of table cache misses. If
the value is large, you probably need to increase
table_cache. Typically you would want this to be
less than 1 or 2 opened tables per second.

* Select_full_join: Joins performed without keys.
This should be zero. This is a good way to catch
development errors, as just a few such queries can
degrease the system's performance.

* Select_scan: Number of queries that performed a
full table scan. In some cases these are OK but
their ratio to all queries should be constant. if
you have the value growing it can be a problem
with the optimizer, lack of indexes or some other
problem

* Slow_queries: Number of queries longer than
--long-query-time or that are not using indexes.
These should be a small fraction of all queries.
If it grows, the system will have performance
problems.

* Threads_created: This should be low. Higher
values may mean that you need to increase the
value of thread_cache or you have the amount of
connections increasing, which also indicates a
potential problem.

3) mysqladmin processlist or "SHOW FULL
PROCESSLIST" command

You can get the number of threads connected and
running by using other statistics, but this is a
good way to check how long queries that are
running take. If there are some very long-running
queries (e.g. due to being badly formulated) the
admin should be informed. You might also want to
check how many queries are in "Locked" state -
these are not counted as running but are inactive,
i.e. a user is waiting on the database to
respond.

4) "SHOW INNODB STATUS"

This statement produces a great deal of
information, from which you should extract the
parts in which you are interested. The first thing
you need to check is: "Per second averages
calculated from the last xx seconds". InnoDB
rounds stats each minute.

* Pending normal aio reads: These are InnoDB IO
request queue sizes. If they are bigger than 10-20
you might have some bottleneck.

* reads/s, avg bytes/read, writes/s, fsyncs/s:
These are IO statistics. Large values for
reads/writes means the IO subsystem is being
loaded. Proper values for these depend on your
system configuration.

* Buffer pool hit rate: The hit rate also depends
a lot on your application. Check your hit rate,
when there are problems.

* inserts/s, updates/s, deletes/s, reads/s: These
are low level row operations that InnoDB does. You
might use these to check your load if it is in
expected range.

4) OS Data. Good tools to see the system status
are vmstat/iostat/mpstat.

To see what kind of information these tools can
provide for you,

read their man pages.

5) MySQL error log - Nothing should written to the
error log, after the server has completed its
initialization sequence, so everything appearing
in the log should be brought to admin's attention
immediately.

6) InnoDB tablespace info.

With InnoDB the only danger is that the tablespace
gets full - the logs can't get full. Best way to
check this is to do: show table status;

You can use any InnoDB table for monitoring the
InnoDB table space free space.
============================





----------------
ur feedback is a very welcome desire

 

[Phaethar]

Just wanted to update this. I think everything is up and running about as good as we're gonna get it. The ext2 filesystem definitely is the fastest, although it takes a long time to recover if the machine goes down. So, I put it back to ext3 and changed the journal method from 'ordered' to 'writeback'. So, while it still pauses briefly to update the journal, it's not nearly as often, and it'd done much quicker. And if it does crash, it appears to recover quite nicely too.

KarveR, if you are going to build a box like this, I'd recommend a couple of things:

1. Use the LSI MegaRAID card. The adaptec has known issues with Linux (Bug 92129 on bugzilla) that we were hit with.

2. If there are going to be a lot of sustained writes to the box and if you are using the ext3 filesystem, you will notice slowdowns and pauses if the journal method is left at default. Ext2 is the fastest, but writeback mode seems to be quite fast as well.

Thanks for all the help everyone!

 

[Chacalinc]

Thanks Phaetar for share your experience and recommends with us!!

Cheers.

 

[sleipnir214]

A star for Phaethar!

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[p1zza]

which table type are you using? i've been developing a multi-threaded application that makes one db connection per thread (the threads are persistent, they don't come and go) and i'm also doing alot of inserts and updates and i found the database to be performing at an unacceptable level. i then switched my major tables from myisam to innodb and this made a huge difference. innodb's row-level locking, as opposed to myisam's table-level locking, performs significantly faster when performing multiple inserts concurrently on the same table.

unfortunately, i don't believe that innodb is enabled by default in mysql, you can find out by running: show variables like 'have_innodb'; if the value is not "YES" then you'll likely need to recompile with innodb enabled, but in my experience it's worth this minor hassle.

best of luck!

 

[KarveR]

Phaethar thanks for starting what will undoubtedly be a much referenced thread, lots of very useful info and input in here. (stars accordingly ).

In the near future, I will begin work on the new server, currently the hardware stands boxed and idle. Time being the key.
Specs:
HP Proliant 3.2Ghz Xeon driven, Ultra 320 10k2 76gig drives, Smart Array 642 raid controller, general design will be Raid5 striped with redundant drive and 6gigs of ram. All redundant fans and PSU's.

Think the hardware should be OK, but I was a little concerned about the config/performance issues that might come my way.

Thanks again for all the great input.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.