How to reduce data by averaging ???
Hi! I have various tables more or less similar to this : mysql desc disk; +--+---+--+-+-+- --+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+- --+ | timecode | datetime | | | -00-00 00:00:00 | | | system_id| smallint(5) unsigned | | MUL | 0 | | | devicename | char(8) | | | | | | device_id| char(8) | | | | | | read_count | mediumint(8) unsigned | YES | | NULL| | | read_kb | mediumint(8) unsigned | YES | | NULL| | | write_count | mediumint(8) unsigned | YES | | NULL| | | write_kb | mediumint(8) unsigned | YES | | NULL| | | servicetime | float(4,2)| YES | | NULL| | | waittime | float(4,2)| YES | | NULL| | | active_queue | float(4,2)| YES | | NULL| | | wait_queue | float(4,2)| YES | | NULL| | | busy | float(4,2)| YES | | NULL| | +--+---+--+-+-+- --+ 13 rows in set (0.00 sec) Lots of data is inserted into these tables and allways (well, more or less) later selected with a ordering by timecode. I would like to reduce the size of old data by averaging the values by hour instead of the current where I usually have 12 samples per hour. The goal is to end up with high resolution data for the last couple of monts, while older and more unimportant data is averaged by hour. If I do a select like this : select substring_index(timecode,':',1),system_id,devicename,device_id,avg(read_coun t),avg(read_kb),avg(write_count),avg(write_kb),avg(servicetime),avg(waittime ),avg(active_queue),avg(wait_queue),avg(busy) from disk where system_id='1' group by substring_index(timecode,':',1) order by substring_index(timecode,':',1) ...I will get all the data averaged by hour, but how do I get it back into a table ??? In Oracle my guess is that I can do this with a combination of a select and insert, but I don't think this is supported in MySQL. As far as I know this is more a less a subselect thing, and MySQL doesn't support subselects. Do I need to make some external scripting (Perl with DBI) or can everything be done inside MySQL ??? -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - 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
RE: CPU usage running mysql
-Original Message- From: David Allen [mailto:[EMAIL PROTECTED]] Sent: lunes, 21 de mayo de 2001 14:36 To: Simon Green; [EMAIL PROTECTED] Subject: Re: CPU usage running mysql Hello again! Do you know if there is any way I can see what the mysqld is doing? If it is taking up 99% of the processor time, it is very busy doing something! I installed the binary version and consequently only have the tools available which came with that distribution. First of all, compare with supported tools such as vmstat or ps to ensure that top is correct. vmstat will not show the process itself but just total usertime, systemtime and idletime. You can also use Symbel ( www.setoolkit.com ), but I've experienced a bug in this too related to cpu/process info on Solaris 6, 7 and possible also 8. This is in all recent releases (3.0 - 3.2) and is present in toptool.se, aw.se and infotool.se. Tools in Symbel that will work and give you some info about mysqld is /opt/RICHPse/examples/pea.se this however tends to loop if you pipe the output to awk or another program. That's another bug ;-) For default debugging tools shipped with a standard Solaris go to /usr/proc/bin : # ls -al /usr/proc/bin total 30 drwxr-xr-x 2 root bin 512 Dec 31 20:25 . drwxr-xr-x 3 root bin 512 Dec 31 20:25 .. lrwxrwxrwx 1 root root 15 Dec 31 20:25 pcred - ../../bin/pcred lrwxrwxrwx 1 root root 16 Dec 31 20:25 pfiles - ../../bin/pfiles lrwxrwxrwx 1 root root 16 Dec 31 20:25 pflags - ../../bin/pflags lrwxrwxrwx 1 root root 14 Dec 31 20:25 pldd - ../../bin/pldd lrwxrwxrwx 1 root root 14 Dec 31 20:25 pmap - ../../bin/pmap lrwxrwxrwx 1 root root 14 Dec 31 20:25 prun - ../../bin/prun lrwxrwxrwx 1 root root 14 Dec 31 20:25 psig - ../../bin/psig lrwxrwxrwx 1 root root 16 Dec 31 20:25 pstack - ../../bin/pstack lrwxrwxrwx 1 root root 15 Dec 31 20:25 pstop - ../../bin/pstop lrwxrwxrwx 1 root root 15 Dec 31 20:25 ptime - ../../bin/ptime lrwxrwxrwx 1 root root 15 Dec 31 20:25 ptree - ../../bin/ptree lrwxrwxrwx 1 root root 15 Dec 31 20:25 pwait - ../../bin/pwait lrwxrwxrwx 1 root root 14 Dec 31 20:25 pwdx - ../../bin/pwdx There are man pages for all of them. They all give useful information about what a given process is doing. You can also use truss. ...ofcourse you can also get some info from MySQL itself with show processlist -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - 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
RE: Having success with 20 million rows
-Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: jueves, 10 de mayo de 2001 6:00 To: Christian Jaeger; David J. Potter; [EMAIL PROTECTED] Subject: Re: Having success with 20 million rows At 11:00 PM +0200 5/9/01, Christian Jaeger wrote: Was the table inaccessible during these 4 days? Or is mysql able to still write to the table while it's being altered? Those are not mutually exclusive possibilities. :-) According to the manual, reads can be done on the original table while the new table is being constructed. Updates are stalled and then applied to the new table after the alteration. http://www.mysql.com/doc/A/L/ALTER_TABLE.html Yep, and that stalling is (IMHO) one of the primary problems with MyISAM ISAM tables. Ofcourse it's possible to design oneself out of these problems, but the only real good solution is locking on row level rather than the whole table. InnoDB tables has row level locking AFAIR. My databases have around 52 insert requests per second, so even a small alter build up a huge amout of pending requests. Regarding the time spent. 4 days seems to be alot to me. what kind of hardware is it running on ? I have MySQL databases with more than 100 million rows and they take only about 4 hours to alter on a 700 MHz, 8 MB cache, EV67 Alpha (with lots of RAM). Even though I run on SMP machines with lots of CPUs, altering is a singlethreaded task (or so it seems), so I don't have any benefit out of it. -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - 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
RE: 64MB Limit on MyISAM indexes?
-Original Message- From: Jeff Brewer [mailto:[EMAIL PROTECTED]] Sent: viernes, 20 de abril de 2001 18:24 To: MySQL Mail List Subject: 64MB Limit on MyISAM indexes? I've got tables in ISAM format whose indexes are way over 64 MB. However, when I try to create a MyISAM table with an index that big I get errors and confirming that with myisamchk I get: myisamchk: warning: Keyfile is almost full, 67107839 of 67107839 used Which is essentially 64MB. Are MyISAM tables able to handle large indexes? I've had ISAM and now have MyISAM indexes larger than 2 GB, so yes. I hope I don't have to go back to ISAM tables... Any suggestions? Haven't seen that error before, so I can't help you. -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - 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
RE: Unaligned address crash on Tru64
Hi! I have tested a bit today. Various logfiles are at : http://www.dassic.dk/~jfn/mysql/ I found that after compiling with full debugging, I got the error that one of the my.cnf parameters was too little and should be increased on the server. The default is 1 MB. I tried to increase it to 16 M, then 32 MB, then 512 MB, then 5 GB. No go at all. I tried both slave server (I have 6 GB on the machine and a unlimited ulimit and full access to all memory (no sysconfigtab limitations), so I'm not low on memory). The configure and make options for this crashing debug version is in the make_bad.sh, and the error in O4_error.txt and the mysqld.trace in mysqld.trace.txt I then decided to strip down the compiler options to what you find in make_good.sh. Did a recompile (output from this in make_good.log.txt (stdout) and make_good.typescript.txt (stderr)). As the name say, this version works. I compiled with no optimization, and arch set to generic since the machine I compile on is a EV67 and the machine I test on is EV56, and I don't want any EV67 instructions to be emulated by the kernel. I deleted master.info and let the slave update about 1 GB of data. This worked without problems and I got no errors. So it appears that the recommended optimization settings from INSTALL-SOURCE does something bad to MySQL in my environment. I've tried -O3 and -O2. No go, only -O0 works. Haven't tried playing with some of the other parameters. -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - 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
RE: Unaligned address crash on Tru64
-Original Message- From: Sasha Pachev [mailto:[EMAIL PROTECTED]] Sent: Saturday, April 07, 2001 6:54 PM To: Nemholt, Jesper Frank Cc: [EMAIL PROTECTED] Subject: Unaligned address crash on Tru64 Jesper: The problem is apparently a bug that under some circumstances tries to store an integer at an unaligned address with movl instruction. Slave code does a lot of address magic, so there is a lot of room for alignment bugs. Aha. I've also recieved unaligned access errors, allthoug without running into a crash, in other situations. I remember recieving it every time I access one of the following : mysqladmin status mysqladmin processlist mysqladmin extended-status mysqladmin variables I haven't checked if it is all of them or just one of them, but I get one "unaligned access" error every time I access a PHP page containing these as systemcalls. Any chance you could run the slave in a debugger and find the line where this happens? I'll try debugging next week. I'll debug the Compaq CC/C++ compiled version, since this is the one I hope to get working (it generally produces faster smaller binaries on Tru64 than GCC). One thing btw. for the MySQL INSTALL-SOURCE Tru64 documentation : Might be a good idea to add that in order to compile recent MySQL versions ( 3.22.x) one has to upgrade the make utility. The one present by default on all Tru64 versions until 5.1 is not able to make MySQL. I haven't checked if the one present on 5.1 or 5.1A is better. -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - 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
Signal 11 crash when running 3.23.36 in slave mode on Tru64
Hi! I've run into a problem with a replication setup. I have a 4 CPU EV5.6 AS4100 running a master database. MySQL is 3.23.36, OS is Tru64 4.0F patchkit 4. MySQL is compiled with Compaq CC C++ with the recommended compiler options mentioned in INSTALL-SOURCE. This master database runs without any problems. Today I setup a slave server on a 12 CPU EV67 GS140, still 4.0F patchkit 4 and MySQL 3.23.36, and still compiled with Compaq CC C++. When starting up the slave I immediately get this : Unaligned access pid=892 mysqld va=0x12021d4e4 pc=0x3ff800e8a24 ra=0x3ff8013df90 inst=0xa6100028 010406 15:47:23 mysqld restarted Unaligned access pid=875 mysqld va=0x12021d4e4 pc=0x3ff800e8a24 ra=0x3ff8013df90 inst=0xa6100028 010406 15:47:23 mysqld restarted Unaligned access pid=881 mysqld va=0x12021d4e4 pc=0x3ff800e8a24 ra=0x3ff8013df90 inst=0xa6100028 010406 15:47:24 mysqld restarted And in the error log : 010406 15:36:16 mysqld restarted /usr/users/un19102/mysql/libexec/mysqld: ready for connections mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died. I start it with safe_mysqld and this script try to restart it over and over again. I have also tried starting it manually. Same result. Sometimes however it does startup, but crashes within 5-10 minutes with the same error. I tried compiling with GCC instead of Compaq CC/C++. Same result, same error. I've also tried various mysqld parameters to turn off dangerous settings and/or add more debugging info. I didn't try yet to compile MySQL with more debugging options etc. As soon as I remove the slave settings from my.cnf and start it as a normal server, everything runs normally. Is it compiler related problems, or is there something wrong with the slave option ? -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - 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
RE: Dual Processor and MySQL !
-Original Message- From: Hamid Hashemi Golpayegani [mailto:[EMAIL PROTECTED]] Sent: sbado, 31 de marzo de 2001 17:09 To: [EMAIL PROTECTED] Subject: Dual Processor and MySQL ! Hi , Sorry for this question may be this question asked before but I can't find it . I want to know that an SMP Dual CPU machine for a MySQL server is better that a single CPU machine ?! I mean is the MySQl use the both CPU's for processing or not ? and how much affect on the responding and which parameters are going better in this case ?! As said by other, if the OS supports SMP, MySQL will benefit from this, since MySQL is a threaded application. However, a singlethreaded SQL (ie. a SQL MySQL is unable to break up into several parts) will only run on one CPU at a time, and this is the situation you will see most of the time. In fact I'm not even sure MySQL does try to break up SQLs into 1 threads, but I know other database servers do. A SQL with several subselects is a good example. Every subselect can run as a seperate thread, but as you probably know, MySQL doesn't support subselects, so there... So, you should not expect to get better single SQL performance from a SMP machine. Where you will get better performance is if you have 1 concurrent user or rather 1 concurrent SQL (who don't lock each others tables), because MySQL (or rather the OS) then will balance the SQLs between the available CPUs. You will allways get better performance out of a single CPU machine rather than a SMP machine if the CPU type is the same and the single processor is about the same clock frequency as the SMP processors combined. A single Pentium III 1 GHz will in single threaded tasks be a little more than twice as fast as a dual Pentium III 500 MHz, and in every case allways faster. If you serve a single threaded task to a dual Pentium III 500 MHz, it perform like a single Pentium III 500 MHz. If you serve a multithreaded task to it, it'll perform somewhere between 500 MHz and close to 1 GHz. SMP should only be considered when you can't get any faster with a single CPU machine, and if you need that extra performance, you should still use the fastest clocked CPUs for SMP, because if you don't it'll feel slow in singlethreaded performance. I've run MySQL on machines with between 2 12 CPUs running either Linux, Solaris or Tru64. With one SQL active at any given time, I've never seen MySQL use more than one CPU. With two SQLs active at the same time, it uses 2 CPUs and so forth, unless the SQLs lock each others tables, which in fact, for me, is a major problem in MySQL. You need a recent MySQL to fully benefit from SMP, since older 3.22.x doesn't have any configure options to control SMP behavior. Secondly, on some platforms it is quite important to compile MySQL with the right flags in order to get the best SMP/thread performance. -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - 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
RE: Dual Processor and MySQL !
-Original Message- From: Hamid Hashemi Golpayegani [mailto:[EMAIL PROTECTED]] Sent: domingo, 01 de abril de 2001 15:07 To: Nemholt, Jesper Frank; [EMAIL PROTECTED] Subject: RE: Dual Processor and MySQL ! Hi , Thank you very much for your compelete description about MySQL and multi processor platforms . According to your mail if I have several connection to my SQL server and I have more than 1 CPU and installed the SMP on my machine the MySQL performance will be better than a single CPU and If I have only 1 connection to my SQL server then single and dual CPU are not import for this situation Correct. . So it is ok ! cause I have about 200 concurrence connection to my SQL machine and it seems that be better to have more that one CPU . Yes, with more than one concurrent query running, you'll benefit from SMP. Exactly how much depends on many things (table locking is one critical thing with MySQL), but with 200 concurrent connections, you'll get pretty close to 100% benefit, which means if you go from one 500 MHz to two 500 MHz CPUs, you'll get close to twice the performance. ...but the execution time of one singlethreaded SQL on a idle machine will not get faster. To get this you need a faster CPU, not more CPUs. Try to run a "show processlist" every now then and see how many connections you have, and how many of those are active or sleeping/waiting for a table to be unlocked. Depending on database design, you might find, with MySQL, that most of the time is spent waiting for a table to unlock. There are some guidelines in the MySQL manual on how to avoid this (by inserting into temporary tables and the likes), but most of these solutions will get rather ugly. Other SQL servers ike Oracle lock on row level which is _much_ better. I hope this will come in MySQL soon. About other SQL servers that you said support multi processing alone without depending on OS and SMP . Can you tell me them ?! I thinks that Oracle would be one of them . All of them (well, those I know) rely on the SMP support in the operating system. In fact, the SQL server doesn't need to know about SMP (or how many CPUs the machine have). The only place I know of where the SMP support is built into the application is on MacOS nad applications like Adobe Photoshop. This is a very ugly hack and only made because Adobe needed the speed, there were SMP hardware, but the OS didn't support SMP. What is important is whether the SQL server is multithreaded or not (and support the thread implementation on the operating system it runs on). If it is multithreaded and dedicate a thread to every query, or maybe is even able to split a query into several threads, then it'll utilize whatever SMP is offered by the OS. Beware that many OSes don't support SMP or have pathetic thread support. You'll find fairly good SMP thread support in most commercial unix versions (Solaris, HP-UX, Tru64, AIX etc.), Windows NT/2000 and recent versions of Linux. Most of the *BSD implementations, and Windows 9x doesn't support SMP, and some not even threads. What I don't know about MySQL is how well (if at all) it is able to split up a query into several threads. On a SQL server like Oracle I know that complex queries with several subselects and things like that are divided into several threads which improve performance alot on a SMP machine. The DBAs where I work use this extensively when they create their SQL statements in order to get the most performance out of the machine (and the query result as fast as possible). Most of your production machines have more than 8 CPUs, so there is alot to win on them. -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - 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
RE: Mysql in a 4-way / 2 GByte server / raid 5
-Original Message- From: Rafael Martinez [mailto:[EMAIL PROTECTED]] Sent: martes, 27 de marzo de 2001 15:28 To: [EMAIL PROTECTED] Subject: Mysql in a 4-way / 2 GByte server / raid 5 Hello I am thinking to install our DB server in a 4-way machine with Intel Xeon processors/1MB cache. I can see in the mysql documentation that mysql works OK in a 2-way machine but that in a 4-way system has to be tested. Is this true? Or the documentation is not updated? Anybody out there with a 4-way machine that runs mysql without problems? (kernel 2.4.x glibc 2.2.12) Our DB has around 120.000.000 "questions" per week and our 2-way/1Gb system works OK, but the trafic is growing very fast and I would like to have a backup plan incase I need it. Thanks for your help. Rafael Martinez I've had MySQL running on a 12 CPU 16 GB machine running Tru64. Runs OK. I normally run it on a 4 CPU 4 GB machine and another one with 3 CPUs and only 512 MB. Both Tru64 Alpha. In recent (3.23) versions there are some my.cnf parameters related to SMP machines. You might want to take a look at them : set-variable= thread_cache=8 set-variable= thread_concurrency=8 # Try number of CPU's*2 The only problem I have is that there appears to be a problem with memory allocation when running MySQL on Tru64. I haven't found out exactly what the problem is, but the result is a crashed MySQL with a "Out of memory" error. Which is rather misleading since there is plenty of free memory and no ulimit or any other limiting kernel parameters. l8r/Jspr - 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
RE: How to index this properly (to make it go faster) ?
-Original Message- From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]] Sent: martes, 27 de marzo de 2001 10:00 To: Nemholt, Jesper Frank Cc: '[EMAIL PROTECTED]' Subject: Re: How to index this properly (to make it go faster) ? [clip] Why that, usertime systemtime are data values, they are never used in any where clause. As far as I can see it's complete waste to put index on those. If all columns of the SELECT clause are part of the index, MySQL will solve the query only with reading from the index file and never touch the data file itself. Since the index is quite big already and usertime+systemtime only add 8 bytes per row, this may pay off regarding the speed win / size increase. Aha. I'll think about that, but I'll have to look in the logfiles to see if there's a pattern regarding what the users select. I don't think it's just usertime systemtime every time. [clip] Depending on the Cardinality of the different columns you may want to change the order to (username,processname,system_id,timecode) or alike. Aha, I'll have to test more with the individual order of combined indexes to see what I can get from it. Well, normally that is not really important, but in your case, processname has a low cardinality and therefore one value has a lot of matches in the index and it is worth a try to put it to the right. So the lower the cardinality the more to the right in a combined index ? [clip] May I bother you to add the following indexes and post the EXPLAIN after adding them:. (system_id, processname, timecode) (system_id, processname, timecode, username) (system_id, processname, timecode, username, usertime, systemtime) I'll try. Tonight I'm going for system_id, processname, timecode I will probably make the next tests on the disk table, since recreating indexes and analyzing/optimizing on the process table takes too many hours. The disk table only takes 20 minutes. Every explain ends up saying "where used" and the showing the full number of rows. This is in 3.22.32. My test setup is the latest, it might be different there. Well, using two different systems isn't a good idea, except if you plan to upgrade your 3.22.32 system, because if we find a solution on your test setup (3.23.?), it won't behave the same on the older MySQL. In most cases, 3.23 is faster than 3.22 (which is to be expected). I plan to upgrade asap, but had a few problems building the latest stable under 4.0f ev56. On a side note, with 3.22.32 I have some weird memory allocation problems. The database runs on a 4 CPU machine with 4 GB memory, and most of these 4 GB are free (or rather used for filesystem cache by the OS). The memory is free if any process want it. With the default settings (ie. no custom my.cnf) everything runs fine, but MySQL use allmost no memory which is not optimal given the size of the database indexes. If I instead use the my-huge.cnf that comes with MySQL which makes a key buffer of 384 MB, a big select makes MySQL crash with a "Out of memory" error. I have ulimit set to unlimited, and no memory allocation errors are logged to /var/adm/messages (Tru64 allways logs to that file if some user hit the limit of some sysconfigtab/kernel parameter). This means that the "Out of memory" error from MySQL was not caused but the OS disallowing mysqld to allocate more memory. I have tried this several times to be sure it's reproduceable. I get a crash every time. Is there a memory allocation bug in MySQL on Tru64 ? Anothing thing: If you cannot get it work with combined indexes, can you post some testcase (i.e. the SQL commands to build a sample database), so that we can play around and see if we have more luck? Sure. Everything will in fact be put on a web page soon, since it's a OpenSource solution. The database is used to store performance info for Tru64, Solaris Linux (and others soon). A bit like MRTG/RDDTool/Cricket but much more dynamic detailed. l8r/Jspr - 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
How to index this properly (to make it go faster) ?
Hi! I have this table : mysql describe proc; +-+---+--+-+ -+---+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+ -+---+ | timecode| datetime | | MUL | -00-00 00:00:00 | | | system_id | smallint(5) unsigned | | | 0 | | | pid | char(8) | | | | | | ppid| char(8) | | | | | | username| char(20) | | | | | | cpu_usage | float(4,2)| YES | | NULL | | | memory_physical | mediumint(8) unsigned | YES | | NULL | | | memory_virtual | mediumint(8) unsigned | YES | | NULL | | | usertime| float(4,2)| YES | | NULL | | | systemtime | float(4,2)| YES | | NULL | | | priority| tinyint(4)| YES | | NULL | | | input_block | mediumint(8) unsigned | YES | | NULL | | | output_block| mediumint(8) unsigned | YES | | NULL | | | major_fault | mediumint(8) unsigned | YES | | NULL | | | minor_fault | mediumint(8) unsigned | YES | | NULL | | | processname | char(20) | | | | | +-+---+--+-+ -+---+ With these indexes : mysql show index from proc; +---++-+--+-+--- +-+--++-+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++-+--+-+--- +-+--++-+ | proc | 1 | timecode|1 | timecode| A |NULL | NULL | NULL | | | proc | 1 | system_id |1 | system_id | A |NULL | NULL | NULL | | +---++-+--+-+--- +-+--++-+ A common select wouldbe something like this : mysql explain SELECT timecode,usertime,systemtime FROM proc WHERE (system_id = '1') AND timecode BETWEEN '2001-03-23 11:08:11' AND '2001-03-24 11:08:11' AND (username = 'statdb') AND (processname = 'httpd') ORDER BY timecode; +---+---++--+-+--+--+--- -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---++--+-+--+--+--- -+ | proc | range | timecode,system_id | timecode | 8 | NULL | 5394 | where used | +---+---++--+-+--+--+--- -+ Or with a bigger timespan like this : mysql explain SELECT timecode,usertime,systemtime FROM proc WHERE (system_id = '1') AND timecode BETWEEN '2001-03-13 11:08:11' AND '2001-03-24 11:08:11' AND (username = 'statdb') AND (processname = 'httpd') ORDER BY timecode; +---+--++--+-+--++-- --+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--++--+-+--++-- --+ | proc | ALL | timecode,system_id | NULL |NULL | NULL | 206878 | where used; Using filesort | +---+--++--+-+--++-- --+ With the same SQL, but with index added on processname : mysql explain SELECT timecode,usertime,systemtime FROM proc WHERE (system_id = '1') AND timecode BETWEEN '2001-03-13 11:08:11' AND '2001-03-24 11:08:11' AND (username = 'statdb') AND (processname = 'httpd') ORDER BY timecode; +---+--++-+-+--- +---++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--++-+-+--- +---++ | proc | ref | timecode,system_id,processname | processname | 30 | const | 16237 | where used; Using filesort | +---+--++-+-+--- +---++ Now, the problem is that I have about 30 system_id's in the database. Each system insert about 500 rows into the proc table every 10 minute. ...so after a month or so I have alot of rows ( 100 million), and a performance problem. In Oracle I have fixed this by partitioning on system_id and/or timecode, but this is not possible to do transparently
RE: How to index this properly (to make it go faster) ?
-Original Message- From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]] Sent: sabado, 24 de marzo de 2001 23:31 To: Nemholt, Jesper Frank Cc: '[EMAIL PROTECTED]' Subject: Re: How to index this properly (to make it go faster) ? First, you should run an analyize on your indexes (e.g. with (my-)isamchk). This will give MySQL a value for "Cardinality" and enable the optimizer to do its work better. Allready doing that. It was missing from the first mail since I'd just created the indexes. Here's from the real one : mysql show index from proc; +---++-+--+-+--- +-+--++-+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++-+--+-+--- +-+--++-+ | proc | 1 | timecode|1 | timecode| A |4220 | NULL | NULL | | | proc | 1 | system_id |1 | system_id | A | 1 | NULL | NULL | | | proc | 1 | processname |1 | processname | A | 88 | NULL | NULL | | +---++-+--+-+--- +-+--++-+ A good index would probably be (system_id,username,processname,timecode). The users hardly use 'username' in their where clause. That's why it's left out (the index started to get rather big also). Maybe even (system_id,username,processname,timecode,usertime,systemtime), which should enable "using index" only. Why that, usertime systemtime are data values, they are never used in any where clause. As far as I can see it's complete waste to put index on those. You may want to use partial keys in order to reduce space consumption, e.g. Maybe even (system_id,username(5),processname(5),timecode), This is a no go since alot of processes are identical on the first 5 characters. The username could be used, but I decided not to, since Oracle (which the solution also has to be compatible with) is smart enough to only store the characters in use. I actually think MySQL does the same. Depending on the Cardinality of the different columns you may want to change the order to (username,processname,system_id,timecode) or alike. Aha, I'll have to test more with the individual order of combined indexes to see what I can get from it. [clip] No index is chosen, because you obviouly SELECTed a major part of the table. This probably won't happen, if you got these 100 million rows are talking about below (because you won't select about 20.000.000 rows, will you?). Well, that's the problem. I will ;-( The database contain performance info for a collection of very large unix servers. Most of them have more than 1000 concurrent processes, more than 100 disk devices and so on. For 34 machines the amount of data in a day is more than 100 MB, when sampled every 10 minute. I currently keep 3 months of history. The database is used to study performance on the machines, like memory use, disk use and so on. The interface is either Microsoft Excel or a PHP/GD based web interface I've made. Most selects go only a few days back, since it's mostly a matter of finding out what caused the machines to be slow, out of memory or whatever the night before, but sometimes we need more than a month to find out about processes leaking memory slowly, development of memory usage, disk I/O development and so on. Performance has been pretty good until now except on the disk table (due to 100 unique disk devices on most machines). Performance for process went all bad when I recently changed from collecting only top 20 processes to collecting all (those 500 - 2000 processes). I could ofcourse go back to top20, but the benefit of having all processes makes me want to avoid that, and the stuff running in Oracle is not hurt due to the partitioning. Reasonable indexes would be the same as above, which should reduce the number of rows to read far enough to prevent a full table scan. [...] Now, the problem is that I have about 30 system_id's in the database. Each system insert about 500 rows into the proc table every 10 minute. ...so after a month or so I have alot of rows ( 100 million), and a performance problem. In Oracle I have fixed this by partitioning on system_id and/or timecode, but this is not possible to do transparently in MySQL, You could probably use the MERGE table type. Aha, I'll take a look on that in the manual. [clip] I have tried to combine the indexes, but then MySQL fails using them properly, Would you please elaborate on this? This is IMHO the main point of your problem. You need these indexes. Problem is that MySQL seems to do a full table scan as soon as I start to combi
RE: Memory problems/bug ?
-Original Message- From: Nemholt, Jesper Frank Sent: viernes, 09 de marzo de 2001 19:52 To: '[EMAIL PROTECTED]' Subject: Memory problems/bug ? Hej! Using MySQL 3.22.32 on Tru64 4.0F patchkit 4. Compiled with Compaq CC. Ran optimize on a table, and after 10 minutes : 010307 17:00:00 Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space mysqld ended on Wed Mar 7 17:00:21 MET 2001 One other question about this error : If I try out the latest stable version, will I be able to use the 3.22.32 database files directly, or should I rather dump the existing and import them to the new ? I'd ofcourse still like some answers regarding my original question... -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - 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
Memory problems/bug ?
Hej! Using MySQL 3.22.32 on Tru64 4.0F patchkit 4. Compiled with Compaq CC. Ran optimize on a table, and after 10 minutes : 010307 17:00:00 Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space mysqld ended on Wed Mar 7 17:00:21 MET 2001 I checked : iratxe.tm.es ulimit unlimited iratxe.tm.es /usr/sbin/swapon -sv Swap partition /dev/rz8b (default swap): Allocated space:48768 pages (381MB) In-use space: 316 pages ( 0%) Free space: 48452 pages ( 99%) Swap partition /dev/rzb11c: Allocated space: 256841 pages (2006MB) In-use space: 312 pages ( 0%) Free space:256529 pages ( 99%) Total swap allocation: Allocated space: 305609 pages (2387MB) Reserved space: 47242 pages ( 15%) In-use space: 628 pages ( 0%) Available space: 258367 pages ( 84%) No ulimit and more than 2 GB free swap and no active swap usage. Before the crash I'd succesfully optimized other (and smaller) tables. I tried once again on the same table, and MySQL crashed again. Same thing happened when I tried to put a new index on the table. I checked the memory status : http://www.dassic.dk/iratxe_memory.png As seen, the optimize (around 17:00) causes the UBC filesystem buffer to allocate more memory, and when it has used all the free memory, MySQL crashes. I don't understand this, since the memory used by the UBC is available for any program that might need it. The operating system will only use memory for UBC if it's not used for anything else, and release UBC memory if a application needs more. Secondly, MySQL crashed even before the machine started to swap, and as seen the active memory increases only because of the UBC, not because MySQL allocate more memory. Does MySQL 3.22.32 have a bug in this area, and if so, is it fixed in later versions ? I've compiled MySQL with large file 64-bit support, and the filsystem (AdvFS) has no problems with large files. l8r/Jspr - 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
