I optimized a mysql table of 2 million records and about 500MB.. it took about 15 minutes.. However, on the same DB now i have another huge table of 88 million records, it size is 2.2 GB and it has about 30 MB to optimize... my questions..
1.- How can I speed up the optimization process so it can take the less possible time? any tweaks to my.cnf?
2.- Should I repair it using phpmyadmin or just from the shell?
3.- Should I stop http traffic during this optimization?
This is a dedicated db mysql server that handles a large VB forum with 5-8 users online average:
I have four servers with a quad xeon, 4gb ram, and 2x300GB SAS 15K RAID0 harddrives, pushing a total of 1.6gbits. It serves a lot of zip files with an average flesize of 180mb. My question is, how can I optimize lighttpd 1.4.19 to push its max with very low IO-wait. I've looked up some stuff and only found options that deal with lighttpd 1.5 and use Linux-AIO for the backend network. Currently I use writev with 16 workers and an read/write idle timeout of 10s. Logging is off, too.
The my sql seems to take much load in my VPs, configuration file is as follows
[mysqld]
set-variable=local-infile=0 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1
it's getting to the point where I need to optimize MySQL to better handle a busy server.
These days it seems MySQL is using 30% - 60% CPU almost constantly. But, the good news is that I've got 4 gigs of ram on this box and their seems to be an access of 600mb free constantly throughout the day.
Is it possible to tweak MySQL to a little more RAM dependent and take some of the load off the CPU? It seems MySQL wont go over 300mb of ram at any given time.. I'm already working on optimizing the SQL Querys on the web page itself.
my.cnf (4.1.22-standard):
# Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). # old_passwords=1
What I did here was create /var/log/mysqld.slow.log for queries that are taking one second or longer. After around 36 hours, here is what I came up with. Since the file size of /var/log/mysqld.slow.log was 219KB, posting it in its entirety it not practical. Therefore, I used a program, the perl script known as mysql_slow_log_parser found on www.retards.org, to parse the mysqld.slow.log to make it easier to interpret and read. The result was that the original filesize of 219KB was converted to the smaller size of 14KB....
Are there any services where an ADMIN can monitor DB usage on system and make some recommendations or even find SQL that is poorly written ...
I have a DUAL harpertown, 10GB RAM, and RAID
I have a typical, gallery, busy VB forum, and video script...
THe site traffic has not increased much but my Memory keeps getting chewed up... i already did some tuning that VBULLLETIN suggested... but the memory still goes... i need a tool or someone to just monitor the server for a day and grab as much diagnostic info as possible..
I suspect that its a single query giving problems cuz i have some custom coded pages...
I don't want to split the DB and HTTP request on diff servers cuz the traffic that i get shouldn't warrant it... for now...
Table '231_message' is marked as crashed and should be repaired
I haven't ever had this happen, is all my data truely lost or can i 'repair' it
i was moving sites to a different server (VPS -> dedicated) and on the vps i have a max of 20gb storage, well as i was using the cpanel transfer it filled this up because it didnt have a warning about this (i dont accept blame )
either way it seemed at this time someone on my site tried to make an adjustment to the table and it crashed it since there wasnt room? i really need to get this fixed asap so I can properly move this site with a working database
I accidentally deleted the columns_priv table in mysql database. How can I bring it back can anyone show me the structure of that table so I can build it back.
what I can do about the following database error in mysql?
Incorrect key file for table '/tmp/#sql_(number of table.MYI); try to repair it
I've repaired, optimized and checked the database in phpmyadmin. Initially 2 tables were showing overhead, but the error message is for a temp table and I'm not sure how to try to repair a temp table.
I wasn't sure where to post this so here goes, I need to migrate a MySQL DB, in the past I have just created an SQL file and used that method (sometimes having to split the SQL file up) but now the DB is about 50 meg and 733,233 records.
Is there an easier way to migrate the Database from one server to another?
if there was a way for me to cut up a large mysql database in shell. We're talking almost 8 gigs here.
I would like to import it in chucks, something like 1 gig per shot. Is that doable with some software that I can use in shell? I wouldn't want to use vim to splice up that sucker.
Or is there a better method of doing this? I already dumped this and I rather not do it again unless if I could perhaps dump this in chunks.
I have a 512mb cPanel VPS and would like to try and save a bit more ram, I have made the below changes to the httpd.conf and have also turned spamd, entropychat, melange, mailman, the only webstats I have is AWStats
Code: MinSpareServers 2 MaxSpareServers 5 StartServers 3 Can anyone recommend any other changes to save some more ram ?
For anyone wanting to know, the VPS is being used to serve my websites
I remember seeing a website/forum where you couldn't post your server stats and httpd.conf settings and experts would give advice on settings you should use.
Does anyone know the url to this site, or site similar to it
Are there some simple things that we can do or perhaps request our server management to do to optimize our server? Not sure where to start or what to ask so would like to learn some more before speaking with the server management company. Often times, you need to know the right questions to ask.
I am having problems with apache going down at least once a day. In Plesk there is a watchdog that monitors the service and then restarts it, but tries 5 times and fails, so apache will still be down and monitoring released. I really don't know what is causing this problem, maybe I could get some help? (Server specs at bottom of post, hope this is all you need)
error_log shows:
Code: [Thu Oct 04 12:05:43 2007] [notice] caught SIGTERM, shutting down [Thu Oct 04 12:05:48 2007] [notice] suEXEC mechanism enabled (wrapper: /usr/sbin/suexec) [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] Init: SSL server IP/port conflict: default-69-64-36-86:443 (/etc/httpd/conf.d/zz010_psa_httpd.conf:271) vs. webmail:443 (/etc/httpd/conf.d/zz010_psa_httpd.conf:344) [Thu Oct 04 12:05:48 2007] [warn] Init: SSL server IP/port conflict: default-69-64-37-16:443 (/etc/httpd/conf.d/zz010_psa_httpd.conf:111) vs. *****:443 (/var/www/vhosts/*****/conf/httpd.include:12) [Thu Oct 04 12:05:48 2007] [warn] Init: You should not use name-based virtual hosts in conjunction with SSL!! [Thu Oct 04 12:05:48 2007] [notice] Digest: generating secret for digest authentication ... [Thu Oct 04 12:05:48 2007] [notice] Digest: done [Thu Oct 04 12:05:48 2007] [notice] mod_python: Creating 4 session mutexes based on 512 max processes and 0 max threads. [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:05:48 2007] [warn] Init: SSL server IP/port conflict: default-69-64-36-86:443 (/etc/httpd/conf.d/zz010_psa_httpd.conf:271) vs. webmail:443 (/etc/httpd/conf.d/zz010_psa_httpd.conf:344) [Thu Oct 04 12:05:48 2007] [warn] Init: SSL server IP/port conflict: default-69-64-37-16:443 (/etc/httpd/conf.d/zz010_psa_httpd.conf:111) vs. *****:443 (/var/www/vhosts/*****/conf/httpd.include:12) [Thu Oct 04 12:05:48 2007] [warn] Init: You should not use name-based virtual hosts in conjunction with SSL!! [Thu Oct 04 12:05:48 2007] [notice] Apache configured -- resuming normal operations [Thu Oct 04 12:06:40 2007] [warn] child process 25195 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25197 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25204 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25207 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25219 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25228 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25232 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25244 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25246 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25257 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25261 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25262 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25265 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25275 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25278 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25281 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25283 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25287 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25351 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25291 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25300 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25302 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25307 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25355 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25365 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25374 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25376 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25377 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25408 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25412 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25423 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25430 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25434 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25437 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25460 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25463 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25474 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25475 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25476 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25565 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25478 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25480 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25481 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25483 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25484 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25485 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25486 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25583 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25489 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25490 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25491 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25493 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25590 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:40 2007] [warn] child process 25591 still did not exit, sending a SIGTERM [Thu Oct 04 12:06:42 2007] [notice] caught SIGTERM, shutting down [Thu Oct 04 12:06:46 2007] [notice] suEXEC mechanism enabled (wrapper: /usr/sbin/suexec) [Thu Oct 04 12:06:46 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:06:46 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:06:46 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:06:46 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:06:46 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:06:46 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:06:46 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:06:46 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:06:46 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:06:46 2007] [warn] RSA server certificate CommonName (CN) `plesk' does NOT match server name!? [Thu Oct 04 12:06:46 2007] [warn] Init: SSL server IP/port conflict: default-69-64-36-86:443 (/etc/httpd/conf.d/zz010_psa_httpd.conf:272) vs. webmail:443 (/etc/httpd/conf.d/zz010_psa_httpd.conf:345)
i have a Pentium D 820, 2GB Ram at LT where i am hosting a few proxies. Using Centos and Directadmin.
It's realy fast and i am happy with it. The only problem i am facing is that load is jumping up to 60 sometimes but just for a few seconds. Especially when a lot of people are using the proxy and it slows down a bit. That affects only the proxified pages and not a few personal html sites i also host there.
At the moment i am seeing in top that the CPU usage varies from 0,2 to 8, 12, 21 etc but just for a second or two
I am running a SQL test server here that is piping HUGE amounts of data for a test project I am running.
Approximately 30 queries a second - constantly. That's over 2.5M queries a day -- so we are talking big (I think?)
I am currently running this test on a
AMD Phenom 9300, 4GB RAM - SATA 500GB HDD and I am running MySQL 5.0.51a i386 on CentOS
I have programmed a process control for our applications purpose - basically it controls the launching of our SQL intense applications, and stops launching when the Load is greater than 2.5
I have plans to optimize the number of queries (I will build in a cache to some of the applications - and run INSERT statements all together) however I am looking for SQL tweaks that will improve performance. Would running the 64bit version work better?
Today i need to run "OPTIMIZE TABLE" from MySQLTuner recommendation.
Now i don't know how to run it ? I try to put in SQL Query via phpmyadmin and:
SQL query:
OPTIMIZE TABLE
MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1