mysql performance

Use Keyword - Explain 

EXPLAIN SELECT * FROM tbl_name WHERE 1 = 0

The output from EXPLAIN is:
+------------------+
| Comment |
+------------------+
| Impossible WHERE |
+------------------+


Normally, EXPLAIN returns more information than that, including information about the indexes that will be used to
scan tables, the types of joins that will be used, and estimates of the number of rows that will need to be scanned
from each table.


Compare columns that have the same type
Try to make indexed columns stand alone in comparisons
1.
Declare columns to be NOT NULL. This gives you faster processing and requires less storage. It will also
simplify queries sometimes because you don't need to check for NULL as a special case.

2.Use PROCEDURE ANALYSE(). If you have MySQL 3.23 or newer, run PROCEDURE ANALYSE() to see what it
tells you about the columns in your table:
SELECT * FROM tbl_name PROCEDURE ANALYSE()
SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256)


3.Consider using ENUM columns. If you have a string column that contains only a limited number of distinct
values, consider converting it to an ENUM column. ENUM values can be processed quickly because they are
represented as numeric values internally

4.Use OPTIMIZE TABLE for tables that are subject to fragmentation. Tables that are modified a great deal,
particularly those that contain variable-length columns, are subject to fragmentation. Fragmentation is bad
because it leads to unused space in the disk blocks used to store your table. Over time, you must read more
blocks to get the valid rows, and performance is reduced. This is true for any table with variable-length
rows, but is particularly acute for BLOB columns because they can vary so much in size. Use of OPTIMIZE
TABLE on a regular basis helps keep performance on the table from degrading

5.·  Shorter SQL statements are faster than longer statements because they involve less parsing on the part of
the server and because they can be sent over the network from the client to the server more quickly.

6.Let MySQL insert default values for you; don't specify columns in INSERT statements that will be assigned
the default value anyway. On average, your statements will be shorter, reducing the number of characters
sent over the network to the server. In addition, because the statements contain fewer values, the server
does less parsing and value conversion.

Perform joins carefully

Joining data from two tables in one query is a very powerful technique, particularly when combined with normalisation. However, if not done right - even by a small margin - you can incur a serious speed hit. To get good performance from your joins, follow these simple rules:

• If two fields contain identical information in different tables, declare them with the same name and with the same type
  
• Filter the query as best as you can, otherwise you can get a very large number of results. For example, joining just three tables of 50 rows each will produce 125,000 records (50x50x50), as MySQL will return every combination of the rows.
  
• Remember that it is sometimes better to have a little data duplication in exchange for the chance to not have slow joins. If your goal is maximum speed, be prepared to break a few rules!
  
• Try to use numbers as opposed to strings when comparing rows in joins - the last thing you want is thousands of strings being compared.
  
• Avoid joining rows where you are comparing non-indexed fields
  

Spot slow queries

Delete Unwanted Logs Master Slave Log

PURGE BINARY LOGS BEFORE '2019-06-08 00:00:00';

folder /var/lib/mysql 

No Sleep Query Required

SELECT * FROM information_schema.processlist WHERE COMMAND LIKE 'query%' ORDER BY TIME DESC;

 

 

 

 Spot slow queries

Perhaps you didn't realise this, but simply by enabling one option in your MySQL configuration file, MySQL will keep a log of all the queries it considers slow (usually taking longer than ten seconds to execute), as well as how many rows the slow query returned.
To activate the slow query log, simply start up MySQL with the option --log-slow-queries = /var/log/wherever/you/want/it. You can optionally also use --log-long-format, which will cause MySQL to consider all queries that don't use indexes as slow queries - this can be a great help if you are trying to add indexes, but you are not sure where they are needed.

Select as little data as possible

1. Do nout use *
2. Use Limit keyword

Optimise your tables

Optimise your tables

The SQL statement "OPTIMIZE TABLE table " lets you specify a table (or several tables, with commas in between) that you would like MySQL to optimise. Notice the spelling is with a Z in the statement. By "optimise", I mean tables that have had a lot of changes made to them since they were created - lots of rows added/deleted/edited, etc; especially if the table uses varying length fields like VARCHARs and TEXTs. OPTIMIZE TABLE instructs MySQL to basically defragment the table to make it faster to read and write from.

Avoid mod_access if you can

Apache has a special module, known as mod_access, that allows you to place files called .htaccess in directories and have them specify authentication information required for that directory. While this is a nice and easy way to solve the issue of stopping unauthorised users access places they shouldn't, it's not very fast.
Mod_access works by looking for a .htaccess file in the directory requested by the user. If it finds it, it loads it and uses it. If not, it goes to the parent directory and looks there. If there's nothing there, it goes to the parent directory of the parent directory, then to the parent of the parent of the parent, etc, until it can go no further. If you don't actually use .htaccess files, all this checking is pointless - and it needs to be done every time a page is requested on your server.
The best way to get around this problem is just to disable mod_access outright, but if you simply have to have it then the only thing I can recommend is that you don't have too many subdirectories - if you have a maximum of two subdirectories, there are only two checks per request, but if you have six subdirectories (such as /html/newsite/images/gnome/highquality/zipped) then the workload is tripled!

Compress your output

The solution is to enable output buffering, and to use gzip compression for the buffers. Output buffering, if you were unaware, makes PHP store up its data into one big chunk, then send it to Apache all at once. Because all the data is kept together and sent all at once, PHP is able to compress it using gzip compression, which will generally reduce the content to around 33% of its original size (that is, 1MB will become 300KB).

garbage collection

use unset(), mysql_free_result(),

php performance Pre-increment where possible

Take advantage of the fact that PHP allows you to post-increment ($i++) and pre-increment (++$i). The meaning is the same as long as you are not writing anything like $j = $i++, however pre-incrementing is almost 10% faster,

Php performance loops

Get your loops right first

 A good rule of thumb to keep in mind is "90% of your scripts execution time is taken up in 10% of the code" 

 

for ($i = 1; $i < count($myarr); ++$i) {
    // }

 

Never right Such type of code . It can be make site very slow.

 

 

 

php performance

Hoare's law tells us "inside every large program is a small program struggling to get out", so you should consider chopping out blocks of code that are outdated, outmoded, replaced, or irrelevant.

When dealing with performance benchmarking, however, you need to be wary - it has been said that "premature optimisation is the root of all evil".


1     Banchmarking
2.    Jmeter
3     code caching
4.    php-performace-with-memecache
5.    php-performance-caching
6.    loops
7.    use preincrement operation
8.   Listen to all errors, big and small
9.    Keep upto date your php version
10  compress-your-output
11. Use persistent connections
12. avoid-modaccess
13 . Optimising your SQL
14 . Optimise your tables
15 .Select as little data as possible
16 Load data intelligently
17 . Use innobdb
18.  Spot slow queries
19.perform-joins-carefully.html
20 Index your data
21 .Increase your buffers
22. PHP Accelerators
23 .advantages of UPDATE LOW_PRIORITY and INSERT DELAYED INTO

php performance Profiling

ProfilingSo you’ve done all the caching and query optimizations, and removed all
the system bottlenecks, but your code is still running too slow. Now you have to
face the music and admit that, actually, your code isn’t perfect and could be im-
226 PHP Master: Write Cutting-edge Code
proved. But you already did the best you could … so, now what? This is where
profiling comes in.
is the act of taking accurate time and/or memory measurements for every action
your code performs. This is then explored to determine where the bottlenecks lie.


There are two tools for profiling that are commonly used:

1. Xdebug
2. XHProf -XHGui

php performance caching

Caching


1. APC
2. Memcache

php performace with memecache

Another setting that you can tweak for optimization is to use a different storage
mechanism for session data; in this case memcached. Memcached is a memorybased,
cluster-friendly key-value store. If you enable the memcache extension
(ext/memcache), you’ll be able to automatically use memcached for session storage
instead of the disk:


Install
$ pecl install memcache # Install ext/memcache
$ memcached –d –m 128 # Start memcached

Settings

session.save_handler = "memcache"
session.save_path = "tcp://localhost:11211"Table 6.1. Performance Figures with and without Memcached

StorageType               AverageResponse  MinimumResponse Maximum Response  Requests per Second
File-based                    836                             98                       7106                        23
MySQL-based             798                            103                      1848                          24
Memcached-based        771                            86                        1473                        25



Memcached is a networked daemon that can easily be spread across multiple servers.
In this case, multiple web servers can use it as a central store for their sessions. This
makes load balancing much easier; all sessions can easily be accessed from all web
servers in a cluster, without the overhead of a central RDBMS (relational database
management system).
As the number of sessions grows, memcached will scale far better.

Php performance code caching

The first item we’re going to cover is opcode caches. You’ve probably heard since
your earliest days as a PHP developer that PHP is a scripting language, an interpreted language, that no compiling is required … well, this isn’t exactly true. Stick with
us here.

So, how do you install this magic? It’s simple:

$ pecl install apc
extension=apc.so

Now Again compare with ab benchmarking technique
Now see difference and you will be most happiest person of world.

PHP Performace Jmeter

JMeter is another Apache project with a GUI, and more capability.With JMeter, you
create a test plan, add thread groups (for example, X number of threads performing
N number of requests each), add samplers (such as performing an HTTP request),
specify their configuration, add other options like a cookie handler, and add
listeners to handle the results.

php performance Apache benchmark

Apache benchmark


ApacheBench is super simple and typically included with your Apache install, or
as part of the Apache development package—the binary is called simply ab. To use
ab, just specify the total number of requests (-n), and the number of simultaneous
threads (-c), and let it go to work. For example, here we are using –n 10000 –c 40
to create 40 simultaneous threads to perform 1,000 requests:


$ ab -n 10000 -c 40 http://www.test.com/

Connect: 1 6 4.8 4 30
Processing: 62 233 49.6 229 708
Waiting: 62 231 50.1 227 705
Total: 63 239 49.5 235 714

The Connection Times section is very interesting, as it comprises four different
numbers:
Connection: how long it takes the web server to open a connection
Processing: how long the request takes, from the time of connection to the end
of the request
Waiting: how long it takes Apache to process the request and send the full
response
Total: how long the request takes from start to finish

comment assist eclipse