Christian, ----- Original Message ----- From: "Christian Jaeger" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Monday, March 03, 2003 2:31 PM Subject: Re: The diferences about /dev/hda1 and access via raw device
> At 18:35 Uhr -0200 23.01.2003, Dyego Souza do Carmo wrote: > >/dev/hda1:20Gnewraw > >/dev/raw1:20Gnewraw > > > >The speed is more fast in the last case or no ? ... > > > >- The biggest difference was (as expected) when doing full table > >scans on tables bigger than what seemed to fit into the innodb > >buffer pool. The first scanning query took the same amount of time > >in both cases (~10 seconds), whereas with OS buffered IO the > >subsequent scans took only about 6 seconds, but unbuffered IO still > >10 seconds for each run. Increasing innodb_buffer_pool_size from > >90MB to 290MB changed it so that also with rawio the subsequent > >queries profit from cached data: (the test machine has 512MB RAM, > >runs linux 2.4.17, one seagate SCSI disk, 1Ghz PentiumIII) > > > > Query time for the first runs after a fresh mysqld start= > [s] *) > >90MB buffer pool: > > /dev/raw/raw1 10.60 11.09 11.01 > > /dev/sda10 10.67 6.82 6.80 > >290MB buffer pool: > > /dev/raw/raw1 9.53 4.18 4.17 3.96**) > > /dev/sda10 9.48 3.98 3.96 > > > >*) note that kernel 2.4 drops it's own buffered pages when the last > >application closes the buffer device, so it's enough to restart > >mysqld and not necessary to reboot the machine before subsequent > >test runs (there's no cache left over). > >**) this last number is from a different run very interesting data. Thank you! > >Thanks, > >Christian. > > > > > > > >PS. some random observations (with mysql 3.23.51+your patches, built > >from Debian source package): > > > >- in some tests (1000 inserts of ~2kb and one commit in a loop, > >looping about once per second) the CPU is >40% idle even when using > >/dev/sda10 (os buffered) for data and > >innodb_flush_log_at_trx_commit=0 (I would have expected it to take > >all CPU since disk shouldn't be limiting?). At first, I had put the > >logs on Reiserfs, but using ext2 didn't change anything. Looks like there is some inefficiency in fsync() in your Linux/hardware combination. > >- mysql takes a long time (at least a minute) for shutting down > >shortly after having deleted 300'000 rows (of ~2kb each), in spite > >of innodb_fast_shutdown=1 and innodb_flush_log_at_trx_commit=3D0 in > >my.cnf. Mysql is taking about 20% cpu during this phase. It seems > >like there's cleanup work going on in the background that can't be > >interrupted even by fast_shutdown, correct? (I assume it isn't > >problematic when mysql is terminated by init issuing SIGKILL). innodb_fast_shutdown=1 is the default nowadays. But even then InnoDB has to flush the modified pages from the buffer pool to disk. That can take several minutes with a fairly large buffer pool. Of course, the fastest shutdown is simply "killall -9 mysqld". Killing the mysqld process is a valid way of shutting down InnoDB, but then InnoDB has to do a crash recovery at the next startup. ... > >(- compared with a mysql 3.23.49 installation on an identical > >machine (with identical setup and identical data, both without any > >other load, both using /dev/sdaX with OS buffering), 3.23.51 took > >longer for the same queries involving smaller table scans (0.26 vs. > >0.17 seconds in mysql client). Not sure what's the reason.) New versions tend to become slower CPU-wise as more debug code and hooks for new features are added. There is also significant random fluctuation which probably depends on how code is located in cache lines of the processor. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
