Input Wanted: Optimizing SQL - For Millions Of Queries
Jul 7, 2008
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?
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....
I have a website that offers web stats. It receives like 30 million hits per day (legitimate) in addition to a large number of invalid URL requests and DOS attacks that max at 50MB. I currently have a server at liquidweb who, after bearing too much with me, informed me that I might have to move away some day because the attacks are affecting their network. I have been with them for 4 years now.
We tried using a normal firewall but it couldn't handle the normal requests. I don't have a budget to afford an expensive one. Firewall tweaking is not easy because of the large number of legitimate requests.
So, my questions are:
1. What should I do? Is there a known, affordable firewall that can help (or any other setting)?
2. Which hosting company will take me and manage to make things work without getting their network affected?
I am leasing servers for about 2 years now, and now I wanted to start with colocation. I am selling VPS so I need VPS nodes. I would buy node with 32Gb RAM, latest Xeons, Hardware Raid 10, etc. so I figured that it is better to colocate in the long run.
Can you suggest me best colocation company in terms of quality of bandwidth and support.
I really need prompt support, ticket answered in 15 minutes, and only premium bandwidth.
Also, is there company in USA which assembles rack servers for a fee. I would like to pay them for the components I find on newegg and their fee, and that they order the components, assemble the hardware, test it, and send it to DC.
I need a budget chicago server -- I know, very difficult to find (last company I went with was Singlehop, and they're great, but I can't afford it for what I'd wish to do.)
Anyways, here's what I need my server for --
Some personal, non busy websites(my webhost that I've been using is being let go by me :[ I need this server for it reliably.) CentOS5 or RH4 is preferred. cPanel is required
I intend to host a few websites, and TWO counter-strike 1.6 servers (not busy or CPU intense really) So..
I purchased some services from hostfresh.com but after waiting 3 days for my hosting to be set up (just basic web host space) i decided to switch to wrzhost.com where i was ready to go within 10 min of buying the services. I sent a support ticked to hostfresh(before i asked for the refund) and a lady told me to contact sales , when i told her i was getting no reply she again told me to contact sales so i bought else where. I emailed sales and billing 2 times each and sent 2 support tickets to them over a week and a half but have gotten no reply at all and when i sent a ticket to the lady that was actually replying , she now wont reply to my tickets also.
Anyone else have a problem with them and how did you get your refund?
I've been with JaguarPc since 1999 and I just realized that through all my years here I never gave them a shout out. These guys are very helpful and it's amazing the type of service they offer for the money. I have 2 virtual private servers with them and absolutely no issues. They manage the entire server for me for $33.95 a month. So if anybody needs good hosting:
I need a company to register my domain, and then forward emails and urls to my actual site that is somewhere else. I don't need any actual web hosting or email pop (at the moment). My main requirement is reliability.
The company I currently use bounces some email to me and forwards other email to a black hole. They're also not replying to my mail, so it's time to leave. BTW, this is a company that has generally excellent reviews.
I've got a decent server, quad processor, 12GB ram, terabyte of hot swappable RAID 10 disk. Its at an Equinix site. They provide UPS, backup generators, etc and fiber to the usual major backbone folks.
But its a lights out facility. I want someone else to handle backups, rebooting when needed, etc.
I'd like to let someone else provide the service. Use my 2U server or I can rent yours.
Need MySql, Apache, Tomcat, SSL, Java, ssh access. I've got wildcard certs for SSL, domain registered, etc.
I'm guessing that I can get this for about $500 per month with low bandwidth (this is an IT application, not peer-to-peer or torrents.). Let me know if my budget is off base. I think one megabyte/second sustained over the month, 95% averaged is suffiicent. At least until the business grows, we can then talk about getting racks of blades at higher cost.
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 have an Architecture/Interior Design firm and send lots of emails and CAD and photo files. I am designing a website and having it hosted for the first time. (I know...I know...I should have done it long ago..) I have read tons of articles on web hosting.
The website will be lots of photos (thumbnails to be enlarged possibly), text.
Has anyone heard of Inmotion? (web hosting company) They seem to have what I am looking for at a decent price and have gotten some good reviews (but I don't know if those reviews are sponsored by someone).
I work on a Macbook Pro and am looking into creating the website on iWeb....
I have gotten my server configured and running, and have been advised to look into creating virtual hosts for each site, I have found good information on setting virtual hosts within postfix, but does anyone have any good reading on configuring Apache2?
I only have one site I intend to host in the immediacy, but I'd like to host multiples eventually.
I'm thinking about colocating in Milwaukee, purchasing a rack over at the Cogent facility to resell and was faced with a problem. The problem is, to some people, a rather simple one. How can a reseller sell their servers when competition sells their services for unlimited space, bandwidth, domains, sub-domains and such for only $1/month?
I've done the math I could with the information provided to me but am not able to get around the above problem. I can see why some users go to this type of service though. A lot of websites don't even need more than what these sellers offer, and with good reason.
Usually that person only uses 10MB of space and 10GB of transfer.
To break even on hosting, I would have to be able to sell 12 servers at $169 each. Our hardware would be fairly new, capable of whatever you want it to do, short of cutting your lawn, clipping your toenails and what have you, all with 5mbps standard port connectivity inside a 1u case. Obviously you can upgrade the port speeds, drives or whatever else you need in it. This is to snag users into coming with us. Bigger guys charge much more per month for higher end hardware.
I guess what I'm trying to ask is simple. Is it even worth it? Are there users out there who are happy and able to put down $100+ a month for a dedicated server? I'm willing to expand our current services to this type of offering, but I need your input based on your experiences on what you think will end up happening.
We host our DNS with DNSmadeeasy.com. We noticed that the number of DNS requests is too high. So we decided to contact their support and ask them for log file, so we can determine
what's going on.
Reply we got from them is the following:
We researched your queries and we found that many of them were coming from the SoftLayer resolving name servers. We phone their admins to report the abuse and they said that the queries were infact coming from your own server with them.
We will not charge for any overage on the queries this month so you can research what on your server is causing these queries. But we will provide you with logs of about 5% of our your traffic.
here is the example of the line from log file sent by them:
May 10 17:15:48 t145 named[29945]: client 66.228.118.72#52529: query: host-196.218.57.145.tedata.net.mydomain.com IN AAAA -E May 10 17:15:48 t145 named[29945]: client 66.228.118.72#52529: query: afontenayssb-151-1-40-153.w83-114.abo.wanadoo.fr.mydomain.com IN AAAA -E
I changed the real domain name to "mydomain.com"
I checked the apache access log and found these hosts there in exact order as they are in log file sent by dnsmadeeasy.
What is strange is that why is my server making back these requests ? Also these requests are bad because these are not valid domain names:
e.g. should be "host-196.218.57.145.tedata.net" instead of "host-196.218.57.145.tedata.net.mydomain.com"
Seems that it includes ".mydomain.com" to the end of every request.
Is there a way to discover what is making those DNS queries?
Is it possible, through some script or otherwise, to send the traffic to certain/chosen domains to a completely different set of nameservers.? Preferably without having too much server load?
I already use a domain catchall, so no NS records are actually registered on the server.
I have been contracted to resolve an issue for a Plesk installation. This installation in particular is receiving the 'no input file specified' error when attempting to access Horde webmail, and I believe it is because:
IIS is in FastCGI mode (as expected) Permissions are not allowing php to execute out of the expected path.
PHP is working for all other domains (there are multiple) on this account, it is just the horde PHP that is not functional.
I have tried contacting Skype support but received no answer.
I have tried running the commands --fix-webmail that were suggested in other threads, but they have no effect.
I am checking DNS Report for a domain and in nameservers records I am getting following:
Recursive Queries
I could use the nameservers listed below to performe recursive queries. It may be that I am wrong but the chances of that are low. You should not have nameservers that allow recursive queries as this will allow almost anyone to use your nameservers and can cause problems. Problem record(s) are: xxx.xxx.xxx.xxx
I am using Windows 2003 Standard and Microsoft DNS Server. Any ideas what is this all about and how to fix it?
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
I'm currently hosting all of my stuff with my own Wild West Reselling Account, i.e., InnoDomains.com.
Problem is I'm running multiple Wordpress Installations and a few websites that have some pretty intense MySQL work going on in the background, and I use about 250GB per month in bandwidth.
My Account gets the 503 downtime errors because according to Customer Service I cannot run more than 50 MySQL queries in one second. Well, that's not much of a challenge because a lengthy wordpress page runs about 90 queries and loads in 2-3 seconds. Time this by three including the other websites and a couple of shopping stores, and I'm easily doing more than 100-150 queries a second more than several times a day, thereby seeing that nasty, irritating error.
My other option is to split up all the websites over basic $ 3.95 per month accounts. I don't want to switch to a dedicated server yet because I'm not really making a whole lot of money (about $ 30 per month). Another thing I could do is move my MySQL testing to a local machine, and that may actually reduce the load on the damn server.
Are there any hosts out there that will allow me to do more than 100 MySQL queries per second? If so, are there any that are any good?
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.
