Date: Tue, 10 Sep 2002 16:07:34 -0700 (PDT) From: Scott Hess <scott@avantgo.com> To: Dan Nelson <dnelson@allantgroup.com> Cc: hackers@d.sparks.net, <freebsd-hackers@FreeBSD.ORG> Subject: Re: Dell 2650 SMP perf question Message-ID: <Pine.LNX.4.44.0209101602160.28967-100000@river.avantgo.com> In-Reply-To: <20020910214001.GB84648@dan.emsphone.com>
next in thread | previous in thread | raw e-mail | index | archive | help
Another item we've done is to run multiple mysqld against the same filesystem tables. This requires you to have the filesystem locking enabled when you build mysql (but I think that's the default on FreeBSD). Also, you need to have multiple clients to connect to the multiple ports (or use a load-balancer of some sort). This allowed us to more fully utilize the I/O capacity of our RAID arrays. The primary issue, and it's a very big one, is that there are a number of SQL commands which are dangerous. OPTIMIZE, ALTER, DELETE without a WHERE clause, and generally any command which unlinks the existing table file and creates a new table file are dangerous. The problem is that the processes other than the one doing the command retain an open file descriptor on the original table file. The process running the command deletes that file, and creates a new one, and thus gets out of sync with the other processes. Later, scott On Tue, 10 Sep 2002, Dan Nelson wrote: > In the last episode (Sep 10), hackers@d.sparks.net said: > > I'm stumped at how little improvement using an SMP kernel gives in a > > Dell 2650. System is dual 2400 xeon processors, 2 GB ram. It's > > intended to be used as a database processor, among other things. A > > perl process that read and input file and updates simple records in a > > mysql database actually run much more slowly: processing ~2million > > records takes 817 seconds with SMP enabled and 262 seconds with it > > disabled. > > > > Simple things like some_program.pl < some_big_file | another_program.pl > > > > seem to take full advantage of the second processor, but this system > > is supposed to run mysql. > > Mysql is a threaded database, and FreeBSD's pthreads are user-space. > This means that mysql will only ever use one CPU, and disk I/O in one > thread will block all other threads. > > You can try: > > * Rebuilding the mysql port with USE_LINUXTHREADS=yes. This will help > lots if your mysql process is CPU-bound, and will help some if you > are heavily I/O bound. > > * Switching to InnoDB tables, which cache much better than MyISAM > tables so you are more likely to get your data from RAM instead of a > blocking disk read. > > * Splitting your single perl program into multiple ones that hit the > database simultaneously. You might be seeing a synchronization > effect where your perl and mysql processes are competing for a SMP > lock or something and the wrong one always wins. > > To Unsubscribe: send mail to majordomo@FreeBSD.org with "unsubscribe freebsd-hackers" in the body of the message
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?Pine.LNX.4.44.0209101602160.28967-100000>