best practice: mysql_multi, VMs w/single instance per or doesn't matter?

I've always had a single physical server that is the qc mysql database for all our applications but it's now up to 85 schemas so I want to break it up along the same lines as production (where there's redundant pools of mysql servers by application class). my basic question is whether it's better

setting useGmtMillisForDatetimes being ignored in context.xml

hey, While developing against MySQL, we ran across its habit of adjusting the Epoch timestamp for your local timezone; adding "?useGmtMillisForDatetimes=true" to the end of the connection URL nipped that issue in the bud. However, when we moved to a Tomcat environment and started using a block in

login logger

hey, I have been playing with different ways to improve diagnostic and audit data in MySQL that don't necessarily involve source mods (ex. general log to fifo w/background parser/logger). I've come up w/the following way of logging connections to give similar data to the listener.log file in Orac

tee command eating filesystem (4.0.18 64-bit)

hey, I just noticed a bizzare behavior on a box we recently built to upgrade a legacy 32-bit 3.23 DB to 64-bit 4.0(.18) - explain this: mysql@:~> df -k /tmp Filesystem 1K-blocks Used Available Use% Mounted on /dev/sda5 521748 42624452620 9% /tmp mysql@:~> m

Re: 1B row/50GB myisam w/5 indexes - build after or during load?

ter than loading the > dump. > > As far as your indices go, I would create them before loading the data in > any case. > > Olaf > > > On 6/12/08 11:25 AM, "Sid Lane" <[EMAIL PROTECTED]> wrote: > > > hey, > > > > as the sub says I hav

1B row/50GB myisam w/5 indexes - build after or during load?

hey, as the sub says I have a dump of a 50GB (MYD/60GB MYI) table I need to load on another server. I guess the bigger question is how can I optimize this, particularly the index builds? I had always thought is was best in these cases to create the table w/o any indexes, load the rows then creat

binlog sequence # rollover

do binlog sequences just rollover back to 0 w/o a resetlogs? will it just automatically go back to mysqld.00 after mysqld.99? any replication implications? I know it sounds like a stupid question and I'm sure the developers are smart enought to have thought of that but we'll be crossing

multiple instances on same server

this is more of a best practices question than a techical one but when/why has anyone run multiple mysql instances on the same server in production? I have a ring replicated pair of mysql servers with ~12 logical databases, each associated with a different application/set of functionality on our s

2008 conference fee?

stupid non-technical ?: does anyone know what the registration fee is going to be for the 2008 conference? my mgr needs a # today to put in next yr's budget & I couldn't find it on the conference site. if it's not been finalized could someone tell me what it was last year? thx!

hardware & clusters

all, I am working on a budget proposal for next year to put in a MySQL cluster but wanted to validate (or correct) a couple of assumptions: 1. do storage nodes benefit far more from additional RAM than they do from faster CPUs/multiple cores? 2. do SQL nodes benefit more from faster CPUs/multi

performance of extended insert vs. load data

all, I need to migrate ~12GB of data from an Oracle 10 database to a MySQL 5.0one in as short a window as practically possible (throw tablespace in r/o, migrate data & repoint web servers - every minute counts). the two approaches I am considering are: 1. write a program that outputs the Oracl

Oracle sequence simulator

all, I have need to simulate the somewhat unique behavior/properties of an Oracle sequence object in MySQL (obviously for porting legacy code). I have come up w/the following solution which seems to work but wanted to run it by the collective to see if I failed to consider anything: create tabl

replication stability w/5.0.27

all, I recently completed upgrading the core database pool for our site from 4.0.18 (32-bit) to 5.0.27 (64-bit) but am now experiencing intermittent replication instability. we replicate ~20M DMLs/day across 18 DB nodes in three datacenters. about once/week I'm getting a 2013 error (error readi

tool to parse general log (3.23.55)

all, I have been tasked with upgrading a critical 3.23.55 database to 5.0(.27-ish). short version is it's never been upgraded because authors have moved on and nobody's sure of everything that uses it. I enabled the general log a few days ago and have a good body of data with which to go code hu

Re: How scaleable is MySQL 5's Innodb with multi-core CPU's?

what do you consider a high number of updates/sec? I'm the DBA for a popular website in that league (well, maybe not google or yahoo but certainly ticketmaster) and we average ~210 DMLs/sec with peaks in excess of 1,000. we use a mixture of myisam for static (or infrequently updated) reference t

Re: prepared stmt from DBD causes segfault

nevermind: http://bugs.mysql.com/bug.php?id=20559 bummer... On 10/30/06, Sid Lane <[EMAIL PROTECTED]> wrote: distro: suse 10.1 (64-bit) MySQL server/shared/client: 5.1.11-0 (rpm) PERL: 5.8.8 DBI: 1.5.2 DBD: 3.0008 I am trying to do some benchmarks w/ & w/o prepared statement

prepared stmt from DBD causes segfault

distro: suse 10.1 (64-bit) MySQL server/shared/client: 5.1.11-0 (rpm) PERL: 5.8.8 DBI: 1.5.2 DBD: 3.0008 I am trying to do some benchmarks w/ & w/o prepared statements but if I enable mysql_server_prepare=1 I get a segfault when I try to execute a query - the prepare seems to work or at leas

Re: MySQL 5.1

any update on the 5.1 general release date? is it still on target for Q4 - Q1? any narrower window? On 8/30/06, Colin Charles <[EMAIL PROTECTED]> wrote: Logan, David (SST - Adelaide) wrote: Hi! > Does anybody have any idea when 5.1 may come to General Release? I am > particularly interested

does Query_time in slow log include time for client to receive answer set?

I have some strange entries in my slow logs whose timestamps corrolate to an event we are investigating: Query_time in the 2-4 range Lock_time: 0 for ALL entries Rows_sent in the single to low double-digits Rows_examined in the low to mid hundreds the question is whether these are cause or effec

replacement for Oracle initcap function

I am finishing up on performing an Oraclectomy on a bunch of legacy java code (don't ask why the DBA got stuck w/this - sore subject) and have one outstanding problem to solve: Oracle has a function, initcap(), which capitalizes the 1st character of each word and lowercases the rest. for example,

Re: Question regarding running 'mysql' from a bash script

one answer to your question as asked would be to wrap the column in a concat() function and put the double quotes around each row. the better answer is to use PERL

Re: MySQL Replication

I'll 2nd that "High Performance MySQL". it is by far the best MySQL book I've come across (though I didn't need the 101 stuff, I specifically needed tuning/architecting for HA, etc.) the only knock I could make (which isn't their fault) is that it needs to be updated for 5.x (can you say 2nd edit

Re: [OT-ish] Hardware for MySQL server

does it absolutely HAVE to be 1u? if you can go 2u we've been really happy w/HP DL385s lately. 2u form (which is still pretty small for a DB server), redundant power supplies (a good thing for DB server), six drive bays (so you can RAID5 or three mirror pairs), remote management card and Opteron

Re: Oracle DMP to mySQL -- Possible???

that looks like a spool file from sqlplus. does it have the data too or just a bunch of describes? if this is what he gave you he is either severly clue-challenged or trying to sabotage you (my $ on later though they're not mutually exclusive). you could write a perl program to parse this into so

MySQL equivilent to Oracle Names Server?

all, does anyone know of any GPL'd equivilent to an Oracle Name Server (essentially DNS for SQL*Net)? I am considering writing such a thing but wanted to see if someone had already invented this particular wheel. essentially I envision a server (probably redundant pair) to which application s

Re: Circular Replication

NOW I see the violence inhierent in the system... this has some profoundly cool possibilities... BWAH-HA-HA-HA!!! muchos!

Re: Circular Replication

stupid ?: what keeps them from getting caught in a write loop? turning off log_slave_updates? I had never thought of this but is has intriging possibilities...

Re: Can I use the information from "SHOW STATUS" in a SELECT statment ?

I don't know if you can do it directly in a mysql shell like that (like you would with v$, dba_ in Oracle) but if you call a show command from PERL (via DBI) it hashes the result just like it were from a select. may not be the most elegant solution but its the best I've come up with though I'll ha

Re: how to determine right value for max_allowed_packet?

I think I've foind the culprit: a problem (logical, not physical) had been discovered with a couple of tables which were fixed by truncating them in the production replication master and reloading them from a mysqldump of the corrected tables from the qc/dev database. the dump was done w/the -e (

how to determine right value for max_allowed_packet?

all, I just finshed hosing down a minor (that could have been FAR worse) fire where replication failed with an: "Error reading packet from server: Packet too large - increase max_allowed_packet on this server" in my error log. I bumped it up from 1M to 4M, restarted mysql (as well as dependant

Re: Migration from ORACLE 9i to MySQL

from a purely religous logical architecture viewpoint it is better to keep the business rules as close to the persistence layer (ex. RDBMS) as possible. in the practical physical/business world it is severly hyperlinearly expensive (both hardware as well as Oracle licenses) to support that model.

Re: parsing show commands (for monitoring/logging)

thanks but I already have a bash version that logs to local filesystem (then hacks the log into insert statements). what I'm working on is something to log to another MySQL DB (outside replication environment) to which I point (f)cgi (, etc.) I think I've answered my own ?: it seems DBI will par

parsing show commands (for monitoring/logging)

does MySQL have a "pretty" way to persist snapshots of various show commands? for example in Oracle you could just do: insert into sysstat_log select sysdate, * from v$sysstat; --(an ugly, overly simple example) can the show commands be called/parsed with DBD/DBI? so far the only way I've com

mysqldump specific tables from multiple databases?

all, is it possible to mysqldump specific tables from multiple databases in a single run? what I am trying to do is get replication slaves to a starting point but am somewhat challenged by the nature of our architecture. specifically, we have a large number of relatively-static (updated only a f

MySQL DBD not following my.cnf for socket file

hope this isn't considered too off-topic but... I have been working on standardizing the directory trees on our MySQL servers (a la OFA for those who speak Oracle) but when I repoint the socket parameter in my.cnf all my perl scripts barf w/: "Can't connect to local MySQL server through socket '/

Re: DOES MYSQL HAS CONCEPT OF SYNONYM

NOT DIRECTLY but if you're using (my)isam you can create softlinks on the underlying filesystem which is a really ugly hack but seems to work. Sid Lane DBA - Site Operations TWCi |-+> | | "Ansari, Raza| | |

Re: porting Oracle schema to MySQL

table from dba_tab_columns 4. use load data to suck in flat files from step 3 there were some things I had to do manually/one-off but that was surprisingly reliable for most of the migration. Sid Lane DBA - Site Operations

Re: anybody used prepared statements in 4.1 succesfully?

ution time/CPU load but is there a definitive/quantitative way to tell/measure? just curious (though I freely admit I've yet to even download 4.1; still proof of concepting on 4.0 so sorry if this is a RTM ?)... Sid Lane DBA - Site Opera

ERROR 1005 during add foreign key

e best notes from building the original "play" box). any suggestions would be greatly appreciated! Sid Lane DBA - Site Operations "WHAT is the average airspeed velocity of an unladen swallow?" -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]