Re: Faster reindexing
Dathan Vance Pattishall [EMAIL PROTECTED] writes: Maybe increasing #use for when mysql is doing a check or repair set-variable= myisam_sort_buffer_size=64M to a higher value will make the index happen faster on the fly. MySQL doesn't seem to honour this variable. I've set it to '1024M', but the process size stays below 512 MB (which are occupied by other buffers). The indexing rate drops to a less than 6 MB per *hour* once 10 million (or so) records have been processed. It appears as if the only solution is splitting the table into 20 or 30 smaller ones and using a MERGE table. But if this is required, I might also do this using the existing RDBMS. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recomended RAM for production server. 3Gb overboard?
Brad Brad [EMAIL PROTECTED] writes: The OpenBSD server is 2.8Ghz and may have as many as 230 mysql sessions with 14 queries a second, the rest will be sleeping (ftp sessions maintain connection). The db directory is 80mb total, 80 MB? Is this a typo? The old server is seems quite happy with 512mb. Ah, no typo. 8-) Obviously, your workload fits into less than a gigabyte. So you certainly don't need three or even four gigabytes of RAM. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Faster reindexing
Dathan Vance Pattishall [EMAIL PROTECTED] writes: #use for when mysql is doing a check or repair set-variable= myisam_sort_buffer_size=64M to a higher value will make the index happen faster on the fly. Oops. I only adjusted the key_buffer value. Probably I should set myisam_sort_buffer_size to several hundred megabytes. If InnoDB indexing doesn't finish either, I'll give it a try (I still hope that MyISAM tables are more light-weight than InnoDB tables and result in higher throughput in a many reads/rare bulk updates scenario). However, the indexes must be maintenance-free once created (no creeping index syndrome). Can it occur that index pages get lost during deletion? But, for a 100 million row table doing a dump and adding that dump back to the db might be your fastest method. Building the index at insertion for a self balancing tree is a faster in some cases (I believe this is the case) then building one on the fly. I don't think this matters much, as reindexing seems to reload the database anyway. My 2 cents. 100 million rows WOW. I initially hoped to store even a bit more. 8-/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creeping index syndrome
Does MySQL require periodic reindexing for indexes on columns whose value permanently increases (while the oldest entries are expired)? Another database suffers from the so-called creeping index syndrome, which results in ever-growing indexes in such cases (some pages in the index can never be reused because they correspond to expired values which will never again be present in the corresponding column). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Faster reindexing
I've got a table with 100 million rows and need some indexes on it (one row is 126 bytes). I'm currently using MyISAM and the indexing proceeds at an astonishingly low rate: about 200 MB per hour. This is rate is far too low; if we had to recover the database for some reason, we'd have to wait for days. The table looks like this: CREATE TABLE flows ( versionCHAR NOT NULL, router CHAR(15) NOT NULL, src_ip CHAR(15) NOT NULL, dst_ip CHAR(15) NOT NULL, protocol TINYINT UNSIGNED NOT NULL, src_port MEDIUMINT UNSIGNED NOT NULL, dst_port MEDIUMINT UNSIGNED NOT NULL, packetsINTEGER UNSIGNED NOT NULL, bytes INTEGER UNSIGNED NOT NULL, src_if MEDIUMINT UNSIGNED NOT NULL, dst_if MEDIUMINT UNSIGNED NOT NULL, src_as MEDIUMINT UNSIGNED NOT NULL, dst_as MEDIUMINT UNSIGNED NOT NULL, src_netCHAR(1) NOT NULL, dst_netCHAR(1) NOT NULL, direction CHAR(1) NOT NULL, class CHAR(1) NOT NULL, start_time CHAR(24), end_time CHAR(24) ); Indexes are created using this statement: mysql ALTER TABLE flows - ADD INDEX dst_ip (dst_ip, src_ip), - ADD INDEX dst_port (dst_port, start_time), - ADD INDEX src_ip (src_ip, start_time), - ADD INDEX time (start_time); In theory, we could represent the columns router, src_ip, dst_ip, start_time, end_time using integers of the appropriate size, but this would make ad-hoc queries harder to type (and porting our applications would be even more difficult). Should I switch to another table type? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql - Dual Xeon or Dual Opteron
Konstantin Yotov [EMAIL PROTECTED] writes: 2x1GHz Intel, 1GB RAM, 40GB WD 7200 8MB cache. We are going to uprade our server but I'am wondering between new Opteron (1.4GHz)and Xeon (2.4). Can't you get a machine for testing before you buy it? Xeon processors aren't necessarily a significant win over Pentium 3s, even with the noticeable difference in clock speed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
