Frequently Asked Questions
What are your rates and why are they so competitive?
We charge per hour and track time in 15 minute increments. Most issues
are resolved and identified very quickly. The reason our prices are so low is because our US based team is completely virtual and we
do not have to pass on the costs of office expenses, commuting, and other expenses on to you. Additionally, most customers
do not require a full time optimization expert, and the costs incurred with hiring an in-house team of employees for
full stack optimization are extremely high and in some cases prohibitive.
What is MySQL Performance Optimization?
MySQL performance optimization is the process of analyzing your
existing system and modifying it as little as possible to substantially improve its responsiveness and reliability.
More sophisticated MySQL database optimization efforts could also involve making broader,
architectural modifications to your system which will eliminate current performance issues and prevent
upcoming issues that you will certainly encounter.
Do I need MySQL Database Optimization?
If you have never done any optimization in the past, and if you or your
developers have never worked on a website that received a significant amount of traffic, then you probably need
MySQL optimization services. MySQL optimization techniques are not as well known as beginner PHP or MySQL tutorials, and many of them
require substantially different approaches than traditional PHP/MySQL development that you may be already familiar with.
I have a small website and it's very slow. Why?
Most small websites are very slow because the software being executed
does not match the hardware that it is being executed on. For example, bloated open-source products such as Magento,
Wordpress, phpMyAdmin, Openx, and others are designed for ease-of-customization and deployment, not speed or efficiency.
In this case, the software requires more resources than the host(s) can allocate, and due to the fact that there
is only so much RAM/CPU's that you can add to a particular server, optimization of your existing resources
is required to maintain growth and lower operating expenses.
This is why there are many plugins for Wordpress and Magento optimization being offered by third party developers. These
plugins attempt to fix the symptoms of poorly optimized code by adding caching layer hacks or static file caches. If these
speed issues did not exist, additional systems such as Memcached (see FAQ on when you need Memcached below) would not have been popularized as much as they have been,
and third-party developers would not have spent considerable effort on attempting to accelerate very popular (and slow) open-source products.
In addition to hardware and software mismatch, your website may be hosted on a shared or virtual server on a poor quality host.
Occassionally, there are datacenters who fake BIOS memory and CPU counts, so if you think you are paying for an 8GB RAM server,
you may really be using a 512MB shared virtual server - instead of a dedicated one with RAID 10 running SAS drives.
However, if your software is developed internally and you have a small user base, and it's running on a server that has more than 1GB of RAM
and you are encountering problems, then your software is most likely not developed efficiently and you need MySQL optimization services.
How do I find out if my code or system is poorly written or architected?
If you are not currently experiencing performance issues, it may be difficult
to determine if your system will have problems in the future. One of the problems is that inexperienced developers
may not be intentionally providing you with bad quality software. Instead, it's what they think is the correct approach at this point
in their career. You can send us random code samples and we can give you a free analysis. You can also
ask your developers questions such as: How many SQL queries are we executing per page request? If the answer is more than 5 - 10, then
you will have problems; will our website work if MySQL goes down; do we escape our strings before inserting them into MySQL?; or
are we currently using indexes to speed up our queries?
Will you work for stock and/or options only?
As much as we would love to help every startup for free, we also
know that startups are extremely high risk investment vehicles and unless your startup is extremely unique, it is highly
unlikely that we will work entirely for stock (for free).
Do you do website design, CSS slicing, or client-side optimization?
We can help you with client-side optimization by helping you identify
website design services to any new customers until a working relationship has been established.
Do you do website development?
We do not offer website design or development services. However, we can help document,
architect, configure, and deploy an environment for your new service. We can also help interview potential development companies and
manage them on your behalf. If you need specific components created for your new or existing system (such as a complex search library, or
distributed graph database) then we can develop these modules for you, and assist a third party developer to integrate them into your system.
We're building a new system and we want to be ready to scale, can you help?
We've seen many live systems and have also helped develop new systems. We can certainly predict most of the bottlenecks and performance pain points on all layers. By engaging a professional optimization consultant early in the process, you can save yourself and your company a large amount of money, time, and emotional distress.
If I want to improve site speed and concurrency, not change my code, and not move servers, what should I do?
Upgrade to the latest stable MySQL release, switch to InnoDB, and use nginx with a php-fpm fastcgi backend.
Our site grew quicker than expected and our programmers are lost, can you help?
We can certainly help train you and your engineers and show them optimization techniques necessary
to scale your system.
We just found out that our programmers have been writing very bad code that violates most unspoken programming laws, can you help?
For every diligent and hard working programmer that asks for our help, there is unfortunately a non-technical (but business smart) client who was deceived by an individual (or a team) of programmers about their coding practices and techniques.
As much as we would like to say that we can help all customers, there have been rare cases that the code had to be entirely rewritten. The reason is because the time, cost, and effort associated with fixing what exists will far exceed redoing it properly from the beginning.
We will gladly help to evaluate your code as part of the performance audit process.
My site is growing and I want to add multiple web servers and load balance my front-end, what changes do I need to make, if any?
One of the first steps of scaling your system is adding multiple front-end web servers. Another common step is offloading all your static files (jpg, pdf, zip) to a dedicated static content server (nginx) or using a CDN from Amazon or Rackspace.
No matter which steps you take after static content offloading, a roadblock you will face is going to be session management and file storage. For example, most
programmers use PHP's $_SESSIONs to store a user's state. When moving from a poorly coded environment your choices are the following:
- Modify php.ini to stop storing $_SESSION in the /tmp folders directory as files. Instead, use shared, redundant, and replicated memcached sessions
- Put up an HTTP load balancer front-end such as HAproxy or NGINX. If you don't know how to enable memcached distributed php sessions, you can do the next best thing and use session affinity (stickiness) which will always route your end user to the same web server. This is not an ideal solution but better than having one server.
- Store all authenticated sessions in a centralized key/value store database and access via an internal authentication api
- Stop storing files in one folder. The folder will be unable to store more than 32,000 files on most file systems. Instead, try to distribute by user id range or upload to a pool of S3 buckets so that each bucket performs directory listings faster.
What's the cheapest and fastest way to increase or give the illusion of a speed increase?
You should offload all static files with a distant expiration date to a static content server and/or server behind a transparent caching proxy; enable the highest level of gzip compression inside your apache, nginx, or IIS configuration; move your DNS to a professional and high speed DNS hosting company such as Nettica.com; install and enable eAccelerator (for php); disable unnecessary PHP and Apache server modules; upgrade to the latest stable release of your language; use various benchmarking and front-end testing tools to boost your site speed; migrate to a more reputable web host or cloud provider that has a fast network and servers that run on good hardware (i.e. SAS/RAID10)
I use PHP/MySQL and/or .NET/MySQL on Windows, is there any hope for me?
Sure! Most system architectures and implementations remain unchanged regardless of which operating system or language you decide to use. You can still use open-source products such as HAproxy or NGINX to load balance your web and db servers, and you can use the same strategies in other areas as well. When optimizing your system always remember that a series of many small optimizations will eventually result in a significant performance increase, particularly if some processes were serial and blocking.
Do I really need memcached to make my system faster? Will memcached improve performance?
No. It really depends on your workload and the type of application you have.
If your system is extremely write heavy then memcached may be useless. In general, caching layers such as memcached add more complexity.
Many inexperienced developers try to use memcached as solutions to bad performance associated with poorly written queries and tables
which are not properly indexed. After you've fully squeezed out all that you can out of your mysql queries and server, and if you certainly
know that many of your transactions are read heavy, then you can offload certain procedures to use memcache.
One thing to keep in mind about
memcache is that it's not a persistent storage solution. If one or more memcache servers fail, any data in it will be lost - so make
sure that you have a copy stored in your mysql database. Likewise, it will take time to warm up your memcache again after a restart.
You can try to take advantage of memcached redundancy capabilities by setting up a memcached pool. If after all of this you decide to use memcached, you may experience significantly faster performance on read only system components that do not change frequently.
NoSQL is a big trend and buzzword, how can I use "it" to improve my system's speed and performance?
NoSQL is a philosophy or set of philosophies that has always existed
among optimization engineers. The theory is straightforward and as follows: Don't use MySQL if you don't absolutely need
a full powered RDBMS. Many questions arise about substitutes for using MySQL. The idea is not to eliminate MySQL
entirely from your stack, but rather to minimize access to it. This means making your application
more resilient and independent of MySQL.
Another popular trend today are schemaless databases. At this point
we would recommend staying away from most of these alpha / pre-beta software products whose performance
claims are highly inflated and are not based on real workload. If these products were indeed superior and worked as advertised,
we would certainly be recommending them to you in this answer, and they would have come into existance long ago (i.e. BDB). These
systems also work terribly with unique indexes and constraints.
What about Key/Value storage? This is another good example of a good
MySQL offloading technique. Depending on your system requirements you could try to offload your Key/Value storage to another
system (TokyoTyrant, TokyoCabinet, etc.) Again, most of these systems claim phenemonal performance, but if you fully evaluate
them with billions of Key/Value pairs and extremely high concurrency they do not work so well.
At this point there is a new competitor: Handlersocket for MySQL. We've deployed
Handlersocket using PHP and Perl for several clients, and the performance substantially exceeds Key/Value storage systems such as
Redis, Cassandra, or other non-relational databases. Using Handlersocket permits you to read or write from the InnoDB storage engine
directly at a high rate of speed not using SQL, and then using SQL when you need to access the table data in a familiar and
Searches in my system are very slow against my database, what do I do?
MySQL is not designed to be a full-text search engine. We recommend
offloading all search processes to a great application called SphinxSearch.
How do I measure the efficacy of my optimization efforts?
Unless your system is in a state-of-emergency you should deploy a system monitoring system. There's no point to pay for something like Cloudkick because you can get the same thing for free (for unlimited servers) with Nagios, Cacti, and many others. Amazon also has a monitoring service for their cloud servers as well. When measuring your optimization results, you should deploy one change at a time and then look for differences in the graphs and performance metrics of a particular server or system process. Over time, you should notice a decrease in the execution time of your system transactions and an overall decrease in system resource utilization. You can also
use experimental systems such as Pimba and monitor execution time at a macro level (the whole page), or granular level (down to a function or class).
My dedicated/managed server provider says I need a bigger server for my database, do I?
Most of the time the answer is no. If you were to engage a professional optimization consultant you could realize savings associated with not upgrading to a bigger server within month one. Many times programmers and engineers simply need to be pointed into the right direction with optimization and database fundamentals and they can significantly improve the performance of their application. One of the biggest problems facing programmers without large scale experience is that everything seems to work quickly and fine when they ran tests on their localhost or an empty database server, but then starts to slow down dramatically under a real environmental workload.
Also, certain things cannot be cured by bigger servers, even with lots of RAM and fast drives. By writing more efficient code, and optimized database queries, you can even downgrade to a cheaper server with better results. Recently after a several hour optimization session with a customer, he was able to move off of a huge dedicated database server for thousands of dollars a month, to a simple cloud server for under $100 a month with significantly faster results.
My server is using 75% or more of its CPU and RAM resources all the time, help!
If your server is using your CPU and RAM this may not be a sign of trouble. In fact, this may mean that your system is extremely well optimized and that you are squeezing every last drop out of your hardware – nice job! But how do you know if you are really utilizing your system to its maximum potential? Generally speaking, if your queries execute quickly, if you experience no downtime, and if you have a high number of transactions/queries per second, then you've done a good job. On the other hand, if you have very little users, very little queries, and a very small database (i.e. 50M rows or less) then you may need a performance audit.
Should I setup a MySQL replication slave as a backup solution for disaster recovery purposes?
MySQL replication slaves (or masters) are a great tool when used in the proper scenario. For example, setting up MySQL as a replication slave may not help you in a mysql related disaster when a recently fired sysadmin (or programmer) issues the DROP or TRUNCATE command on your master in a terminal session that he still has open at home. Both commands will be executed on the slave and your data will be gone. In less dramatic situations when there is a true disaster, your mysql replication slave may be affected as well. What are mysql replication slaves good for, since they are not a real backup solution?
MySQL slaves can be a great tool as an intermediary step to a backup solution. For example, you can perform mysql dumps off your slaves. Additionally, you can increase your system's read throughput by offloading reporting and analytics modules to run against slave databases. You can also parallelize queries (or dumps) across many replicated slaves to increase overall system throughput. Don't forget, slaves don't help scale write performance and replication lag will eventually occur!
My MySQL server is very slow what should I do before I contact you?
There are several things to consider when improving MySQL performance. Before you contact us, please
wait to see that this isn't just a temporary spike caused by a batch process, a hacker, or unusually high user activity.
For non-mysql experts, the easiest way to determine why your server is very slow is to use the process of elimination.
First, verify that your hardware is not in degraded mode and that the CPU/RAM/Disk usage is not extremely high. Verify that there are no other system processes running on the same server that are utilizing too many resources. Often times the MySQL daemon is functioning properly, but a heavily used Apache instance on the same machine is utilizing most of the resources.
Try to identify which parts of the system are slow and try to correlate these with ongoing system processes such as batch jobs, mysqldumps or imports, or too many transactions. Continue the elimination process by confirming that any write heavy tables are all using the INNODB storage engine.
Next, eliminate all non-critical system components piece by piece. You can easily disable extraneous audit logs and/or move them to another physical server.
Generally there are too many factors to consider when blindly diagnosing the speed of your MySQL server, however if you use a systematic process you can decrease your problem hunting time.
Here is a basic outline of the steps you can take:
- Eliminate all external non-mysql factors
- Eliminate all non-mandatory write or read heavy service such as logging
- Disable non-mandatory system component or modules
- Add and/or enable mysql slow query logging and queries not using indexes to the my.cnf file
- Set your server's interactive timeout to a low number like 5, to automatically kill any long running queries.
- Make sure all your tables use the InnoDB storage engine
- Make sure all your tables have indexes and multi-column indexes where they are needed (use EXPLAIN)
- Make sure that all your queries where clause sequence match up with those of the index
- Add tracing comments to all your mysql queries in your application using comment notation (i.e. SELECT a,b FROM c /* members_class::function login:: query 1 */
- Always close database connections with mysql_close() (or applicable command for your language).
- Repeat the process until satisfied with your improvements.
Should I put varnishd in front of my nginx setup?
No, nginx is much faster by itself.
Should I put squid in front of my nginx setup?
No, nginx is much faster by itself.